| Bug #6378 | Lost Connection when deleting using left joins with InnoDB tables | ||
|---|---|---|---|
| Submitted: | 2 Nov 2004 4:27 | Modified: | 21 Nov 2004 21:21 |
| Reporter: | Brian Hibma | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S2 (Serious) |
| Version: | 4.0.22 & 4.1.7 | OS: | Linux (Linux Mandrake 9.1) |
| Assigned to: | Assigned Account | CPU Architecture: | Any |
[2 Nov 2004 4:27]
Brian Hibma
[2 Nov 2004 7:39]
Heikki Tuuri
Hi! Thank you for the bug report. I am able to repeat this. The reason for the bug is that MySQL is trying to delete a row from table b though b contains no row at all! I guess the multi-table delete code is confused by the left join. gdb reveals that first MySQL asks for a row in table b. InnoDB correctly returns that there is no row. Then MySQL asks for a row in table a. I have assigned this bug to the author of the multi-table delete. Regards, Heikki 041102 9:01:01InnoDB: Assertion failure in thread 147466 in file row0mysql.c li ne 1122 InnoDB: Failing assertion: node->pcur->rel_pos == BTR_PCUR_ON InnoDB: We intentionally generate a memory trap. InnoDB: Submit a detailed bug report to http://bugs.mysql.com. InnoDB: If you get repeated assertion failures or crashes, even InnoDB: immediately after the mysqld startup, there may be InnoDB: corruption in the InnoDB tablespace. Please refer to InnoDB: http://dev.mysql.com/doc/mysql/en/Forcing_recovery.html InnoDB: about forcing recovery. Program received signal SIGSEGV, Segmentation fault. [Switching to Thread 147466 (LWP 1124)] 0x08270551 in row_update_for_mysql (mysql_rec=0x8b3f888 "ÿ", prebuilt=0x402cf068) at row0mysql.c:1122 1122 ut_a(node->pcur->rel_pos == BTR_PCUR_ON); Current language: auto; currently c (gdb) bt #0 0x08270551 in row_update_for_mysql (mysql_rec=0x8b3f888 "ÿ", prebuilt=0x402cf068) at row0mysql.c:1122 #1 0x082009cd in ha_innobase::delete_row(char const*) (this=0x8b3f780, record=0x0) at ha_innodb.cc:2767 #2 0x081c855d in multi_delete::send_data(List<Item>&) (this=0x8b448f8, values=@0x8b46734) at sql_delete.cc:408 #3 0x081b1e3f in end_send (join=0x8b44930, join_tab=0x8b45928, end_of_records=false) at sql_select.cc:6357 #4 0x081b0d37 in sub_select (join=0x8b44930, join_tab=0x8b457e8, end_of_records=false) at sql_select.cc:5781 #5 0x081b0942 in do_select (join=0x8b44930, fields=0x8b457e8, table=0x0, procedure=0x0) at sql_select.cc:5665 #6 0x081a61a7 in JOIN::exec() (this=0x8b44930) at sql_select.cc:1463 #7 0x081a6614 in mysql_select(THD*, Item***, st_table_list*, unsigned, List<Ite m>&, Item*, unsigned, st_order*, st_order*, Item*, st_order*, unsigned long, sel ect_result*, st_select_lex_unit*, st_select_lex*) (thd=0x8b465a8, rref_pointer_array=0x8b467b4, tables=0x8b44610, wild_num=0, fields=@0x0, conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=277105280, result=0x8b448f8, unit=0x8b465ec, select_lex=0x8b466cc) at sql_select.cc:1584 #8 0x0817be12 in mysql_execute_command(THD*) (thd=0x8b465a8) at sql_parse.cc:2822 #9 0x0817edfd in mysql_parse(THD*, char*, unsigned) (thd=0x8b465a8, inBuf=0x8b44450 "DELETE TableA, TableB FROM a As TableA LEFT JOIN b As Table B ON TableB.apk =\nTableA.pk", length=146040288) at sql_parse.cc:4051 #10 0x08178343 in dispatch_command(enum_server_command, THD*, char*, unsigned) (command=COM_QUERY, thd=0x8b465a8, packet=0x8b40419 "DELETE TableA, TableB FROM a As TableA LEFT JOIN b As Tabl eB ON TableB.apk =\nTableA.pk", packet_length=87) at sql_parse.cc:1463 #11 0x08177c57 in do_command(THD*) (thd=0x8b465a8) at sql_parse.cc:1278 #12 0x0817713f in handle_one_connection (arg=0x0) at sql_parse.cc:1022 #13 0x40062f60 in pthread_start_thread () from /lib/i686/libpthread.so.0 #14 0x400630fe in pthread_start_thread_event () from /lib/i686/libpthread.so.0 #15 0x401f5327 in clone () from /lib/i686/libc.so.6 (gdb) print *prebuilt $1 = {magic_n = 78540783, table = 0x402cca68, trx = 0x402cac68, sql_stat_start = 0, mysql_has_locked = 1, clust_index_was_generated = 1, index = 0x402cd468, read_just_key = 0, used_in_HANDLER = 0, template_type = 0, n_template = 2, null_bitmap_len = 1, need_to_access_clustered = 1, templ_contains_blob = 0, mysql_template = 0x402cf868, heap = 0x402cff28, ins_node = 0x0, ins_upd_rec_buff = 0x0, hint_need_to_fetch_extra_cols = 0, upd_node = 0x402cbe10, ins_graph = 0x0, upd_graph = 0x402cc068, pcur = 0x402d0268, clust_pcur = 0x402d0368, sel_graph = 0x402cbd28, search_tuple = 0x402cf128, row_id = "\000\000\000\000\000", clust_ref = 0x402cbc68, select_lock_type = 5, stored_select_lock_type = 5, mysql_row_len = 9, n_rows_fetched = 0, fetch_direction = 4, fetch_cache = { 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0}, fetch_cache_first = 0, n_fetch_cached = 0, blob_heap = 0x0, old_vers_heap = 0x0, magic_n2 = 78540783} (gdb) print *table $2 = {id = {high = 0, low = 134322}, type = 1, heap = 0x402cdd28, name = 0x402cb9f8 "test/b", dir_path_of_temp_table = 0x0, space = 0, ibd_file_missing = 0, tablespace_discarded = 0, name_hash = 0x0, id_hash = 0x0, n_def = 6, n_cols = 6, cols = 0x402cb868, indexes = { count = 2, start = 0x402cd468, end = 0x402cd568}, foreign_list = { count = 0, start = 0x0, end = 0x0}, referenced_list = {count = 0, start = 0x0, end = 0x0}, table_LRU = {prev = 0x0, next = 0x402cd668}, mem_fix = 0, n_mysql_handles_opened = 1, n_foreign_key_checks_running = 0, cached = 1, auto_inc_lock = 0x402cb970, query_cache_inv_trx_id = {high = 0, low = 0}, locks = {count = 1, start = 0x402ca668, end = 0x402ca668}, mix_id = {high = 0, low = 0}, mix_len = 0, mix_id_len = 4, mix_id_buf = "\006\000\000\000\000\000\000\000\004\000\000", cluster_name = 0x0, does_not_fit_in_memory = 0, stat_n_rows = 0, stat_clustered_index_size = 1, stat_sum_of_other_index_sizes = 1, stat_initialized = 1, stat_modified_counter = 0, autoinc_mutex = { lock_word = 0, os_fast_mutex = {global = {__m_reserved = 0, __m_count = 0, __m_owner = 0x0, __m_kind = 0, __m_lock = {__status = 0, __spinlock = 0}}, mutex = {__m_reserved = 0, __m_count = 0, __m_owner = 0x0, __m_kind = 3, __m_lock = {__status = 0, __spinlock = 0}}, file = 0x84046b2 "os0sync.c", line = 574, count = 0, thread = 0}, waiters = 0, list = {prev = 0x402ceacc, next = 0x402cfccc}, level = 999, cfile_name = 0x83e629d "dict0mem.c", cline = 87, magic_n = 979585}, autoinc_inited = 0, autoinc = 555127459240, magic_n = 76333786} (gdb) ---------------------------------- Breakpoint 1, row_search_for_mysql (buf=0x8b55a30 "ÿ", mode=1, prebuilt=0x402c4668, match_mode=0, direction=0) at row0sel.c:2787 2787 dict_index_t* index = prebuilt->index; (gdb) next 2788 dtuple_t* search_tuple = prebuilt->search_tuple; (gdb) 2789 btr_pcur_t* pcur = prebuilt->pcur; (gdb) 2790 trx_t* trx = prebuilt->trx; (gdb) print *index $6 = {id = {high = 0, low = 584383}, heap = 0x402cd428, type = 1, name = 0x402cd668 "GEN_CLUST_INDEX", table_name = 0x402c45a0 "test/b", table = 0x402c4468, space = 0, page_no = 899, trx_id_offset = 6, n_user_defined_cols = 0, n_uniq = 1, n_def = 5, n_fields = 5, fields = 0x402cd678, indexes = {prev = 0x0, next = 0x402cd568}, tree = 0x402cd868, tree_indexes = {prev = 0x0, next = 0x0}, cached = 1, search_info = 0x402cd6e0, stat_n_diff_key_vals = 0x402cd728, stat_index_size = 1, stat_n_leaf_pages = 1, magic_n = 76789786} (gdb) next 2797 ulint err = DB_SUCCESS; (gdb) 2803 ibool unique_search = FALSE; (gdb) 2804 ibool unique_search_from_clust_index = FALSE; (gdb) 2805 ibool mtr_has_extra_clust_latch = FALSE; (gdb) 2806 ibool moves_up = FALSE; (gdb) 2807 ibool set_also_gap_locks = TRUE; (gdb) 2820 if (prebuilt->magic_n != ROW_PREBUILT_ALLOCATED) { (gdb) 2833 if (trx->n_mysql_tables_in_use == 0) { (gdb) 2852 if (trx->has_search_latch (gdb) 2869 if (direction == 0) { (gdb) 2870 trx->op_info = "starting index read"; (gdb) 2872 prebuilt->n_rows_fetched = 0; (gdb) 2873 prebuilt->n_fetch_cached = 0; (gdb) 2874 prebuilt->fetch_cache_first = 0; (gdb) 2876 if (prebuilt->sel_graph == NULL) { (gdb) 2941 if (match_mode == ROW_SEL_EXACT (gdb) 2968 mtr_start(&mtr); (gdb) 2979 if (unique_search (gdb) 3083 if (trx->has_search_latch) { (gdb) 3088 trx_start_if_not_started(trx); (gdb) 3090 if (trx->isolation_level <= TRX_ISO_READ_COMMITTED (gdb) 3111 if (direction == 0) { (gdb) 3112 if (mode == PAGE_CUR_GE || mode == PAGE_CUR_G) { (gdb) 3113 moves_up = TRUE; (gdb) 3116 moves_up = TRUE; (gdb) 3119 thr = que_fork_get_first_thr(prebuilt->sel_graph); (gdb) 3121 que_thr_move_to_run_state_for_mysql(thr, trx); (gdb) 3123 clust_index = dict_table_get_first_index(index->table); (gdb) 3125 if (direction != 0) { (gdb) 3132 } else if (dtuple_get_n_fields(search_tuple) > 0) { (gdb) 3138 if (mode == PAGE_CUR_G) { (gdb) 3139 btr_pcur_open_at_index_side(TRUE, index, (gdb) 3147 if (!prebuilt->sql_stat_start) { (gdb) 3160 } else if (prebuilt->select_lock_type == LOCK_NONE) { (gdb) 3167 if (prebuilt->select_lock_type == LOCK_S) { (gdb) 3170 err = lock_table(0, index->table, LOCK_IX, thr); (gdb) 3173 if (err != DB_SUCCESS) { (gdb) 3177 prebuilt->sql_stat_start = FALSE; (gdb) 3184 rec = btr_pcur_get_rec(pcur); (gdb) 3192 if (rec == page_get_infimum_rec(buf_frame_align(rec))) { (gdb) 3570 if (mtr_has_extra_clust_latch) { (gdb) 3591 if (moves_up) { (gdb) 3592 moved = btr_pcur_move_to_next(pcur, &mtr); (gdb) 3597 if (!moved) { (gdb) 3184 rec = btr_pcur_get_rec(pcur); (gdb) 3192 if (rec == page_get_infimum_rec(buf_frame_align(rec))) { (gdb) 3201 if (rec == page_get_supremum_rec(buf_frame_align(rec))) { (gdb) 3203 if (prebuilt->select_lock_type != LOCK_NONE (gdb) 3212 if ( srv_locks_unsafe_for_binlog == FALSE ) (gdb) 3214 err = sel_set_rec_lock(rec, index, (gdb) 3219 if (err != DB_SUCCESS) { (gdb) 3227 goto next_rec; (gdb) 3570 if (mtr_has_extra_clust_latch) { (gdb) 3591 if (moves_up) { (gdb) 3592 moved = btr_pcur_move_to_next(pcur, &mtr); (gdb) 3597 if (!moved) { (gdb) 3598 btr_pcur_store_position(pcur, &mtr); (gdb) 3600 if (match_mode != 0) { (gdb) 3606 goto normal_return; (gdb) 3648 que_thr_stop_for_mysql_no_error(thr, trx); (gdb) 3650 mtr_commit(&mtr); (gdb) 3652 if (prebuilt->n_fetch_cached > 0) { (gdb) 3661 if (ret == DB_SUCCESS) { (gdb) 3665 trx->op_info = ""; (gdb) 3667 return(ret); (gdb) 3668 } (gdb) ha_innobase::index_read(char*, char const*, unsigned, ha_rkey_function) ( this=0x8b55928, buf=0x5dd <Address 0x5dd out of bounds>, key_ptr=0x0, key_len=1501, find_flag=HA_READ_AFTER_KEY) at ha_innodb.cc:179 179 { Current language: auto; currently c++ (gdb) 180 if (srv_thread_concurrency >= 500) { (gdb) 185 srv_conc_exit_innodb(trx); (gdb) 2990 if (ret == DB_SUCCESS) { (gdb) 2994 } else if (ret == DB_RECORD_NOT_FOUND) { (gdb) 2998 } else if (ret == DB_END_OF_INDEX) { (gdb) 2999 error = HA_ERR_KEY_NOT_FOUND; (gdb) 3000 table->status = STATUS_NOT_FOUND; (gdb) 3003 table->status = STATUS_NOT_FOUND; (gdb) 3006 DBUG_RETURN(error); (gdb) 3007 } (gdb) c Continuing. Breakpoint 1, row_search_for_mysql (buf=0x8b47ac0 "ý\001", mode=1, prebuilt=0x402c3068, match_mode=0, direction=0) at row0sel.c:2787 2787 dict_index_t* index = prebuilt->index; Current language: auto; currently c (gdb) next 2788 dtuple_t* search_tuple = prebuilt->search_tuple; (gdb) 2789 btr_pcur_t* pcur = prebuilt->pcur; (gdb) 2790 trx_t* trx = prebuilt->trx; (gdb) 2797 ulint err = DB_SUCCESS; (gdb) 2803 ibool unique_search = FALSE; (gdb) 2804 ibool unique_search_from_clust_index = FALSE; (gdb) print *index $7 = {id = {high = 0, low = 584381}, heap = 0x402c3228, type = 1, name = 0x402c3468 "GEN_CLUST_INDEX", table_name = 0x402cc5a0 "test/a", table = 0x402cc468, space = 0, page_no = 918, trx_id_offset = 6, n_user_defined_cols = 0, n_uniq = 1, n_def = 4, n_fields = 4, fields = 0x402c3478, indexes = {prev = 0x0, next = 0x402c3368}, tree = 0x402c3668, tree_indexes = {prev = 0x0, next = 0x0}, cached = 1, search_info = 0x402c34d0, stat_n_diff_key_vals = 0x402c3518, stat_index_size = 1, stat_n_leaf_pages = 1, magic_n = 76789786} (gdb) next 2805 ibool mtr_has_extra_clust_latch = FALSE; (gdb) 2806 ibool moves_up = FALSE; (gdb) 2807 ibool set_also_gap_locks = TRUE; (gdb) 2820 if (prebuilt->magic_n != ROW_PREBUILT_ALLOCATED) { (gdb) 2833 if (trx->n_mysql_tables_in_use == 0) { (gdb) 2852 if (trx->has_search_latch (gdb) 2869 if (direction == 0) { (gdb) c Continuing. 041102 9:10:40InnoDB: Assertion failure in thread 147466 in file row0mysql.c li ne 1122 InnoDB: Failing assertion: node->pcur->rel_pos == BTR_PCUR_ON InnoDB: We intentionally generate a memory trap. InnoDB: Submit a detailed bug report to http://bugs.mysql.com. InnoDB: If you get repeated assertion failures or crashes, even InnoDB: immediately after the mysqld startup, there may be InnoDB: corruption in the InnoDB tablespace. Please refer to InnoDB: http://dev.mysql.com/doc/mysql/en/Forcing_recovery.html InnoDB: about forcing recovery. Program received signal SIGSEGV, Segmentation fault. 0x08270551 in row_update_for_mysql (mysql_rec=0x8b55a30 "ÿ", prebuilt=0x402c4668) at row0mysql.c:1122 1122 ut_a(node->pcur->rel_pos == BTR_PCUR_ON); (gdb)
[4 Nov 2004 17:14]
MySQL Verification Team
An interim note. A bug exists in 4.0 as well.
[4 Nov 2004 17:30]
Heikki Tuuri
Hi! This may be a duplicate of bug #5837 for which Sergey Petrunia has a patch. I am reassigning this bug report to Sergey. Regards, Heikki
