MySQL Bugs: #33953: mysqld dies on search federated table using nullable index with

Bug #33953	mysqld dies on search federated table using nullable index with < or <= operator
Submitted:	21 Jan 2008 7:55	Modified:	13 Feb 2008 14:41
Reporter:	Masaaki HIROSE	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Federated storage engine	Severity:	S2 (Serious)
Version:	5.0.54, 5.0.45	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	federated, Signal 11

Description:
mysqld crash down when search federated table using where clause < or <= with column that has index and does not have NOT NULL constraint.

How to repeat:
1. create original table
  (borrow DDL from http://dev.mysql.com/doc/refman/5.1/en/federated-create.html )

DROP TABLE IF EXISTS test_table;
CREATE TABLE test_table (
    id     INT(20) NOT NULL AUTO_INCREMENT,
    name   VARCHAR(32) NOT NULL DEFAULT '',
    other  INT(20),
    PRIMARY KEY  (id),
    INDEX name (name),
    INDEX other_key (other)
)
ENGINE=MyISAM
DEFAULT CHARSET=latin1;

2. create fedrated table on another host

DROP TABLE IF EXISTS federated_table;
CREATE TABLE federated_table (
    id     INT(20) NOT NULL AUTO_INCREMENT,
    name   VARCHAR(32) NOT NULL DEFAULT '',
    other  INT(20),
    PRIMARY KEY  (id),
    INDEX name (name),
    INDEX other_key (other)
)
ENGINE=FEDERATED
DEFAULT CHARSET=latin1
CONNECTION='mysql://fed_user@remote_host/federated/test_table';

3. do select and mysqld is down...

* > and >= is OK.

SELECT * FROM federated_table WHERE other >  10;
SELECT * FROM federated_table WHERE other >= 10;
Empty set (0.00 sec)

* but < and <= cause mysqld down

SELECT * FROM federated_table WHERE other <  10;
SELECT * FROM federated_table WHERE other <= 10;
ERROR 2013 (HY000): Lost connection to MySQL server during query

2'. in contrast, mysqld does not down when the column has NOT NULL constraint.

DROP TABLE IF EXISTS federated_table;
CREATE TABLE federated_table (
    id     INT(20) NOT NULL AUTO_INCREMENT,
    name   VARCHAR(32) NOT NULL DEFAULT '',
    other  INT(20) NOT NULL,
    PRIMARY KEY  (id),
    INDEX name (name),
    INDEX other_key (other)
)
ENGINE=FEDERATED
DEFAULT CHARSET=latin1
CONNECTION='mysql://fed_user@remote_host/federated/test_table';

2''. (one more) in contrast, mysqld does not down when the column does not have index.

DROP TABLE IF EXISTS federated_table;
CREATE TABLE federated_table (
    id     INT(20) NOT NULL AUTO_INCREMENT,
    name   VARCHAR(32) NOT NULL DEFAULT '',
    other  INT(20),
    PRIMARY KEY  (id),
    INDEX name (name)
)
ENGINE=FEDERATED
DEFAULT CHARSET=latin1
CONNECTION='mysql://fed_user@remote_host/federated/test_table';

-- 
following is mysql.err when mysqld going down.

080121 15:50:01 - mysqld got signal 11;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

key_buffer_size=8388600
read_buffer_size=131072
max_used_connections=1
max_connections=50
threads_connected=1
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 116991 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd=0x8a97ba0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
Cannot determine thread, fp=0xb4e4d548, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x81b9a61
0x82db865
0x82dd43f
0x8265cde
0x8254e9c
0x8260146
0x82138dd
0x8204303
0x820acbc
0x81ffe99
0x8200b2b
0x81fbf44
0x81cf0de
0x81d5a43
0x81cda9e
0x81cd59c
0x81ccc14
0xb7fafb63
0xb7eeb18a
New value of fp=(nil) failed sanity check, terminating stack trace!
Please read http://dev.mysql.com/doc/mysql/en/using-stack-trace.html and follow instructions on how to resolve the stack trace. Resolved
stack trace is much more helpful in diagnosing the problem, so please do 
resolve it
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x8ada6a8 = SELECT * FROM federated_table WHERE other <  10
thd->thread_id=1
The manual page at http://www.mysql.com/doc/en/Crashing.html contains
information that should help you find out what is causing the crash.

Number of processes running now: 0
080121 15:50:02  mysqld restarted
080121 15:50:02  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
080121 15:50:02  InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 137006564.
InnoDB: Doing recovery: scanned up to log sequence number 0 137006564
InnoDB: Last MySQL binlog file position 0 1660, file name ./mysql-bin.000010
080121 15:50:02  InnoDB: Started; log sequence number 0 137006564
080121 15:50:02 [Note] Recovering after a crash using mysql-bin
080121 15:50:02 [Note] Starting crash recovery...
080121 15:50:02 [Note] Crash recovery finished.
080121 15:50:02 [Note] /usr/local/app/mysql/bin/mysqld: ready for connections.
Version: '5.0.45-log'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL)

Thank you for a bug report. Verified just as described also with 5.0.54 on Windows:

C:\Program Files\MySQL\MySQL Server 5.0>bin\mysql -uroot -proot test -P3308
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.0.54-enterprise-gpl-nt-log MySQL Enterprise Server (GPL)

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

mysql> DROP TABLE IF EXISTS federated_table;
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> CREATE TABLE federated_table (
    ->     id     INT(20) NOT NULL AUTO_INCREMENT,
    ->     name   VARCHAR(32) NOT NULL DEFAULT '',
    ->     other  INT(20),
    ->     PRIMARY KEY  (id),
    ->     INDEX name (name),
    ->     INDEX other_key (other)
    -> )
    -> ENGINE=FEDERATED
    -> DEFAULT CHARSET=latin1
    -> CONNECTION='mysql://root:root@127.0.0.1:3310/test/test_table';
Query OK, 0 rows affected (0.06 sec)

mysql> SELECT * FROM federated_table WHERE other >  10;
Empty set (0.27 sec)

mysql> SELECT * FROM federated_table WHERE other >=  10;
Empty set (0.00 sec)

mysql> SELECT * FROM federated_table WHERE other < 10;
ERROR 2013 (HY000): Lost connection to MySQL server during query

Stack trace is:

 	mysqld-nt.exe!ha_federated::create_where_from_key(String * to=0x050aeeec, st_key * key_info=0x01ab76f0, const st_key_range * start_key=0x045367a0, const st_key_range * end_key=0x045367ac, int from_records_in_range=0)  Line 1147 + 0x2 bytes	C++
 	mysqld-nt.exe!ha_federated::read_range_first(const st_key_range * start_key=0x045367a0, const st_key_range * end_key=0x045367ac, int eq_range_arg=0, int sorted=0)  Line 2221	C++
 	mysqld-nt.exe!handler::read_multi_range_first(st_key_multi_range * * found_range_p=0x050af0dc, st_key_multi_range * ranges=0x045367a0, unsigned int range_count=1, int sorted=0, st_handler_buffer * buffer=0x00000000)  Line 2528	C++
 	mysqld-nt.exe!QUICK_RANGE_SELECT::get_next()  Line 6897	C++
 	mysqld-nt.exe!rr_quick(st_read_record * info=0x044ee214)  Line 224 + 0xe bytes	C++
 	mysqld-nt.exe!join_init_read_record(st_join_table * tab=0x044ee1d0)  Line 11307 + 0x4 bytes	C++
 	mysqld-nt.exe!sub_select(JOIN * join=0x044ecd70, st_join_table * join_tab=0x044ee1d0, int end_of_records=0)  Line 10667	C++
 	mysqld-nt.exe!do_select(JOIN * join=0x00000000, List<Item> * fields=0x04540ca8, st_table * table=0x00000000, Procedure * procedure=0x00000000)  Line 10430 + 0x8 bytes	C++
 	mysqld-nt.exe!JOIN::exec()  Line 2119	C++
 	mysqld-nt.exe!mysql_select(THD * thd=0x0453fcb8, Item * * * rref_pointer_array=0x04540d38, TABLE_LIST * tables=0x044ec928, unsigned int wild_num=1, List<Item> & fields={...}, Item * conds=0x044ecc08, unsigned int og_num=0, st_order * order=0x00000000, st_order * group=0x00000000, Item * having=0x00000000, st_order * proc_param=0x00000000, unsigned __int64 select_options=2156153344, select_result * result=0x044ecd60, st_select_lex_unit * unit=0x04540978, st_select_lex * select_lex=0x04540c08)  Line 2298	C++
 	mysqld-nt.exe!handle_select(THD * thd=0x0453fcb8, st_lex * lex=0x04540918, select_result * result=0x044ecd60, unsigned long setup_tables_done_option=0)  Line 257 + 0x79 bytes	C++
 	mysqld-nt.exe!mysql_execute_command(THD * thd=0x0453fcb8)  Line 2675 + 0xa bytes	C++
 	mysqld-nt.exe!mysql_parse(THD * thd=0x0453fcb8, const char * inBuf=0x044ec7d8, unsigned int length=46, const char * * found_semicolon=0x050afb64)  Line 6114	C++
 	mysqld-nt.exe!dispatch_command(enum_server_command command=COM_QUERY, THD * thd=0x0453fcb8, char * packet=0x044e87b9, unsigned int packet_length=47)  Line 1823	C++
 	mysqld-nt.exe!do_command(THD * thd=0x00000000)  Line 1595 + 0xe bytes	C++
 	mysqld-nt.exe!handle_one_connection(void * arg=0x0453fcb8)  Line 1201 + 0x9 bytes	C++
 	mysqld-nt.exe!pthread_start(void * param=0x01a82718)  Line 85 + 0x3 bytes	C
>	mysqld-nt.exe!_threadstart(void * ptd=0x01a84f68)  Line 196 + 0x6 bytes	C
 	kernel32.dll!7c80b683() 	
 	[Frames below may be incorrect and/or missing, no symbols loaded for kernel32.dll]	
 	mswsock.dll!71a55ccc()

A duplicate of BUG#33946.