I'm upgrade my drupal from, 4.7.5 to 5rc1 without any error message, but when I'm try to browse my web, it seems my blocks are disappeared.

I'm try to look at the block section in Administer, everything looks as same as before upgrade. But when I look around drupal log there 're many error shown as below:-

Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '=' query: SELECT DISTINCT b.* FROM blocks b LEFT JOIN blocks_roles r ON b.module = r.module AND b.delta = r.delta WHERE b.theme = 'minnelli' AND b.status = 1 AND (r.rid IN (2) OR r.rid IS NULL) ORDER BY b.region, b.weight, b.module in /home/(disclosed)/public_html/includes/database.mysql.inc on line 167.

I thought it's a collation between table block and block_role is not matched so I use phpMyAdmin to browse those 2 tables, and I found that the collation of block is 'utf8_unicode_ci and the collation of block_role is 'utf8_general_ci', so I change both to 'utf8_unicode_ci' then bingo! my blocks is back!!

After look around 5rc1's update.php with my foolish php knowledge I'm got nothing, but I think the problem is there.

CommentFileSizeAuthor
#4 convert.php.txt890 bytesmfer

Comments

cog.rusty’s picture

cog.rusty commented

AFAIK, collation is not specified anywhere in Drupal. Since each character set has a default collation, that one is used by default.

The default collation for the latin1 character set is latin1_swedish_ci
The default collation for the utf8 character set is utf8_general_ci

Collation errors involving latin1_swedish_ci mean that the wrong character set has been set somewhere.
Collation errors involving utf8_unicode_ci mean that a collation has been explicitly set by the user (perhaps when creating a new database) or by a module at some time.

cog.rusty’s picture

cog.rusty commented

Also in direct comparisons with strings such as 'minnelli', the connection's character set (and default collation) is used for the string, and there is a SET NAMES utf8 for the connection in Drupal.

kengggg’s picture

kengggg commented

it might be human error at the time of database creation or something, but what i wonder is, why this problem is not occur in 4.7.5?.

mfer’s picture

mfer commented
StatusFileSize
new convert.php.txt890 bytes

@kengggg - Drupal 5 adds several new tables in utf8_general_ci that aren't in 4.7.4 or 4.7.5. If your original tables aren't in utf8_general_ci your blocks won't display. I had this problem. I used the attached script to convert my table to utf8_general_ci.

Warning: I don't support this code and am not liable for any problems it may cause. It is very loosely tested but it worked for me. Change the server, database user name, password, and database name (in 2 places).

RobRoy’s picture

RobRoy commented
Category: bug » support

This is a support request. Your db/table/field encodings were incorrect in 4.7 and it looks like that was causing the problem. They should have been utf8_general_ci as stated above. What version of MySQL?

kengggg’s picture

kengggg commented

mfer - the new table is including block_role, or not? i'm not sure what is a collation i used in pervious 4.7.x (one thing i know is it's utf8_x_ci). thanks for you script anyway, i did it myself via phpMyAdmin :D

RobBoy - i got you point, but how can i know it's incorrect while my previous 4.7.x was work fine? it might be my wrong procedure when i move entire of database to a new host but how can i know what is the correct collation?
i think update script should put something like integrity verfication to verify 'something important' to prevent such kind of this problem. :D

kengggg’s picture

kengggg commented

forgot ... mysql is version 4.1.21

cog.rusty’s picture

cog.rusty commented

The blocks_roles table appeared for the first time in Drupal 5. Compare /database/database.mysql in 4.7 with /modules/system/system.install in 5. It could have taken its collation from the whole database's default collation (in which case check that too).

I find it a good idea to have a verification check of character set/collation at installation or update. Besides obvious error messages. people often have more subtle problems with search or even with login authentication.

Moreover, I think that because of broken collations in Fantastico installations, problems in backup/restore between MySQL 4.0- and 4.1+ etc there is a need for tools like the script in #4 maintained regularly. Perhaps a "repair toolkit" module? Or could they find a home in devel module? I can't do it myself, so I'd better shadap.

mfer’s picture

mfer commented

@CogRusty - Having a feature that checks the character set of the database tables might be good to have on the new status page. Sounds like this could be a good feature for 6.

mfer’s picture

mfer commented

@kengggg - is this still a problem?

kengggg’s picture

kengggg commented

mfer - I already solve this problem since I change a collation of those 2 tables to the same, in my case 'utf8_unicode_ci'.

In my seems-to-be-a-power-user's point of view, this problem is not serious, but for the end users it's very serious, I think. So it sounds good if drupal put character checking features into the next release.

kengggg’s picture

kengggg commented
Title: upgrade 4.7.5 to 5rc1 problem » upgrade 4.7.5 to 5rc1 problem - need database encoding validation at update process
Category: support » feature

Change category to feature support and add more Title description.

mfer’s picture

mfer commented
Version: 5.0-rc1 » 6.x-dev
chx’s picture

chx commented
Status: Active » Closed (won't fix)

Noone followed up.