Update problem 'warnings about character encoding'

I am also having this same problem, although running the database update script had no problems. But when I try and access the site nothing hapens. If I look at my error logs I see this:

[Wed Feb  1 16:34:42 2006] [error] PHP Warning:  Illegal mix of collations (utf8_general_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT) for operation 'like'\nquery: SELECT * FROM access WHERE status = 0 AND type = 'host' AND LOWER('67.130.19.190') LIKE LOWER(mask) in /Library/WebServer/Documents/mywebsite/includes/database.mysql.inc on line 124
[Wed Feb  1 16:34:43 2006] [error] PHP Warning:  Unknown column 'referer' in 'field list'\nquery: INSERT INTO watchdog (uid, type, message, severity, link, location, referer, hostname, timestamp) VALUES (1, 'php', 'Unknown column 'weight' in 'order clause'\\nquery: SELECT name, filename, throttle, bootstrap FROM system WHERE type = 'module' AND status = 1 ORDER BY weight ASC, filename ASC in /Library/WebServer/Documents/mywebsite/includes/database.mysql.inc on line 124.', 2, '', '/mywebsite/?q=admin', '', '67.130.19.190', 1138833283) in /Library/WebServer/Documents/mywebsite/includes/database.mysql.inc on line 124
[Wed Feb  1 16:34:43 2006] [error] PHP Warning:  Unknown column 'referer' in 'field list'\nquery: INSERT INTO watchdog (uid, type, message, severity, link, location, referer, hostname, timestamp) VALUES (1, 'php', 'array_keys(): The first argument should be an array in /Library/WebServer/Documents/mywebsite/includes/menu.inc on line 1189.', 2, '', '/mywebsite/?q=admin', '', '67.130.19.190', 1138833283) in /Library/WebServer/Documents/mywebsite/includes/database.mysql.inc on line 124

Looks like a conflict between the previous database collation of latin1_swedish_ci and the new UTF-8. I have not idea how to fix this though. I am using MySQL 4.1.9-standard.

This 'bug' has hit almost everyone. Leaving this 'critical' bacause we are going to be swamped with bug reports and annoyed people if we do not fix this.

Yes, they are only warnings, but it looks very nasty.

Would documenting this in update.php be an option?

Well, I was able to come up with a quick personal sollution for this issue. I just did a DUMP of my SQL and then found and replaced the latin for UTF in the charset. Then when I reimported it into MySQL drupal didn't have any more problems. Is there some way the update script can change the collation of all the fields in your SQL?

Here are some examples of charset conversion in SQL... for mysql.

http://dev.mysql.com/doc/refman/4.1/en/charset-conversion.html

I marked http://drupal.org/node/47730 as a duplicate of this issue.

Also, I'd say that this should not be "critical". It will generate lots of forum messages if it isn't fixed, but there is a known workaround.

I posted this in http://drupal.org/node/46564#comment-93374; sorry if I am showing my newbie-ness by also posting here.

I got the character encoding message upgrading from a hosted 4.6.3 database to 4.7 CVS as of 2006-02-12. I was not able to lsubsequently log into the site.

For people upgrading an existing database: what worked for me was exporting the 4.6 database using phpMyAdmin, but under the Structure settings, set "SQL export compatability" to MySQL40. This has the effect of removing “DEFAULT CHARSET=latin1” from many of the CREATE TABLE statements in the export file. When I imported this cleansed structure and data into my new empty database for 4.7, the subsequent upgrade worked flawlessly.

There must be a way to do this with straight SQL code, bypassing phpMyAdmin, but I don't know enough to make that work.

This issue also appears in bug report http://drupal.org/node/46424 and in the beta 4 announcement http://drupal.org/node/46564#comment-93374 .

I'm really not following this.

Can't we just @suppress warnings for these queries if there's no actual bug and forget about it?

I attempted to update my current drupal powered site and found that three tables failed to be updated ( accesslog, node, locales_target). Afater updating, the only problem I found is the display of the forum titles. The encoding seems to be messed up. So when they are listed in the forum, the titles were not displayed properly. But the title displayed at the top of the post had no problem at all. So i don't know what's wrong. Maybe it is related to the mix of illegal character problem.

I tried again. If I just updated to beta-3, then everything went well and there is no problem. But if I contitue to update my website to beta-4, then the three updating failure appeared (as I mentioned above) and the tiltes of the forum posts are messed up. So the prolem is caused by beta-4.

update is still possible, just shows warnings. downgrading.

Unfortunately it does more than show warnings. It makes the upgraded Drupal unusable.

At the very least this seems a critical documentation bug. I just used CVS (yesterday's which looks to be identical to B5) and upgraded a site. There are no warnings and things seem to "work". That is until you try to log in. The installation insists your user name or password is wrong and there is no way to have it changed as it won't recognize the email address either. At the very least it seems some stern warning about this should appear in UPGRADE.txt and possibly be a warning displayed to users in update.php.

Josh, thanks for your follow-up. What makes you believe that your problem is related to this issue? I'd like to understand. Such information would help us address this issue.

This is still a problem with beta5. I used both the mysqli and mysql driver. The update.php did not take care of it and it should be referenced in the update.php help or displayed better in the handbook. I think this is rather critical because I work with databases and knew what to do and the solutions. If Drupal wants to be marketed well as a CMS solution, it needs to be easy to use and upgrade. Food for thought.

I tried the following:

Import database.mysql
Try using Druapl: error as listed before.

Then go to Database view in phpsqlmyadmin.
Choose Operations (last tab on the right , the view is server>servername database > databasename)
Choose utf_general_ci in Collation (last lin, third "Go" button)
This wll show up the output:
ALTER DATABASE `web4unit78011` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci

Then go back to the Be sure also to have the same collation in the Server page.

Drop allthe tables

Now do the import again

It worked up to now.

Perhaps there is an easier way, I don't know.

Ouch. I just hit this bug. I've been running the DRUPAL-4-7 branch from CVS and I just upgraded (it's about 2 weeks since the last cvs update). When I tried to login the DB lookup failed with the same error concerning mixing collations on the user table. I found a fix here in the form of this operation:

ALTER TABLE <i>tablename</i> CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

The problem may be because I upgraded my MySQL database recently (3.23->4.1) and perhaps did not transfer the data correctly. Could the upgrade script check the encoding of all the tables and alter them? Or would this MySQL ALTER command completely break non-latin1 characters?

I have the same problem BUT i am starting from a fresh installation of Drupal (that is, I am not upgrading from an old one).

Warning: Illegal mix of collations (utf8_general_ci,COERCIBLE) and (latin1_general_ci,IMPLICIT) for operation 'like' query: SELECT * FROM access WHERE status = 1 AND type = 'host' AND LOWER('127.0.0.1') LIKE LOWER(mask) in D:\programs\xampp\htdocs\stp\includes\database.mysql.inc on line 120

I used PHPMyAdmin to install the Drupal DB and I've since fixed the error.

Solution:
From a fresh install, when pasting the SQL commands in the SQL textarea of PHPMyAdmin (from file database.4.1.mysql), make sure you click the dropdown box for character set and choose utf8_general_ci

Help me facing the same problem.

i just moved my site from my local server (mysql 4.1) to a host that only had mysql 4.0 and 5.. so i went with 5.

I started to get the warnigns listed above and tried the solution listed in #15 above... did tables one by one until warnings stopped; ended up modifying:

access, category, user, category_node

now i have no warnings.. BUT.. even thuogh i can log into site.. as soon as i do a refresh i am logged out again.. maybe this is a different issue though.

20 sites with db and email accounts galore + massive server crash = VERY long night...

I also faced this problem using drupal 4.7.4, and searching forum for hours,and fixed it finally like following:
1.creat the database in phpadmin
2.run the sql:
ALTER DATABASE `yourdatabasename` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci
3.Then import database.mysql
and then just follow the installation instruction as drupal desribed

you can see my demo site:

Drupal Demo Site

I made a script when I migrated my 4.6 sites to 4.7. I'll attach it here in hope it helps someone (not the finest piece of code, but worked for me).