MySQL Bugs: #74606: Assertion `thd->transaction.stmt.is_empty()' failed in close_mysql

Bug #74606	Assertion `thd->transaction.stmt.is_empty()' failed in close_mysql_tables
Submitted:	28 Oct 2014 15:56	Modified:	29 Oct 2014 9:53
Reporter:	Ramesh Sivaraman	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	5.6.20-debug, 5.6.22, 5.7.6	OS:	Linux (CentOS 7)
Assigned to:		CPU Architecture:	Any

Description:
mysqld: /ssd/ramesh/mysql-server/mysql-5.6/sql/sql_base.cc:9555: void close_mysql_tables(THD*): Assertion `thd->transaction.stmt.is_empty()' failed.
15:53:05 UTC - mysqld got signal 6 ;

**** GDB info

#0  0x00007fec30584771 in pthread_kill () from /lib64/libpthread.so.0
#1  0x0000000000a9035e in my_write_core (sig=6) at /ssd/ramesh/mysql-server/mysql-5.6/mysys/stacktrace.c:422
#2  0x0000000000723688 in handle_fatal_signal (sig=6) at /ssd/ramesh/mysql-server/mysql-5.6/sql/signal_handler.cc:230
#3  <signal handler called>
#4  0x00007fec2f3905c9 in raise () from /lib64/libc.so.6
#5  0x00007fec2f391cd8 in abort () from /lib64/libc.so.6
#6  0x00007fec2f389536 in __assert_fail_base () from /lib64/libc.so.6
#7  0x00007fec2f3895e2 in __assert_fail () from /lib64/libc.so.6
#8  0x0000000000770b81 in close_mysql_tables (thd=0x7feb9d324000) at /ssd/ramesh/mysql-server/mysql-5.6/sql/sql_base.cc:9555
#9  0x000000000080dc84 in drop_server (thd=0x7feb9d324000, server_options=0x7feb9d327150, if_exists=false) at /ssd/ramesh/mysql-server/mysql-5.6/sql/sql_servers.cc:601
#10 0x00000000007d5d4b in mysql_execute_command (thd=0x7feb9d324000) at /ssd/ramesh/mysql-server/mysql-5.6/sql/sql_parse.cc:4914
#11 0x00000000007d8fa4 in mysql_parse (thd=0x7feb9d324000, rawbuf=0x7feb9401f010 "drop server 's1'", length=16, parser_state=0x7fec30b7be70) at /ssd/ramesh/mysql-server/mysql-5.6/sql/sql_parse.cc:6245
#12 0x00000000007cc557 in dispatch_command (command=COM_QUERY, thd=0x7feb9d324000, packet=0x7feb9d362001 "drop server 's1'", packet_length=16) at /ssd/ramesh/mysql-server/mysql-5.6/sql/sql_parse.cc:1332
#13 0x00000000007cb646 in do_command (thd=0x7feb9d324000) at /ssd/ramesh/mysql-server/mysql-5.6/sql/sql_parse.cc:1034
#14 0x0000000000793a61 in do_handle_one_connection (thd_arg=0x7feb9d324000) at /ssd/ramesh/mysql-server/mysql-5.6/sql/sql_connect.cc:982
#15 0x000000000079354a in handle_one_connection (arg=0x7feb9d324000) at /ssd/ramesh/mysql-server/mysql-5.6/sql/sql_connect.cc:898
#16 0x0000000000ad4b5c in pfs_spawn_thread (arg=0x7fec2cbfeb00) at /ssd/ramesh/mysql-server/mysql-5.6/storage/perfschema/pfs.cc:1860
#17 0x00007fec3057fdf3 in start_thread () from /lib64/libpthread.so.0
#18 0x00007fec2f45101d in clone () from /lib64/libc.so.6

How to repeat:
DROP DATABASE test;CREATE DATABASE test;USE test;
use mysql;
CREATE TABLE servers(Server_name char DEFAULT'',Host char DEFAULT'',Db char DEFAULT'',Username char DEFAULT'',Password char DEFAULT'',Port INT,Socket char DEFAULT'',Wrapper char DEFAULT'',Owner char DEFAULT'',KEY (Server_name)) comment='MySQL Foreign Servers t';
drop server 's1';

Thank you for the bug report. I couldn't repeat with recent source server on CentOS 6.5 and Windows both 64-bits. Please try with most recent source:

[miguel@cuzcatlan data]$ cd ..
[miguel@cuzcatlan 5.6]$ bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.22-debug Source distribution

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> DROP DATABASE test;CREATE DATABASE test;USE test;
Query OK, 1 row affected (0.47 sec)

Query OK, 1 row affected (0.00 sec)

Database changed
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> CREATE TABLE servers(Server_name char DEFAULT'',Host char DEFAULT'',Db char DEFAULT'',Username char DEFAULT'',Password char DEFAULT'',Port INT,Socket char DEFAULT'',Wrapper char DEFAULT'',Owner char DEFAULT'',KEY (Server_name)) comment='MySQL Foreign Servers t';
ERROR 1050 (42S01): Table 'servers' already exists
mysql> drop server 's1';
ERROR 1477 (HY000): The foreign server name you are trying to reference does not exist. Data source error:  s1
mysql>

mysql> SHOW VARIABLES LIKE "%VERSION%";
+-------------------------+---------------------+
| Variable_name           | Value               |
+-------------------------+---------------------+
| innodb_version          | 5.6.22              |
| protocol_version        | 10                  |
| slave_type_conversions  |                     |
| version                 | 5.6.22-debug        |
| version_comment         | Source distribution |
| version_compile_machine | x86_64              |
| version_compile_os      | Linux               |
+-------------------------+---------------------+
7 rows in set (0.00 sec)

mysql>

Could you please try with below testcase. Command "drop table servers" was missing from old testcase.

use mysql;
drop table servers;
CREATE TABLE servers(Server_name char DEFAULT'',Host char DEFAULT'',Db char DEFAULT'',Username char DEFAULT'',Password char DEFAULT'',Port INT,Socket char DEFAULT'',Wrapper char DEFAULT'',Owner char DEFAULT'',KEY (Server_name)) comment='MySQL Foreign Servers t';
drop server 's1';

Thank you for the feedback.
With the latest test case debug builds asserts.

// 5.6.22 debug build

2014-10-31 06:25:06 10637 [Note] /data/ushastry/server/mysql-advanced-5.6.22/bin/mysqld-debug: ready for connections.
Version: '5.6.22-enterprise-commercial-advanced-debug'  socket: '/tmp/mysql.sock'  port: 3306  MySQL Enterprise Server - Advanced Edition Debug (Commercial)
mysqld-debug: /export/home/pb2/build/sb_0-13146752-1409933888.09/mysqlcom-pro-5.6.22/sql/sql_base.cc:9555: void close_mysql_tables(THD*): Assertion `thd->transaction.stmt.is_empty()' failed.
00:55:26 UTC - mysqld got signal 6 ;

(gdb) bt
#0  0x0000003bf260c8ac in pthread_kill () from /lib64/libpthread.so.0
#1  0x0000000000afe982 in my_write_core (sig=6) at /export/home/pb2/build/sb_0-13146752-1409933888.09/mysqlcom-pro-5.6.22/mysys/stacktrace.c:422
#2  0x000000000077e6cc in handle_fatal_signal (sig=6) at /export/home/pb2/build/sb_0-13146752-1409933888.09/mysqlcom-pro-5.6.22/sql/signal_handler.cc:230
#3  <signal handler called>
#4  0x0000003bf2232635 in raise () from /lib64/libc.so.6
#5  0x0000003bf2233e15 in abort () from /lib64/libc.so.6
#6  0x0000003bf222b75e in __assert_fail_base () from /lib64/libc.so.6
#7  0x0000003bf222b820 in __assert_fail () from /lib64/libc.so.6
#8  0x00000000007cd70d in close_mysql_tables (thd=0x8b8edd0) at /export/home/pb2/build/sb_0-13146752-1409933888.09/mysqlcom-pro-5.6.22/sql/sql_base.cc:9555
#9  0x000000000086cf7f in drop_server (thd=0x8b8edd0, server_options=0x8b91f20, if_exists=false) at /export/home/pb2/build/sb_0-13146752-1409933888.09/mysqlcom-pro-5.6.22/sql/sql_servers.cc:601
#10 0x0000000000834231 in mysql_execute_command (thd=0x8b8edd0) at /export/home/pb2/build/sb_0-13146752-1409933888.09/mysqlcom-pro-5.6.22/sql/sql_parse.cc:4914
#11 0x00000000008375d1 in mysql_parse (thd=0x8b8edd0, rawbuf=0x7fba68004fd0 "drop server 's1'", length=16, parser_state=0x7fba98162680)
    at /export/home/pb2/build/sb_0-13146752-1409933888.09/mysqlcom-pro-5.6.22/sql/sql_parse.cc:6245
#12 0x000000000082a3a2 in dispatch_command (command=COM_QUERY, thd=0x8b8edd0, packet=0x8cbe721 "drop server 's1'", packet_length=16)
    at /export/home/pb2/build/sb_0-13146752-1409933888.09/mysqlcom-pro-5.6.22/sql/sql_parse.cc:1332
#13 0x0000000000829407 in do_command (thd=0x8b8edd0) at /export/home/pb2/build/sb_0-13146752-1409933888.09/mysqlcom-pro-5.6.22/sql/sql_parse.cc:1034
#14 0x00000000007f0887 in do_handle_one_connection (thd_arg=0x8b8edd0) at /export/home/pb2/build/sb_0-13146752-1409933888.09/mysqlcom-pro-5.6.22/sql/sql_connect.cc:982
#15 0x00000000007f0358 in handle_one_connection (arg=0x8b8edd0) at /export/home/pb2/build/sb_0-13146752-1409933888.09/mysqlcom-pro-5.6.22/sql/sql_connect.cc:898
#16 0x0000000000e5c19d in pfs_spawn_thread (arg=0x8c81e50) at /export/home/pb2/build/sb_0-13146752-1409933888.09/mysqlcom-pro-5.6.22/storage/perfschema/pfs.cc:1860
#17 0x0000003bf26079d1 in start_thread () from /lib64/libpthread.so.0
#18 0x0000003bf22e886d in clone () from /lib64/libc.so.6
(gdb)

Thanks,
Umesh

// 5.7.6 debug build

2014-10-31T01:01:12.611966Z 0 [Note] /data/ushastry/server/mysql-advanced-5.7.6/bin/mysqld-debug: ready for connections.
Version: '5.7.6-m16-enterprise-commercial-advanced-debug'  socket: '/tmp/mysql.sock'  port: 3306  MySQL Enterprise Server - Advanced Edition Debug (Commercial)
mysqld-debug: /pb2/build/sb_0-13146106-1409929295.44/mysqlcom-pro-5.7.6-m16/sql/sql_base.cc:10076: void close_mysql_tables(THD*): Assertion `thd->get_transaction()->is_empty(Transaction_ctx::STM
T)' failed.
01:01:33 UTC - mysqld got signal 6 ;

(gdb) bt
#0  0x0000003bf260c8ac in pthread_kill () from /lib64/libpthread.so.0
#1  0x0000000000e794c5 in my_write_core (sig=6) at /pb2/build/sb_0-13146106-1409929295.44/mysqlcom-pro-5.7.6-m16/mysys/stacktrace.c:247
#2  0x000000000086cb4e in handle_fatal_signal (sig=6) at /pb2/build/sb_0-13146106-1409929295.44/mysqlcom-pro-5.7.6-m16/sql/signal_handler.cc:219
#3  <signal handler called>
#4  0x0000003bf2232635 in raise () from /lib64/libc.so.6
#5  0x0000003bf2233e15 in abort () from /lib64/libc.so.6
#6  0x0000003bf222b75e in __assert_fail_base () from /lib64/libc.so.6
#7  0x0000003bf222b820 in __assert_fail () from /lib64/libc.so.6
#8  0x0000000000b306cb in close_mysql_tables (thd=0x7fe2b8000d30) at /pb2/build/sb_0-13146106-1409929295.44/mysqlcom-pro-5.7.6-m16/sql/sql_base.cc:10076
#9  0x0000000000bee573 in Sql_cmd_drop_server::execute (this=0x7fe2b8005c20, thd=0x7fe2b8000d30) at /pb2/build/sb_0-13146106-1409929295.44/mysqlcom-pro-5.7.6-m16/sql/sql_servers.cc:863
#10 0x0000000000ba8698 in mysql_execute_command (thd=0x7fe2b8000d30) at /pb2/build/sb_0-13146106-1409929295.44/mysqlcom-pro-5.7.6-m16/sql/sql_parse.cc:4815
#11 0x0000000000baa012 in mysql_parse (thd=0x7fe2b8000d30, parser_state=0x7fe2f097d660) at /pb2/build/sb_0-13146106-1409929295.44/mysqlcom-pro-5.7.6-m16/sql/sql_parse.cc:5427
#12 0x0000000000b9dec7 in dispatch_command (command=COM_QUERY, thd=0x7fe2b8000d30, packet=0x7fe2b80098b1 "drop server 's1'", packet_length=16)
    at /pb2/build/sb_0-13146106-1409929295.44/mysqlcom-pro-5.7.6-m16/sql/sql_parse.cc:1250
#13 0x0000000000b9cb7b in do_command (thd=0x7fe2b8000d30) at /pb2/build/sb_0-13146106-1409929295.44/mysqlcom-pro-5.7.6-m16/sql/sql_parse.cc:834
#14 0x0000000000caa2d8 in handle_connection (arg=0xdada670) at /pb2/build/sb_0-13146106-1409929295.44/mysqlcom-pro-5.7.6-m16/sql/conn_handler/connection_handler_per_thread.cc:298
#15 0x0000000001241f24 in pfs_spawn_thread (arg=0xdb26b40) at /pb2/build/sb_0-13146106-1409929295.44/mysqlcom-pro-5.7.6-m16/storage/perfschema/pfs.cc:2137
#16 0x0000003bf26079d1 in start_thread () from /lib64/libpthread.so.0
#17 0x0000003bf22e886d in clone () from /lib64/libc.so.6