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
