change in mysql version?

[k0r54]

Hi,

All of a sudden today a clients website stopped allowing her to log into her admin panel. Its a very simple loging i use this

SELECT * FROM login WHERE Username = '$_POST[Username]' AND Password = password('$_POST[Password]')


Now, i copy and paste this into the servers phpmyadmin sql bit and it doesn't work (obviously i change the post for real data).

I get this error message: -

#1267 - Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='

Im still waiting for a reply from the hosting service to see if they have changes version at all!


Thanks
k0r54

[k0r54]

hi,

Any ideas, i have tried messing about but still nothing :(

Thanks
k0r54

[AdamBrill]

Well, no guarantees, but you could try changing your query to this:

$sql = "SELECT * FROM `login` WHERE `Username` = '".$_POST[Username]."' AND `Password` = password('".$_POST[Password]."');";

That is closer to what the query should have looke like in the first place.

[k0r54]

Hi, no i have tried that.

I have noticed that my phpmyadmin now has an attribute called collation on each field. And the char and varchar fields are filled with

utf8_general_ci

Thanks
k0r54

[AdamBrill]

Is MySQL working at all right now? Have you tried even more simple queries to test it such as:

SELECT * FROM `login` WHERE Username = 'someusername';

Or just:

SELECT * FROM `login` WHERE 1;

Do those work?

[k0r54]

yes they work fine??

I have tested both queries to check before :confused:

[AdamBrill]

It is most likely that the problem is because of an upgrade, probably to MySQL 4.1. What you need to do is change the collation where it is latin1_swedish_ci to utf8_general_ci. I would try something like this:

ALTER TABLE `login` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

Good luck! :)

[k0r54]

sorry to say :(

I have done that to, it is set to that now.

[AdamBrill]

BTW, you may want to backup the database before you try this, just to be on the safe side. It's always a good idea to back it up before you change major settings like this...

[AdamBrill]

If it wouldn't be a major pain to do, try deleting the table and re-creating it. Use this syntax:

CREATE TABLE `login` ( ... ) DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

You can backup the data before you delete it so you can restore it afterward...

[k0r54]

Yes that works thank you :)

Ok but what exactly is the collation?

Thanks
k0r54

[AdamBrill]

The collation is used for comparing characters in a character set. If you want to learn more about it, you could look at this site: http://www.informit.com/articles/article.asp?p=328641 It's not a great site on it, but at least it gives you some info.