>Description:
When altering a tables columns, MySQL doesn't check if the
current data in these columns fits into the new options.
Although dumping such a table works, restore fails.
>How-To-Repeat:
Create a table 'test' with a column 'id' and do as following:
insert into test (id) values(0);
insert into test (id) values(0);
insert into test (id) values(1);
So, three rows exist in this table with values 0,0,1.
Now alter the column id to be unique and autoincrement.
MySQL makes the changes, although the data doesn't suit
the new options.
Now dump the table (mysqldump --opt test > test.sql) and
try to restore the table to a new database (mysql database < test.sql).
Since the table is created with full identities (unique, autoincrement),
insert into test (id) values(0);
insert into test (id) values(0);
insert into test (id) values(1);
fails with a 'duplicate key' problem.
When only using unique without autoincrement, the second insert fails
(for a duplicate key).
When using autoincrement and unique, the first 0 has been incremented
to 1, the second to 2 and the third insert for 1 gives the
'duplicate key' problem. Beside this, the new data doesn't match
the original data.
A 'alter table test type=$newtype' also fails with 'duplicate key'
(since data is being copied to a new table with new options).
>Fix:
"Don't alter table data which doesn't match the options" would
be the real solution for newly created databases/tables.
"Change mysqldump to create table, insert data and afterwards alter
the table options" would be another option (which still allows
such syntactically wrong databases). Quite a few users here have
created such tables, fixing them includes explaining the problem
to each user - argh.
Including both fixing options might be the best for the current
situations :-)
>Submitter-Id: <submitter ID>
>Originator: Anders Henke <[EMAIL PROTECTED]>
>Organization: Schlund+Partner AG
>MySQL support: none
>Synopsis: Altering Data with unmatching options doesn't fail
>Severity: serious
>Priority: medium
>Category: mysql
>Class: sw-bug
>Release: mysql-3.23.43 (Source distribution)
>Server: /usr/bin/mysqladmin Ver 8.21 Distrib 3.23.43, for pc-linux-gnu on i686
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license
Server version 3.23.43-Max-log
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/run/mysqld/mysqld.sock
Uptime: 9 hours 51 min 22 sec
Threads: 4 Questions: 1590920 Slow queries: 238 Opens: 253874 Flush tables: 2
Open tables: 64 Queries per second avg: 44.837
>Environment:
System: Linux rdb6 2.4.10 #1 SMP Thu Oct 4 14:29:57 CEST 2001 i686 unknown
Architecture: i686
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='-O6 -fomit-frame-pointer' CXX='gcc'
CXXFLAGS='-O6 -fomit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti'
LDFLAGS='-static'
LIBC:
lrwxrwxrwx 1 root root 13 Jun 7 19:47 /lib/libc.so.6 -> libc-2.1.3.so
-rwxr-xr-x 1 root root 888192 Jun 9 17:39 /lib/libc-2.1.3.so
-rw-r--r-- 1 root root 2090160 Jun 9 17:40 /usr/lib/libc.a
-rw-r--r-- 1 root root 178 Jun 9 17:40 /usr/lib/libc.so
Configure command: ./configure --prefix=/usr --libexecdir=/usr/sbin
--localstatedir=/var/mysql/data --enable-shared --without-perl --without-readline
--without-docs --without-bench --with-mysqld-user=mysql --with-extra-charsets=all
--enable-assembler --with-raid --with-mysqld-ldflags=-all-static
--with-client-ldflags=-all-static --with-charset=latin1 --with-bench
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
