Troubleshooting MySQL Dumps and Imports

The various versions of MySQL are more or less compatible with one another, but problems arise pretty regularly, especially when it comes to moving between versions of MySQL. Here are some notes about common MySQL dumping and importing issues, particularly those relevant to Back-End, Drupal, and CiviCRM.

Reserved words

Reserved words can change from release to release. Problems with reserved words in table and field names (BE has a few) can be solved by adding --quote-names to the mysqldump command:

mysqldump --opt --all-databases --quote-names -u username -p > dump_file_name

InnoDB problems

Problems also arise with older versions of mysqldump when there are InnoDB tables with foreign keys (as in the case of CiviCRM). The problem is that the foreign key must exist and be indexed, which might not yet be the case on an import. The MySQL manual notes that if mysql dump doesn't include a statement disabling foreign key checking, then it can be done manually from the mysql CLI as follows:

mysql> SET FOREIGN_KEY_CHECKS = 0;
mysql> SOURCE dump_file_name;
mysql> SET FOREIGN_KEY_CHECKS = 1;

Upgrades

The above steps will already help with this, but importing data from an older version of MySQL into a newer requires using some additional tools to make sure the tables are correctly updated to the new specs. The latest versions of MySQL (5.0.19+) offer the mysql_upgrade command that does this. If it's not available (as was recently the case on MySQL 5.0.18 on SUSE 10.1), run these commands:

mysqlcheck --check --all-databases --auto-repair
mysql_fix_privilege_tables

Auto increment

Apparently the versions of mysqldump included with MySQL versions 3.23 and 4.0 don't properly mark autoincrement fields, which can wreak havok with Drupal installs. Fortunately, there is a simple fix for this problem in Drupal here: http://drupal.org/node/65665.

Another autoincrement problem arises when the value '0' (zero) is inserted into an autoincrement field; by default, MySQL attempts to set this field to '1' (one), which can cause problems if a subsequent statement also tries to insert that value. The workaround for this is to temporarily turn off MySQL's usual handling of autoinsert fields with the statement SET sql_mode='NO_AUTO_VALUE_ON_ZERO'.

 I recently combined this with the above fix for foreign key checking as follows:

mysql -u root -p -e "SET SESSION sql_mode='NO_AUTO_VALUE_ON_ZERO'; SET FOREIGN_KEY_CHECKS = 0; SOURCE /home/stevem/mysql_all.sql; SET FOREIGN_KEY_CHECKS = 1;" 

Passing the SESSION keyword makes it explicit that the sql_mode is set only for this one client connection. 


Section: 


Code: 


Add new comment

CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions.