Textpattern CMS

#1 2016-10-20 17:43:16

mrdale

Member

From: Walla Walla

Registered: 2004-11-19

Posts: 2,215

Website

Illegal mix of collations

Sometimes when performing queries directly in the TXP database.

for example
bc.. set from="some text";set to=“some other text”;
UPDATE `textpattern` SET `Title`=replace(`Title`, from, to);

recently on late TXP builds I get the following error

Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT), (utf8mb4_general_ci,IMPLICIT), (utf8mb4_general_ci,IMPLICIT) for operation 'replace'

1. Did something change in recent revs of textpattern that relates to the tables collation?
2. is it safe for me to simply change all the tables to utf8mb4_unicode_ci?

The following script will do it… but is it wise?

#Convert the database
ALTER DATABASE mrdale_wow_stage CHARACTER SET utf8 COLLATE utf8_unicode_ci;
# Convert all the tables
ALTER TABLE textpattern CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE txp_category CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE txp_css CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE txp_discuss CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE txp_discuss_nonce CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE txp_file CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE txp_form CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE txp_image CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE txp_lang CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE txp_link CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE txp_log CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE txp_page CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE txp_plugin CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE txp_prefs CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE txp_section CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE txp_token CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE txp_users CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Update… I still get the error.