MySQL Bugs: #18697: mysqldump ignores command-line options depending on their order

Bug #18697	mysqldump ignores command-line options depending on their order
Submitted:	31 Mar 2006 18:01	Modified:	17 May 2006 17:07
Reporter:	Rob Blick	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S1 (Critical)
Version:	4.1.18/5.0BK/5.1BK	OS:	Linux (Linux x86_64 2.6.22 kernel)
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
mysqldump produces different dumpfiles with the same command-line options depending on the order of the options.  This unpredictable behavior, which I've only witnessed in 4.1.18 (4.1.14 and 5.0.19 seemed to work fine), yielded a corrupted dump from one of our production servers.  We lost data (hence, the "critical" status)!  mysqldump is the only way to back up a MySQL database; for it to produce a corrupted dump is absolutely unacceptable if MySQL is used in a production environment.

How to repeat:
--go into mysql
use test;
--create two tables as sources of the dump.
mysql> create table t_myisam (a int primary key);
Query OK, 0 rows affected (0.02 sec)

mysql> create table t_innodb (a int primary key) ENGINE=innodb;
Query OK, 0 rows affected (0.01 sec)

--insert some test data
mysql> insert into t_myisam values (1), (2), (3);   
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into t_innodb values (4), (5), (6);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> exit

--now, dump the test database using two mysqldump commands, each with the same options but in a different order:
[mysqldba@igsp-db-01a ~]$ mysqldump --add-drop-table --add-locks --allow-keywords --create-options --default-character-set=latin1 --disable-keys --extended-insert --skip-opt --quick -uroot -p test > test_no.sql
Enter password: 
[mysqldba@igsp-db-01a ~]$ mysqldump --skip-opt --add-drop-table --add-locks --allow-keywords --create-options --default-character-set=latin1 --disable-keys --extended-insert --quick -uroot -p test > test_yes.sql
Enter password: 
[mysqldba@igsp-db-01a ~]$

--note that the only difference between the two mysqldump commands is the location of the "--skip-opt" option.

--now, compare test_no.sql with test_yes.sql.  Among the differences:
test_no.sql does not have "DROP TABLE IF EXISTS"
test_no.sql does not have the ENGINE=InnoDB DEFAULT CHARSET=latin1 table options
test_no.sql does not lock the tables or disable keys before insertion
test_no.sql does not use the extended-insert syntax

Though not illustrated here, using the first mysqldump command also does not honor the --default-character-set option (this caused our data corruption/data loss).  In fact, there may be other options, not readily visible in the dumpfile, that are not honored by the first mysqldump command.

Suggested fix:
An error like this deserves an immediate fix.  It is imperitive that one be able to consistently and reliably backup a MySQL database without questioning or doubting the integrity of the dumpfile.

Correction:  5.0.19 also suffers from similar behavior.

Thank you for the bug report. I was able to repeat as reported.

miguel@hegel:~/dbs/4.1> diff -s test_yes.sql test_no.sql 
15d14
< DROP TABLE IF EXISTS `t_innodb`;
19c18
< ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
---
> );
<cut>

mysqldump behaves as expected, if conflicting options are specified, the later one takes the effect.

But I don't see it documented at http://dev.mysql.com/doc/refman/5.0/en/command-line-options.html.

Please explain how these are conflicting options.  If one does not specify --skip-opt, then the options --add-drop-table, --add-locks, --create-options, --extended-insert, --disable-keys and --quick are redundant (b/c they are covered by --opt, which is enabled by default), but they are not conflicting.  If one does specify --skip-opt, then these options are neither redundant nor conflicting, b/c --opt is not in effect.  The two remaining options, --allow-keywords and --default-character-set, do not conflict with any of the other options, either.  I don't understand how this is "expected behavior."

According to mysqldump --help,

  --opt               Same as --add-drop-table, --add-locks, --create-options,
                      --quick, --extended-insert, --lock-tables, --set-charset,
                      and --disable-keys. Enabled by default, disable with
                      --skip-opt.
  --skip-opt          Disable --opt. Disables --add-drop-table, --add-locks,
                      --create-options, --quick, --extended-insert,
                      --lock-tables, --set-charset, and --disable-keys.

So, if you will write --add-drop-table --skip-opt, you will have conflicting options as --skip-opt disables --add-drop-table. Whatever option is later in the command line wins.

With --add-drop-table --skip-opt you have "--add-drop-table behavior" disabled, with --skip-opt --add-drop-table it will be enabled.

Thank you for your bug report. This issue has been addressed in the
documentation. The updated documentation will appear on our website
shortly, and will be included in the next release of the relevant
product(s).

Additional info:

The principle that later options take precedence is
not specific to the command line, so I'll add a note about
it to the more general option-processing section:

http://dev.mysql.com/doc/refman/5.0/en/program-options.html

OK, I think I understand.  --skip-opt, according to you, ACTIVELY DISABLES the options that would normally be enabled if --skip-opt were not included.  In other words, including --skip-opt is the same as adding "--skip-add-drop-table --skip-add-locks --skip-create-options --skip-disable-keys --skip-extended-insert --skip-lock-tables --skip-quick --skip-set-charset" to the mysqldump command.  I understood --skip-opt to simply NOT ACTIVELY ENABLE the options governed by --opt (i.e., including --skip-opt was the same as NOT INCLUDING "--add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset" to the mysqldump command.  The following therefore follows:

If I do mysqldump --add-drop-table --skip-opt..., then:
--- by your interpretation, and the actual behavior, this equates to mysqldump --add-drop-table --skip-add-drop-table = conflict.
--- but by my interpretation, this equates to mysqldump --add-drop-table = no conflict.

This is a significant distinction; please update the docs to reflect this.