Bug #55973 Assertion `thd->transaction.stmt.is_empty()' on CREATE TABLE .. SELECT I_S.PART
Submitted: 13 Aug 2010 15:35 Modified: 10 Sep 2010 3:02
Reporter: Elena Stepanova Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.5.6-m3-debug, 5.6.1-m3-debug OS:Any
Assigned to: Jon Olav Hauglid
Tags: regression
Triage: Triaged: D1 (Critical)

[13 Aug 2010 15:35] Elena Stepanova
Description:
Version: '5.5.6-m3-debug-log'  
mysqld: mysql-5.5.6-m3/sql/sql_base.cc:5356: bool open_normal_and_derived_tables(THD*, TABLE_LIST*, uint): Assertion `thd->transaction.stmt.is_empty()' failed.
100813 18:20:49 - mysqld got signal 6 ;

#5  0x00002b0806c17286 in __assert_fail () from /lib64/libc.so.6
#6  0x0000000000541039 in open_normal_and_derived_tables (thd=0x16b1c50, tables=0x17f3608, flags=1282)
    at 5.5-bugfixing/mysql-5.5.6-m3/sql/sql_base.cc:5356
#7  0x00000000005e1530 in get_all_tables (thd=0x16b1c50, tables=0x1736de8, cond=0x0)
    at 5.5-bugfixing/mysql-5.5.6-m3/sql/sql_show.cc:3623
#8  0x00000000005cf4d7 in get_schema_tables_result (join=0x174a0a0, executed_place=PROCESSED_BY_JOIN_EXEC)
    at 5.5-bugfixing/mysql-5.5.6-m3/sql/sql_show.cc:6671
#9  0x00000000005ca286 in JOIN::exec (this=0x174a0a0)
    at 5.5-bugfixing/mysql-5.5.6-m3/sql/sql_select.cc:1886
#10 0x00000000005cc4f7 in mysql_select (thd=0x16b1c50, rref_pointer_array=0x16b3da0, tables=0x1736de8, wild_num=1,
    fields=<value optimized out>, conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2953054976,
    result=0x17384d8, unit=0x16b35d0, select_lex=0x16b3bd0)
    at 5.5-bugfixing/mysql-5.5.6-m3/sql/sql_select.cc:2541
#11 0x00000000005ccee7 in handle_select (thd=0x16b1c50, lex=0x16b3520, result=0x17384d8, setup_tables_done_option=0)
    at 5.5-bugfixing/mysql-5.5.6-m3/sql/sql_select.cc:296
#12 0x000000000058615b in mysql_execute_command (thd=0x16b1c50)
    at 5.5-bugfixing/mysql-5.5.6-m3/sql/sql_parse.cc:2726
#13 0x0000000000588c0e in mysql_parse (thd=0x16b1c50,
    rawbuf=0x1736630 "CREATE TABLE my_partitions \nAS SELECT * FROM information_schema.partitions", length=<value optimized out>,
    parser_state=0x4588b5d0) at 5.5-bugfixing/mysql-5.5.6-m3/sql/sql_parse.cc:5903
#14 0x0000000000589e28 in dispatch_command (command=COM_QUERY, thd=0x16b1c50, packet=<value optimized out>,
    packet_length=<value optimized out>) at 5.5-bugfixing/mysql-5.5.6-m3/sql/sql_parse.cc:1138
#15 0x000000000058a39a in do_command (thd=0x16b1c50)
    at 5.5-bugfixing/mysql-5.5.6-m3/sql/sql_parse.cc:810
#16 0x000000000063bf8c in do_handle_one_connection (thd_arg=0x16b1c50)
    at 5.5-bugfixing/mysql-5.5.6-m3/sql/sql_connect.cc:1191
#17 0x000000000063c5a3 in handle_one_connection (arg=<value optimized out>)
    at 5.5-bugfixing/mysql-5.5.6-m3/sql/sql_connect.cc:1130
#18 0x000000000084f81a in pfs_spawn_thread (arg=<value optimized out>)
    at 5.5-bugfixing/mysql-5.5.6-m3/storage/perfschema/pfs.cc:1015
#19 0x00002b0806232143 in start_thread () from /lib64/libpthread.so.0
#20 0x00002b0806cae8cd in clone () from /lib64/libc.so.6
#21 0x0000000000000000 in ?? ()

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x1736630 = CREATE TABLE my_partitions
AS SELECT * FROM information_schema.partitions
thd->thread_id=2
thd->killed=NOT_KILLED

Not reproducible on 5.5.5-m3

How to repeat:
# Make sure server runs with default storage engine InnoDB

--source include/have_innodb.inc

CREATE VIEW my_v AS SELECT 1;
CREATE TABLE my_partitions 
   AS SELECT * FROM information_schema.partitions;
[16 Aug 2010 13:49] Nirbhay Choubey
Similar failure noticed while running following set of statements on
5.5.6-m3-debug :

USE test;
CREATE TABLE IF NOT EXISTS `SERVER_INFO` (`ROW_FORMAT` varchar(10));
INSERT INTO `SERVER_INFO` (`ROW_FORMAT`) SELECT `ROW_FORMAT` FROM INFORMATION_SCHEMA.TABLES WHERE `TABLE_NAME`='dummy';
[17 Aug 2010 6:54] Nirbhay Choubey
Selecting 'ENGINE' (instead of 'ROW_FORMAT') from INFORMATION_SCHEMA.TABLES, as in the above statement does not cause the assertion to fail.
[19 Aug 2010 14:27] 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/116267

3114 Jon Olav Hauglid	2010-08-19
      Bug #55973 Assertion `thd->transaction.stmt.is_empty()'
                 on CREATE TABLE .. SELECT I_S.PART
      
      This assert could be triggered if an InnoDB table was created using
      CREATE TABLE ... AS SELECT where the query used an I_S table, and
      a view existed in the database.
      
      The assert was triggered if open_normal_and_derived_tables() failed
      and a statement transaction had been started. This will usually not
      happen as tables are opened before a statement transaction is started.
      However, CREATE TABLE ... AS SELECT starts a transaction in order
      to insert tuples into the new table. And if the SELECT is an I_S query,
      all current tables and views can be opened in order to fill the I_S
      table on the fly. If a view is discovered, open will fail as it is
      instructed to open tables only (OPEN_TABLE_ONLY). This would then
      trigger the assert.
      
      This patch fixes the problem by adjusting the assert to take into
      consideration the possibility of tables being opened as part of
      an I_S query. This is similar to what is already done for 
      close_tables_for_reopen().
      
      Test case added to information_schema_inno.test.
[20 Aug 2010 7:17] 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/116332

3114 Jon Olav Hauglid	2010-08-20
      Bug #55973 Assertion `thd->transaction.stmt.is_empty()'
                 on CREATE TABLE .. SELECT I_S.PART
      
      This assert was triggered if an InnoDB table was created using
      CREATE TABLE ... AS SELECT where the query used an I_S table, and
      a view existed in the database. It would also be triggered for
      any statement changing an InnoDB table (e.g. INSERT, UPDATE, DELETE)
      which had a subquery referencing an I_S table.
      
      The assert was triggered if open_normal_and_derived_tables() failed
      and a statement transaction had been started. This will usually not
      happen as tables are opened before a statement transaction is started.
      However, e.g. CREATE TABLE ... AS SELECT starts a transaction in order
      to insert tuples into the new table. And if the subquery references
      an I_S table, all current tables and views can be opened in order to
      fill the I_S table on the fly. If a view is discovered, open will fail
      as it is instructed to open tables only (OPEN_TABLE_ONLY). This would
      cause the assert to be triggered.
      
      The assert was added in the patch for Bug#52044 and was therefore
      not in any released versions of the server.
      
      This patch fixes the problem by adjusting the assert to take into
      consideration the possibility of tables being opened as part of
      an I_S query. This is similar to what is already done for 
      close_tables_for_reopen().
      
      Test case added to information_schema_inno.test.
[20 Aug 2010 7:34] Jon Olav Hauglid
Pushed to mysql-5.5-runtime (5.5.6-m3).
[25 Aug 2010 9:22] Bugs System
Pushed into mysql-5.5 5.5.6-m3 (revid:alik@ibmvm-20100825092002-2yvkb3iwu43ycpnm) (version source revid:alik@ibmvm-20100825092002-2yvkb3iwu43ycpnm) (merge vers: 5.5.6-m3) (pib:20)
[30 Aug 2010 8:29] Bugs System
Pushed into mysql-trunk 5.6.1-m4 (revid:alik@sun.com-20100830082732-n2eyijnv86exc5ci) (version source revid:alik@sun.com-20100830082732-n2eyijnv86exc5ci) (merge vers: 5.6.1-m4) (pib:21)
[30 Aug 2010 8:33] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100830082745-n6sh01wlwh3itasv) (version source revid:alik@sun.com-20100830082745-n6sh01wlwh3itasv) (pib:21)
[10 Sep 2010 3:02] Paul Dubois
Noted in 5.5.6, 5.6.1 changelogs.

An assertion was raised if (1) an InnoDB table was created using
CREATE TABLE ... SELECT where the query used an INFORMATION_SCHEMA
table and a view existed in the database; or (2) sny statement that
modified an InnoDB table had a subquery referencing an
INFORMATION_SCHEMA table.