überEllis.com

Drupal version: n/a
Module: n/a

I've run into several issues running different versions of MySQL between development server and live server.

The following tip from a thread on drupal.org:
dump 2 versions of the database

For version 4.0, it would be:

mysqldump --opt --compatible=mysql40 -v -u DATABASE_USER -p DATABASE_NAME > database.40.mysql

For version 4.1, use:

mysqldump --opt -v -u DATABASE_USER -p DATABASE_NAME > database.mysql

I've learnt something new with regards to dumping databases for different versions of MySQL - A source of many grey hair in the past..

Often, once a 4.0 database has been imported into a 4.1 server, the following error message is common:

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

To get by this, you need to convert the character set of each of the tables (this solution courtesy of Willie):

ALTER DATABASE {DATABASE_NAME} DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

And then:

ALTER TABLE {TABLE_NAME} CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

for each table in the error message.

For the reverse, editing the dump file can have pleasing results:

1. Search for ENGINE=MyISAM and replace with TYPE=MyISAM
2. Search for DEFAULT CHARSET=latin1 and replace with nothing Note: the character set might be something else, like "utf8"
3. Search for character set latin1 collate latin1_bin and replace with nothing Note: the character set might be something else, like "utf8"