MySQL Bugs: #23035: Error 124 or Server Crash on complex query

Bug #23035	Error 124 or Server Crash on complex query
Submitted:	6 Oct 2006 0:37	Modified:	6 Oct 2006 1:11
Reporter:	Dave Pullin (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.24a-community-nt-log	OS:	Windows (W2K & Linux (FC4))
Assigned to:		CPU Architecture:	Any

Description:
This query
select (
    select sessionnbr from temp as s where s.personNbr=p.personNbr 
    and session_date<('2006-10-02')  order by session_date desc limit 1
        ) as sessionNbr
        from (select personnbr from temp) as p;
yields error ERROR 1030 (HY000): Got error 124 from storage engine
on Windows and crashes the server on Linux.

This is a substantially simplified query from the real life problem. Most changes to it eliminates the bug. The bug depends on the 'temp' table being what is recreated in the 'how to repeat'.

(I get error 124 on complex queries rather a lot; this is the first one that I could isolate to a simple repeatable example.)

CONSOLE Log
mysql> select version();
+--------------------------+
| version()                |
+--------------------------+
| 5.0.24a-community-nt-log |
+--------------------------+
1 row in set (0.00 sec)

mysql> drop table if exists temp;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE `temp` ( `sessionNbr` int(11)  auto_increment, `personNbr` i
nt(11) , `session_date` date ,  PRIMARY KEY (`sessionNbr`), UNIQUE KEY `session_
date` (`session_date`,`personNbr`) )
    -> ;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into temp (sessionNbr,personNbr,session_date)
    -> select 1,1,'2006-10-02' union select 2,1,'2006-10-03' union select 3,2,'2
006-10-03'
    -> ;
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select (
    ->     select sessionnbr from temp as s where s.personNbr=p.personNbr
    ->     and session_date<('2006-10-02')  order by session_date desc limit 1
    ->         ) as sessionNbr
    ->         from (select personnbr from temp) as p;
ERROR 1030 (HY000): Got error 124 from storage engine
mysql>

How to repeat:
select version();
drop table if exists temp;
CREATE TABLE `temp` ( `sessionNbr` int(11)  auto_increment, `personNbr` int(11) , `session_date` date ,  PRIMARY KEY (`sessionNbr`), UNIQUE KEY `session_date` (`session_date`,`personNbr`) ) 
;
insert into temp (sessionNbr,personNbr,session_date)
select 1,1,'2006-10-02' union select 2,1,'2006-10-03' union select 3,2,'2006-10-03'
;
select (
    select sessionnbr from temp as s where s.personNbr=p.personNbr 
    and session_date<('2006-10-02')  order by session_date desc limit 1
        ) as sessionNbr
        from (select personnbr from temp) as p;

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

I was able to repeat on Windows with the server version reported however
I was unable to repeat with current source server either on Windows or
Linux:

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.24a-community-nt

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

mysql> select version();
+----------------------+
| version()            |
+----------------------+
| 5.0.24a-community-nt |
+----------------------+
1 row in set (0.00 sec)

mysql> drop table if exists temp;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE `temp` ( `sessionNbr` int(11)  auto_increment, `personNbr` int(11)
    -> , `session_date` date ,  PRIMARY KEY (`sessionNbr`), UNIQUE KEY `session_date`
    -> (`session_date`,`personNbr`) )
    -> ;
Query OK, 0 rows affected (0.05 sec)

mysql> insert into temp (sessionNbr,personNbr,session_date)
    -> select 1,1,'2006-10-02' union select 2,1,'2006-10-03' union select
    -> 3,2,'2006-10-03'
    -> ;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select (
    ->     select sessionnbr from temp as s where s.personNbr=p.personNbr
    ->     and session_date<('2006-10-02')  order by session_date desc limit 1
    ->         ) as sessionNbr
    ->         from (select personnbr from temp) as p;
ERROR 1030 (HY000): Got error 124 from storage engine
mysql>
--------------------------------------------------------------------------------
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.26    |
+-----------+
1 row in set (0.02 sec)

mysql> drop table if exists temp;
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> CREATE TABLE `temp` ( `sessionNbr` int(11)  auto_increment, `personNbr` int(11)
    -> , `session_date` date ,  PRIMARY KEY (`sessionNbr`), UNIQUE KEY `session_date`
    -> (`session_date`,`personNbr`) )
    -> ;
Query OK, 0 rows affected (0.05 sec)

mysql> insert into temp (sessionNbr,personNbr,session_date)
    -> select 1,1,'2006-10-02' union select 2,1,'2006-10-03' union select
    -> 3,2,'2006-10-03'
    -> ;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select (
    ->     select sessionnbr from temp as s where s.personNbr=p.personNbr
    ->     and session_date<('2006-10-02')  order by session_date desc limit 1
    ->         ) as sessionNbr
    ->         from (select personnbr from temp) as p;
+------------+
| sessionNbr |
+------------+
|       NULL |
|       NULL |
|       NULL |
+------------+
3 rows in set (0.02 sec)