MySQL Bugs: #446: insert into an innodb table failed

Bug #446	insert into an innodb table failed
Submitted:	18 May 2003 7:02	Modified:	28 Jun 2003 9:15
Reporter:	[ name withheld ]
Status:	Closed
Category:	Server	Severity:	S1 (Critical)
Version:	4.1.0-alpha	OS:	Linux (linux)
Assigned to:	Bugs System	Target Version:

Description:
When I issued the following sql statement to insert some values into a table, mysql
server crashed:
INSERT INTO PERSONNEL(INDIVIDUAL_ID, PARENT_ID) VALUES((SELECT ID FROM INDIVIDUAL WHERE
NAME='anonymous' AND DOMAIN_ID=(SELECT ID FROM DOMAIN WHERE NAME='__SPECIAL' AND
PARENT_ID IS NULL)), 1);

The log said:
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
sort_buffer_size=2097144
max_used_connections=0
max_connections=100
threads_connected=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 225791 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.
 
thd=0x87bbbe0
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=0xbe7feb48, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x811d7bc
0x400204e1
0x8156f86
0x815600a
0x815615d
0x8151c42
0x814c82a
0x8147965
0x8103689
0x8101e43
0x80c9ec7
0x814383b
0x81589a6
0x812c4b2
0x812eb99
0x81295a7
0x812920a
0x8128b55
0x4001a706
0x402727a7
New value of fp=(nil) failed sanity check, terminating stack trace!
Please read http://www.mysql.com/doc/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 0x87ccb58 = INSERT INTO PERSONNEL(INDIVIDUAL_ID, PARENT_ID) VALUES((SELECT
ID FROM INDIVIDUAL WHERE NAME='anonymous' AND DOMAIN_ID=(SELECT ID FROM DOMAIN WHERE
NAME='__SPECIAL' AND PARENT_ID IS NULL)), 1)
thd->thread_id=1
 
Successfully dumped variables, if you ran with --log, take a look at the
details of what thread 1 did to cause the crash.  In some cases of really
bad corruption, the values shown above may be invalid.
 
The manual page at http://www.mysql.com/doc/C/r/Crashing.html contains
information that should help you find out what is causing the crash.
 
Number of processes running now: 0
030518 19:41:00  mysqld restarted
030518 19:41:01  InnoDB: Database was not shut down normally.
InnoDB: Starting recovery from log files...
InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 3988612
InnoDB: Doing recovery: scanned up to log sequence number 0 4018655
030518 19:41:01  InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21
22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51
52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81
82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
InnoDB: Apply batch completed
030518 19:41:01  InnoDB: Flushing modified pages from the buffer pool...
030518 19:41:01  InnoDB: Started
/usr/local/mysql/libexec/mysqld: ready for connections.
Version: '4.1.0-alpha'  socket: '/tmp/mysql.sock'  port: 3306
 
I am running a linux box with mysql 4.1.0-alpha from source code compiled with gcc
version 3.2 20020903 (Red Hat Linux 8.0 3.2-7) and glibc-2.3.1-46 (rpm from redhat 8.0).

How to repeat:
First, create 3 tables:
CREATE TABLE DOMAIN (
	ID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
	NAME VARCHAR(32) NOT NULL,
	DESCRIPTION VARCHAR(255),
	PARENT_ID INT UNSIGNED,
	INDEX(PARENT_ID),
	FOREIGN KEY (PARENT_ID) REFERENCES DOMAIN(ID)
) TYPE=INNODB CHARSET=UTF8 AUTO_INCREMENT=10;
CREATE TABLE INDIVIDUAL (
	ID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,

	DOMAIN_ID INT UNSIGNED NOT NULL,
	INDEX(DOMAIN_ID),
	FOREIGN KEY (DOMAIN_ID) REFERENCES DOMAIN(ID),

	USERNAME VARCHAR(255) NOT NULL,
	PASSWORD VARCHAR(64) NOT NULL,
	PASSWORD_EXPIRE_DATE DATETIME,
	ACTIVATE_DATE DATETIME,
	EXPIRE_DATE DATETIME,
	TIMEZONE_OFFSET TINYINT NOT NULL DEFAULT 8,
	EMAIL VARCHAR(255),
	STATUS SET('LOCKED', 'ACTIVE', 'TO_BE_APPROVED', 'CHANGING_PASSWORD') DEFAULT 'ACTIVE'
) TYPE=INNODB CHARSET=UTF8 AUTO_INCREMENT=100;
CREATE TABLE PERSONNEL (
	ID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
	NAME VARCHAR(32),
	INDEX(NAME),
	DESCRIPTION VARCHAR(255),

	INDIVIDUAL_ID INT UNSIGNED,
	INDEX(INDIVIDUAL_ID),
	FOREIGN KEY (INDIVIDUAL_ID) REFERENCES INDIVIDUAL(ID),

	PARENT_ID INT UNSIGNED,
	INDEX(PARENT_ID),
	FOREIGN KEY (PARENT_ID) REFERENCES PERSONNEL(ID)
) TYPE=INNODB CHARSET=UTF8 AUTO_INCREMENT=100;

And then, inert some values into these tables:
INSERT INTO DOMAIN(ID, NAME, DESCRIPTION, PARENT_ID) VALUES(1, '__SPECIAL', 'This is a
special domain for special users, such as root and anonymous.', NULL);
INSERT INTO INDIVIDUAL(ID, DOMAIN_ID, USERNAME, PASSWORD) VALUES(2, (SELECT ID FROM
DOMAIN WHERE NAME='__SPECIAL' AND PARENT_ID IS NULL), 'anonymous', SHA1(''));

The above two statements can be successfully issued. When you run the following
statement, mysql will crash:
INSERT INTO PERSONNEL(INDIVIDUAL_ID, PARENT_ID) VALUES((SELECT ID FROM INDIVIDUAL WHERE
NAME='anonymous' AND DOMAIN_ID=(SELECT ID FROM DOMAIN WHERE NAME='__SPECIAL' AND
PARENT_ID IS NULL)), 1);

mysql log reports signal 11 captured.

Note that Subquery fails with Syntax error:

mysql> (SELECT ID FROM
    -> INDIVIDUAL WHERE NAME='anonymous');
ERROR 1054: Unknown column 'NAME' in 'where clause'

With correct syntax Subquery INSERT works.

mysql> INSERT INTO PERSONNEL(INDIVIDUAL_ID, PARENT_ID) VALUES((SELECT ID FROM
    -> INDIVIDUAL WHERE USERNAME='anonymous' AND DOMAIN_ID=(SELECT ID FROM DOMAIN WHERE
NAME='__SPECIAL' AND PARENT_ID IS NULL)), 1);
Query OK, 1 row affected (0.05 sec)

With original Subquery MySQL crashes with following backtrace:

(gdb) bt
#0  add_key_field (key_fields=0xbe7feff4, and_level=0, field=0x8b059b8, eq_func=true,
value=0x8b118a0, usable_tables=13726952749189300224) at sql_select.cc:1846
#1  0x081770ca in add_key_fields (stat=0x8b0e188, key_fields=0xbe7feff4,
and_level=0x8b118a0, cond=0x0, usable_tables=18446744073709551615) at item_func.h:117
#2  0x08176eb4 in add_key_fields (stat=0x8b0e188, key_fields=0xbe7feff4,
and_level=0xbe7feff8, cond=0x8b12a40, usable_tables=18446744073709551615) at
sql_select.cc:1898
#3  0x081775d5 in update_ref_and_keys (thd=0x8b07600, keyuse=0x8b0dc18,
join_tab=0x8b0e188, tables=1, cond=0x8b12a40, normal_tables=18446744073709551615,
select_lex=0x2) at sql_select.cc:2134
#4  0x081760a3 in make_join_statistics (join=0x8b0d2f8, tables=0x0, conds=0x8b12a40,
keyuse_array=0x2) at sql_select.cc:1535
#5  0x08172ab8 in JOIN::optimize() (this=0x8b0d2f8) at sql_select.cc:491
#6  0x0811ff09 in subselect_single_select_engine::exec() (this=0x8b12b20) at
item_subselect.cc:814
#7  0x0811d981 in Item_singlerow_subselect::val_int() (this=0x8b12ab8) at
item_subselect.cc:275
#8  0x080de924 in Item::save_in_field(Field*, bool) (this=0x8b12ab8, field=0x8b05e80,
no_conversions=false) at item.cc:999
#9  0x0816de8d in fill_record(List<Item>&, List<Item>&) (fields=@0x2, values=@0x8b07600)
at sql_base.cc:2240
#10 0x081882b7 in mysql_insert(THD*, st_table_list*, List<Item>&, List<List<Item> >&,
List<Item>&, List<Item>&, enum_duplicates) (thd=0x8b064d0, table_list=0x8b12c40,
fields=@0x8b068d4, values_list=@0x8b068ec, update_fields=@0x8b067ac,
update_values=@0x8b068e0, duplic=DUP_ERROR) at sql_insert.cc:261
#11 0x08152639 in mysql_execute_command(THD*) (thd=0x8b064d0) at sql_parse.cc:2287
#12 0x081556c3 in mysql_parse(THD*, char*, unsigned) (thd=0x8b064d0, inBuf=0x8b11320
"INSERT INTO PERSONNEL(INDIVIDUAL_ID, PARENT_ID) VALUES((SELECT ID FROM\nINDIVIDUAL WHERE
NAME='anonymous' AND DOMAIN_ID=(SELECT ID FROM DOMAIN\nWHERE NAME='__SPECIAL' AND
PARENT_ID IS NULL)), 1)", length=145778184) at sql_parse.cc:3380
#13 0x0814f799 in dispatch_command(enum_server_command, THD*, char*, unsigned)
(command=COM_QUERY, thd=0x8b064d0, packet=0x8b07d41 "INSERT INTO PERSONNEL(INDIVIDUAL_ID,
PARENT_ID) VALUES((SELECT ID FROM\nINDIVIDUAL WHERE NAME='anonymous' AND DOMAIN_ID=(SELECT
ID FROM DOMAIN\nWHERE NAME='__SPECIAL' AND PARENT_ID IS NULL)), 1)", packet_length=193) at
sql_parse.cc:1249
#14 0x0814f016 in do_command(THD*) (thd=0x8b064d0) at sql_parse.cc:1049
#15 0x0814e53e in handle_one_connection (arg=0x2) at sql_parse.cc:838
#16 0x40042812 in pthread_start_thread_event () from /lib/libpthread.so.0

ChangeSet 
  1.1542 03/05/20 17:09:53 bell@sanja.is.com.ua +1 -0

Thank you for bugreport, patch included in bk repository and will be included 
in next MySQL release.