Trying to update a published node to view revision handling in 4.7 beta 3 (cvs 25/01/2006). I get this error:

    * user warning: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '=' query: SELECT * FROM users u WHERE LOWER(name) = LOWER('agentken') in /Library/WebServer/htdocs/drupal47/includes/database.mysql.inc on line 124.
    * The username agentken does not exist.

Watchdog reports this as a PHP error.

Using MySQL 5.1.2

CommentFileSizeAuthor
#6 fix_illegal_mix_of_collations.patch394 bytesjsaints

Comments

agentrickard’s picture

agentrickard
he/him
Primary language English
Location Georgia (US)
commented
Title: PHP error updating node: illegal mix of collations » PHP error: illegal mix of collations
Priority: Normal » Critical

It gets worse. When I logged out, I can no longer log back in. When I try, I get:

*user warning: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1 query: SELECT DISTINCT(p.perm) FROM role r INNER JOIN permission p ON p.rid = r.rid WHERE r.rid IN () in /Library/WebServer/htdocs/drupal47/includes/database.mysql.inc on line 124.


*user warning: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '=' query: SELECT * FROM users WHERE status = 1 AND name = LOWER('agentken') in /Library/WebServer/htdocs/drupal47/includes/database.mysql.inc on line 124.


*Sorry. Unrecognized username or password.
agentrickard’s picture

agentrickard
he/him
Primary language English
Location Georgia (US)
commented

Running MYSQL 4.1.13. I tried dropping the drupal database and re-installing with utf8_general_ci collation.

Under that setup, I am able to login after creating the first account. But if I try to edit my account, I get this errror:


    * warning: array_merge() [function.array-merge]: Argument #2 is not an array in /Library/WebServer/htdocs/drupal47/includes/form.inc on line 180.
    * warning: Missing argument 1 for password_confirm_validate() in /Library/WebServer/htdocs/drupal47/includes/form.inc on line 595.

When I logout after this, I can no longer login with either my original Drupal-defined password, or my new, user-defined password.

QUESTIONS:

What is the proper COLLATION of the core Drupal database table in MySQL?
Is this whole issue Drupal-related or PEBKAC?

andyb’s picture

andyb commented

I had the exact same problem, but it looks like there's a fix.

For me, I had a 4.7.0-beta3 running. After deploying 4.7.0-beta4 I was getting the exact same collation errors. After visiting http://my.site/update.php and running the database update script everything was fine again. There are a large number of ALTER TABLE statements that reset the default character set to utf8 on the Drupal tables.

Not sure if it helps you, but maybe it would be worth downloading the official -beta4 release and running the update script.

moshe weitzman’s picture

moshe weitzman commented
Status: Active » Fixed
Anonymous’s picture

(not verified) commented
Status: Fixed » Closed (fixed)
jsaints’s picture

jsaints commented
Version: » 4.7.0-beta4
Status: Closed (fixed) » Needs review
StatusFileSize
new fix_illegal_mix_of_collations.patch394 bytes

I found this same error in drupal 4.7 beta 4. I developed a website on a server (ubuntu breezy) that uses mysql 4.0.x. When I moved the site to a Fedora (core 4) server running mysql 4.1.11 I encounter the error

user error: Illegal mix of collations
(latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE)

Attached is a patch that fixes the problem. Seems we need to explicitly set the encoding to utf8 with a query. I found the solution in the php Gallery2 wiki at http://codex.gallery2.org/index.php/Gallery2:InstallationErrors

I have heard rumors that mysql 4.1.13 fixes this issue. but I do not have an installation available for testing.

bryansd’s picture

bryansd commented
Status: Needs review » Active

I'm sorry for possibly reopening this with the wrong logic, but I have concerns if the problem is not that fact that we are forcing UTF8. I just upgraded Drupal from MySQL 3.23 to 4.1.15-1 and I'm having problems in 4.7 Beta 4. Another site at 4.6.5 is perfectly fine with everything in latin1_swedish_ci .

Here is the concern for me...on the same server I'm running both Wordpress and SMF and neither of these applications were affected by my upgrade of MySQL. As far as I can tell they just go with the default collation of latin1_swedish_ci.

I was up late last night so the memory may be screwy...but I thought I read somewhere that the forcing of UTF8 was introduced because there was a problem with someone not being able to import a database from backup due to the collation change from UTF8 to latin1_swedish_ci in MySQL. If code was introduced to deal with this issue that forces the UTF8...maybe it's not appropriate within the Drupal core. There is a big assumption that collation will always be UTF8 and something about forcing the collation isn't sitting well with me.

Ok I'm not quite sure what I'm talking about and I'm rambling so I'll shut up. I let someone either correct me and close this issue again or be nudged to my way of thinking.

-Bryan

puregin’s picture

puregin commented
Status: Active » Closed (fixed)

Seems like Drupal (and every other sane software :) is moving to UTF8 throughout. Having a standard is good. If someone has a great reason for moving to some other charset, they can always do so. So, I'm happy with this approach.

I believe that jsaint's patch actually addresses a different ('though of course is is related) issue from the one that this report opened with (the original issue dealt with different collations in different tables, an internal inconsistency, while the issue jsaints reports has a different error message - inconsistency between the charset for tables and the connection)

So... I'm marking this again as closed - please open a new issue if required.

dobson’s picture

dobson commented

For those who are searching about and have come across this thread: Something that I had not been able to find before on the forums.

Drupal (4.7b4) was working perfectly, no collation errors, except in my own query string, created and executed from my module.

My query was using LOWER() as part of the SQL, and I was receiving the same error message, a clash between utf8_general_ci and latin1_swedish_ci. It seems that removing the LOWER() fixed the issue, and I believe that LOWER() is not supported for one of these collations. Still a little bewildered, but at least I can now eat my SQL.