Bug #38191 Crash in List<Cached_item>::delete_elements at sql_list.h:400
Submitted: 17 Jul 2008 8:13 Modified: 5 Oct 2008 22:04
Reporter: Philip Stoev Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S1 (Critical)
Version:5.1, 6.0 BZR OS:Any
Assigned to: Igor Babaev CPU Architecture:Any
Triage: D1 (Critical)

[17 Jul 2008 8:13] Philip Stoev
Description:
When executing a SELECT/SELECT EXPLAIN workload containing subqueries, mysqld crashed as follows:

#0  0x00110416 in __kernel_vsyscall ()
#1  0x00581c78 in pthread_kill () from /lib/libpthread.so.0
#2  0x085786bb in my_write_core (sig=11) at stacktrace.c:310
#3  0x08245c05 in handle_segfault (sig=11) at mysqld.cc:2507
#4  <signal handler called>
#5  0x081a8a9f in List<Cached_item>::delete_elements (this=0xa17679c) at sql_list.h:400
#6  0x082c8c6c in JOIN::cleanup (this=0xa175790, full=true) at sql_select.cc:6756
#7  0x082d02b0 in JOIN::destroy (this=0xa175790) at sql_select.cc:2232
#8  0x082d0288 in JOIN::destroy (this=0xa0d45f0) at sql_select.cc:2228
#9  0x083d6f9f in st_select_lex::cleanup (this=0xa092690) at sql_union.cc:768
#10 0x083d6e0b in st_select_lex_unit::cleanup (this=0xa092818) at sql_union.cc:646
#11 0x083d7003 in st_select_lex::cleanup (this=0xa084450) at sql_union.cc:775
#12 0x082d4927 in mysql_select (thd=0xa0830e8, rref_pointer_array=0xa084548, tables=0xa0924a0, wild_num=0, fields=@0xa0844e4, conds=0xa093a08, og_num=3,
    order=0xa093c78, group=0xa093b78, having=0x0, proc_param=0x0, select_options=2147764737, result=0xa093de8, unit=0xa0841d8, select_lex=0xa084450)
    at sql_select.cc:2382
#13 0x082d953c in handle_select (thd=0xa0830e8, lex=0xa08417c, result=0xa093de8, setup_tables_done_option=0) at sql_select.cc:269
#14 0x08254f7a in execute_sqlcom_select (thd=0xa0830e8, all_tables=0xa0924a0) at sql_parse.cc:4765
#15 0x08256c40 in mysql_execute_command (thd=0xa0830e8) at sql_parse.cc:2073
#16 0x0825f8c2 in mysql_parse (thd=0xa0830e8,
    inBuf=0xa091ec0 "SELECT DISTINCT AVG(DISTINCT OUTR . `datetime_key` ) AS X FROM C AS OUTR WHERE EXISTS ( SELECT DISTINCT INNR . `datetime_key` AS Y FROM BB AS INNR WHERE OUTR . `datetime_key` <= '2005-1-26' ORDER BY I"..., length=390, found_semicolon=0xb2bfd25c) at sql_parse.cc:5645
#17 0x08260499 in dispatch_command (command=COM_QUERY, thd=0xa0830e8, packet=0xa085369 "", packet_length=390) at sql_parse.cc:1137
#18 0x082615f8 in do_command (thd=0xa0830e8) at sql_parse.cc:794
#19 0x0824ed76 in handle_one_connection (arg=0xa0830e8) at sql_connect.cc:1115
#20 0x0057d32f in start_thread () from /lib/libpthread.so.0
#21 0x0049a27e in clone () from /lib/libc.so.6

The crash is here:

(gdb) list
395       {
396         list_node *element,*next;
397         for (element=first; element != &end_of_list; element=next)
398         {
399           next=element->next;
400           delete (T*) element->info; <<<<<<<<<<< HERE
401         }
402         empty();
403       }
404     };

(gdb) print element
$4 = (list_node *) 0xa15e178
(gdb) print element->info
$5 = (void *) 0xa15e158

How to repeat:
If this is repeatable, a test case will be uploaded.
[17 Jul 2008 8:49] Philip Stoev
Test case for bug 38191

Attachment: bug38191.test (application/octet-stream, text), 3.70 KiB.

[17 Jul 2008 8:51] Philip Stoev
A test case for this bug was just uploaded. The offending query is of the form:

SELECT *
FROM t1 WHERE EXISTS (
 SELECT DISTINCT `datetime_key`
 FROM t2
 WHERE t1 . `datetime_key` <= '2005-1-26'
 ORDER BY t2 . `date_nokey`
);
[17 Jul 2008 10:22] Sveta Smirnova
Thank you for the report.

Verified as described using versions 5.1.28 and 6.0.6. Crash does not happens if MyISAM storage engine is used.
[17 Jul 2008 10:24] Philip Stoev
Very likely the bug is universal and MyISAM is not affected only because in this particular test case one of the tables contains 1 row only.
[26 Jul 2008 3:50] Igor Babaev
With version 5.0 the query crashes the server as well:

mysql> SELECT VERSION();

+--------------+
| VERSION()    |
+--------------+
| 5.0.68-debug |
+--------------+
1 row in set (0.00 sec)

mysql> SELECT *
    -> FROM t1 AS OUTR WHERE EXISTS (
    ->  SELECT DISTINCT
    ->  INNR . `datetime_key` AS Y
    ->  FROM t2 AS INNR
    ->  WHERE OUTR . `datetime_key` <= '2005-1-26'
    ->  ORDER BY INNR . `date_nokey`
    -> );
+----+-----------+---------+------------+------------+----------+------------+---------------------+---------------------+-------------+---------------+
| pk | int_nokey | int_key | date_key   | date_nokey | time_key | time_nokey | datetime_key        | datetime_nokey      | varchar_key | varchar_nokey |
+----+-----------+---------+------------+------------+----------+------------+---------------------+---------------------+-------------+---------------+
|  2 |         1 |       6 | 2005-12-23 | 2005-12-23 | 02:24:28 | 02:24:28   | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | g           | g             |
|  3 |         0 |       3 | 2009-09-14 | 2009-09-14 | 00:00:00 | 00:00:00   | 2000-01-30 16:39:40 | 2000-01-30 16:39:40 | q           | q             |
|  4 |         0 |       3 | 0000-00-00 | 0000-00-00 | 00:00:00 | 00:00:00   | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | c           | c             |
|  5 |         1 |       6 | 2007-03-29 | 2007-03-29 | 15:49:00 | 15:49:00   | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | m           | m             |
|  6 |         4 |       0 | 2002-12-04 | 2002-12-04 | 00:00:00 | 00:00:00   | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | o           | o             |
|  7 |         9 |       0 | 2005-01-28 | 2005-01-28 | 00:00:00 | 00:00:00   | 2001-05-18 00:00:00 | 2001-05-18 00:00:00 | w           | w             |
|  8 |         6 |       0 | 0000-00-00 | 0000-00-00 | 06:57:25 | 06:57:25   | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | m           | m             |
|  9 |         0 |       0 | 0000-00-00 | 0000-00-00 | 00:00:00 | 00:00:00   | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | z           | z             |
| 10 |         4 |       6 | 2006-08-15 | 2006-08-15 | 00:00:00 | 00:00:00   | 2002-04-12 14:44:25 | 2002-04-12 14:44:25 | j           | j             |
| 12 |         9 |       7 | 0000-00-00 | 0000-00-00 | 00:00:00 | 00:00:00   | 2004-07-05 00:00:00 | 2004-07-05 00:00:00 | {           | {             |
| 13 |         4 |       3 | 2007-01-26 | 2007-01-26 | 23:00:51 | 23:00:51   | 2001-05-16 00:00:00 | 2001-05-16 00:00:00 | f           | f             |
| 14 |         7 |       0 | 2004-03-27 | 2004-03-27 | 00:00:00 | 00:00:00   | 2005-01-24 03:30:37 | 2005-01-24 03:30:37 |             |               |
| 15 |         6 |       0 | 2006-07-26 | 2006-07-26 | 18:43:57 | 18:43:57   | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | {           | {             |
| 16 |         0 |       6 | 2000-01-14 | 2000-01-14 | 00:00:00 | 00:00:00   | 2000-09-21 00:00:00 | 2000-09-21 00:00:00 | o           | o             |
| 17 |         9 |       8 | 0000-00-00 | 0000-00-00 | 21:15:08 | 21:15:08   | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | a           | a             |
| 18 |         2 |       0 | 2004-10-27 | 2004-10-27 | 00:00:00 | 00:00:00   | 2004-03-24 22:13:43 | 2004-03-24 22:13:43 |             |               |
| 19 |         7 |       4 | 0000-00-00 | 0000-00-00 | 08:38:27 | 08:38:27   | 2002-03-18 19:51:44 | 2002-03-18 19:51:44 | t           | t             |
+----+-----------+---------+------------+------------+----------+------------+---------------------+---------------------+-------------+---------------+
17 rows in set (0.00 sec)

mysql> select 1;
ERROR 2013 (HY000): Lost connection to MySQL server during query

================================================================
And the crash is in the same place as for 5.1.

080725 20:44:05 [Note] /home/igor/dev-bzr/mysql-5.0/sql/mysqld: ready for connections.
Version: '5.0.68-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
[New Thread -1256514656 (LWP 5637)]

Program received signal SIGSEGV, Segmentation fault.
[Switching to Thread -1256514656 (LWP 5637)]
0x081a0b5b in List<Cached_item>::delete_elements (this=0x8d82af4) at sql_list.h:416
[26 Jul 2008 5:02] Igor Babaev
The crash can be reproduced with a simpler test:

mysql> CREATE TABLE t1(pk int PRIMARY KEY, a int, INDEX idx(a));
Query OK, 0 rows affected (0.15 sec)

mysql> INSERT INTO t1 VALUES (1, 10), (3, 30), (2, 20);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE t2(pk int PRIMARY KEY, a int, b int, INDEX idxa(a));
Query OK, 0 rows affected (0.07 sec)

mysql> INSERT INTO t2 VALUES (2, 20, 700), (1, 10, 200), (4, 10, 100);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

+--------------+
| VERSION()    |
+--------------+
| 5.0.68-debug |
+--------------+
1 row in set (0.02 sec)

mysql> SELECT * FROM t1    WHERE EXISTS (SELECT DISTINCT a FROM t2 WHERE t1.a < t2.a ORDER BY b);
+----+------+
| pk | a    |
+----+------+
|  1 |   10 |
|  3 |   30 |
|  2 |   20 |
+----+------+
3 rows in set (0.01 sec)

==================================================

80725 22:00:33 [Note] /home/igor/dev-bzr/mysql-5.0/sql/mysqld: ready for connections.
Version: '5.0.68-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
[New Thread -1256596576 (LWP 5827)]

Program received signal SIGSEGV, Segmentation fault.
[Switching to Thread -1256596576 (LWP 5827)]
0x081a0b5b in List<Cached_item>::delete_elements (this=0x8d867c4) at sql_list.h:416
[26 Jul 2008 20:45] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/50563

2647 Igor Babaev	2008-07-26
      Fixed bug #38191.
      Calling List<Cached_item>::delete_elements for the same list twice
      caused a crash of the server in the function JOIN::cleaunup.
      Ensured that delete_elements() in JOIN::cleanup would be called only once.
[12 Aug 2008 14:49] Bugs System
Pushed into 6.0.7-alpha  (revid:igor@mysql.com-20080726204407-czkkjx10563sjdy7) (version source revid:davi.arnaut@sun.com-20080812141852-8e6knbqclpfd8irn) (pib:3)
[12 Aug 2008 15:15] Bugs System
Pushed into 5.1.28  (revid:igor@mysql.com-20080726204407-czkkjx10563sjdy7) (version source revid:davi.arnaut@sun.com-20080812142843-he05ncsggstbn57z) (pib:3)
[12 Aug 2008 19:12] Bugs System
Pushed into 5.0.68  (revid:igor@mysql.com-20080726204407-czkkjx10563sjdy7) (version source revid:davi.arnaut@sun.com-20080812185100-d47qb8mz2ye6pe6b) (pib:3)
[28 Aug 2008 20:17] Bugs System
Pushed into 6.0.7-alpha  (revid:cbell@mysql.com-20080822132131-uveo6wiuecy6m2b8) (version source revid:cbell@mysql.com-20080822132131-uveo6wiuecy6m2b8) (pib:3)
[13 Sep 2008 19:36] Bugs System
Pushed into 6.0.6-alpha  (revid:igor@mysql.com-20080726204407-czkkjx10563sjdy7) (version source revid:sergefp@mysql.com-20080611231653-nmuqmw6dedjra79i) (pib:3)
[5 Oct 2008 22:04] Paul Dubois
Noted in 5.0.68, 5.1.28, 6.0.7 changelogs.

Queries containing a subquery with DISTINCT and ORDER BY could cause
a server crash.
[12 Jan 2009 0:19] Sean Jenkins
This bug still occurs in 5.0.75 despite release notes indicating it resolved and verifying sources patched.  A standard install of Pligg (pligg.com) running the query "SELECT count(*) FROM pligg_pageviews,pligg_links WHERE `pv_type`='story' AND link_id IN(SELECT DISTINCT link_id FROM pligg_links WHERE link_id=pv_page_id);" causes MySQL's memory to bloat leading to a server crash.
[13 Jan 2009 20:02] Sergey Petrunya
The new problem was reported as a separate BUG#42037 (thanks Sean) and is being addressed in that bug entry.