>Description:
A query like this will make mysqld sit forever with a state of
"Creating tmp table" (as shown by mysqladmin processlist):
SELECT a.field, b.text_field
FROM a
LEFT JOIN b USING (some_field)
ORDER BY some_field
If you do a normal join (WHERE a.some_field = b.some_field), it
will work fine. If you change b.text_field from type 'text' to
type 'varchar(N)', it will work fine. If you leave out the ORDER
BY, it will work fine.
>How-To-Repeat:
# MySQL dump 5.13
#
# Host: localhost Database: thimble
#--------------------------------------------------------
# Server version 3.22.20a
#
# Table structure for table 'ids'
#
CREATE TABLE ids (
table_name varchar(64) DEFAULT '' NOT NULL,
id int(10) unsigned DEFAULT '0' NOT NULL,
PRIMARY KEY (table_name,id)
);
#
# Dumping data for table 'ids'
#
INSERT INTO ids VALUES ('issues',2);
INSERT INTO ids VALUES ('cols',7);
#
# Table structure for table 'info'
#
CREATE TABLE info (
table_name varchar(64) DEFAULT '' NOT NULL,
text_field varchar(64) DEFAULT '' NOT NULL,
PRIMARY KEY (table_name)
);
#
# Dumping data for table 'info'
#
INSERT INTO info VALUES ('issues','issue_startdate');
INSERT INTO info VALUES ('cols','column_basename');
SELECT
id, text_field
FROM
ids
LEFT JOIN
info ON (ids.table_name = info.table_name)
ORDER BY
info.table_name;
ALTER TABLE info MODIFY text_field TEXT NOT NULL;
SELECT
id, text_field
FROM
ids
LEFT JOIN
info ON (ids.table_name = info.table_name)
ORDER BY
info.table_name;
# it should hang here...
>Fix:
Use VARCHAR field, if possible.
>Submitter-Id:
>Originator:
>Organization:
>MySQL support: licence
>Synopsis: mysqld hangs creating tmp table when left join + text + order by
>Severity: non-critical
>Priority: medium
>Category: mysql
>Class: sw-bug
>Release: mysql-3.22.20a (Source distribution)
>Server: /usr/local/mysql/bin/mysqladmin Ver 7.8 Distrib 3.22.20a, for unknown-
freebsd2.2.7 on i386
TCX Datakonsult AB, by Monty
Server version 3.22.20a
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /usr/local/mysql/var/mysql.sock
Uptime: 2 min 56 sec
Threads: 2 Questions: 47 Slow queries: 0 Opens: 12 Flush tables: 1 Open tab
les: 5
>Environment:
<machine, os, target, libraries (multiple lines)>
System: FreeBSD envy.opus1.com 2.2.7-RELEASE FreeBSD 2.2.7-RELEASE #0: Tue Sep 2
2 11:25:23 MST 1998 tim@stripped:/usr/src/sys/compile/ENVY i386
Some paths: /usr/bin/perl /usr/bin/make /usr/local/bin/gmake /usr/bin/gcc /usr/
bin/cc
GCC: Reading specs from /usr/local/egcs/lib/gcc-lib/i386-unknown-freebsd2.2.7/eg
cs-2.91.57/specs
gcc version egcs-2.91.57 19980901 (egcs-1.1 release)
Compilation info: CC='gcc' CFLAGS='' CXX='gcc' CXXFLAGS='-felide-constructors
-fno-exceptions -fno-rtti' LDFLAGS=''
Configure command: ./configure --prefix=/usr/local/mysql --with-unix-socket-pat
h=/usr/local/mysql/var/mysql.sock --without-readline --with-named-thread-libs=-l
c_r --without-docs --with-mysqld-user=mysql
Perl: This is perl, version 5.005_02 built for i386-freebsd
