Bug #6378 Lost Connection when deleting using left joins with InnoDB tables

Description:
When trying to delete from two or more tables using a left join I receive the error message: "Lost connection to MySQL server during query".  It only seems to occur when there is no matching data in the table being left joined, and when the database type is InnoDB.

Upon searching the bugs database I discovered a similar issue that could not be replicated (http://bugs.mysql.com/bug.php?id=2171).  I have taken the same query but tweaked to include setting the database type which should allow for replication of the issue.  I've searched all over Google and nobody seems to have come up with this scenario.

How to repeat:
Below are two sets of commands that work fine, and the third that fails.

-- Works fine
CREATE TABLE a (pk INT) ENGINE = MyISAM;
CREATE TABLE b (pk INT, apk INT) ENGINE = MyISAM;
CREATE INDEX ia ON a ( pk );
CREATE INDEX iba ON b ( apk );
INSERT a VALUES(1);
DELETE TableA, TableB FROM a As TableA LEFT JOIN b As TableB ON TableB.apk = TableA.pk;
DROP TABLE a;
DROP TABLE b;

-- Also works fine
CREATE TABLE a (pk INT) ENGINE = InnoDB;
CREATE TABLE b (pk INT, apk INT) ENGINE = MyISAM;;
CREATE INDEX ia ON a ( pk );
CREATE INDEX iba ON b ( apk );
INSERT a VALUES(1);
DELETE TableA, TableB FROM a As TableA LEFT JOIN b As TableB ON TableB.apk = TableA.pk;
DROP TABLE a;
DROP TABLE b;

-- Produces an error
CREATE TABLE a (pk INT) ENGINE = InnoDB;
CREATE TABLE b (pk INT, apk INT) ENGINE = InnoDB;
CREATE INDEX ia ON a ( pk );
CREATE INDEX iba ON b ( apk );
INSERT a VALUES(1);
DELETE TableA, TableB FROM a As TableA LEFT JOIN b As TableB ON TableB.apk = TableA.pk;
DROP TABLE a;
DROP TABLE b;

Here is the output from the first of the two successful queries:

mysql> CREATE TABLE a (pk INT) ENGINE = MyISAM;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE b (pk INT, apk INT) ENGINE = MyISAM;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE INDEX ia ON a ( pk );
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> CREATE INDEX iba ON b ( apk );
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> INSERT a VALUES(1);
Query OK, 1 row affected (0.00 sec)

mysql> DELETE TableA, TableB FROM a As TableA LEFT JOIN b As TableB ON TableB.apk = TableA.pk;
Query OK, 1 row affected (0.00 sec)

mysql> DROP TABLE a;
Query OK, 0 rows affected (0.00 sec)

mysql> DROP TABLE b;
Query OK, 0 rows affected (0.00 sec)

... And here is the output from the third query that throws the "lost connection" error:

mysql> CREATE TABLE a (pk INT) ENGINE = InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE b (pk INT, apk INT) ENGINE = InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE INDEX ia ON a ( pk );
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> CREATE INDEX iba ON b ( apk );
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> INSERT a VALUES(1);
Query OK, 1 row affected (0.01 sec)

mysql> DELETE TableA, TableB FROM a As TableA LEFT JOIN b As TableB ON TableB.apk = TableA.pk;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> DROP TABLE a;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...

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)

An interim note.

A bug exists in 4.0 as well.

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

Verifed that fix for BUG#5837 fixes this bug too, marking as duplicate of BUG#5837.