| Bug #14143 | select with many left joins on large data set makes server crash | ||
|---|---|---|---|
| Submitted: | 19 Oct 2005 15:10 | Modified: | 3 Nov 2005 13:58 |
| Reporter: | Berto van de Kraats | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server: Prepared statements | Severity: | S1 (Critical) |
| Version: | 5.0.16-nightly-20051017-debug | OS: | Linux (Linux Suse sles 9 sp1) |
| Assigned to: | Assigned Account | CPU Architecture: | Any |
[19 Oct 2005 15:10]
Berto van de Kraats
[19 Oct 2005 15:13]
Berto van de Kraats
Cpp trace of problem
Attachment: bug14143.cpp (text/plain), 32.35 KiB.
[19 Oct 2005 15:13]
Berto van de Kraats
Table data to reproduce the problem
Attachment: bug14143.table.dump.bz2 (application/octet-stream, text), 127.10 KiB.
[19 Oct 2005 17:32]
MySQL Verification Team
Thank you for bug report and test case.
051019 15:16:40 [Note] /home/miguel/dbs/5.0/libexec/mysqld: ready for connections.
Version: '5.0.15-debug' socket: '/tmp/mysql.sock' port: 3306 Source distribution
[New Thread 1132456880 (LWP 2477)]
[Thread 1132456880 (zombie) exited]
[New Thread 1132456880 (LWP 2554)]
Program received signal SIGSEGV, Segmentation fault.
[Switching to Thread 1132456880 (LWP 2554)]
0x00000000 in ?? ()
(gdb) bt full
#0 0x00000000 in ?? ()
No symbol table info available.
#1 0x08330648 in Materialized_cursor::fetch (this=0x8eb9a90, num_rows=1) at sql_cursor.cc:589
thd = (class THD *) 0x8e3d3f0
res = 0
#2 0x08254ff6 in mysql_stmt_fetch (thd=0x8e3d3f0, packet=0x8e619c1 "\001", packet_length=9) at sql_prepare.cc:2329
stmt_id = 1
num_rows = 1
stmt = (Prepared_statement *) 0x8e25970
stmt_backup = {<ilink> = {_vptr.ilink = 0x85dcea8, prev = 0x0, next = 0x0}, <Query_arena> = {_vptr.Query_arena = 0x85dcebc,
free_list = 0x85b1c48, mem_root = 0x43000000, is_backup_arena = false, state = 1132452252}, main_mem_root = {free = 0x0, used = 0x0,
pre_alloc = 0x0, min_malloc = 0, block_size = 8714890, block_num = 0, first_block_usage = 0, error_handler = 0x8e699f0}, main_lex = {
_vptr.st_lex = 0x85b1c98, yylineno = 1132452016, yytoklen = 1132452012, yylval = 0x437fd8a8, unit = {<st_select_lex_node> = {
_vptr.st_select_lex_node = 0x85b1be8, next = 0x8e6da28, prev = 0x3, master = 0x437fdfdc, slave = 0x86a4e30, link_next = 0x8e6da55,
link_prev = 0x9, options = 641441306521886684, uncacheable = 0 '\0', linkage = UNSPECIFIED_TYPE, no_table_names_allowed = 33,
no_error = 67}, result_table_list = {next_local = 0x86afe00, next_global = 0x8e6daa0, prev_global = 0x1a, db = 0x0,
alias = 0x1 <Address 0x1 out of bounds>, table_name = 0x8e6da2c '\217' <repeats 200 times>...,
schema_table_name = 0x7 <Address 0x7 out of bounds>, option = 0x437fdfdc "", on_expr = 0x86a6b40, prep_on_expr = 0x8e6da34,
cond_equal = 0xb, natural_join = 0x437fdfdc, is_natural_join = 228, join_using_fields = 0x8e6da40, join_columns = 0x9,
is_join_columns_complete = 21, next_name_resolution_table = 0x40175d17, use_index = 0x8e6da4a, ignore_index = 0x437fd938,
table = 0x814ae35, derived_result = 0x437fd970, correspondent_table = 0x401760c0, derived = 0x437fd948, schema_table = 0x8587216,
schema_select_lex = 0x87cdac0, schema_table_reformed = 176, schema_table_param = 0x437fd9f8, select_lex = 0x81e11bc,
view = 0x437fd970, field_translation = 0x437fd9a0, field_translation_end = 0x50, ancestor = 0x8792fc0, belong_to_view = 0x4,
next_leaf = 0x0, where = 0x8e699d8, check_option = 0x0, query = {str = 0x1 <Address 0x1 out of bounds>, length = 0}, md5 = {
str = 0x8e699d8 "XYâ\bйæ\bê\025[\bR\006", length = 149148656}, source = {str = 0x437fd9a0 "a\\i\bØ\231æ\bèÙ\177CDâV\bm\001",
length = 80}, view_db = {str = 0x50 <Address 0x50 out of bounds>, length = 139914240}, view_name = {str = 0x8792fc0 "!",
length = 1132452256}, timestamp = {str = 0x437fd99c "vàV\ba\\i\bØ\231æ\bèÙ\177CDâV\bm\001", length = 1132452248}, definer = {
user = {str = 0x862eaf9 "check_grant", length = 140697376}, host = {str = 0x437fd9e8 "hÚ\177C5±!\ba\b", length = 139911286},
password = {str = 0x8695c61 "free_root", length = 149330392}}, file_version = 600916383118645736,
updatable_view = 4863845678679327085, revision = 4863845645452040668, algorithm = 4863845661499457608,
view_suid = 641369254150527448, with_check = 640592448053051392, effective_with_check = 104 'h', effective_algorithm = 218 'Ú',
grant = {grant_table = 0x821abbf, version = 149148656, privilege = 149148656, want_privilege = 1132452456},
engine_data = 9212841275701, callback_func = 0x85ec128 <vtable for sys_var_thd_enum+40>, lock_type = 1132452376,
outer_join = 142285108, shared = 1132452348, db_length = 0, table_name_length = 142285108, updatable = 8, straight = 218,
updating = 127, force_index = 67, ignore_leaves = false, dep_tables = 584969826678594120, on_expr_dep_tables = 606688526110440181,
nested_join = 0x87b1934, embedding = 0x437fda48, join_list = 0x87b1934, cacheable_table = 44, table_in_first_from_clause = 218,
skip_temporary = 127, contain_auto_increment = 67, multitable_view = false, compact_view_format = false, where_processed = false,
required_type = 142285108, timestamp_buffer = "8Ú\177C\000\000\000\000H\034[\b¨Ú\177Cb8\036\b", prelocking_placeholder = 122},
union_result = 0x437fda7c, table = 0x437fda78, result = 0x437fda74, found_rows_for_union = 140656312, res = false, prepared = false,
---Type <return> to continue, or q <return> to quit---
optimized = false, executed = false, cleaned = 168, item_list = {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x87b1934,
last = 0x437fda6c, elements = 0}, <No data fields>}, types = {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x87b1934,
last = 0x437fda78, elements = 0}, <No data fields>}, global_parameters = 0x0, return_to = 0x0,
select_limit_cnt = 641201336976670720, offset_limit_cnt = 4863852310108831744, item = 0x0, thd = 0x8e3d3f0, fake_select_lex = 0x0,
union_distinct = 0x0, describe = 125, last_procedure = 0x12cd}, select_lex = {<st_select_lex_node> = {
_vptr.st_select_lex_node = 0x85b1b88, next = 0x437fdfac, prev = 0x0, master = 0x100, slave = 0x87b1934, link_next = 0x437fdac4,
link_prev = 0x0, options = 4863846709613762868, uncacheable = 0 '\0', linkage = UNSPECIFIED_TYPE, no_table_names_allowed = 220,
no_error = 218}, context = {<Sql_alloc> = {<No data fields>}, outer_context = 0x0, table_list = 0x0,
first_name_resolution_table = 0x87b1934, last_name_resolution_table = 0x437fdaec, select_lex = 0x0, error_processor = 0x437fdaf4,
error_processor_data = 0x0, resolve_in_select_list = 52, check_privileges = true}, db = 0x87b1934 "4\031{\b", where = 0x437fdb04,
having = 0x0, prep_where = 0x437fdb0c, parent_lex = 0x87b1934, olap = 1132452628, table_list = {elements = 0, first = 0x437fdb1c "",
next = 0x0}, group_list = {elements = 0, first = 0x0, next = 0x0}, item_list = {<base_list> = {<Sql_alloc> = {<No data fields>},
first = 0x87b1934, last = 0x437fdb34, elements = 0}, <No data fields>},
interval_list = {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x87b1934, last = 0x437fdb40,
elements = 0}, <No data fields>}, use_index = {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x87b1934,
last = 0x437fdb4c, elements = 0}, <No data fields>}, use_index_ptr = 0x1,
ignore_index = {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x87b1934, last = 0x437fdb5c,
<cut>
[21 Oct 2005 8:55]
Sergey Petrunya
Here is location of the crash:
#1 0x082199a2 in mysql_stmt_fetch (thd=0x8c88988, packet=0x8cacf59 "\001", packet_length=9) at sql_prepare.cc:2329
#2 0x081acd24 in dispatch_command (command=COM_STMT_FETCH, thd=0x8c88988, packet=0x8cacf59 "\001", packet_length=9) at sql_parse.cc:1663
#3 0x081ac666 in do_command (thd=0x8c88988) at sql_parse.cc:1498
#4 0x081ab6e3 in handle_one_connection (arg=0x8c88988) at sql_parse.cc:1143
#5 0xb7f5a13d in pthread_start_thread () from /lib/libpthread.so.0
#6 0xb7f5a2e2 in pthread_start_thread_event () from /lib/libpthread.so.0
#7 0xb7e8c07a in clone () from /lib/libc.so.6
(gdb) p *table->file
$7 = {<Sql_alloc> = {<No data fields>}, _vptr.handler = 0x8f8f8f8f, table = 0x8f8f8f8f, ht = 0x8f8f8f8f, ref = 0x8f8f8f8f <Address 0x8f8f8f8f out of bounds>, dupp_ref = 0x8f8f8f8f <Address 0x8f8f8f8f out of bounds>, data_file_length = 10344644715844964239, max_data_file_length = 10344644715844964239, index_file_length = 10344644715844964239, max_index_file_length = 10344644715844964239, delete_length = 10344644715844964239, auto_increment_value = 10344644715844964239, records = 10344644715844964239, deleted = 10344644715844964239, raid_chunksize = 2408550287, mean_rec_length = 2408550287, create_time = -1886417009, check_time = -1886417009, update_time = -1886417009, multi_range_sorted = 143, multi_range_curr = 0x8f8f8f8f, multi_range_end = 0x8f8f8f8f, multi_range_buffer = 0x8f8f8f8f, save_end_range = {key = 0x8f8f8f8f <Address 0x8f8f8f8f out of bounds>, length = 2408550287, flag = 2408550287}, end_range = 0x8f8f8f8f, range_key_part = 0x8f8f8f8f, key_compare_result_on_equal = -1886417009, eq_range = 143, errkey = 2408550287, sortkey = 2408550287, key_used_on_scan = 2408550287, active_index = 2408550287, ref_length = 2408550287, block_size = 2408550287, raid_type = 2408550287, raid_chunks = 2408550287, ft_handler = 0x8f8f8f8f, inited = 2408550287, auto_increment_column_changed = 143, implicit_emptied = 143, pushed_cond = 0x8f8f8f8f}
(gdb) up
i.e. table->file points to uninitialized data.
One can successfully prepare/execute several times the same prepared statement using SQL syntax for PS. It seems that the bug is in cursors.
[21 Oct 2005 8:57]
Sergey Petrunya
Kostja, feel free to reassign back if there will be reasons to beleive the bug is not in the cursors.
[2 Nov 2005 14:10]
Konstantin Osipov
The supplied test case doesn't crash the server anymore:
kostja@dragonfly:~> ./a.out
!!! Error (628):
Expected value:1
Returned value:0
mysql error:
a.out: ./bug14143.cpp:628: int main(): Assertion `0' failed.
zsh: 835 abort (core dumped) ./a.out
However, there are plenty of warnings when the server is run under valgrind.
Also it occasionally crashes when run under gdb.
There is no easy way to reproduce the problem however :(
An equivalent test case in SQL:
delimiter |
drop procedure p1|
create procedure p1()
begin
declare a0, a1, a2, a3, a4, a5, a6, a7, a8, a9,
b0, b1, b2, b3, b4, b5, b6, b7, b8, b9,
c0, c1, c2, c3, c4, c5, c6, c7, c8, c9,
d0, d1, d2, d3, d4, d5, d6, d7, d8, d9,
e0, e1, e2, e3, e4, e5, e6, e7, e8, e9,
f0, f1, f2, f3, f4, f5, f6, f7, f8, f9,
g0, g1, g2, g3, g4, g5, g6, g7, g8, g9,
h0, h1, h2, h3, h4, h5, h6, h7, h8, h9,
i0, i1, i2, i3, i4, i5, i6, i7, i8, i9,
j0, j1, j2, j3, j4 varchar(255);
declare done int default 0;
declare c cursor for
SELECT a0.t_item, a0.t_seak, a0.t_cuqp, a0.t_cupp, a0.t_cpgp,
a0.t_csgp, a0.t_ccur, a0.t_prip, a0.t_cvat, a0.t_otbp,
a0.t_buyr, a0.t_edco, a0.t_mlco, a0.t_rtdp, a0.t_rtdm,
a0.t_rtqp, a0.t_rtqm, a0.t_acci, a0.t_mmnf, a0.t_cims,
a0.t_cofc, a0.t_cwar, a0.t_retw, a0.t_vryn, a0.t_suti,
a0.t_sutu, a0.t_qual, a0.t_hstq, a0.t_hstd, a0.t_casl,
a0.t_txtp, 90, a1.t_dsca, a1.t_kitm, b0.t_nama,
a2.t_nama, a3.t_dsca, a4.t_dsca, a5.t_dsca, a6.t_dsca,
a7.t_dsca, a8.t_dsca, a7.t_cwar, a7.t_cwar, a1.t_item,
a1.t_kitm, a1.t_citg, a1.t_itmt, a1.t_dsca, a1.t_dscb,
a1.t_dscc, a1.t_dscd, a1.t_seak, a1.t_seab, a1.t_uset,
a1.t_cuni, a1.t_cwun, a1.t_wght, a1.t_ctyp, a1.t_ltct,
a1.t_csel, a1.t_csig, a1.t_ctyo, a1.t_cpcl, a1.t_cood,
a1.t_eitm, a1.t_umer, a1.t_cpln, a1.t_ccde, a1.t_cmnf,
a1.t_cean, a1.t_cont, a1.t_cntr, a1.t_cprj, a1.t_repl,
a1.t_cpva, a1.t_dfit, a1.t_stoi, a1.t_cpcp, a1.t_unef,
a1.t_ichg, a1.t_uefs, a1.t_seri, a1.t_styp, a1.t_psiu,
a1.t_efco, a1.t_indt, a1.t_chma, a1.t_edco, a1.t_mcoa,
a1.t_opts, a1.t_txta, 90, a9.t_otbp
FROM (((((((((ttdipu001090 AS a0 LEFT JOIN ttccom001090 AS a2 ON
a2.t_emno = a0.t_buyr)
LEFT JOIN ttcmcs002090 AS a3 ON a3.t_ccur = a0.t_ccur)
LEFT JOIN ttcmcs037090 AS a4 ON a4.t_cvat = a0.t_cvat)
LEFT JOIN ttcmcs024090 AS a5 ON a5.t_cprg = a0.t_cpgp)
LEFT JOIN ttcmcs044090 AS a6 ON a6.t_csgp = a0.t_csgp)
LEFT JOIN ttdpur012090 AS a7 ON a7.t_cofc = a0.t_cofc)
LEFT JOIN ttcmcs003090 AS a8 ON a8.t_cwar = a0.t_cwar)
LEFT JOIN ttccom120090 AS a9 ON a9.t_otbp = a0.t_otbp)
LEFT JOIN ttccom100090 AS b0 ON b0.t_bpid = a9.t_otbp),
ttcibd001090 a1
WHERE
a1.t_item = a0.t_item AND
(a0.t_item >= " PURCHASEORDERITEM2260 ") AND
a0.t_item = a1.t_item
ORDER BY 1;
declare continue handler for sqlstate '02000' set done = 1;
open c;
repeat
fetch from c into a0, a1, a2, a3, a4, a5, a6, a7, a8, a9,
b0, b1, b2, b3, b4, b5, b6, b7, b8, b9,
c0, c1, c2, c3, c4, c5, c6, c7, c8, c9,
d0, d1, d2, d3, d4, d5, d6, d7, d8, d9,
e0, e1, e2, e3, e4, e5, e6, e7, e8, e9,
f0, f1, f2, f3, f4, f5, f6, f7, f8, f9,
g0, g1, g2, g3, g4, g5, g6, g7, g8, g9,
h0, h1, h2, h3, h4, h5, h6, h7, h8, h9,
i0, i1, i2, i3, i4, i5, i6, i7, i8, i9,
j0, j1, j2, j3;
-- if not done then
-- select a0, a1, a2, a3, a4, a5, a6, a7, a8, a9,
-- b0, b1, b2, b3, b4, b5, b6, b7, b8, b9,
-- c0, c1, c2, c3, c4, c5, c6, c7, c8, c9,
-- d0, d1, d2, d3, d4, d5, d6, d7, d8, d9,
-- e0, e1, e2, e3, e4, e5, e6, e7, e8, e9,
-- f0, f1, f2, f3, f4, f5, f6, f7, f8, f9,
-- g0, g1, g2, g3, g4, g5, g6, g7, g8, g9,
-- h0, h1, h2, h3, h4, h5, h6, h7, h8, h9,
-- i0, i1, i2, i3, i4, i5, i6, i7, i8, i9,
-- j0, j1, j2, j3;
-- end if;
until done end repeat;
close c;
end|
call p1()|
[3 Nov 2005 13:58]
Konstantin Osipov
This is a duplicate of Bug#14210 (fixed).
