>Description:
create table ... with a select statement at the end seems to think
there are duplicate entries:
>How-To-Repeat:
mysql> create table nr_current (id int unsigned not null primary key) TYPE=HEAP
> select distinct protein_id from nr where current = 1;
ERROR 1062: Duplicate entry '0' for key 1
mysql> select * from nr where protein_id = 0;
Empty set (0.02 sec)
[ same result with TYPE=MyISAM ]
>Fix:
mysql> create table nr_current (id int unsigned not null primary key) type=HEAP;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into nr_current (id) select distinct protein_id from nr where current =
1;
Query OK, 693878 rows affected (52.62 sec)
Records: 693878 Duplicates: 0 Warnings: 0
>Submitter-Id: <submitter ID>
>Originator: Aaron J. Mackey
>Organization:
University of Virginia
>MySQL support: none
>Synopsis: erroneous duplicate row error on create table ... select
>Severity: non-critical
>Priority: low
>Category: mysql
>Class: sw-bug
>Release: mysql-3.23.36 (Official MySQL RPM)
>Server: /usr/bin/mysqladmin Ver 8.18 Distrib 3.23.36, 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.36
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/lib/mysql/mysql.sock
Uptime: 20 min 47 sec
Threads: 2 Questions: 56 Slow queries: 6 Opens: 42 Flush tables: 1 Open tables:
19 Queries per second avg: 0.045
>Environment:
System: Linux wrpx00.bioch.virginia.edu 2.2.14-5.0smp #1 SMP Tue Mar 7 21:01:40 EST
2000 i686 unknown
Architecture: i686
Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/egcs-2.91.66/specs
gcc version egcs-2.91.66 19990314/Linux (egcs-1.1.2 release)
Compilation info: CC='egcs' CFLAGS='-O6 -fomit-frame-pointer -mpentium' CXX='egcs'
CXXFLAGS='-O6 -fomit-frame-pointer -felide-constructors
-fno-exceptions -fno-rtti -mpentium' LDFLAGS=''
LIBC:
lrwxrwxrwx 1 root root 13 Mar 30 08:54 /lib/libc.so.6 -> libc-2.1.3.so
-rwxr-xr-x 1 root root 4101324 Feb 29 2000 /lib/libc-2.1.3.so
-rw-r--r-- 1 root root 20272704 Feb 29 2000 /usr/lib/libc.a
-rw-r--r-- 1 root root 178 Feb 29 2000 /usr/lib/libc.so
Configure command: ./configure --disable-shared --with-mysqld-ldflags=-all-static
--with-client-ldflags=-all-static --enable-assembler --with-mysqld-user=mysql
--with-unix-socket-path=/var/lib/mysql/mysql.sock --prefix=/
--with-extra-charsets=complex --exec-prefix=/usr --libexecdir=/usr/sbin
--sysconfdir=/etc --datadir=/usr/share --localstatedir=/var/lib/mysql
--infodir=/usr/info --includedir=/usr/include --mandir=/usr/man --without-berkeley-db
--without-innobase '--with-comment=Official MySQL RPM'
---------------------------------------------------------------------
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
