MySQL Bugs: #7815: mysqldump --delayed-inserts can produce unusable dump

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

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.

In discussion with Paul (Paul, please see my email to you).

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).

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).

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

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

Noted in mysqldump section, 5.0.7 changelog.

By modifying check_if_ignore_table() we could get --delayed-insert to work now (in 5.0.7).

Back to verified, patch is not immediate to write and this is low priority.

Need to talk to Guilhem about where this is currently.

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

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

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

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

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

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

pushed 09.27

Noted in 5.0.14 changelog that --delayed-insert has
been re-enabled.