MySQL Bugs: #16655: Killing connection during UNION select returns error 1114 (table full)

Bug #16655	Killing connection during UNION select returns error 1114 (table full)
Submitted:	19 Jan 2006 21:56	Modified:	2 Nov 2006 13:24
Reporter:	Gregert Johnson	Email Updates:
Status:	Unsupported	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	4.0.27-BK, 4.0.24-standard	OS:	Linux (Linux)
Assigned to:	Georgi Kodinov	CPU Architecture:	Any

Description:
In our application, a query that is taking too long to complete can be terminated by killing its connection.  Normally, this results in MySQL error 1053 (Server shutdown in progress).  This is detected an a java servlet using the JDBC connector as either exception com.mysql.jdbc.CommunicationsException, or a SQLException with SQL state 08S01 (communication link failure).

However, if the query is a UNION, then MySQL error 1114 is returned when the connection is killed, indicating that a temporary table is full.  A message to that effect is also returned in the SQLException.

The UNION contains two almost identical SELECTs; when either one of them is executed separately and then killed, the normal error is returned (1053).

How to repeat:
For what it's worth, this is the query, although I'm hoping that this sort of behavior may have been noticed by others, and the query itself may not be the issue:

(SELECT c.call_key callkey, call_ep1.endpt_id, call_ep2.endpt_id, 
1000 * c.start_time + FLOOR(c.start_time_ext / 1000 + 0.5) ts, 
c.src_display_name, c.src_ip, c.call_from, 
c.dst_display_name, c.dst_ip, c.call_to, c.call_duration, c.call_key ck 
FROM call c 
LEFT OUTER JOIN endpoint call_ep1 ON call_ep1.endpt_key = c.src_endpt_key 
LEFT OUTER JOIN endpoint call_ep2 ON call_ep2.endpt_key = c.dst_endpt_key 
WHERE c.start_time BETWEEN 1137524493 AND 1137688349 
AND (c.start_time+c.start_time_ext/1e6) >=1137524493 
AND (c.start_time+c.start_time_ext/1e6) < 1137688349  
AND (c.src_ip LIKE '10.16.95%')) 
UNION 
(SELECT c.call_key callkey, call_ep1.endpt_id, call_ep2.endpt_id, 
1000 * c.start_time + FLOOR(c.start_time_ext / 1000 + 0.5) ts, 
c.src_display_name, c.src_ip, c.call_from, 
c.dst_display_name, c.dst_ip, c.call_to, c.call_duration, c.call_key ck 
FROM call c 
LEFT OUTER JOIN endpoint call_ep1 ON call_ep1.endpt_key = c.src_endpt_key 
LEFT OUTER JOIN endpoint call_ep2 ON call_ep2.endpt_key = c.dst_endpt_key 
WHERE c.start_time BETWEEN 1137524493 AND 1137688349 
AND (c.start_time+c.start_time_ext/1e6) >= 1137524493 
AND (c.start_time+c.start_time_ext/1e6) < 1137688349  
AND (c.dst_ip LIKE '10.16.95%')) 
ORDER BY ts DESC LIMIT 6000

A clue might be that this query potentially returns large amounts of data, although the returned data set is limited by the LIMIT clause, after a sort.

Suggested fix:
While a work-around exists, it would be more convenient if the same exception were thrown for UNIONs as for single SELECTs.

Thank you for a problem report. Please, send the SHOW CREATE TABLE results for all the tables used in your query. EXPLAIN results for the query may be also useful.

Explain result:

+----------+--------+---------------+---------+---------+-----------------+-------+-------------+
| table    | type   | possible_keys | key     | key_len | ref             | rows  | Extra       |
+----------+--------+---------------+---------+---------+-----------------+-------+-------------+
| c        | range  | ie1,ie5       | ie5     |      19 | NULL            | 50076 | Using where |
| call_ep1 | eq_ref | PRIMARY       | PRIMARY |       4 | c.src_endpt_key |     1 |             |
| call_ep2 | eq_ref | PRIMARY       | PRIMARY |       4 | c.dst_endpt_key |     1 |             |
| c        | range  | ie1,ie6       | ie6     |      19 | NULL            | 31403 | Using where |
| call_ep1 | eq_ref | PRIMARY       | PRIMARY |       4 | c.src_endpt_key |     1 |             |
| call_ep2 | eq_ref | PRIMARY       | PRIMARY |       4 | c.dst_endpt_key |     1 |             |
+----------+--------+---------------+---------+---------+-----------------+-------+-------------+

Table definitions:

Create Table: CREATE TABLE `call` (
  `call_key` bigint(20) unsigned NOT NULL auto_increment,
  `alternate_db_key` int(10) unsigned NOT NULL default '0',
  `start_time` int(11) NOT NULL default '-1',
  `start_time_ext` int(11) NOT NULL default '-1',
  `time_key` int(11) NOT NULL default '-1',
  `end_time` int(11) NOT NULL default '-1',
  `end_time_ext` int(11) NOT NULL default '-1',
  `call_duration` int(11) default NULL,
  `post_dial_delay` int(11) default NULL,
  `connect_latency` int(11) default NULL,
  `disconnect_time` int(11) default NULL,
  `flags` tinyint(3) unsigned NOT NULL default '0',
  `protocol_mask` int(10) unsigned NOT NULL default '0',
  `failure_reason` varchar(255) NOT NULL default '',
  `call_from` varchar(255) NOT NULL default '',
  `call_to` varchar(255) NOT NULL default '',
  `src_ip` varchar(255) NOT NULL default '',
  `dst_ip` varchar(255) NOT NULL default '',
  `src_display_name` varchar(255) NOT NULL default '',
  `dst_display_name` varchar(255) NOT NULL default '',
  `tag_mask` bigint(20) unsigned NOT NULL default '0',
  `src_endpt_key` int(10) unsigned default NULL,
  `dst_endpt_key` int(10) unsigned default NULL,
  PRIMARY KEY  (`call_key`),
  KEY `ak1` (`alternate_db_key`),
  KEY `ie1` (`start_time`,`start_time_ext`),
  KEY `ie2` (`end_time`,`end_time_ext`),
  KEY `ie3` (`call_from`(13),`start_time`),
  KEY `ie4` (`call_to`(13),`start_time`),
  KEY `ie5` (`src_ip`(15),`start_time`),
  KEY `ie6` (`dst_ip`(15),`start_time`)
) TYPE=MRG_MyISAM INSERT_METHOD=LAST UNION=(call_p13,call_p14,call_p15,call_p16,call_p17,call_p18,call_p19,call_p20,call_p21,call_p22,call_p23,call_p24)

Create Table: CREATE TABLE `endpoint` (
  `endpt_key` int(10) unsigned NOT NULL auto_increment,
  `endpt_id` varchar(80) NOT NULL default '',
  PRIMARY KEY  (`endpt_key`),
  UNIQUE KEY `ak1` (`endpt_id`)
) TYPE=MyISAM

Please, specify, how you killed that connection? With KILL statement (http://dev.mysql.com/doc/refman/4.1/en/kill.html) form the other mysql session or with kill Linux command for the client application?

Query was killed with the mysql "kill" command, executed on another connection, NOT the linux kill command.

Verified with a simpler test case on 4.0.27-BK. The key point is MERGE table that is used in UNION.

Test case:

create table tbig (c1 int auto_increment primary key, c2 char(200)) engine=MyISAM;
insert into tbg(c2) values('abc');
insert into tbig(c2) values('abc');
... many times, to get, say 64K rows
insert into tbig(c2) values('abc');
create table tbg2 as select * from tbig;
create table tm (c1 int auto_increment primary key, c2 char(200)) engine
=MRG_MyISAM UNION=(tbig, tbg2);

Then from one session (id 2):

mysql> (select 1 c0, tm.* from tm) UNION (select 2 c0, tm.* from tm) order by c2 desc;

From the other (id 1):

kill 2;

And in the first you'll get:

ERROR 1114: The table '#sql_43b2_0' is full
mysql> select version();
ERROR 2013: Lost connection to MySQL server during query
mysql> select version();
ERROR 2006: MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    3
Current database: test

+------------+
| version()  |
+------------+
| 4.0.27-log |
+------------+
1 row in set (0.02 sec)

It should be at least properly documented!

I just ran a similar test with a NON-merge table similar to tbig, and got the same result:

On connection 1:

mysql> show create table tst\G
*************************** 1. row ***************************
       Table: tst
Create Table: CREATE TABLE `tst` (
  `c1` int(11) NOT NULL default '0',
  `c2` char(200) default NULL
) TYPE=MyISAM

mysql> select count(*) from tst;
+----------+
| count(*) |
+----------+
|   229256 |
+----------+

mysql> (select 1 c0, tst.* from tst) union (select 2 c0, tst.* from tst) order by c2 desc;

On another connection -

Connection 2:

mysql> show processlist;

...
| 1 | root        | localhost | test | Query   | 5      | converting HEAP to MyISAM

mysql> kill 1;

Connection 1:

ERROR 1114: The table '#sql_1b62_0' is full

----------------------------------------

So, it appears that the MERGE table is not the main issue.

Further, when a simple SELECT is performed (no UNION):

Connection 1:

mysql> select 1 c0, tst.* from tst order by c2 desc;

Connection 2:

mysql> show processlist;

...

| 1 | root        | localhost | test | Query   | 6      | Sorting result

mysql> kill 1;

Connection 1:

ERROR 1053: Server shutdown in progress

Thank you for taking the time to report a problem.  Unfortunately you are not using a current version of the product you reported a problem with -- the problem might already be fixed. Please download a new version from http://www.mysql.com/downloads/

If you are able to reproduce the bug with one of the latest versions, please change the version on this bug report to the version you tested and change the status back to "Open".  Again, thank you for your continued support of MySQL.

I failed to repeat the problem with mysql version 4.1.22-BK (even by placing breakpoints at the appropriate places in code to ascertain proper timing of the kill request).
Since 4.0 release is out of active support please retry with the latest 4.1 release where this bug appears to be fixed.