MySQL Bugs: #20422: MySQL crashes with signal 11 on NOT IN(negative,positive) on UNSIGNED INT

Bug #20422	MySQL crashes with signal 11 on NOT IN(negative,positive) on UNSIGNED INT
Submitted:	13 Jun 2006 9:49	Modified:	14 Jun 2006 9:39
Reporter:	Wouter de Jong	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.22	OS:	Linux (Linux i386 / x86_64)
Assigned to:		CPU Architecture:	Any

Description:
MySQL 5.0.22 (and below) crashes on Linux (others not tested) i386 and x86_64 (Fedora Core 5) when doing a query with 'NOT IN(negative,positive)' on an UNSIGNED INT column on a MyISAM-table.

The server get's a signal 11 and restarts. It also writes out a core-file (though not always as it seems).

However, there is 1 condition for this to happen: the table must be created on MySQL 4.1 (4.0 and below not tested) and then re-used on 5.0

a REPAIR TABLE does not correct the problem, only recreating the table and re-inserting the data.

How to repeat:
On MySQL 4.1 :

CREATE TABLE test (id INT UNSIGNED NOT NULL);
INSERT INTO test (id) VALUES(1);
INSERT INTO test (id) VALUES(2);

Copy table to MySQL 5.0 and then :

SELECT id FROM test WHERE id NOT IN (-1,1);

MySQL should crash now with signal 11 and you should get : 

ERROR 2013 (HY000): Lost connection to MySQL server during query

Suggested fix:
You can prevent/fix this when doing a mysqldump and then a restore as recommended by the docs when upgrading from MySQL 4.1 to MySQL 5.0

However, this is in some cases NOT possible for many ISP's that are offering Shared Hosting for MySQL. Too many data and databases/tables, etc.

The ultimate fix should be in MySQL, since it should not crash.
I really hope you guys can fix this in 5.0.23 release ! :)

I'll attach a backtrace so you can see where the problem occurs.

backtrace of mysql crashing on NOT IN(negative,positive)

Attachment: mysql-backtrace.txt (text/plain), 3.17 KiB.

http://bugs.mysql.com/bug.php?id=16281 is a similar issue about MySQL 5.0 failing on tables created in 4.x ... this was fixed in 5.0.21, so hopefully this issue can get fixed too (soon :>)

Hi Wouter and thanks for your problem report.
Sorry, I was unable to verify:
  MySQL 5.0.23 bk build
  FC5 x64 latest kernel on ADM64
  Table was created on Suse linux running MySQL 4.1.23. and copied into "test" directory on FC5 server. I did notthing with table (no repair etc...).

060613 18:48:21  mysqld started
060613 18:48:21  InnoDB: Started; log sequence number 0 43655
060613 18:48:21 [Note] /var/lib/mysql/libexec/mysqld: ready for connections.
Version: '5.0.23'  socket: '/tmp/mysql.sock'  port: 3307  Source distribution

[root@FC5X64 mysql]# bin/mysql -uroot --port=3307 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.23

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

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| testint        |
+----------------+
1 row in set (0.00 sec)

mysql> select * from testint;
+----+
| id |
+----+
|  1 |
|  2 |
+----+
2 rows in set (0.00 sec)

mysql> SELECT * FROM testint WHERE ID IN (1,2);
+----+
| id |
+----+
|  1 |
|  2 |
+----+
2 rows in set (0.00 sec)

mysql> SELECT * FROM testint WHERE ID IN (-1,2);
+----+
| id |
+----+
|  2 |
+----+
1 row in set (0.00 sec)

mysql> SELECT * FROM testint WHERE ID IN (-1,1);
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

mysql> SELECT ID FROM testint WHERE ID IN (-1,1);
+----+
| ID |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

mysql> SELECT ID FROM testint WHERE ID NOT IN (-1,1);
+----+
| ID |
+----+
|  2 |
+----+
1 row in set (0.00 sec)

... and so on...

Hi Tonci,

You're right.
It seems that I've simplified the reproduce table a bit too much accidently :(

Could you try this table please ?

mysql> CREATE TABLE test (id INT UNSIGNED NOT NULL PRIMARY KEY);
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO test (id) VALUES(1);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO test (id) VALUES(2);
Query OK, 1 row affected (0.00 sec)

'id' should be a PRIMARY KEY.

I've just been able to reproduce this on a few machines running 4.1.20 -> 5.0.21 and 5.0.22
(All compiled --without-debug this time)

Hi Wouter. It seems this is fixed in 5.0.23 since I'm unable to repeat.
  Suse 10.0:
mysql> select version();
+------------------+
| version()        |
+------------------+
| 4.1.21-debug-log |
+------------------+
1 row in set (0.00 sec)

mysql> show create table testint\G
*************************** 1. row ***************************
       Table: testint
Create Table: CREATE TABLE `testint` (
  `id` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> select * from testint;
+----+
| id |
+----+
|  1 |
|  2 |
+----+
2 rows in set (0.00 sec)

Copy table files from Suse/MySQL 4.1.21 to FC5/MySQL 5.0.23

Fedora Core (2.6.16-1.2111_FC5)
I tested with client on FC5 and on WinXP SP2. Below is test from XinXP client. 

C:\mysql507\bin>mysql -uroot -h10.192.192.7 --port=3307 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.0.23

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

mysql> SELECT ID FROM testint WHERE ID NOT IN (-1,1);
+----+
| ID |
+----+
|  2 |
+----+
1 row in set (0.00 sec)

mysql>

Test with client on FC5:

[root@FC5X64 mysql]# bin/mysql -uroot --port=3307
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.23

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

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.23    |
+-----------+
1 row in set (0.01 sec)

mysql> use test;
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> SELECT id FROM testint WHERE id NOT IN (-1,1);
+----+
| id |
+----+
|  2 |
+----+
1 row in set (0.01 sec)

mysql> 

Server log from FC5 AMDx64:
060613 18:48:21  mysqld started
060613 18:48:21  InnoDB: Started; log sequence number 0 43655
060613 18:48:21 [Note] /var/lib/mysql/libexec/mysqld: ready for connections.
Version: '5.0.23'  socket: '/tmp/mysql.sock'  port: 3307  Source distribution
060613 18:54:23 [Note] /var/lib/mysql/libexec/mysqld: Normal shutdown

060613 18:54:23  InnoDB: Starting shutdown...
060613 18:54:25  InnoDB: Shutdown completed; log sequence number 0 43655
060613 18:54:25 [Note] /var/lib/mysql/libexec/mysqld: Shutdown complete

060613 18:54:25  mysqld ended

060614 09:23:19  mysqld started
060614  9:23:20  InnoDB: Started; log sequence number 0 43655
060614  9:23:20 [Note] /var/lib/mysql/libexec/mysqld: ready for connections.
Version: '5.0.23'  socket: '/tmp/mysql.sock'  port: 3307  Source distribution
060614  9:37:51 [Note] /var/lib/mysql/libexec/mysqld: Normal shutdown

060614  9:37:51  InnoDB: Starting shutdown...
060614  9:37:53  InnoDB: Shutdown completed; log sequence number 0 43655
060614  9:37:53 [Note] /var/lib/mysql/libexec/mysqld: Shutdown complete

060614 09:37:53  mysqld ended

Hi Tonci,

I just downloaded and installed 5.0.23-bk, and it is fixed indeed.
I just wonder... 'what' change fixed it ? :)

Thanks for your efforts in looking into this :)

PS. it would be so nice, if MySQL could provide nightly snapshots from BitKeeper tree so you don't need all the tools required to run BUILD/autorun.sh but simply can run ./configure :)

http://bugs.mysql.com/bug.php?id=19618

I guess the fix is from that, however I could only reproduce the above when using smallint, and not with bigint/int. Still weird my problem only happened on tables created on 4.x

I'm happy it's fixed though :)