List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:November 23 2001 8:23pm
Subject:Re: [BUG: mysqldump incorrectly generates INSERTs for
AUTO_INCREMENT columns]
View as plain text  
At 10:38 AM +0000 11/23/01, dima@stripped wrote:
>  >Description:
>  Say we have column named id int NOT NULL AUTO_INCREMENT
>  Say we have record, where id=0 (why not?)

Because AUTO_INCREMENT columns are for storing sequences of postive numbers.
Not negative numbers, and not zero.

Your problems stem from using a column contrary to the way it's intended
to be used.

>  Say we used mysqldump to  backup our table.
>  One day we tryied to restore our table, but we will never get record
>  where id=0 back, becouse rule for AUTO_INCREMENT rows will apply. So
>  insert into table set id=0; will be treated as subject to apply next
>  value for AUTO_INCREMENT row. And we never get id=0 record restored.
>>How-To-Repeat:
>shell> mysql test
>
>CREATE TABLE Products (
>id int  unsigned NOT NULL auto_increment,
>PRIMARY KEY (id)
>) TYPE=MyISAM;
>
>INSERT INTO Products Values(0);
>INSERT INTO Products Values(0);
>INSERT INTO Products Values(0);
>INSERT INTO Products Values(0);
>INSERT INTO Products Values(0);
>UPDATE Products set id=0 where id=3;
>
>quit
>
>shell> mysqldump test Products > /tmp/dmp
>shell> echo 'drop table Products;' | mysql test
>shell> mysql test < /tmp/dmp
>shell> mysql test
>mysql> select * from Products where id=0;
>Empty set (0.02 sec)
>>Fix:
>  1. Patch for mysqldump utility required. So when Zero (or NULL) value found
>     in AUTO_INCREMENT source field, additional command emitted to the dump.
>     Like
>     Update Table set column = 0 where .......(depends on table spec).....;
>  2. If patch is not important from your point of view, please add a note to
>     the documentation for those who want to have Zero values in AUTO_INCREMENT
>     fields about side effect of mysqldump utility.
>
>>Submitter-Id:	<submitter ID>
>>Originator:	Dmitry Dorofeev
>>Organization:
>   Deem0N                    ** dima@stripped **
>   Natural born YASPer       http://www.yasp.com/
>   Just Another Perl Monger  http://spb.pm.org/
>   R U X Y founder           http://www.ruxy.org.ru/
>>
>>MySQL support: [none]
>>Synopsis:	<mysqldump does not generate correct dump for 
>>AUTO_INCREMENT columns>  
>>Severity:
>>Priority:
>>Category:	mysql
>>Class:
>>Release:	mysql-3.23.41 (Source distribution)
>
>>Environment:
>
>System: FreeBSD rux 4.4-STABLE FreeBSD 4.4-STABLE #0: Wed Nov 14 
>17:26:30 MSK 2001     butthead@rux:/usr/src/sys/compile/RUX40  i386
>
>
>Some paths:  /usr/bin/perl /usr/bin/make /usr/local/bin/gmake 
>/usr/bin/gcc /usr/bin/cc
>GCC: Using builtin specs.
>gcc version 2.95.3 20010315 (release) [FreeBSD]
>Compilation info: CC='cc'  CFLAGS='-O -pipe '  CXX='c++' 
>CXXFLAGS='-O -pipe  -felide-constructors -fno-rtti -fno-exceptions' 
>LDFLAGS=''
>LIBC:
>-r--r--r--  1 root  wheel  1205812 31 ¦À' 14:52 /usr/lib/libc.a
>lrwxr-xr-x  1 root  wheel  9 31 ¦À' 14:52 /usr/lib/libc.so ->
> libc.so.4
>-r--r--r--  1 root  wheel  520516 15 ¡-"  2000 /usr/lib/libc.so.3
>-r--r--r--  1 root  wheel  573100 31 ¦À' 14:52 /usr/lib/libc.so.4
>Configure command: ./configure  --localstatedir=/var/db/mysql 
>--without-perl --without-debug --without-readline --without-bench 
>--with-mit-threads=no --with-libwrap --with-low-memory 
>--enable-assembler --with-berkeley-db --prefix=/usr/local 
>i386--freebsd4.4
>Perl: This is perl, version 5.005_03 built for i386-freebsd
>
>---------------------------------------------------------------------
>Before posting, please check:
>    http://www.mysql.com/manual.php   (the manual)
>    http://lists.mysql.com/           (the list archive)
>
>To request this thread, e-mail <mysql-thread92031@stripped>
>To unsubscribe, e-mail <mysql-unsubscribe-paul=snake.net@stripped>
>Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Thread
[BUG: mysqldump incorrectly generates INSERTs for AUTO_INCREMENT columns]dima23 Nov
  • MYSQL 4.0 in production?johnlucas-Arluna23 Nov
    • Re: MYSQL 4.0 in production?Sinisa Milivojevic23 Nov
  • Re: [BUG: mysqldump incorrectly generates INSERTs forAUTO_INCREMENT columns]Paul DuBois23 Nov