Description:
Hi,
We found what appears to be a mysql bug. Assuming that we have found the true cause that triggers the bug, then it is easily worked around. However, it took some time to find the trigger. Until we found the trigger, we assumed that there was something seriously wrong with mysql.
Basically, we often manage the environment of mysql by setting variables like
##############################
...
APP_INSTALL=/SOME/PATH
export APP_INSTALL
TMPDIR=$APP_INSTALL/temp
export TMPDIR
....
#start mysql server
##############################
We found that when TMPDIR resembled /SOME/PATH//temp, i.e. it has an extra /, then certain queries in mysql would either fail (4.1.9) or return strange results (4.0.22). I have included a query and sample data that reproduces the problem for us.
In 4.1.9, we would get the following error.
mysql> select term_topics.term_topic_name, term_topics.term_topic_id, term_topics.term_topic_owner from term_topics LEFT JOIN term_folder_members ON term_topics.term_topic_id = term_folder_members.term_topic_id WHERE term_folder_members.term_folder_id = 3 order by term_topic_name;
ERROR 1030 (HY000): Got error 1 from storage engine
mysql>
We saw this error on Linux and Solaris systems. I installed a binary debug build of mysql on a Linux system to verify that the problem was not with the way we build mysql. This problem would go away if you removed the extra slash from TMPDIR and restarted mysql.
In 4.0.22 on a Solaris machine, we noticed that this query would return the correct 2 rows on the first run, but running the query again would return 4 rows, again 6 rows, again 8 rows, etc. We did not try to debug this problem further, as we had already upgraded to 4.1.9. It was also fixed by removing the extra slashes from TMPDIR.
Please let me know if you need any additional information. FWIW, I did look through your current bugs, but I did not find anything related to this.
Thanks,
Roma Radford
Query
=========
select term_topics.term_topic_name, term_topics.term_topic_id, term_topics.term_topic_owner from term_topics LEFT JOIN term_folder_members ON term_topics.term_topic_id = term_folder_members.term_topic_id WHERE term_folder_members.term_folder_id = 3 order by term_topic_name;
Sample Data
===========
USE test;
## Create and initialize the "term_folder_members" table.
## This contains all the information needed to link the topics with the correct folder
CREATE TABLE term_folder_members (
term_folder_id INTEGER DEFAULT 0 NOT NULL,
term_topic_id INTEGER DEFAULT 0 NOT NULL,
INDEX(term_folder_id),
INDEX(term_topic_id));
## Create and initialize the "term_topics" table.
## This contains all the topics used by the alias stuff
CREATE TABLE term_topics (
term_topic_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
term_topic_name VARCHAR(255) DEFAULT NULL,
term_topic_name_upper VARCHAR(255) DEFAULT NULL,
term_topic_owner VARCHAR(50) DEFAULT NULL,
INDEX( term_topic_name_upper ),
PRIMARY KEY( term_topic_id ) );
INSERT INTO term_topics VALUES ( NULL, 'drugs', 'DRUGS', 'demo_a' );
INSERT INTO term_topics VALUES ( NULL, 'terror', 'TERROR', 'demo_a' );
INSERT INTO term_folder_members VALUES ( 3, 1 );
INSERT INTO term_folder_members VALUES ( 3, 2 );
Relevant info from mysqlbug
===========================
>Release: mysql-4.1.9-debug (Official MySQL-debug binary)
>C compiler: 2.95.3
>C++ compiler: 2.95.3
>Environment:
<machine, os, target, libraries (multiple lines)>
System: Linux scrat 2.4.20-28.7 #1 Thu Dec 18 11:23:36 EST 2003 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/2.96/specs
gcc version 2.96 20000731 (Red Hat Linux 7.3 2.96-110)
Compilation info: CC='gcc' CFLAGS='-O2 -mcpu=pentiumpro' CXX='gcc' CXXFLAGS='-O2 -mcpu=pentiumpro -felide-constructors' LDFLAGS='' ASFLAGS=''
LIBC:
lrwxrwxrwx 1 root root 13 Apr 7 2004 /lib/libc.so.6 -> libc-2.2.5.so
-rwxr-xr-x 1 root root 1260480 Apr 15 2002 /lib/libc-2.2.5.so
-rw-r--r-- 1 root root 2310808 Apr 15 2002 /usr/lib/libc.a
-rw-r--r-- 1 root root 178 Apr 15 2002 /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-debug binary' '--with-extra-charsets=complex' '--with-server-suffix=-debug' '--enable-thread-safe-client' '--enable-local-infile' '--enable-assembler' '--disable-shared' '--with-client-ldflags=-all-static' '--with-debug' '--with-mysqld-ldflags=-all-static' '--with-readline' '--with-embedded-server' '--with-innodb' 'CFLAGS=-O2 -mcpu=pentiumpro' 'CPPFLAGS=-DDEFAULT_THREAD_STACK=126976' 'CXXFLAGS=-O2 -mcpu=pentiumpro -felide-constructors' 'CXX=gcc'
How to repeat:
Environment
=========
Set TMPDIR to have an extra slash such as /usr/local/mysql/temp. You will need to ensure that the mysql user can write to this directory. Restart the mysql server.
Query
=========
select term_topics.term_topic_name, term_topics.term_topic_id, term_topics.term_topic_owner from term_topics LEFT JOIN term_folder_members ON term_topics.term_topic_id = term_folder_members.term_topic_id WHERE term_folder_members.term_folder_id = 3 order by term_topic_name;
Sample Data
===========
USE test;
## Create and initialize the "term_folder_members" table.
## This contains all the information needed to link the topics with the correct folder
CREATE TABLE term_folder_members (
term_folder_id INTEGER DEFAULT 0 NOT NULL,
term_topic_id INTEGER DEFAULT 0 NOT NULL,
INDEX(term_folder_id),
INDEX(term_topic_id));
## Create and initialize the "term_topics" table.
## This contains all the topics used by the alias stuff
CREATE TABLE term_topics (
term_topic_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
term_topic_name VARCHAR(255) DEFAULT NULL,
term_topic_name_upper VARCHAR(255) DEFAULT NULL,
term_topic_owner VARCHAR(50) DEFAULT NULL,
INDEX( term_topic_name_upper ),
PRIMARY KEY( term_topic_id ) );
INSERT INTO term_topics VALUES ( NULL, 'drugs', 'DRUGS', 'demo_a' );
INSERT INTO term_topics VALUES ( NULL, 'terror', 'TERROR', 'demo_a' );
INSERT INTO term_folder_members VALUES ( 3, 1 );
INSERT INTO term_folder_members VALUES ( 3, 2 );
Relevant info from mysqlbug
===========================
>Release: mysql-4.1.9-debug (Official MySQL-debug binary)
>C compiler: 2.95.3
>C++ compiler: 2.95.3
>Environment:
<machine, os, target, libraries (multiple lines)>
System: Linux scrat 2.4.20-28.7 #1 Thu Dec 18 11:23:36 EST 2003 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/2.96/specs
gcc version 2.96 20000731 (Red Hat Linux 7.3 2.96-110)
Compilation info: CC='gcc' CFLAGS='-O2 -mcpu=pentiumpro' CXX='gcc' CXXFLAGS='-O2 -mcpu=pentiumpro -felide-constructors' LDFLAGS='' ASFLAGS=''
LIBC:
lrwxrwxrwx 1 root root 13 Apr 7 2004 /lib/libc.so.6 -> libc-2.2.5.so
-rwxr-xr-x 1 root root 1260480 Apr 15 2002 /lib/libc-2.2.5.so
-rw-r--r-- 1 root root 2310808 Apr 15 2002 /usr/lib/libc.a
-rw-r--r-- 1 root root 178 Apr 15 2002 /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-debug binary' '--with-extra-charsets=complex' '--with-server-suffix=-debug' '--enable-thread-safe-client' '--enable-local-infile' '--enable-assembler' '--disable-shared' '--with-client-ldflags=-all-static' '--with-debug' '--with-mysqld-ldflags=-all-static' '--with-readline' '--with-embedded-server' '--with-innodb' 'CFLAGS=-O2 -mcpu=pentiumpro' 'CPPFLAGS=-DDEFAULT_THREAD_STACK=126976' 'CXXFLAGS=-O2 -mcpu=pentiumpro -felide-constructors' 'CXX=gcc'
Suggested fix:
Description: Hi, We found what appears to be a mysql bug. Assuming that we have found the true cause that triggers the bug, then it is easily worked around. However, it took some time to find the trigger. Until we found the trigger, we assumed that there was something seriously wrong with mysql. Basically, we often manage the environment of mysql by setting variables like ############################## ... APP_INSTALL=/SOME/PATH export APP_INSTALL TMPDIR=$APP_INSTALL/temp export TMPDIR .... #start mysql server ############################## We found that when TMPDIR resembled /SOME/PATH//temp, i.e. it has an extra /, then certain queries in mysql would either fail (4.1.9) or return strange results (4.0.22). I have included a query and sample data that reproduces the problem for us. In 4.1.9, we would get the following error. mysql> select term_topics.term_topic_name, term_topics.term_topic_id, term_topics.term_topic_owner from term_topics LEFT JOIN term_folder_members ON term_topics.term_topic_id = term_folder_members.term_topic_id WHERE term_folder_members.term_folder_id = 3 order by term_topic_name; ERROR 1030 (HY000): Got error 1 from storage engine mysql> We saw this error on Linux and Solaris systems. I installed a binary debug build of mysql on a Linux system to verify that the problem was not with the way we build mysql. This problem would go away if you removed the extra slash from TMPDIR and restarted mysql. In 4.0.22 on a Solaris machine, we noticed that this query would return the correct 2 rows on the first run, but running the query again would return 4 rows, again 6 rows, again 8 rows, etc. We did not try to debug this problem further, as we had already upgraded to 4.1.9. It was also fixed by removing the extra slashes from TMPDIR. Please let me know if you need any additional information. FWIW, I did look through your current bugs, but I did not find anything related to this. Thanks, Roma Radford Query ========= select term_topics.term_topic_name, term_topics.term_topic_id, term_topics.term_topic_owner from term_topics LEFT JOIN term_folder_members ON term_topics.term_topic_id = term_folder_members.term_topic_id WHERE term_folder_members.term_folder_id = 3 order by term_topic_name; Sample Data =========== USE test; ## Create and initialize the "term_folder_members" table. ## This contains all the information needed to link the topics with the correct folder CREATE TABLE term_folder_members ( term_folder_id INTEGER DEFAULT 0 NOT NULL, term_topic_id INTEGER DEFAULT 0 NOT NULL, INDEX(term_folder_id), INDEX(term_topic_id)); ## Create and initialize the "term_topics" table. ## This contains all the topics used by the alias stuff CREATE TABLE term_topics ( term_topic_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, term_topic_name VARCHAR(255) DEFAULT NULL, term_topic_name_upper VARCHAR(255) DEFAULT NULL, term_topic_owner VARCHAR(50) DEFAULT NULL, INDEX( term_topic_name_upper ), PRIMARY KEY( term_topic_id ) ); INSERT INTO term_topics VALUES ( NULL, 'drugs', 'DRUGS', 'demo_a' ); INSERT INTO term_topics VALUES ( NULL, 'terror', 'TERROR', 'demo_a' ); INSERT INTO term_folder_members VALUES ( 3, 1 ); INSERT INTO term_folder_members VALUES ( 3, 2 ); Relevant info from mysqlbug =========================== >Release: mysql-4.1.9-debug (Official MySQL-debug binary) >C compiler: 2.95.3 >C++ compiler: 2.95.3 >Environment: <machine, os, target, libraries (multiple lines)> System: Linux scrat 2.4.20-28.7 #1 Thu Dec 18 11:23:36 EST 2003 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/2.96/specs gcc version 2.96 20000731 (Red Hat Linux 7.3 2.96-110) Compilation info: CC='gcc' CFLAGS='-O2 -mcpu=pentiumpro' CXX='gcc' CXXFLAGS='-O2 -mcpu=pentiumpro -felide-constructors' LDFLAGS='' ASFLAGS='' LIBC: lrwxrwxrwx 1 root root 13 Apr 7 2004 /lib/libc.so.6 -> libc-2.2.5.so -rwxr-xr-x 1 root root 1260480 Apr 15 2002 /lib/libc-2.2.5.so -rw-r--r-- 1 root root 2310808 Apr 15 2002 /usr/lib/libc.a -rw-r--r-- 1 root root 178 Apr 15 2002 /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-debug binary' '--with-extra-charsets=complex' '--with-server-suffix=-debug' '--enable-thread-safe-client' '--enable-local-infile' '--enable-assembler' '--disable-shared' '--with-client-ldflags=-all-static' '--with-debug' '--with-mysqld-ldflags=-all-static' '--with-readline' '--with-embedded-server' '--with-innodb' 'CFLAGS=-O2 -mcpu=pentiumpro' 'CPPFLAGS=-DDEFAULT_THREAD_STACK=126976' 'CXXFLAGS=-O2 -mcpu=pentiumpro -felide-constructors' 'CXX=gcc' How to repeat: Environment ========= Set TMPDIR to have an extra slash such as /usr/local/mysql/temp. You will need to ensure that the mysql user can write to this directory. Restart the mysql server. Query ========= select term_topics.term_topic_name, term_topics.term_topic_id, term_topics.term_topic_owner from term_topics LEFT JOIN term_folder_members ON term_topics.term_topic_id = term_folder_members.term_topic_id WHERE term_folder_members.term_folder_id = 3 order by term_topic_name; Sample Data =========== USE test; ## Create and initialize the "term_folder_members" table. ## This contains all the information needed to link the topics with the correct folder CREATE TABLE term_folder_members ( term_folder_id INTEGER DEFAULT 0 NOT NULL, term_topic_id INTEGER DEFAULT 0 NOT NULL, INDEX(term_folder_id), INDEX(term_topic_id)); ## Create and initialize the "term_topics" table. ## This contains all the topics used by the alias stuff CREATE TABLE term_topics ( term_topic_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, term_topic_name VARCHAR(255) DEFAULT NULL, term_topic_name_upper VARCHAR(255) DEFAULT NULL, term_topic_owner VARCHAR(50) DEFAULT NULL, INDEX( term_topic_name_upper ), PRIMARY KEY( term_topic_id ) ); INSERT INTO term_topics VALUES ( NULL, 'drugs', 'DRUGS', 'demo_a' ); INSERT INTO term_topics VALUES ( NULL, 'terror', 'TERROR', 'demo_a' ); INSERT INTO term_folder_members VALUES ( 3, 1 ); INSERT INTO term_folder_members VALUES ( 3, 2 ); Relevant info from mysqlbug =========================== >Release: mysql-4.1.9-debug (Official MySQL-debug binary) >C compiler: 2.95.3 >C++ compiler: 2.95.3 >Environment: <machine, os, target, libraries (multiple lines)> System: Linux scrat 2.4.20-28.7 #1 Thu Dec 18 11:23:36 EST 2003 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/2.96/specs gcc version 2.96 20000731 (Red Hat Linux 7.3 2.96-110) Compilation info: CC='gcc' CFLAGS='-O2 -mcpu=pentiumpro' CXX='gcc' CXXFLAGS='-O2 -mcpu=pentiumpro -felide-constructors' LDFLAGS='' ASFLAGS='' LIBC: lrwxrwxrwx 1 root root 13 Apr 7 2004 /lib/libc.so.6 -> libc-2.2.5.so -rwxr-xr-x 1 root root 1260480 Apr 15 2002 /lib/libc-2.2.5.so -rw-r--r-- 1 root root 2310808 Apr 15 2002 /usr/lib/libc.a -rw-r--r-- 1 root root 178 Apr 15 2002 /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-debug binary' '--with-extra-charsets=complex' '--with-server-suffix=-debug' '--enable-thread-safe-client' '--enable-local-infile' '--enable-assembler' '--disable-shared' '--with-client-ldflags=-all-static' '--with-debug' '--with-mysqld-ldflags=-all-static' '--with-readline' '--with-embedded-server' '--with-innodb' 'CFLAGS=-O2 -mcpu=pentiumpro' 'CPPFLAGS=-DDEFAULT_THREAD_STACK=126976' 'CXXFLAGS=-O2 -mcpu=pentiumpro -felide-constructors' 'CXX=gcc' Suggested fix: