Advertisement:

Author Topic: mysql replace illegal mix of collations.  (Read 9998 times)

Offline Sincere

  • Full Member
  • ***
  • Posts: 631
mysql replace illegal mix of collations.
« on: November 11, 2009, 01:05:49 PM »
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:

 
Code: [Select]
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 ?

« Last Edit: November 11, 2009, 01:25:03 PM by Sincere »
Do you need a special mod for your smf forums? contact me for a quote.

Offline Norv

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 18,313
  • Blue Wolf
Re: mysql replace illegal mix of collations.
« Reply #1 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).
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

Offline Sincere

  • Full Member
  • ***
  • Posts: 631
Re: mysql replace illegal mix of collations.
« Reply #2 on: November 11, 2009, 03:06:45 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.

Offline Norv

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 18,313
  • Blue Wolf
Re: mysql replace illegal mix of collations.
« Reply #3 on: November 11, 2009, 03:11:26 PM »
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