Bug #14929 mysqldump output missing auto-increment value (and other things)
Submitted: 14 Nov 2005 22:31 Modified: 13 Dec 2005 0:59
Reporter: Rodd Clarkson Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:mysql-5.0.15-2 OS:Linux (fedora core rawhide)
Assigned to: CPU Architecture:Any

[14 Nov 2005 22:31] Rodd Clarkson
Description:
I've dumped a database from mysql-5.0.15-2 using the following command:

DROP TABLE IF EXISTS `tblContents`;
CREATE TABLE `tblContents` (
  `intContentID` int(11) NOT NULL auto_increment,
  `strTitle` varchar(150) default NULL,
  `chrStatus` char(3) default NULL,
  `intParent` int(11) default NULL,
  `intOrder` int(11) default NULL,
  `strGroups` text,
  `dtPosted` datetime default NULL,
  `strAccess` text,
  `dtActivate` datetime default NULL,
  `dtDeactivate` datetime default NULL,
  `intShortList` int(11) default NULL,
  `strShortListTitle` varchar(80) default NULL,
  `strTextLink` varchar(100) default NULL,
  `strType` varchar(100) default 'content',
  `strExtraLink` varchar(255) default NULL,
  `strTimeStamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update
CURRENT_TIMESTAMP,
  `dtTimeStamp` timestamp NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`intContentID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Notice the follow:

intContentID has no auto_increment option in test.sql output.
strTimeStamp is different
there is no DEFAULT CHARSET=latin1 in the test.sql output.

Version-Release number of selected component (if applicable):

mysqlclient14-4.1.14-1
mysqlclient10-3.23.58-6
mysql-5.0.15-2
mysql-server-5.0.15-2

How to repeat:
This bug is easily repeated by re-dumping the database and checking output.
[14 Nov 2005 22:38] Rodd Clarkson
Also, there's a whole lot of missing comment from my original bug report.  (mmmm, but in firefox, because it was there, but I digress).

I'm going to refile the description of the problem in it's entirity.

I've dumped a database from mysql-5.0.15-2 using the following command:

DROP TABLE IF EXISTS `tblContents`;
CREATE TABLE `tblContents` (
  `intContentID` int(11) NOT NULL auto_increment,
  `strTitle` varchar(150) default NULL,
  `chrStatus` char(3) default NULL,
  `intParent` int(11) default NULL,
  `intOrder` int(11) default NULL,
  `strGroups` text,
  `dtPosted` datetime default NULL,
  `strAccess` text,
  `dtActivate` datetime default NULL,
  `dtDeactivate` datetime default NULL,
  `intShortList` int(11) default NULL,
  `strShortListTitle` varchar(80) default NULL,
  `strTextLink` varchar(100) default NULL,
  `strType` varchar(100) default 'content',
  `strExtraLink` varchar(255) default NULL,
  `strTimeStamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update
CURRENT_TIMESTAMP,
  `dtTimeStamp` timestamp NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`intContentID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

A snip of the output in test.sql shows:

DROP TABLE IF EXISTS `tblContents`;
CREATE TABLE `tblContents` (
  `intContentID` int(11) NOT NULL,
  `strTitle` varchar(150) default NULL,
  `chrStatus` char(3) default NULL,
  `intParent` int(11) default NULL,
  `intOrder` int(11) default NULL,
  `strGroups` text,
  `dtPosted` datetime default NULL,
  `strAccess` text,
  `dtActivate` datetime default NULL,
  `dtDeactivate` datetime default NULL,
  `intShortList` int(11) default NULL,
  `strShortListTitle` varchar(80) default NULL,
  `strTextLink` varchar(100) default NULL,
  `strType` varchar(100) default 'content',
  `strExtraLink` varchar(255) default NULL,
  `strTimeStamp` timestamp NOT NULL,
  `dtTimeStamp` timestamp NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`intContentID`)
) TYPE=MyISAM;

A snip from the output of a dump from the same database using mysql-4.1 shows:

DROP TABLE IF EXISTS `tblContents`;
CREATE TABLE `tblContents` (
  `intContentID` int(11) NOT NULL auto_increment,
  `strTitle` varchar(150) default NULL,
  `chrStatus` char(3) default NULL,
  `intParent` int(11) default NULL,
  `intOrder` int(11) default NULL,
  `strGroups` text,
  `dtPosted` datetime default NULL,
  `strAccess` text,
  `dtActivate` datetime default NULL,
  `dtDeactivate` datetime default NULL,
  `intShortList` int(11) default NULL,
  `strShortListTitle` varchar(80) default NULL,
  `strTextLink` varchar(100) default NULL,
  `strType` varchar(100) default 'content',
  `strExtraLink` varchar(255) default NULL,
  `strTimeStamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `dtTimeStamp` timestamp NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`intContentID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Notice the follow:

intContentID has no auto_increment option in test.sql output.
strTimeStamp is different
there is no DEFAULT CHARSET=latin1 in the test.sql output.

Version-Release number of selected component (if applicable):

mysqlclient14-4.1.14-1
mysqlclient10-3.23.58-6
mysql-5.0.15-2
mysql-server-5.0.15-2
[15 Nov 2005 7:16] Hartmut Holzgraefe
What are the command line options you are calling mysqldump with?
And are there any mysqldump specific options set in your /etc/my.cnf file?

This looks like a dump created with one of the --compat modes being active to me ...
[15 Nov 2005 9:36] Rodd Clarkson
Sheesh, Mea Culpa on the bad bug report.

/etc/my.cnf contains:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

[mysql.server]
user=mysql
basedir=/var/lib

[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

The command for the dump was:

mysqldump --add-drop-table --compatible=mysql40 -u root -p pancakeday2

I needed it to be compatible with a mysql-4.x database on White Box Linux.
[15 Nov 2005 11:35] Valeriy Kravchuk
Sorry, but it is not a bug. It is a documented behaviour. Read the manual (http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html) carefully:

"MYSQL40

Equivalent to NO_FIELD_OPTIONS, HIGH_NOT_PRECEDENCE."

And, before that:

"NO_FIELD_OPTIONS

Don't print MySQL-specific column options in the output of SHOW CREATE TABLE. This mode is used by mysqldump in portability mode."

So, that is what you explicitely asked for.
[16 Nov 2005 0:39] Rodd Clarkson
Right, well it may be 'not a bug' but it's remarkably different to using the mysql323 flag which will produce an output that results in a working database under mysql-3.23

Interestingly (having checked the docs on this for 4.1) which implies the same behaviour for mysql323

MYSQL323

Equivalent to NO_FIELD_OPTIONS, HIGH_NOT_PRECEDENCE. 

but infact, when you mysqldump --compatible=mysql323 from mysql-4.1 you get the auto_increment flag in the CREATE clause.

I'd really argue that this is broken behaviour.  If all I've done is asked for a mysql323 or mysql40 compatible database I would expect it to work out of the box.  If this isn't to be the case, then I would expect 'man mysqldump' to make this very very very clear, instead of having to look up a technical reference to discover implied behaviour (in the man pages) isn't implied and that the database dump you're producing will NOT work as expected.

'nough said.
[13 Dec 2005 0:59] Timothy Smith
I'm marking this as a duplicate of bug 15208, instead of "Not a bug", as further discussion has happened there.
[29 Apr 2009 6:20] Saghir Taj
use --All switch