Textpattern CMS

#1 2017-06-15 11:47:50

Phil_

Member

Registered: 2009-09-21

Posts: 37

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

#2 2017-06-15 12:06:21

Phil_

Member

Registered: 2009-09-21

Posts: 37

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)

#3 2017-06-15 12:07:22

Phil_

Member

Registered: 2009-09-21

Posts: 37

Re: Database schema error

FOLLOW UP
===

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

Many thanks

Phil

#4 2017-06-15 13:16:33

colak

Admin

From: Cyprus

Registered: 2004-11-20

Posts: 9,172

Website GitHub Mastodon Twitter

Re: Database schema error

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

---

Yiannis
——————————
NeMe | hblack.art | EMAP | A Sea change | Toolkit of Care
I do my best editing after I click on the submit button.

#5 2017-06-15 13:26:57

Dragondz

Moderator

From: Algérie

Registered: 2005-06-12

Posts: 1,548

Website GitHub Twitter

Re: Database schema error

Hi

I think it s utf8_general_ci

---

Conception site web Algerie | Livre Algerie | Construction Piscine Algerie

#6 2017-06-15 13:42:21

philwareham

Core designer

From: Haslemere, Surrey, UK

Registered: 2009-06-11

Posts: 3,564

Website GitHub Mastodon

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.

#7 2017-06-15 13:54:15

Phil_

Member

Registered: 2009-09-21

Posts: 37

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