Bug #8720 Server crash after lock tables MyISAM table, select from InnoDB temporary table
Submitted: 23 Feb 2005 2:20 Modified: 23 Feb 2005 9:09
Reporter: peter mckinna Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:4.1.9-nt OS:Windows (Windows XP)
Assigned to: CPU Architecture:Any

[23 Feb 2005 2:20] peter mckinna 
Description:
A strange combination of locking and using a temporary table
causes the mysql server to crash on the select from the temporary table
or a delete from the temp table.

I have only tested this on windows. This code was working before
my update to 4.1.9. I was using 4.1.1 on Linux.

How to repeat:
Create a new database and run the following script.

DROP TABLE IF EXISTS aaa;
CREATE TABLE aaa  (
  Id int(11) NOT NULL auto_increment,
  name varchar(255) default NULL,
  PRIMARY KEY  (Id)
) TYPE=MyISAM DEFAULT CHARSET=latin1;

create temporary table tmp(parentid int(11) not null, primary key(parentid));
lock tables aaa write;
insert into tmp values(1);
select * from tmp;
unlock tables;
[23 Feb 2005 3:16] MySQL Verification Team 
Even the queries applied are different it is related to the same bug
http://bugs.mysql.com/bug.php?id=8677

Is necessary to start the server as:

C:\mysql\bin>mysqld-debug --standalone --console --default-storage-engine=innodb
050223  0:09:08  InnoDB: Started; log sequence number 0 43634
mysqld-debug: ready for connections.
Version: '4.1.11-debug'  socket: ''  port: 3306  Source distribution

Notice: --default-storage-engine=innodb

Then when the crash occurs:

InnoDB: Error: MySQL is trying to perform a SELECT
InnoDB: but it has not locked any tables in ::external_lock()!
TRANSACTION 0 1281, not started, OS thread id 1476, thread declared inside InnoDB 500
MySQL thread id 1, query id 6 localhost 127.0.0.1 root Sending data
select * from tmp

050223  0:09:44InnoDB: Assertion failure in thread 1476 in file C:\mysql-4.1.11\innobase\row\row0sel.c line 2922
InnoDB: Failing assertion: 0
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.

Thank you for the bug report.
[23 Feb 2005 8:34] Heikki Tuuri 
Miguel, Peter,

yes, indeed. If one uses LOCK TABLES on a MyISAM table, and creates and accesses an InnoDB type temp table, InnoDB asserts and crashes mysqld.

Fixed in 4.1.11.

Thank you,

Heikki