At 10:38 AM +0000 11/23/01, [EMAIL PROTECTED] 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 ** [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 --------------------------------------------------------------------- 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
