AUTO_INCREMENT columns are for positive integers. You're trying to use
the column for an unintended use, so it's not surprising that there are
unintended consequences.
At 6:43 PM +0100 2/3/01, [EMAIL PROTECTED] wrote:
>Description:
> If I use a table containing AUTO_INCREMENT field, and I have there
> a 0 value, OPTIMIZE TABLE will fail.
>How-To-Repeat:
> CREATE TABLE test(
> ID DECIMAL(5,0) NOT NULL AUTO_INCREMENT,
> text CHAR(20) NOT NULL,
> PRIMARY KEY(ID)); // or INDEX(ID))
>
> INSERT INTO test(text) VALUES('sample');
> UPDATE test SET ID=0 WHERE ID=1;
> INSERT INTO test(text) VALUES('sample-2');
> SELECT * FROM test;
>
> ID text
> -----------------
> 0 sample
> 1 sample-2
>
> OPTIMIZE TABLE; //ERROR: 1062
>
> If I used INDEX(ID) not PRIMARY KEY(ID), OPTIMIZE TABLE doesn't
> fail, but data will be corrupted:
>
> SELECT * FROM test;
>
> ID text
> -----------------
> 1 sample !!
> 1 sample-2 !!
>
>Fix:
> I don't use optimize table, but PLEASE don't make inpossible to
> insert 0 value to an AUTO_INCREMENT field!
>
>>Submitter-Id: <submitter ID>
>>Originator: Nemeth Istvan
>Organization: JAK
> <organization of PR author (multiple lines)>
>MySQL support: none
>Synopsis: problem with OPTIMIZE TABLE
>Severity: non-critical
>Priority: medium
>Category: mysql
>Class: sw-bug
>Release: mysql-3.22.32 (Source distribution)
>Server: /usr/bin/mysqladmin Ver 8.0 Distrib 3.22.32, for pc-linux-gnu on i586
>TCX Datakonsult AB, by Monty
>
>Server version 3.22.32-log
>Protocol version 10
>Connection Localhost via UNIX socket
>UNIX socket /var/run/mysqld/mysqld.sock
>Uptime: 51 days 7 hours 33 min 43 sec
>
>Threads: 1 Questions: 16086 Slow queries: 11 Opens: 104 Flush
>tables: 4 Open tables: 13
>Environment:
> AMD K6-2 450, 192M RAM, 30G HDD, Debian Linux 2.2
>System: Linux linux 2.2.17 #3 Tue Dec 5 16:13:05 CET 2000 i586 unknown
>Architecture: i586
>
>Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gcc /usr/bin/cc
>GCC: Reading specs from /usr/lib/gcc-lib/i386-linux/2.95.2/specs
>gcc version 2.95.2 20000220 (Debian GNU/Linux)
>Compilation info: CC='gcc' CFLAGS='-O2 -fomit-frame-pointer'
>CXX='g++' CXXFLAGS='-O2 -fomit-frame-pointer -felide-constructors
>-fno-exceptions -fno-rtti' LDFLAGS=''
>Configure command: ./configure --enable-shared --without-readline
>--enable-assembler --with-mysqld-user=mysql
>--with-unix-socket-path=/var/run/mysqld/mysqld.sock --prefix=/usr
>--exec-prefix=/usr --libexecdir=/usr/sbin --sysconfdir=/etc/mysql
>--datadir=/usr/share --localstatedir=/var/lib/mysql
>--infodir=/usr/share/info --includedir=/usr/include
>--mandir=/usr/share/man
>Perl: This is perl, version 5.005_03 built for i386-linux
>
>---------------------------------------------------------------------
>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
--
Paul DuBois, [EMAIL PROTECTED]
---------------------------------------------------------------------
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
