News:

Wondering if this will always be free?  See why free is better.

Main Menu

mysql replace illegal mix of collations.

Started by Sincere, November 11, 2009, 01:05:49 PM

Previous topic - Next topic

Sincere

hello. I converted successfully from vb 3.8.4 to smf1.1.10 (yes using the vb3.7 converter).

I am quite happy with the outcome so far.

I need to test things further.

but one thing comes out clear:

all the links on my previous forums are not now any more valid.

in order to fix the links in all posts, I, a non-mysql coder, tried this:

SET @BEFORE='/forum/showthread.php?t=';
SET @AFTER='/leforum/index.php?topic=';
UPDATE smf_messages SET body =REPLACE(body,@BEFORE,@AFTER)



to my surprise this is giving me the error :


UPDATE smf_messages SET body = REPLACE( body, @BEFORE , @AFTER ) ;

MySQL said: Documentation
#1270 - Illegal mix of collations (utf8_general_ci,IMPLICIT), (utf8_unicode_ci,IMPLICIT), (utf8_unicode_ci,IMPLICIT) for operation 'replace'

I even tried to import this code in a text file...... same result.

server settings:
Apache v2.2.13 (cgi)     
PHP    5.2.6

Any help there ?

Do you need a special mod for your smf forums? contact me for a quote.

Norv

Can you please tell:
- the collation of the row 'body' in smf_messages, is "utf8_general_ci"?
- do you do that in phpMyAdmin ? If yes, please try to go to the page with server variables, and eventually make a screenshot or copy of the first page it displays (there are many variables but the first ones may be of interest).
To-do lists are for deferral. The more things you write down the later they're done... until you have 100s of lists of things you don't do.

File a security report | Developers' Blog | Bug Tracker


Also known as Norv on D* | Norv N. on G+ | Norv on Github

Sincere

Quote from: Norv on November 11, 2009, 03:01:24 PM
Can you please tell:
- the collation of the row 'body' in smf_messages, is "utf8_general_ci"?
- do you do that in phpMyAdmin ? If yes, please try to go to the page with server variables, and eventually make a screenshot or copy of the first page it displays (there are many variables but the first ones may be of interest).

thank you for your kind reply.

the collation of the body column (not row) is UTF8
the collation in phpmyadmin and charset were also UTF8

I simply changed this collation in phpmyadmin homepage to latin1_general_ci , and then I could like a charm execute my command....

then I set back the collation to UTF8 again...

thank you for your help.

Do you need a special mod for your smf forums? contact me for a quote.

Norv

Cheers!
Then perhaps setting it to "utf8-general-ci" would work just fine and you don't need to undo it. (it must have been "utf8-unicode-ci" before)
To-do lists are for deferral. The more things you write down the later they're done... until you have 100s of lists of things you don't do.

File a security report | Developers' Blog | Bug Tracker


Also known as Norv on D* | Norv N. on G+ | Norv on Github

Advertisement: