Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE)
I have upgraded a website from drupal 4.7.4 to drupal 5.1 (I have also moved
website from one server to another), now 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?
I have also posted this in drupal forum http://drupal.org/node/140303.
What I've done is I have exported data from old db via phpMyAdmin and
imported into new database.
Since it related to mysql so i'm posting here.
--
Sharique uddin Ahmed Farooqui
(C++/C# Developer, IT Consultant)
http://www.managefolio.net/news/
A revolution is about to begin.
A world is about to change.
And you and me are the initiator.
Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) and Replication
Hello all, I'm having a very peculiar and nerve racking experience with MySQL 4.1.7 running on Fedora Core 2. On the master the query executes just fine, however on the slave I get: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='' on query. Default database: 'BRITEERNO'. Query: 'Update contacts set firstname = '',lastname = '',email = '' ,company = '',address1 = '',address2 = '' ,address3 = '',city = '',state = 'XX' ,zip = '', country = 'XX',phone = '' ,fax = '',user = '',pass = '',changed_on = now() ,status = 'X' where RNK_id = ''' I'm completely and uterly stumped by this, I have no idea of where to go from here. Any help will be greatly appreciated. Thank, Michael Grubb 000domains.com Below is output from my master and slave show variables command. Show variables on the master: character_set_client latin1 character_set_connection latin1 character_set_database latin1 character_set_results latin1 character_set_server latin1 character_set_system utf8 character_sets_dir /usr/share/mysql/charsets/ collation_connection latin1_swedish_ci collation_database latin1_swedish_ci collation_server latin1_swedish_ci Show variables on the slave: character_set_client latin1 character_set_connection latin1 character_set_database latin1 character_set_results latin1 character_set_server latin1 character_set_system utf8 character_sets_dir /usr/share/mysql/charsets/ collation_connection latin1_swedish_ci collation_database latin1_swedish_ci collation_server latin1_swedish_ci signature.asc Description: PGP signature signature.asc Description: OpenPGP digital signature
RE: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) and Replication
Michael, Normally I would let a fellow domain registrar fend for themselves, but I'm feeling nice today. :) Do a show create table contacts and see what the charset is set too. I bet the character set on the slave is different. Are you running this from the command line? Or are you getting this error in the error_log? Donny -Original Message- From: Michael Grubb [mailto:[EMAIL PROTECTED] Sent: Monday, November 15, 2004 2:49 PM To: [EMAIL PROTECTED] Subject: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) and Replication Hello all, I'm having a very peculiar and nerve racking experience with MySQL 4.1.7 running on Fedora Core 2. On the master the query executes just fine, however on the slave I get: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='' on query. Default database: 'BRITEERNO'. Query: 'Update contacts set firstname = '',lastname = '',email = '' ,company = '',address1 = '',address2 = '' ,address3 = '',city = '',state = 'XX' ,zip = '', country = 'XX',phone = '' ,fax = '',user = '',pass = '',changed_on = now() ,status = 'X' where RNK_id = ''' I'm completely and uterly stumped by this, I have no idea of where to go from here. Any help will be greatly appreciated. Thank, Michael Grubb 000domains.com Below is output from my master and slave show variables command. Show variables on the master: character_set_client latin1 character_set_connection latin1 character_set_databaselatin1 character_set_results latin1 character_set_server latin1 character_set_systemutf8 character_sets_dir /usr/share/mysql/charsets/ collation_connection latin1_swedish_ci collation_database latin1_swedish_ci collation_server latin1_swedish_ci Show variables on the slave: character_set_client latin1 character_set_connectionlatin1 character_set_database latin1 character_set_results latin1 character_set_server latin1 character_set_system utf8 character_sets_dir /usr/share/mysql/charsets/ collation_connection latin1_swedish_ci collation_database latin1_swedish_ci collation_server latin1_swedish_ci -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) and Replication
Donny,
I certainly appreciate your help.
"show create table contacts" shows the same output on both the master
and the slave ('latin1').
This is being run by the slave's SQL thread, and the Error is shown in
the "Last error" line of
"show slave status" and the error log.
Thanks again,
Michael
Donny Simonton wrote:
Michael,
Normally I would let a fellow domain registrar fend for themselves, but I'm
feeling nice today. :)
Do a "show create table contacts" and see what the charset is set too.
I bet the character set on the slave is different. Are you running this
from the command line?
Or are you getting this error in the error_log?
Donny
-Original Message-
From: Michael Grubb [mailto:[EMAIL PROTECTED]]
Sent: Monday, November 15, 2004 2:49 PM
To: [EMAIL PROTECTED]
Subject: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and
(utf8_general_ci,COERCIBLE) and Replication
Hello all,
I'm having a very peculiar and nerve racking experience with MySQL 4.1.7
running on Fedora Core 2.
On the master the query executes just fine, however on the slave I get:
Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and
(utf8_general_ci,COERCIBLE) for operation '='' on query. Default
database: 'BRITEERNO'. Query: 'Update contacts
set firstname = '',lastname = '',email =
'' ,company = '',address1 = '',address2 = ''
,address3 = '',city = '',state =
'XX' ,zip = '', country = 'XX',phone = ''
,fax = '',user = '',pass =
'',changed_on = now() ,status = 'X'
where RNK_id = '''
I'm completely and uterly stumped by this, I have no idea of where to go
from here.
Any help will be greatly appreciated.
Thank,
Michael Grubb
000domains.com
Below is output from my master and slave "show variables" command.
Show variables on the master:
character_set_client latin1
character_set_connection latin1
character_set_database latin1
character_set_results latin1
character_set_server latin1
character_set_system utf8
character_sets_dir
/usr/share/mysql/charsets/
collation_connection
latin1_swedish_ci
collation_database
latin1_swedish_ci
collation_server latin1_swedish_ci
Show variables on the slave:
character_set_client latin1
character_set_connection latin1
character_set_database latin1
character_set_results
latin1
character_set_server
latin1
character_set_system utf8
character_sets_dir /usr/share/mysql/charsets/
collation_connection latin1_swedish_ci
collation_database
latin1_swedish_ci
collation_server
latin1_swedish_ci
signature.asc
Description: OpenPGP digital signature
RE: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) and Replication
Got me stumped on this one, I've been using 4.1 for over a year now and I
haven't run into this problem except somebody was using phpmyadmin or
something. Maybe somebody else can help with this one, sorry.
Donny
_
From: Michael Grubb [mailto:[EMAIL PROTECTED]
Sent: Monday, November 15, 2004 4:23 PM
To: Donny Simonton
Cc: [EMAIL PROTECTED]
Subject: Re: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and
(utf8_general_ci,COERCIBLE) and Replication
Donny,
I certainly appreciate your help.
show create table contacts shows the same output on both the master and
the slave ('latin1').
This is being run by the slave's SQL thread, and the Error is shown in the
Last error line of
show slave status and the error log.
Thanks again,
Michael
Donny Simonton wrote:
Michael,
Normally I would let a fellow domain registrar fend for themselves, but I'm
feeling nice today. :)
Do a show create table contacts and see what the charset is set too.
I bet the character set on the slave is different. Are you running this
from the command line?
Or are you getting this error in the error_log?
Donny
-Original Message-
From: Michael Grubb [mailto:[EMAIL PROTECTED]
Sent: Monday, November 15, 2004 2:49 PM
To: [EMAIL PROTECTED]
Subject: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and
(utf8_general_ci,COERCIBLE) and Replication
Hello all,
I'm having a very peculiar and nerve racking experience with MySQL 4.1.7
running on Fedora Core 2.
On the master the query executes just fine, however on the slave I get:
Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and
(utf8_general_ci,COERCIBLE) for operation '='' on query. Default
database: 'BRITEERNO'. Query: 'Update contacts
set firstname = '',lastname = '',email =
'' ,company = '',address1 = '',address2 = ''
,address3 = '',city = '',state =
'XX' ,zip = '', country = 'XX',phone = ''
,fax = '',user = '',pass =
'',changed_on = now() ,status = 'X'
where RNK_id = '''
I'm completely and uterly stumped by this, I have no idea of where to go
from here.
Any help will be greatly appreciated.
Thank,
Michael Grubb
000domains.com
Below is output from my master and slave show variables command.
Show variables on the master:
character_set_client latin1
character_set_connection latin1
character_set_database latin1
character_set_results latin1
character_set_server latin1
character_set_system utf8
character_sets_dir
/usr/share/mysql/charsets/
collation_connection
latin1_swedish_ci
collation_database
latin1_swedish_ci
collation_server latin1_swedish_ci
Show variables on the slave:
character_set_client latin1
character_set_connection latin1
character_set_database latin1
character_set_results
latin1
character_set_server
latin1
character_set_system utf8
character_sets_dir /usr/share/mysql/charsets/
collation_connection latin1_swedish_ci
collation_database
latin1_swedish_ci
collation_server
latin1_swedish_ci
Problem with Mysql 4.1.3: Error #1267 - Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE)
Hi All I am very confused about MySQL 4.1.3. I have problems with the character set and the collation. Under 4.0.20 I had never such problems. I downloaded the beta-files from version 4.1.3b and zipped it to c:\mysql. Then I loaded my data within a script with load data infile into my tables... The point is, that all tables have now the collation 'latin1_swedish_ci'?!!? Is this the default collation? Why does this collation appear? Interesting is, that the meta-data (all tables in the mysql-db) does have the 'latin1_swedish_ci' - collation too. The problem is now with phpmyadmin, if I make a select like SUBSTRING_INDEX( domain, '.', -2 ) from url_cat, the following error-message appears: Error #1267 - Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE). I found out, that if I use the following statement select SUBSTRING_INDEX( domain, _latin1'.', -2 ) from url_cat it works But the Problem is, i won't change all my old scripts from 4.0!!! I found out, that if I use mysql in DOS, everything works without error!!! Do I have to change something in phpmyadmin (i have version 2.6.0rc1)? I am very confused about it and i don't know how and which character-set and collation I have to choose. I live in Switzerland... Which character-set and collation I have to choose? Is there a 'standard' character-set/collation I should use? Do I have to set parameters in the my.cnf - file? My system runs under Windows XP with SP2. Thank you in advance for your help. Yours Martin Rytz DBA from Switzerland
RE: Problem with Mysql 4.1.3: Error #1267 - Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE)
This is not a mysql problem, it is a phpmyadmin problem. You must be using 2.6.0 rc1, download 2.6.0 beta 2 and you won't have the problem any more. I have complained to them about it, but not exactly sure what they are going to do. Donny -Original Message- From: Martin Rytz [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 18, 2004 9:35 AM To: [EMAIL PROTECTED] Subject: Problem with Mysql 4.1.3: Error #1267 - Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) Hi All I am very confused about MySQL 4.1.3. I have problems with the character set and the collation. Under 4.0.20 I had never such problems. I downloaded the beta-files from version 4.1.3b and zipped it to c:\mysql. Then I loaded my data within a script with load data infile into my tables... The point is, that all tables have now the collation 'latin1_swedish_ci'?!!? Is this the default collation? Why does this collation appear? Interesting is, that the meta-data (all tables in the mysql-db) does have the 'latin1_swedish_ci' - collation too. The problem is now with phpmyadmin, if I make a select like SUBSTRING_INDEX( domain, '.', -2 ) from url_cat, the following error-message appears: Error #1267 - Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE). I found out, that if I use the following statement select SUBSTRING_INDEX( domain, _latin1'.', -2 ) from url_cat it works But the Problem is, i won't change all my old scripts from 4.0!!! I found out, that if I use mysql in DOS, everything works without error!!! Do I have to change something in phpmyadmin (i have version 2.6.0rc1)? I am very confused about it and i don't know how and which character-set and collation I have to choose. I live in Switzerland... Which character-set and collation I have to choose? Is there a 'standard' character-set/collation I should use? Do I have to set parameters in the my.cnf - file? My system runs under Windows XP with SP2. Thank you in advance for your help. Yours Martin Rytz DBA from Switzerland -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
