Bug #8497 Strange Errors when TMPDIR contains an extra slash
Submitted: 14 Feb 2005 13:48 Modified: 9 May 2005 2:26
Reporter: Roma Radford Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.1.9 OS:Solaris (Solaris and Linux)
Assigned to: Jim Winstead

[14 Feb 2005 13:48] Roma Radford
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:
[14 Feb 2005 14:25] Roma Radford
Under "How to repeat"->Environment, I wrote

Set TMPDIR to have an extra slash such as /usr/local/mysql/temp.

This should have been

Set TMPDIR to have an extra slash such as /usr/local/mysql//temp.

--Roma
[25 Apr 2005 23:00] Jim Winstead
The patch for this can be found at http://lists.mysql.com/internals/22570
[7 May 2005 15:12] Jim Winstead
Fixed in 4.1.12 and 5.0.6.
[9 May 2005 2:26] Paul Dubois
Noted in 4.1.12, 5.0.6 changelogs.