Illegal mix of collations
Sharique - April 30, 2007 - 14:42
I have upgraded a website from when I try to login I'm getting following error.
user warning: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '=' query: user_is_blocked SELECT name FROM users WHERE status = 0 AND name = LOWER('user1') in /www/mysite/includes/database.mysql.inc on line 172.
user warning: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '=' query: user_load SELECT * FROM users u WHERE LOWER(name) = LOWER('user1') AND pass = '7b063a8b8aa2219449cb35f4e415295f' AND status = 1 in /www/mysite/includes/database.mysql.inc on line 172.
Sorry, unrecognized username or password. Have you forgotten your password?
Same website is working
Same website is working perfectly o my local machine.
------
Sharique uddin Ahmed Farooqui
IT head, Managefolio.com
I have the same problem
I have the same problem on a site when a hosting company decided to move servers on me. I look like for some reason when the new database was created on the new server it got a ton of it's tables filled with the same latin_swedish collation issue.
If you have PHPmyAdmin you can go into your DB and manually change the collation on the "user" table fields to utf8_general_ci and you'll be able to log in at least. It's as far as I've gotten with the issue. I have not been able to figure out a way to alter all the fields that need it in an easy way.
Once I was able to log in, none of my posts inline images show up, I'm not sure if that's a collation issue or something else emtirley. If you could let me know if you have the same issue once you get logged in, that would be helpful.
We are also experiencing
We are also experiencing same issue. We still have to find solution of this issue.
-----
Sharique uddin Ahmed Farooqui
IT head, Managefolio.com
Conversion script
Can be found at:
http://alecweb.ulyssis.org/ConvertDrupal
Any sort of "ALTER CHARACTER SET" through SQL itself will result in corrupted data. The only way to fix is to export and reimport while preserving the data, but changing the "character set" statements.
--
If you have a problem, please search before posting a question.
Easier and more complete
You might want to try these steps:
http://www.mostrey.be/drupal_character_encoding_mysql
One more fix...
I've noticed that others fixed the problem using the following article.
http://www.mostrey.be/drupal_character_encoding_mysql
I tried that one, and it sure enough did a great job almost. The tables listed in the fix sure enough were fixed, and it does fix the data. However, what about all the other tables in your database?
I didn't want to leave them with the swedish collation, so I did some digging around on the internet and found a script which i modified slightly. It returns a list of SQL Queries that can be run on the database to change the collations to whatever you want. I simply copied and pasted them into my PHPMyAdmin for the database to be changed. I share it here with all the cautions that I can give. I can't be responsible for any loss of data or any damage to your installation. That being said, this script does not make any changes to your databases.
Copy the following into a file and place it on your web server. Next point your browser to the the file on your website.
Good luck.
-webservant
P.S. This worked on MySQL version 4.1.22. I have not tried it on any newer versions of MySQL.
<?php
// this script will output the queries need to change all fields/tables to a different collation
// it is HIGHLY suggested you take a MySQL dump prior to running any of the generated
// this code is provided as is and without any warranty
// Code provided by <a href="http://www.phpwact.org/php/i18n/utf-8/mysql
//" title="http://www.phpwact.org/php/i18n/utf-8/mysql
//" rel="nofollow">http://www.phpwact.org/php/i18n/utf-8/mysql
//</a> Minor modifications made by webservant
die("Make a backup of your MySQL database then comment out this line");
set_time_limit(0);
// collation you want to change:
$convert_from = 'latin1_swedish_ci';
// collation you want to change it to:
$convert_to = 'utf8_general_ci';
// character set of new collation:
$character_set= 'utf8';
$show_alter_table = true;
$show_alter_field = true;
$show_alter_database = true;
// DB login information
$username = 'username';
$password = 'password';
$database = 'database';
$host = 'localhost';
mysql_connect($host, $username, $password);
mysql_select_db($database);
$rs_tables = mysql_query(" SHOW TABLES ") or die(mysql_error());
print '<pre>';
// Alter database collation
if ($show_alter_database) {
echo("ALTER DATABASE `$database` DEFAULT CHARACTER SET $character_set COLLATE $convert_to;\r\n");
}
while ($row_tables = mysql_fetch_row($rs_tables)) {
$table = mysql_real_escape_string($row_tables[0]);
// Alter table collation
if ($show_alter_table) {
echo("ALTER TABLE `$table` DEFAULT CHARACTER SET $character_set COLLATE $convert_to;\r\n");
}
$rs = mysql_query(" SHOW FULL FIELDS FROM `$table` ") or die(mysql_error());
while ($row=mysql_fetch_assoc($rs)) {
if ($row['Collation']!=$convert_from)
continue;
// Is the field allowed to be null?
if ($row['Null']=='YES') {
$nullable = ' NULL ';
} else {
$nullable = ' NOT NULL';
}
// Does the field default to null, a string, or nothing?
if ($row['Default']==NULL) {
$default = " DEFAULT NULL";
} else if ($row['Default']!='') {
$default = " DEFAULT '".mysql_real_escape_string($row['Default'])."'";
} else {
$default = '';
}
// Alter field collation:
if ($show_alter_field) {
$field = mysql_real_escape_string($row['Field']);
echo "ALTER TABLE `$table` CHANGE `$field` `$field` $row[Type] CHARACTER SET $character_set COLLATE $convert_to $nullable; \r\n";
}
}
}
?>
add them to the array
Webservant, you can just add all extra tablenames in the array: convert_to_utf8(array( ..
seems to work for me! At
seems to work for me! At least, the site didnt die, and i dont get immediate errors in my watchdog. Ive DP 5.10
defining the objectives
it appears that you have to set all tables AND table columns to utf8_general_ci. i am not entirely sure on this but if you are able to duplicate your server environment, you can test this theory.
i encountered a similar problem when i was interfacing with custom built tables inside drupal's database.
what i did was to set every table's default character set and every columns therein to utf8_general_ci. this appears to be the fundamental objective when solving this issue. this can be done via your mysql admin interface or phpmyadmin.
I'm having the grief
Hi,
I'm having the same error only slightly different.
user warning: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '=' query: SELECT t.* FROM term_node r INNER JOIN term_data t ON r.tid = t.tid INNER JOIN vocabulary v ON t.vid = v.vid LEFT JOIN forum_access fa ON t.tid = fa.tid LEFT JOIN acl acl ON acl.name = t.tid AND acl.module = 'forum_access' LEFT JOIN acl_user aclu ON aclu.acl_id = acl.acl_id AND aclu.uid = 0 WHERE ((fa.grant_view >= 1 AND fa.rid IN (1)) OR fa.tid IS NULL OR aclu.uid = 0) AND ( r.nid = 3 ) ORDER BY v.weight, t.weight, t.name in mysite\httpdocs\includes\database.mysql.inc on line 172.
I have changed some tables manually to utf8_general_ci in phpmyadmin so there were no more Latin tables shown. I still got the error so I've run the phpscript on this thread but no change... what am I missing.
It looks to me to have something to do with forum access and this message only shows on my site when the login block is shown. can I remove the forum module and put it back again. I have done a lot of work on this site so obviously don't want to have to start again.
thanks in advance
Check fields
Check fields, text field might have latin collations .
----
Sharique uddin Ahmed Farooqui
Web Developer
http://it.Managefolio.com