| Bug #446 | insert into an innodb table failed | ||
|---|---|---|---|
| Submitted: | 18 May 2003 5:02 | Modified: | 28 Jun 2003 7:15 |
| Reporter: | [ name withheld ] | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S1 (Critical) |
| Version: | 4.1.0-alpha | OS: | Linux (linux) |
| Assigned to: | Oleksandr Byelkin | CPU Architecture: | Any |
[18 May 2003 22:37]
Alexander Keremidarski
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
[20 May 2003 7:12]
Oleksandr Byelkin
ChangeSet 1.1542 03/05/20 17:09:53 bell@sanja.is.com.ua +1 -0
[28 Jun 2003 7:15]
Oleksandr Byelkin
Thank you for bugreport, patch included in bk repository and will be included in next MySQL release.
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.