Bug #22941 mysqldump with --skip-opt will omit auto_increment in the resulting dump
Submitted: 3 Oct 2006 14:24 Modified: 4 Oct 2006 8:27
Reporter: C3PO Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: mysqldump Command-line Client Severity:S2 (Serious)
Version:at least 4.1.21, 4.1.12 OS:.
Assigned to: CPU Architecture:Any
Tags: --skip-opt will omit auto_increment

[3 Oct 2006 14:24] C3PO
Description:
The line:

mysqldump -h host database -u root -p --skip-opt --add-drop-table
will discard auto_increment attribute from dump

without --skip-opt:

mysqldump -h host database -u root -p --add-drop-table
CREATE TABLE `user_contacts` (
  `auto` int(11) NOT NULL auto_increment,
  `user_id` int(11) NOT NULL default '0',
  `contact_id` int(11) NOT NULL default '0',
  PRIMARY KEY  (`auto`),
  KEY `user_ind` (`user_id`),
  KEY `contact_ind` (`contact_id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=cp1251;

with --skip-opt
mysqldump -h host database -u root -p --skip-opt --add-drop-table
CREATE TABLE `user_contacts` (
  `auto` int(11) NOT NULL,
  `user_id` int(11) NOT NULL default '0',
  `contact_id` int(11) NOT NULL default '0',
  PRIMARY KEY  (`auto`),
  KEY `user_ind` (`user_id`),
  KEY `contact_ind` (`contact_id`)
);

which is _very_ frustrating :-(

How to repeat:
1. 

CREATE TABLE `user_contacts` (
  `auto` int(11) NOT NULL auto_increment,
  `user_id` int(11) NOT NULL default '0',
  `contact_id` int(11) NOT NULL default '0',
  PRIMARY KEY  (`auto`),
  KEY `user_ind` (`user_id`),
  KEY `contact_ind` (`contact_id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=cp1251;

2.
mysqldump -h host database -u root -p --skip-opt --add-drop-table >d1.sql
(no auto_increment)

3.
mysqldump -h host database -u root -p --add-drop-table >d2.sql

Suggested fix:
I've seen this bug fixed a couple of times. The previous fixes were related to mysql40 compatibility.
[4 Oct 2006 6:40] Sveta Smirnova
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Please, read carefully what --opt and --skip-opt options do at http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html
[4 Oct 2006 8:27] C3PO
Hm... Thank you for the early reply, but I can't see any suggestion that --skip-opt skips autoincrementing, or that --opt is somehow related to the autoincrement. Correct me if I'm wrong but autoincrement is related to the database sturcture while -opt is only how mysqldump optimizes the resulting dump. Cutting autoincrement _will_ render the resulting dump useless.

I will be explicit as to why we need to use skip-opt at all. The problem is that the newer mysql versions are doing all inserts in ONE LINE by default. That is, if we want to modify the dump manually we can't open it as a text file any longer. Most of the editors will fail to handle such a huge line of text. There is more - the optimized inserts are unreadable. Personally I don't care whether the dump is uploading 15 or 20 minutes as long as it is readable by the human being. To keep it readable we use --skip-opt which is cutting auto_increment all the time by reasons unknown. Could you provide more background as to why dump optimization procedure may affect database structure in mysql manual? I mean information dumped is not a replica of the source - at sometimes you find it out too late.

==== snap ====
--opt 

This option is shorthand; it is the same as specifying --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset. It should give you a fast dump operation and produce a dump file that can be reloaded into a MySQL server quickly. 

The --opt option is enabled by default. To disable the options that it enables, use --skip-opt. To disable only certain of the options enabled by --opt, use their --skip forms; for example, --skip-add-drop-table or --skip-quick. Alternatively, use --skip-opt to disable the options enabled by --opt, followed by options to enable the features that you want. Options are processed in order, so the options to enable features must follow --skip-opt. For example, --skip-opt --extended-insert enables extended inserts, but --extended-insert --skip-opt does not. 
==== /snap ====
[16 Nov 2009 15:00] Ralf Hauser
see also Bug #23848
[28 May 2019 21:19] Björn Voigt
This bug is very old, but still exists (tested with MySQL 5.6.44).

The bug effectively causes data loss by unusable databases, if the user does not know how to restore the original schema.