Bug #7815 mysqldump --delayed-inserts can produce unusable dump
Submitted: 11 Jan 2005 18:25 Modified: 29 Sep 2005 18:14
Reporter: Paul DuBois Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version: OS:
Assigned to: Bugs System CPU Architecture:Any

[11 Jan 2005 18:25] Paul DuBois
Description:
mysqldump --delayed-insert produces INSERT DELAYED
statements instead of INSERT statements.

However, if the dump contains tables for storage engines
that don't support DELAYED, reloading the dump file will
fail with an error and those tables will not be reloaded.

How to repeat:
drop table if exists t;
create table t (i int) engine = innodb;
insert into t set i = 1;

Then run mysqldump:

mysqldump --delayed-insert test t > junk

Then try to reload the dump file:

mysql test < junk

Result:

ERROR 1031 (HY000) at line 30: Table storage engine for 't' doesn't have this option

Suggested fix:
I guess mysqldump would need to know which storage
engines support DELAYED and not write DELAYED for
tables from those engines.

Or else these engines would need to ignore DELAYED.
[24 Apr 2005 21:07] Guilhem Bichot
In discussion with Paul (Paul, please see my email to you).
[12 May 2005 13:08] Guilhem Bichot
I have asked Monty to tell the historical reasons and to choose one solution (either change the server to ignore DELAYED for non-MyISAM engines (possibly issuing a warning) or change mysqldump to always do SHOW TABLE STATUS to check).
[13 May 2005 19:36] Guilhem Bichot
Agreed on fix with Monty:
- mysqldump of 5.0 will ignore --delayed-insert (print a warning to stderr, and a note in the option's description)
- when Monty has pushed the needed pieces of the table definition cache, we'll re-enable the option in mysqldump in the version where he has pushed (5.0 or 5.1).
[18 May 2005 11:02] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/25024
[18 May 2005 11:07] Guilhem Bichot
In 5.0.7 I disabled --delayed-insert in mysqldump (a warning is printed to stderr if this option is used, and the option is turned off). Hopefully it can be re-enabled later, but for now the change should be noted into changelog of 5.0.7.
ChangeSet@1.1850, 2005-05-18 13:02:09+02:00, gbichot@quadita2.mysql.com
  mysqldump.c:  disabling --delayed-insert until BUG#7815 is fixed
[24 May 2005 19:21] Paul DuBois
Noted in mysqldump section, 5.0.7 changelog.
[24 May 2005 19:35] Guilhem Bichot
By modifying check_if_ignore_table() we could get --delayed-insert to work now (in 5.0.7).
[24 May 2005 20:06] Guilhem Bichot
Back to verified, patch is not immediate to write and this is low priority.
[27 Aug 2005 18:48] Patrick Galbraith
Need to talk to Guilhem about where this is currently.
[4 Sep 2005 17:25] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/29296
[8 Sep 2005 21:34] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/29531
[9 Sep 2005 23:45] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/29593
[23 Sep 2005 17:42] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/30281
[27 Sep 2005 5:43] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/30361
[27 Sep 2005 16:42] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/30398
[28 Sep 2005 23:12] Patrick Galbraith
pushed 09.27
[29 Sep 2005 18:14] Paul DuBois
Noted in 5.0.14 changelog that --delayed-insert has
been re-enabled.