Table handler error with self-join multi-table DELETE
am 13.09.2004 15:56:36 von Aaron Brown>Description:
When doing a two-table delete where the two tables are one
table joined to itself, I get the error
ERROR 1030: Got error 134 from table handler
when I delete (out of two matching rows) the one with the
higher id field rather than the lower. Deleting the higher
rather than the lower ID doesn't always cause the error,
but deleting the lower rather than the higher seems to
dependably avoid it.
I have not tried this on version 4.1. It looks similar to
Bug #374, which, however, I was not able to duplicate.
>How-To-Repeat:
-- A demonstration of bug with a multiple-table delete with
-- a self-join. mysql --version gives:
-- mysql Ver 12.21 Distrib 4.0.14, for pc-linux (i686)
DROP DATABASE IF EXISTS MultiTableDeleteDemoTemp;
CREATE DATABASE MultiTableDeleteDemoTemp;
USE MultiTableDeleteDemoTemp;
CREATE TABLE t (
id int not null auto_increment PRIMARY KEY,
str char(32));
-- This two-row dataset is the smallest I could find that
-- demonstrated the problem for me. Most but not all
-- datasets with duplicates are vulnerable to the problem.
-- (For instance, "('pineapple'), ('banana'), ('pear'),
-- ('apple'), ('apple')" works fine for me.)
INSERT INTO t (str)
VALUES ('apple'),
('apple');
-- Display duplicates:
SELECT *
FROM t, t AS t2
WHERE t.str = t2.str
AND t.id > t2.id;
-- If < is used, things work fine -- if > is used, the error
-- message
-- ERROR 1030: Got error 134 from table handler
-- is issued. This is presumably because it's assuming two
-- different tables rather than one aliased one.
DELETE t
FROM t, t AS t2
WHERE t.str = t2.str
AND t.id > t2.id;
-- Display duplicates again -- should be none, but may be
-- more depending on how many duplicates were in the
-- original dataset and how far the DELETE got before
-- erroring out:
SELECT *
FROM t, t AS t2
WHERE t.str = t2.str
AND t.id > t2.id;
>Fix:
As noted above, deleting rows that were inserted later
rather than earlier seems to be a workaround.
>Originator: Aaron Brown
>Organization:
Oak Adaptive
>MySQL support: none
>Synopsis: Table handler error w/ self-join DELETE; depends on order of deletion
>Category: mysql
>Class: sw-bug
>Release: mysql-4.0.14-standard (Official MySQL-standard binary)
>Server: /usr/local/bin/mysqladmin Ver 8.40 Distrib 4.0.14, for pc-linux 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 4.0.14-standard-log
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /tmp/mysql.sock
Uptime: 3 days 16 hours 23 min 0 sec
Threads: 5 Questions: 224619 Slow queries: 24 Opens: 21889 Flush tables: 1
Open tables: 64 Queries per second avg: 0.706
>C compiler: 2.95.3
>C++ compiler: 2.95.3
>Environment:
System: Linux PINE 2.4.20-28.9 #1 Thu Dec 18 13:45:22 EST 2003 i686 i686 i386
GNU/Linux
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/3.2.2/specs
Configured with:
.../configure --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info --en
able-shared --enable-threads=posix --disable-checking --with-system-zlib --enable
-__cxa_atexit --host=i386-redhat-linux
Thread model: posix
gcc version 3.2.2 20030222 (Red Hat Linux 3.2.2-5)
Compilation info: CC='gcc' CFLAGS='-O2 -mcpu=pentiumpro' CXX='gcc'
CXXFLAGS='-O2 -mcpu=pentiumpro -felide-constructors' LDFLAGS='' ASFLAGS=''
LIBC:
lrwxrwxrwx 1 root root 13 Jan 30 2004 /lib/libc.so.6 ->
libc-2.3.2.so
-rwxr-xr-x 1 root root 1561228 Nov 12 2003 /lib/libc-2.3.2.so
-rw-r--r-- 1 root root 2332200 Nov 12 2003 /usr/lib/libc.a
-rw-r--r-- 1 root root 204 Nov 12 2003 /usr/lib/libc.so
Configure command: ./configure '--prefix=/usr/local/mysql'
'--localstatedir=/usr/local/mysql/data' '--libexecdir=/usr/local/mysql/bin'
'--with-comment=Official MySQL-standard binary' '--with-extra-charsets=complex'
'--with-server-suffix=-standard' '--enable-thread-safe-client'
'--enable-local-infile' '--enable-assembler' '--disable-shared'
'--with-client-ldflags=-all-static' '--with-mysqld-ldflags=-all-static'
'--with-innodb' 'CFLAGS=-O2 -mcpu=pentiumpro'
'CXXFLAGS=-O2 -mcpu=pentiumpro -felide-constructors' 'CXX=gcc'
--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org