[Using Sakai] TIP: restoring MySQL data from "mysqldump" to avoid "can't create table" error

[will at serensoft.com]

MySQL Tip --

When you use MySQL for your persistent storage, there's a bit of a
trick to restoring your mysqldump'd data back into a live database:

If you try to restore your mysql data straight from the mysqldump
output you'll get errors like this:

   # mysqldump realSakaiDB > mysqldump-file.sql
   # echo "create database mirrordatabase default character set utf8" | mysql
   # mysql mirrordatabase < mysqldump-file.sql
   ERROR 1005 (HY000): Can't create table
'./mirrordatabase/ASN_AP_ITEM_ACCESS_T.frm' (errno: 150)

The problem is that the table definition includes foreign keys that
are referring to other tables that don't exist yet. You could conjure
up a script to go through the mysqldump file and rearrange them in
dependency order, which would be a fun exercise for third-year CS
students, or...


SOLUTION:

Prefix your data-load SQL with "SET FOREIGN_KEY_CHECKS = 0" (and turn
it back on after you're all loaded up):

   # mysql mirrordatabase
   mysql> SET FOREIGN_KEY_CHECKS = 0;
   Query OK, 0 rows affected (0.00 sec)

   mysql> \. mysqldump-file.sql
   [snip]

   mysql> SET FOREIGN_KEY_CHECKS = 1;
   Query OK, 0 rows affected (0.00 sec)


Easy!


VERSIONS:

MySQL 5.0.51a
Sakai 2.6.x r60737

-- 
will trillich
"Tis the set of the sails / And not the gales / That tells the way we
go." -- Ella Wheeler Wilcox