MySQL Bugs: #12736: Server crash during a select

Bug #12736	Server crash during a select
Submitted:	22 Aug 2005 21:53	Modified:	18 Oct 2005 10:16
Reporter:	[ name withheld ] (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.0.9-beta-nt-max/BK source	OS:	Windows (windows 2000 sp4/Linux)
Assigned to:	Konstantin Osipov	CPU Architecture:	Any

Description:
Server crashes during a select, in the errror log I read:

InnoDB: Error: MySQL is trying to perform a SELECT
InnoDB: but it has not locked any tables in ::external_lock()!
TRANSACTION 0 6401, ACTIVE 0 sec, OS thread id 1744, thread declared inside InnoDB 497
MySQL thread id 1, query id 3 localhost 127.0.0.1 root Sending data
select count(country) as countrycount, sum(funds_requested) as smcnt, country, (select sum(funds_requested) from schools) as total_funds
from schools
group by country

How to repeat:
Create table:

DROP TABLE IF EXISTS `test`.`schools`;
CREATE TABLE `schools` (
  `school_name` varchar(45) NOT NULL,
  `country` varchar(45) NOT NULL,
  `funds_requested` float NOT NULL,
  `schooltype` varchar(45) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Load it with sample data.

Issue the following query:

select count(country) as countrycount, sum(funds_requested) as smcnt, country, (select sum(funds_requested) from schools) as total_funds
from schools
group by country

It will crash immediately if the query is executed with MySQL Query browser or upon issueing the "exit" command if executed from command line.

The error is log shows:

InnoDB: Error: MySQL is trying to perform a SELECT
InnoDB: but it has not locked any tables in ::external_lock()!
TRANSACTION 0 6401, ACTIVE 0 sec, OS thread id 1744, thread declared inside InnoDB 497
MySQL thread id 1, query id 3 localhost 127.0.0.1 root Sending data
select count(country) as countrycount, sum(funds_requested) as smcnt, country, (select sum(funds_requested) from schools) as total_funds
from schools
group by country

Could you provide the data you have that leads to the server crash? I wasn't able to crash the server with several rows. Regarding the error you see in the error log file, if you install a newer version of MySQL it will probably won't appear any more.

I was able to reproduce the crash with 5.0.9. Testing a 5.0.12 server not
crash but still InnoDB launchs an error message in the log. Will test latest
Windows server and on Linux.

Thank you for the bug report.

c:\mysql\bin>mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.9-beta-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE `schools` (
    ->   `school_name` varchar(45) NOT NULL,
    ->   `country` varchar(45) NOT NULL,
    ->   `funds_requested` float NOT NULL,
    ->   `schooltype` varchar(45) NOT NULL
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.11 sec)

mysql>
mysql> insert into schools values (
    -> "the school",
    -> "USA",
    -> 1200,
    -> "Human");
Query OK, 1 row affected (0.02 sec)

mysql> select count(country) as countrycount, sum(funds_requested) as smcnt, country,
    -> (select sum(funds_requested) from schools) as total_funds
    -> from schools
    -> group by country
    -> ;
+--------------+-------+---------+-------------+
| countrycount | smcnt | country | total_funds |
+--------------+-------+---------+-------------+
|            1 |  1200 | USA     |        1200 |
+--------------+-------+---------+-------------+
1 row in set (0.03 sec)

mysql> exit
Bye

050822 20:13:17  InnoDB: Started; log sequence number 0 48871
050822 20:13:18 [Note] mysqld-nt: ready for connections.
Version: '5.0.9-beta-nt'  socket: ''  port: 3306  Source distribution
InnoDB: Error: MySQL is trying to perform a SELECT
InnoDB: but it has not locked any tables in ::external_lock()!
TRANSACTION 0 1282, not started, OS thread id 5440
MySQL thread id 1, query id 3 localhost 127.0.0.1 root Sending data
select count(country) as countrycount, sum(funds_requested) as smcnt, country,
(select sum(funds_requested) from schools) as total_funds
from schools
group by country

InnoDB: Error: MySQL is trying to perform a SELECT
InnoDB: but it has not locked any tables in ::external_lock()!
TRANSACTION 0 1283, ACTIVE 0 sec, OS thread id 5440
MySQL thread id 1, query id 3 localhost 127.0.0.1 root Sending data
select count(country) as countrycount, sum(funds_requested) as smcnt, country,
(select sum(funds_requested) from schools) as total_funds
from schools
group by country

050822 20:16:47InnoDB: Assertion failure in thread 5440 in file D:\builds\mysql-5.0.9-beta\innobase\trx\trx0trx.c line 288
InnoDB: Failing assertion: trx->conc_state == TRX_NOT_STARTED
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. Please refer to
InnoDB: http://dev.mysql.com/doc/mysql/en/Forcing_recovery.html
InnoDB: about forcing recovery.
InnoDB: Thread 4864 stopped in file D:\builds\mysql-5.0.9-beta\innobase\os\os0sync.c line 487
InnoDB: Thread 1380 stopped in file D:\builds\mysql-5.0.9-beta\innobase\sync\sync0arr.c line 126

c:\mysql\bin>

c:\mysql\bin>mysqld-nt --standalone --console
050822 20:19:09  InnoDB: Started; log sequence number 0 113194
050822 20:19:10 [Note] mysqld-nt: ready for connections.
Version: '5.0.12-beta-nt'  socket: ''  port: 3306  Source distribution
InnoDB: Error: MySQL is trying to perform a SELECT
InnoDB: but it has not locked any tables in ::external_lock()!
TRANSACTION 0 6402, not started, OS thread id 4872
MySQL thread id 1, query id 3 localhost 127.0.0.1 root Sending data
select count(country) as countrycount, sum(funds_requested) as smcnt, country,
(select sum(funds_requested) from schools) as total_funds
from schools
group by country

InnoDB: Error: MySQL is trying to perform a SELECT
InnoDB: but it has not locked any tables in ::external_lock()!
TRANSACTION 0 6403, ACTIVE 0 sec, OS thread id 4872
MySQL thread id 1, query id 3 localhost 127.0.0.1 root Sending data
select count(country) as countrycount, sum(funds_requested) as smcnt, country,
(select sum(funds_requested) from schools) as total_funds
from schools
group by country

050822 20:19:52  InnoDB: Warning: MySQL is closing a connectionInnoDB: that has an active InnoDB transaction. We roll back that
InnoDB: transaction. 0 row modifications to roll back.

With latest BK pull, still the below error is showed by InnoDB:

miguel@hegel:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.12-beta-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE `schools` (
    ->   `school_name` varchar(45) NOT NULL,
    ->   `country` varchar(45) NOT NULL,
    ->   `funds_requested` float NOT NULL,
    ->   `schooltype` varchar(45) NOT NULL
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.08 sec)

mysql> 
mysql> insert into schools values (
    -> "the school",
    -> "USA",
    -> 1200,
    -> "Human");
Query OK, 1 row affected (0.00 sec)

mysql> select count(country) as countrycount, sum(funds_requested) as smcnt, country,
    -> (select sum(funds_requested) from schools) as total_funds
    -> from schools
    -> group by country
    -> ;
+--------------+-------+---------+-------------+
| countrycount | smcnt | country | total_funds |
+--------------+-------+---------+-------------+
|            1 |  1200 | USA     |        1200 |
+--------------+-------+---------+-------------+
1 row in set (0.01 sec)

mysql> exit
Bye
miguel@hegel:~/dbs/5.0> 

miguel@hegel:~/dbs> cd 5.0/
miguel@hegel:~/dbs/5.0> libexec/mysqld
050822 21:39:11  InnoDB: Started; log sequence number 0 98242
050822 21:39:11 [Note] libexec/mysqld: ready for connections.
Version: '5.0.12-beta-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
050822 21:40:19 [ERROR] trx->active_trans == 0, but trx->conc_state != TRX_NOT_STARTED
050822 21:40:19 [Warning] MySQL is closing a connection that has an active InnoDB transaction.  0 row modifications will roll back.

Hi!

Note that I have removed the error print and the assertion from 5.0.12 temporarily, because cursors do not obey that lock count rule currently. That does not mean that this bug would have been fixed! Only the error print is suppressed in 5.0.12.

The bug probably is that MySQL releases table locks too early when processing the SQL query. In the past years there have been about 10 bugs of this type. InnoDB's assertions have uncovered them.

Regards,

Heikki

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/internals/30926

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/internals/31015

Sorry, Jon, pushed into 5.0 tree, tagged 5.0.15

Added to 5.0.15 changelog.