Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 12 of 12
  1. #1
    New to the CF scene
    Join Date
    Aug 2011
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Illegal mix of collations (I am Noob Please help)

    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 = 'asdfasd@asdfsdf.com' 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 !

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,436
    Thanks
    75
    Thanked 4,372 Times in 4,337 Posts
    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/...et-result.html
    http://dev.mysql.com/doc/refman/5.5/...t-convert.html

    Code:
    ... 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:
    Code:
    SELECT CHARSET(name) FROM dle_users LIMIT 1
    Or, if you might have more than one CHARSET in use in that one column,
    Code:
    SELECT DISTINCT CHARSET(name) dle_users
    Last edited by Old Pedant; 08-30-2011 at 02:31 AM.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,436
    Thanks
    75
    Thanked 4,372 Times in 4,337 Posts
    I suppose if you wanted to be absolutely sure you could do
    Code:
    ... 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).
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #4
    New to the CF scene
    Join Date
    Aug 2011
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,436
    Thanks
    75
    Thanked 4,372 Times in 4,337 Posts
    Well, I *thought* I did.

    First thing to try is to change your code where it does
    Code:
    ...OR LOWER(name) REGEXP '[[:<:]][a?][s5][s5]f[[:>:]]' OR...
    to
    Code:
    ... 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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #6
    New to the CF scene
    Join Date
    Aug 2011
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts
    where can i find this code ?

  • #7
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,436
    Thanks
    75
    Thanked 4,372 Times in 4,337 Posts
    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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #8
    New to the CF scene
    Join Date
    Aug 2011
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts
    i see many tables in my phpmyadmin it does not have any table called regxp !!!

  • #9
    New to the CF scene
    Join Date
    Aug 2011
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts
    bro i see this now from here can u guide me please ?

  • #10
    New to the CF scene
    Join Date
    Aug 2011
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  • #11
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,436
    Thanks
    75
    Thanked 4,372 Times in 4,337 Posts
    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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #12
    New to the CF scene
    Join Date
    Aug 2011
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •