Bug #19551 mysqldump with --delayed-insert and InnoDB
Submitted: 4 May 2006 22:48 Modified: 4 May 2006 22:51
Reporter: Josh Trutwin Email Updates:
Status: Duplicate Impact on me:
Category:MySQL Server Severity:S2 (Serious)
Version:4.1.19 OS:Any (any)
Assigned to: CPU Architecture:Any

[4 May 2006 22:48] Josh Trutwin
I use mysqldump to create nightly backups.  I recently had to perform a restore due to a botched RAID install and found out that I couldn't restore my InnoDB tables from the dump file because the --delayed-insert I used in my backup script was getting put into the inserts on InnoDB tables which caused the inserts to fail.  Could --delayed-insert please only work with MyISAM tables (and other tables that support it) and not InnoDB?

Here was my backup script - I have since removed --delayed-insert:

/usr/local/mysql/bin/mysqldump --add-locks \
                                             --add-drop-table \
                                             --all-databases \
                                             --allow-keywords \
                                             --complete-insert \
                                             --compress \
                                             --create-options \
                                             --delayed-insert \
                                             --delete-master-logs \
                                             --disable-keys \
                                             --extended-insert \
                                             --flush-logs \
                                             --force \
                                             --lock-all-tables \
                                             --master-data=2 \
                                             --no-autocommit \
                                             --quick \
                                             --quote-names \
                                             --socket=/usr/local/mysql/mysql.sock \
                                             --password=xxxx \
                                             --user=root \
                                             | bzip2 -c > backup.sql.bz2


How to repeat:
Run above command and try to restore

Suggested fix:
Only have the INSERT DELAYED put in the dump file for MyISAM table types (and other supporting table types) and not InnoDB - or have InnoDB support delayed insert.  :)
[4 May 2006 22:51] Josh Trutwin
Sorry - my search kung fu is bad - this is a dupe of http://bugs.mysql.com/bug.php?id=7815

Guess I need to upgrade to 5.0...