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]