List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:February 3 2001 6:57pm
Subject:Re: bug? in OPTIMIZE TABLE command
View as plain text  
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, ace@stripped 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 <mysql-thread64072@stripped>
>To unsubscribe, e-mail <mysql-unsubscribe-paul=snake.net@stripped>
>Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php


-- 
Paul DuBois, paul@stripped
Thread
bug? in OPTIMIZE TABLE commandace3 Feb
  • Re: bug? in OPTIMIZE TABLE commandPaul DuBois3 Feb