Textpattern CMS

Phil_

Member

Registered: 2009-09-21

Posts: 31

Database schema error

I have a bit of a tale of woe. I took a backup of my database to move to another server. The app used suffered from a phpmyadmin bug that corrupted the database but I didn’t realise until it was too late. I’ve fixed most of the problems but I have one that has me stumped.

Symptom: When I click on the “Plugin” tab I get a popup message saying
“Internal error “Illegal mix of collations (utf8_general_ci,COERCIBLE) and (latin1_general_ci,IMPLICIT) for operation ‘strcmp’

If I click “OK” I get this message from Textpattern:
I’m sorry. I’m afraid I can’t do that; plugin is an unsafe operation.

It looks like when I have installed the backup on the new server I have possibly, inadvertently used the wrong table schema. I can’t find anywhere what the correct schema is or how to correct the error in a non destructive way.

Having gone through the mess that was created by the backup being corrupted I am very wary about doing anything that will stop my site working.

Any help or advice will be gratefully received.

Phil

Phil_

Member

Registered: 2009-09-21

Posts: 31

Re: Database schema error

FIXED IT
=
I just ran:

ALTER TABLE txp_plugin CONVERT TO CHARACTER SET utf8

and it all works fine.

I might backup other tables and try that.

Last edited by Phil_ (2017-06-15 13:19:25)

Phil_

Member

Registered: 2009-09-21

Posts: 31

Re: Database schema error

FOLLOW UP
===

Can anyone confirm if utf8 is correct collation to use for Textpattern Mysql tables please.

Many thanks

Phil

colak

Admin

From: Cyprus

Registered: 2004-11-20

Posts: 7,050

Website

Re: Database schema error

As far as I know, utf-8 is the correct character set

---

Yiannis
——————————
neme.org | hblack.net | LABS | State Machines | NeMe @ github

Dragondz

Moderator

From: Algérie

Registered: 2005-06-12

Posts: 1,300

Website

Re: Database schema error

Hi

I think it s utf8_general_ci

---

Conception site web Algerie

philwareham

Core designer

From: Farnham, Surrey, UK

Registered: 2009-06-11

Posts: 3,112

Website

Re: Database schema error

Dragondz wrote #305981:

Hi

I think it s utf8_general_ci

I have previously also used utf8_unicode_ci, utf8mb4_general_ci and utf8mb4_unicode_ci without any problems. utf8mb4 allows for additional symbols in your data such as emoticons.

Phil_

Member

Registered: 2009-09-21

Posts: 31

Re: Database schema error

Many thanks.

I have converted more of the tables using:

ALTER TABLE txp_##insert_table_sufix## CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

With no ill effects.

Best wishes

Phil