MySQL Bugs: #5834: SHOW CREATE TABLE...LOCAL with InnoDB triggers sig11 crash

Bug #5834	SHOW CREATE TABLE...LOCAL with InnoDB triggers sig11 crash
Submitted:	30 Sep 2004 22:51	Modified:	30 Oct 2004 8:58
Reporter:	Christian Hammers (Silver Quality Contributor) (OCA)	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	4.0.21	OS:	Linux (Debian GNU/Linux)
Assigned to:		CPU Architecture:	Any

Description:
Hello

(The following has been reported as Debian bug report 272980)

The current Debian packages have a reproducible crash situation that can be triggert
by "mysqldump --opt" and has been confirmed by three people now from where at least
two backtraces were nearly identical.

The backtrace from my system generated using the error log output:
  # resolve_stack_dump  /usr/share/doc/mysql-server/mysqld.sym t
  0x810f1eb handle_segfault + 667
  0xffffe420 _end + -138598536
  0x891dd38 _end + 5185680
  0x819896d _Z18mysqld_show_createP3THDP13st_table_list + 45
  0x811e204 _Z21mysql_execute_commandv + 7188
  0x8121428 _Z11mysql_parseP3THDPcj + 328
  0x811b967 _Z16dispatch_command19enum_server_commandP3THDPcj + 1191
  0x811b472 _Z10do_commandP3THD + 114
  0x811ad38 handle_one_connection + 888
  0x41183ae5 _end + 953515581
  0x88f2f44 _end + 5010076

bye,

-christian-

How to repeat:
The commands that can trigger the crash are as follows (note the correct result after
the first crash!)

  # mysqladmin drop test; mysqladmin create test;
  # mysql test

  mysql> create table something ( myid int not null, somevalue varchar(128) not null ) type=InnoDB;
  Query OK, 0 rows affected (0.01 sec)

  mysql> LOCK TABLES `something` READ /*!32311 LOCAL */;
  Query OK, 0 rows affected (0.00 sec)

  mysql> show create table `something`;
  ERROR 2013: Lost connection to MySQL server during query
  mysql> Aborted

Without the "/*!32311 LOCAL */" there are no problems.

NOTE: Probably due to the query cache the server has to be restarted between your tries, else the queries seem to just work.

Suggested fix:
none

I could not repeat this problem using 4.0.21 or 4.1.4.

Also I wasn't able to repeat the behavior reported.

Hello

Some more debugging information. 

* mysqld variables regarding "locks":
========================
enable-locking                    FALSE
memlock                           FALSE
external-locking                  FALSE
bdb_max_lock                      10000
bdb_lock_max                      10000
innodb_lock_wait_timeout          50

* Invocation:
=========
$ mysql <<EOT
USE test;
DROP TABLE IF EXISTS t;
CREATE TABLE t (i int) TYPE=innodb;
LOCK TABLES `t` READ /*!32311 LOCAL */;
SHOW CREATE TABLE `t`;
EOT

* GDB output with debugging symbols:
=========================
# gdb ./sql/mysqld
GNU gdb 6.1-debian
Copyright 2004 Free Software Foundation, Inc.
GDB is free software, covered by the GNU General Public License, and you are
welcome to change it and/or distribute copies of it under certain conditions.
Type "show copying" to see the conditions.
There is absolutely no warranty for GDB.  Type "show warranty" for details.
This GDB was configured as "i386-linux"...Using host libthread_db library "/lib/tls/libthread_db.so.1".

(gdb) 
(gdb) 
(gdb) run
Starting program: /tmp/mysql-dfsg-4.0.21/sql/mysqld 
[Thread debugging using libthread_db enabled]
[New Thread 1077007456 (LWP 18409)]
[New Thread 1138858928 (LWP 18412)]
[New Thread 1147247536 (LWP 18413)]
[New Thread 1155636144 (LWP 18414)]
[New Thread 1164024752 (LWP 18415)]
041003 19:22:26  InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 98 99 
InnoDB: Apply batch completed
[New Thread 1177193392 (LWP 18416)]
[New Thread 1185582000 (LWP 18417)]
[New Thread 1193970608 (LWP 18418)]
041003 19:22:26  InnoDB: Started
[New Thread 1210882992 (LWP 18419)]
[New Thread 1211014064 (LWP 18420)]
/tmp/mysql-dfsg-4.0.21/sql/mysqld: ready for connections.
Version: '4.0.21-debug-log'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  Source distribution
[New Thread 1211149232 (LWP 18423)]
InnoDB: Error: select_lock_type is 99999999 inside ::start_stmt()!
041003 19:22:35InnoDB: Assertion failure in thread 1211149232 in file ha_innodb.cc line 4581
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. See section 6.1 of
InnoDB: http://www.innodb.com/ibman.php about forcing recovery.

Program received signal SIGSEGV, Segmentation fault.
[Switching to Thread 1211149232 (LWP 18423)]
0x08192d43 in ha_innobase::start_stmt (this=0x8a9df20, thd=0x8a9e9e8) at ha_innodb.cc:4581
4581                            ut_error;
(gdb) where
#0  0x08192d43 in ha_innobase::start_stmt (this=0x8a9df20, thd=0x8a9e9e8) at ha_innodb.cc:4581
#1  0x08136ce0 in check_lock_and_start_stmt (thd=0x16b, table=0x8a98b40, lock_type=TL_UNLOCK)
    at sql_base.cc:1519
#2  0x08136e28 in open_ltable (thd=0x8a9e9e8, table_list=0x8aa3a50, lock_type=TL_UNLOCK) at sql_base.cc:1570
#3  0x081a3d20 in mysqld_show_create (thd=0x8a9e9e8, table_list=0x8aa3a50) at sql_show.cc:575
#4  0x0811d022 in mysql_execute_command () at sql_parse.cc:1866
#5  0x081202b6 in mysql_parse (thd=0x8a9e9e8, inBuf=0x8a9eb18 "\001", length=21) at sql_parse.cc:3014
#6  0x0811addb in dispatch_command (command=COM_QUERY, thd=0x8a9e9e8, 
    packet=0x8a98ec9 "SHOW CREATE TABLE `t`", packet_length=21) at sql_parse.cc:1083
#7  0x0811a7bf in do_command (thd=0x8a9e9e8) at sql_parse.cc:953
#8  0x08119d8d in handle_one_connection (arg=0x0) at sql_parse.cc:737
#9  0x401609b4 in start_thread () from /lib/tls/libpthread.so.0
#10 0x00000000 in ?? ()
(gdb) print *prebuild 
No symbol "prebuild" in current context.
(gdb) print *prebuilt
$1 = {magic_n = 78540783, table = 0x42b38a68, trx = 0x42b38468, sql_stat_start = 1, mysql_has_locked = 1, 
  clust_index_was_generated = 1, index = 0x0, read_just_key = 0, used_in_HANDLER = 0, template_type = 1, 
  n_template = 0, null_bitmap_len = 0, need_to_access_clustered = 0, templ_contains_blob = 0, 
  mysql_template = 0x0, heap = 0x42b38928, ins_node = 0x0, ins_upd_rec_buff = 0x0, 
  hint_no_need_to_fetch_extra_cols = 1, upd_node = 0x0, ins_graph = 0x0, upd_graph = 0x0, pcur = 0x42b30968, 
  clust_pcur = 0x42b2c868, sel_graph = 0x0, search_tuple = 0x42b30f28, row_id = "\000\000\000\000\000", 
  clust_ref = 0x42b2cc68, select_lock_type = 0, stored_select_lock_type = 99999999, mysql_row_len = 5, 
  n_rows_fetched = 0, fetch_direction = 0, fetch_cache = {0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0}, 
  fetch_cache_first = 0, n_fetch_cached = 0, blob_heap = 0x0, old_vers_heap = 0x0, magic_n2 = 78540783}
(gdb) 

The problem, though, seems the variable "select_lock_type" which is 0 (LOCK_NONE) instead
of LOCK_X (eXclusive) or LOCK_S (Shared) which the innobase::start_stmt() function seems to
assume.
            /* When we first come here after LOCK TABLES,
                select_lock_type is set to LOCK_S or LOCK_X. Store the value
                in case we run also consistent reads and need to restore the
                value later. */

                if (prebuilt->select_lock_type != LOCK_NONE) {
                        prebuilt->stored_select_lock_type =
                                        prebuilt->select_lock_type;
                }

I'm unable to diagnose further but I hope I could convince you to let a developer have a closer look at it :-)

thanks,

-christian-

I also can't reproduce it.

Christian, did you test it using official MySQL binary or Debian binary? If the second, could you test it with MySQL binary?

This is very probably a duplicate of bug #5538, which has been fixed in the source repository. The bug manifests itself in 4.0.21, not in the 4.1 series.

Hi!

Yes, this is the mysqldump -l bug that slipped into 4.0.21.

Use --quick --single-transaction instead to work around in 4.0.21.

Regards,

Heikki

Fixed in 4.0.22.