Re: odd behaviour with auto_increment
On 9/26/2006 4:02 PM, Dan Buettner wrote: 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 Dan, That explains it! I see the NO_AUTO_VALUE_ON_ZERO in the dump as well. Thanks! Jorrit -- 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]
odd behaviour with auto_increment
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]
Re: odd behaviour with auto_increment
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]
