Jorrit, it's a known behavior, not a bug. Recent versions of MySQL will, when given a zero (0) as a value for an auto incrementing identity column, simply fill in the next auto incrementing value ... unless you flip a switch to specifically tell it not to do that ... see: http://dev.mysql.com/doc/refman/4.1/en/server-sql-mode.html and look for NO_AUTO_VALUE_ON_ZERO
HTH, Dan On 9/26/06, Jorrit Kronjee <[EMAIL PROTECTED]> wrote:
Dear list, I discovered something that seems to be odd behaviour. I have a basic table with one column set to auto_increment: mysql> DESCRIBE basic_table; +---------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------------+-------------+------+-----+---------+----------------+ | Id | int(11) | | PRI | NULL | auto_increment | | simple_column | varchar(11) | | | | | +---------------+-------------+------+-----+---------+----------------+ 2 rows in set (0.00 sec) One of the values of `Id' was later changed from 1 to 0, resulting in the following output. mysql> SELECT * FROM basic_table; +----+---------------+ | Id | simple_column | +----+---------------+ | 0 | foo | | 2 | bar | +----+---------------+ 2 rows in set (0.00 sec) Now if I create a dump of the table, I get the following queries (and a few more uninteresting locking queries, which I won't paste here): CREATE TABLE `basic_table` ( `Id` int(11) NOT NULL auto_increment, `simple_column` varchar(11) NOT NULL default '', PRIMARY KEY (`Id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; INSERT INTO `basic_table` VALUES (0,'foo'),(2,'bar'); If I run this dump from the MySQL CLI with '\. dump.sql' I get the same table as with the previous SELECT. However, if I run these two commands from the dump directly on the mysql CLI, the table looks as follows: mysql> SELECT * FROM basic_table; +----+---------------+ | Id | simple_column | +----+---------------+ | 1 | foo | | 2 | bar | +----+---------------+ 2 rows in set (0.00 sec) Apparently MySQL thought I didn't know a Id-value for the row with 'foo' (because it was zero), so it made one up. This is kind of odd, because a dump query should always generate the same dataset. Am I doing anything wrong? Is this a known bug? - Jorrit P.S. For this test, I've used MySQL version 4.1.21-log. -- System Developer Infopact Network Solutions Hoogvlietsekerkweg 170 3194 AM Rotterdam Hoogvliet tel. +31 (0)88 - 4636700 fax. +31 (0)88 - 4636799 mob. +31 (0)6 - 14105968 [EMAIL PROTECTED] http://www.infopact.nl/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
