| Bug #18279 | While using complex queries with bracketed joins mysqld-nt service terminate | ||
|---|---|---|---|
| Submitted: | 16 Mar 2006 12:53 | Modified: | 6 Apr 2006 0:23 |
| Reporter: | Andrey Kazachkov | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S1 (Critical) |
| Version: | 5.0.19-win/5.0.20BK/5.1.8BK | OS: | Windows (Windows/Linux Suse 10) |
| Assigned to: | Igor Babaev | CPU Architecture: | Any |
[16 Mar 2006 14:17]
MySQL Verification Team
Thank you for the bug report. I was able to repeat on Linux too with
current source server on 5.0/5.1:
Version: '5.1.8-beta-debug' socket: '/tmp/mysql.sock' port: 3306 Source distribution
[New Thread 1119902640 (LWP 5900)]
Program received signal SIGSEGV, Segmentation fault.
[Switching to Thread 1119902640 (LWP 5900)]
0x081b14bf in Item_cond::fix_fields (this=0x9267fc0, thd=0x9219f08, ref=0x9267710) at item_cmpfunc.cc:2549
2549 while (item->type() == Item::COND_ITEM &&
(gdb)
Version: '5.0.20-debug' socket: '/tmp/mysql.sock' port: 3306 Source distribution
[New Thread 1131862960 (LWP 5875)]
[Thread 1131862960 (zombie) exited]
[New Thread 1131862960 (LWP 5880)]
Program received signal SIGSEGV, Segmentation fault.
[Switching to Thread 1131862960 (LWP 5880)]
0x0818dead in Item_cond::fix_fields (this=0x8eab5c0, thd=0x8e619e8, ref=0x8eaad18) at item_cmpfunc.cc:2549
2549 while (item->type() == Item::COND_ITEM &&
(gdb) bt full
#0 0x0818dead in Item_cond::fix_fields (this=0x8eab5c0, thd=0x8e619e8, ref=0x8eaad18) at item_cmpfunc.cc:2549
tmp_table_map = 4
li = {<base_list_iterator> = {list = 0x8eab62c, el = 0x8ea60f8, prev = 0x8eab658, current = 0x8ea60f8}, <No data fields>}
item = (class Item *) 0xa5a5a5a5
buff = "8¹vC"
__PRETTY_FUNCTION__ = "virtual bool Item_cond::fix_fields(THD*, Item**)"
#1 0x0822e0c7 in setup_conds (thd=0x8e619e8, tables=0x8e97260, leaves=0x8ea91a8, conds=0x8ea5f4c) at sql_base.cc:4741
embedded = (TABLE_LIST *) 0x8eaacf8
embedding = (TABLE_LIST *) 0x8eaacf8
select_lex = (SELECT_LEX *) 0x8e83dd0
arena = (class Query_arena *) 0x0
backup = {_vptr.Query_arena = 0x8613788, free_list = 0x822b74a, mem_root = 0x10, is_backup_arena = false, state = 1131854264}
it_is_update = false
_db_func_ = 0x8e83e58 "pqé\bpqé\b\001"
_db_file_ = 0x4376b990 "X>è\b<9~\bpqé\b<9~\b"
table = (TABLE_LIST *) 0x8eaa808
_db_level_ = 135593396
_db_framep_ = (char **) 0x4376b948
#2 0x0825fd52 in setup_without_group (thd=0x8e619e8, ref_pointer_array=0x8eab400, tables=0x8e97260, leaves=0x8ea91a8, fields=@0x8e83e58,
all_fields=@0x8ea5ed8, conds=0x8ea5f4c, order=0x0, group=0x0, hidden_group_fields=0x8ea5ebe) at sql_select.cc:282
_db_func_ = 0x8e97170 "<9~\b¸·ê\b__vtmp_nGroupToMove"
_db_file_ = 0x87e393c "<9~\b"
res = 142489916
save_allow_sum_func = 0
_db_level_ = 149438040
_db_framep_ = (char **) 0x85bbac5
<cut>
[28 Mar 2006 10:34]
Konstantin Osipov
The bug is not stored procedures specific, here is a simple test case to repeat the bug with prepared statements: drop table if exists t1, t2, t3, t4, t5; drop view if exists v1; create table t1 (id1 int primary key, id2 int); create table t2 (id1 int primary key, id2 int); create table t3 (id1 int primary key, id2 int); create table t4 (id1 int primary key, id2 int); create table t5 (id1 int primary key, id2 int); create view v1 as select t1.id1 as id, t5.id1 as ngroupbynsa from t1 inner join t2 on t2.id2 = t1.id1 left outer join (t3 inner join t4 on t4.id1 = t3.id2 inner join t5 on t4.id2 = t5.id1) on t3.id2 is not null; prepare stmt from "select * from v1 where id=2"; execute stmt; execute stmt; The server crashes on the second execution of the statement, which hints that there is an optimization at the first execution which damages the parsed statement tree.
[28 Mar 2006 10:35]
Konstantin Osipov
0xffffe410 in __kernel_vsyscall ()
(gdb) bt
#0 0xffffe410 in __kernel_vsyscall ()
#1 0x40219541 in raise () from /lib/tls/libc.so.6
#2 0x4021adbb in abort () from /lib/tls/libc.so.6
#3 0x40212925 in __assert_fail () from /lib/tls/libc.so.6
#4 0x085b7062 in __cxa_pure_virtual () at my_new.cc:51
#5 0x0818c704 in Item_cond::fix_fields (this=0x8861c40, thd=0x884b6f0,
ref=0x886a658) at item_cmpfunc.cc:2549
#6 0x0822f3e2 in setup_conds (thd=0x884b6f0, tables=0x8865a30,
leaves=0x8868028, conds=0x8860524) at sql_base.cc:4741
#7 0x08259e37 in setup_without_group (thd=0x884b6f0,
ref_pointer_array=0x88618f8, tables=0x8865a30, leaves=0x8868028,
fields=@0x8863104, all_fields=@0x8860490, conds=0x8860524, order=0x0,
group=0x0, hidden_group_fields=0x8860476) at sql_select.cc:282
#8 0x0823874a in JOIN::prepare (this=0x885f760, rref_pointer_array=0x88631c0,
tables_init=0x8865a30, wild_num=0, conds_init=0x88619d0, og_num=0,
order_init=0x0, group_init=0x0, having_init=0x0, proc_param_init=0x0,
select_lex_arg=0x8863078, unit_arg=0x8862e50) at sql_select.cc:339
#9 0x0823d77d in mysql_select (thd=0x884b6f0, rref_pointer_array=0x88631c0,
tables=0x8865a30, wild_num=0, fields=@0x8863104, conds=0x88619d0,
og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0,
select_options=2424588800, result=0x885f3b8, unit=0x8862e50,
select_lex=0x8863078) at sql_select.cc:1879
#10 0x08238379 in handle_select (thd=0x884b6f0, lex=0x8862e40,
result=0x885f3b8, setup_tables_done_option=0) at sql_select.cc:238
#11 0x081fc9f5 in mysql_execute_command (thd=0x884b6f0) at sql_parse.cc:2483
#12 0x082682f1 in Prepared_statement::execute (this=0x8862e00,
expanded_query=0x40b74a20, open_cursor=false) at sql_prepare.cc:2909
#13 0x082669c4 in mysql_sql_stmt_execute (thd=0x884b6f0) at sql_prepare.cc:2303
#14 0x081fca46 in mysql_execute_command (thd=0x884b6f0) at sql_parse.cc:2497
#15 0x08205aa2 in mysql_parse (thd=0x884b6f0, inBuf=0x8865d00 "execute stmt",
length=12) at sql_parse.cc:5710
#16 0x081fa9fb in dispatch_command (command=COM_QUERY, thd=0x884b6f0,
packet=0x884ebe9 "execute stmt", packet_length=13) at sql_parse.cc:1720
#17 0x081fa21f in do_command (thd=0x884b6f0) at sql_parse.cc:1516
#18 0x081f92b4 in handle_one_connection (arg=0x884b6f0) at sql_parse.cc:1159
#19 0x40175297 in start_thread () from /lib/tls/libpthread.so.0
#20 0x402b037e in clone () from /lib/tls/libc.so.6
[28 Mar 2006 10:41]
Konstantin Osipov
Brian, the bug is not stored procedures specific, as the test case above demonstrates. It's likely that LEFT JOIN elimination algorithm uses wrong memory to allocate new items. Please reassign to Igor's team, I can review the patch if necessary.
[28 Mar 2006 17:43]
Konstantin Osipov
An update: a view is not necessary either. prepare stmt from "select t1.id1 as id, t5.id1 as ngroupbynsa from t1 inner join t2 on t2.id2 = t1.id1 left outer join (t3 inner join t4 on t4.id1 = t3.id2 inner join t5 on t4.id2 = t5.id1) on t3.id2 is not null where t1.id1=2"; execute stmt; execute stmt; Also crashes the server.
[30 Mar 2006 0:45]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/4299
[31 Mar 2006 16:18]
Igor Babaev
ChangeSet 1.2113 06/03/29 16:45:29 igor@rurik.mysql.com +4 -0 Fixed bug #18279: crash in the cases when on conditions are moved out of a nested join to the on conditions for the nest. The bug happened due to: 1. The function simplify_joins could change on expressions for nested joins. Yet modified on expressions were not saved in prep_on_expr. 2. On expressions were not restored for nested joins in reinit_stmt_before_use. The fix will appear in 5.0.20. It was merged into 5.1.
[6 Apr 2006 0:23]
Paul DuBois
Noted in 5.0.20, 5.1.9 changelogs. Complex queries with nested joins could cause a server crash. (Bug #18279)
Description: The problem also exists in previous version 5.0.18-win. While using complex queries with bracketed joins mysqld-nt service either terminates abnormally or dies with the EXCEPTIONACCESS_VIOLATION. I can send memory dumps for both cases. Such stacks are possible 1. Stack with abnornmal termination ffffffff 000000ff 00000000 ntdll!NtTerminateProcess+0xb 000000ff 0062868d 000000ff KERNEL32!ExitProcess+0x72 000000ff 033575b8 0335cad8 mysqld_nt!__crtExitProcess+0x2f 000000ff 00000001 00000000 mysqld_nt!doexit+0xa9 000000ff 00000000 00628d32 mysqld_nt!_exit+0xd 00000019 0043c067 0335c1f0 mysqld_nt!_amsg_exit+0x22 0335c1f0 0101ae58 0335c1f0 mysqld_nt!_purecall+0x12 0101ae58 0335c210 03348ec8 mysqld_nt!Item_cond::fix_fields+0x77 0335bd50 03348ec8 0335a738 mysqld_nt!setup_conds+0x11a 0101ae58 0335c910 03348ec8 mysqld_nt!setup_without_group+0x46 03342e74 03348ec8 00000000 mysqld_nt!JOIN::prepare+0x1c1 0101ae58 03342e74 03348ec8 mysqld_nt!mysql_select+0x16e 0101ae58 03342ac8 03348e38 mysqld_nt!handle_select+0xaf 0101ae58 0101ae58 004a2701 mysqld_nt!mysql_execute_command+0x514 0101ae58 0333f178 033492a0 mysqld_nt!sp_instr_stmt::exec_core+0xd 0101ae58 0333f178 00000000 mysqld_nt!sp_lex_keeper::reset_lex_and_exec_core+0xd1 00000017 0333f178 03348538 mysqld_nt!sp_instr_stmt::execute+0x71 0101ae58 0101b39c 0101ae58 mysqld_nt!sp_head::execute+0x2ba 0101ae58 0101b39c 0101ae58 mysqld_nt!sp_head::execute_procedure+0x27d 0101ae58 0101bf80 010379f7 mysqld_nt!mysql_execute_command+0x474a 0101ae58 010379e0 00000017 mysqld_nt!mysql_parse+0x102 00000003 0101ae58 0103c911 mysqld_nt!dispatch_command+0x522 0101ae58 004e4f20 0102a0f8 mysqld_nt!do_command+0xad 0101ae58 0102a0f8 0323fb38 mysqld_nt!handle_one_connection+0x26e 0102a0f8 0323fb38 77fcae15 mysqld_nt!pthread_start+0x3b 0101c3b8 0323fb38 77fcae15 mysqld_nt!_threadstart+0x6c 0062b8b4 0101c3b8 00000000 KERNEL32!BaseThreadStart+0x52 2. Stack with exception access violation mysqld_nt!Field_bit_as_char::store+0x18 mysqld_nt!Item_cond::fix_fields+0x122 mysqld_nt!setup_without_group+0x46 mysqld_nt!JOIN::prepare+0x1c1 mysqld_nt!mysql_select+0x16e mysqld_nt!handle_select+0xaf mysqld_nt!mysql_execute_command+0x514 mysqld_nt!sp_instr_stmt::exec_core+0xd mysqld_nt!sp_lex_keeper::reset_lex_and_exec_core+0xd1 mysqld_nt!sp_instr_stmt::execute+0x71 mysqld_nt!sp_head::execute+0x2ba mysqld_nt!sp_head::execute_procedure+0x27d mysqld_nt!mysql_execute_command+0x474a mysqld_nt!sp_instr_stmt::exec_core+0xd mysqld_nt!sp_lex_keeper::reset_lex_and_exec_core+0xd1 mysqld_nt!sp_instr_stmt::execute+0x71 mysqld_nt!sp_head::execute+0x2ba mysqld_nt!sp_head::execute_procedure+0x27d mysqld_nt!mysql_execute_command+0x474a mysqld_nt!mysql_parse+0x102 mysqld_nt!dispatch_command+0x522 mysqld_nt!do_command+0xad mysqld_nt!handle_one_connection+0x26e mysqld_nt!pthread_start+0x3b mysqld_nt!_threadstart+0x6c WARNING: Stack unwind information not available. Following frames may be wrong. KERNEL32!lstrcmpiW+0xb7 How to repeat: Here is the simplest script that I managed to create Second run of the provided stored procedure `ak_fault_proc` results to the problem. delimiter GO DROP DATABASE IF EXISTS TEST1 GO CREATE DATABASE TEST1 GO USE TEST1 GO DROP TABLE IF EXISTS ak_test1 GO CREATE TABLE ak_test1( nId1 int PRIMARY KEY, nId2 int ) GO DROP TABLE IF EXISTS ak_test2 GO CREATE TABLE ak_test2( nId1 int PRIMARY KEY, nId2 int ) GO DROP TABLE IF EXISTS ak_test3 GO CREATE TABLE ak_test3( nId1 int PRIMARY KEY, nId2 int ) GO DROP TABLE IF EXISTS ak_test4 GO CREATE TABLE ak_test4( nId1 int PRIMARY KEY, nId2 int ) GO DROP TABLE IF EXISTS ak_test5 GO CREATE TABLE ak_test5( nId1 int PRIMARY KEY, nId2 int ) GO CREATE VIEW v_ak_t_1 AS SELECT ak_test1.nId1 AS `nId` , `ak_test5`.`nId1` AS `nGroupByNsa` FROM ak_test1 INNER JOIN ak_test2 ON ak_test2.nId2 = `ak_test1`.`nId1` LEFT OUTER JOIN ( ak_test3 INNER JOIN ak_test4 ON ak_test4.nId1 = ak_test3.nId2 INNER JOIN ak_test5 ON ak_test4.nId2 = ak_test5.nId1 )ON ak_test3.nId2 IS NOT NULL GO DROP PROCEDURE IF EXISTS `ak_fault_proc` GO CREATE PROCEDURE `ak_fault_proc` ( IN __vtmp_nId INT) BEGIN /* Declarations */ DECLARE __st_FETCH_STATUS INT; DECLARE __vtmp_nGroupToMove INT; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET __st_FETCH_STATUS = 1; SELECT nGroupByNsa INTO `__vtmp_nGroupToMove` FROM v_ak_t_1 WHERE v_ak_t_1.nId = `__vtmp_nId`; END GO CALL `ak_fault_proc`(2) GO CALL `ak_fault_proc`(2) GO