| 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: | |
| 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
[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
