>Description:
Say we have column named id int NOT NULL AUTO_INCREMENT
Say we have record, where id=0 (why not?)
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 ** [EMAIL PROTECTED] **
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 <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
