Illegal mix of collations (I am Noob Please help)

[xtone]

Hi i've restored my DB 3 days b4 and got this error when any1 tries to register on my Datalife Engine 9.3 site !

MySQL Error!
------------------------

The Error returned was:
Illegal mix of collations (cp1251_general_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation 'regexp'

Error Number:
1267

SELECT COUNT(*) as count FROM dle_users WHERE email = '[email protected]' OR LOWER(name) REGEXP '[[:<:]][a?][s5][s5]f[[:>:]]' OR name = 'assf'

i think its a collation of two languages need help right away please !
i dont wanna lose my DB as i've worked very hard for it so can any1 help me here ?

Note The previous version was 9.2 which i backedup and restored to 9.3 version

i am not a pro and on a shared hosting so i can edit and run queries if you want me to through phpmyadmin !

[Old Pedant]

You can force one collation to match the other easily, without changing any data in the table(s).

I *BELIEVE* that message is telling you that the string you used in the REGEXP '[[:<:]][a?][s5][s5]f[[:>:]]' is UTF8.

And then I *think* it is saying LOWER(name) is CP1251 (presumably because name in the DB is CP1251).
Code page 1251 is Windows Cyrillic...does that match what you are using in that table?

So you can simply coerce name to UTF8.

http://dev.mysql.com/doc/refman/5.5/en/charset-result.html

http://dev.mysql.com/doc/refman/5.5/en/charset-convert.html

... OR CONVERT( LOWER(name) USING UTF8 ) REGEXP '[[:<:]][a?][s5][s5]f[[:>:]]' ...
or (not sure which is better)
... OR LOWER( CONVERT(name USING UTF8 ) ) REGEXP '[[:<:]][a?][s5][s5]f[[:>:]]' ...

I could have the two backwards, of course (the expression being CP1251 and name being UTF8).

If you want to find out for sure what charset/collation name is, do this:

SELECT CHARSET(name) FROM dle_users LIMIT 1

Or, if you might have more than one CHARSET in use in that one column,

SELECT DISTINCT CHARSET(name) dle_users

[Old Pedant]

I suppose if you wanted to be absolutely sure you could do

... OR LOWER( CONVERT(name USING UTF8 ) ) REGEXP CONVERT('[[:<:]][a?][s5][s5]f[[:>:]]' USING UTF8) ...

You should be able to convert any string to UTF8, so that *should* be safe.

Sorry I've never actually encountered this before, but I did have to convert an old ASCII MySQL table to UTF8 and we did it using something similar to this (converting all the data in one go).

[xtone]

em sorry but i really dont understand the MYSQL language much so can u please guide me more deeply or in easy words what i can do to prevent this ?

i am on shared hosting and it has phpmyadmin installed in it so what i can do here guide me step by step please.

[Old Pedant]

Well, I *thought* I did.

First thing to try is to change your code where it does

...OR LOWER(name) REGEXP '[[:<:]][a?][s5][s5]f[[:>:]]' OR...

to

... OR LOWER( CONVERT(name USING UTF8 ) ) REGEXP CONVERT('[[:<:]][a?][s5][s5]f[[:>:]]' USING UTF8) ...

If that doesn't work, then let's try something else.

[xtone]

where can i find this code ?

[Old Pedant]

Look in your first post!

That's where I got it from.

Wherever that line of SQL code was that you posted, find it and change it.

I can't see your source code when you don't post it.

[xtone]

i see many tables in my phpmyadmin it does not have any table called regxp !!!

[xtone]

bro i see this now from here can u guide me please ?

[xtone]

thankx bro for the help and guidance i got it i changed the dle_users CP1251 to dle_users utf8_general_ci and it worked

[Old Pedant]

Is there a reason that you don't make *ALL* the tables UTF8??

But *PLEASE* make sure you do a 100% backup of your database before making changes like that to entire tables! It *SHOULD* be safe, but you should never take such a chance.

[xtone]

not a particular reason for that but em concious if anything goes wrong i dont wanna lose my DB and yeah i'll try that but first i'll backup my ad then i'll tell you what happened next.