Bug #20493 on partition tables, select and show command casue server crash

• Submitted: 16 Jun 2006 5:58
• Modified: 18 Jun 2010 12:46
• Reporter: steven zhoucn
• Status: Closed
• Category: MySQL Server: InnoDB storage engine
• Severity: S1 (Critical)
• Version: 5.1.11/5.1BK
• OS: Linux (linux 2.4.20-8)
• Assigned to: Heikki Tuuri
• CPU Architecture: Any

[16 Jun 2006 5:58] steven zhoucn

Description:
I use mysql5.1.11 and mysql5.1.12(from bt), find:
I create table use normal.sql. Then I create two local connection use mysql

In the first connect, I use these sql:
select dataId from normal where account like 'g%' and captureTime between '2006-04-03 00:00:00' and '2006-04-03 01:00:00' and
 hourNum=0 order by account desc limit 10;
SHOW TABLE STATUS LIKE 'normal';

It success!

In the secord connect, I use the same sql, the server crash!

How to repeat:
1: start server
2: In the same computer, use mysql -u root test < normal.sql
4: get the first connect: mysql -u root test, and input 
-------------------------------------------------------------------------------
select dataId from normal where account like 'g%' and captureTime between '2006-04-03 00:00:00' and '2006-04-03 01:00:00' and
 hourNum=0 order by account desc limit 10;
SHOW TABLE STATUS LIKE 'normal';
-------------------------------------------------------------------------------
5: get the second connect: mysql -u root test, and input:
-------------------------------------------------------------------------------
select dataId from normal where account like 'g%' and captureTime between '2006-04-03 00:00:00' and '2006-04-03 01:00:00' and
 hourNum=0 order by account desc limit 10;
SHOW TABLE STATUS LIKE 'normal';
-------------------------------------------------------------------------------

[16 Jun 2006 6:01] steven zhoucn

normal.sql

Attachment: normal.sql (text/plain), 119.41 KiB.

[16 Jun 2006 6:03] steven zhoucn

config file

Attachment: my.cnf (application/octet-stream, text), 4.88 KiB.

[16 Jun 2006 13:01] MySQL Verification Team

GDB Full back trace

Attachment: bt-bug20493.txt (text/plain), 14.67 KiB.

[16 Jun 2006 13:05] MySQL Verification Team

Thank you for the bug report I was able to repeat on Suse 10:

060616  9:53:05 [Note] /home/miguel/dbs/5.1/libexec/mysqld: ready for connections.
Version: '5.1.12-beta-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
[New Thread 1435552688 (LWP 5935)]
060616  9:53:05 [Note] SCHEDULER: Manager thread booting
060616  9:53:05 [Note] SCHEDULER: Loaded 0 events
060616  9:53:05 [Note] SCHEDULER: Suspending operations
[New Thread 1435753392 (LWP 5937)]
[New Thread 1436355504 (LWP 5940)]
060616  9:54:08InnoDB: Assertion failure in thread 1436355504 in file ha_innodb.cc line 6826
InnoDB: Failing assertion: prebuilt->trx == (trx_t*) current_thd->ha_data[innobase_hton.slot]
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 1436355504 (LWP 5940)]
0x08354ee4 in ha_innobase::innobase_read_and_init_auto_inc (this=0x9303170, ret=0x559ce9e8) at ha_innodb.cc:6825
6825            ut_a(prebuilt->trx ==
(gdb) bt full

Please see attached file with GDB back trace. Notice that is
necessary to use the my.cnf provided with the test case for
to get the crash, starting the server with default values not
crashes.

[29 Jul 2006 11:28] Heikki Tuuri

InnoDB initializes prebuilt->trx normally in ::external_lock() with a call of ha_innobase::update_thd().

Hmm... a question is why we do not get the same crash without partitions?

An obvious fix is to call ha_innobase::update_thd() also in the function below.

/***********************************************************************
This function initializes the auto-inc counter if it has not been
initialized yet. This function does not change the value of the auto-inc
counter if it already has been initialized. In parameter ret returns
the value of the auto-inc counter. */

int
ha_innobase::innobase_read_and_init_auto_inc(
/*=========================================*/
                                /* out: 0 or error code: deadlock or lock wait
                                timeout */
        longlong*       ret)    /* out: auto-inc value */
{
        row_prebuilt_t* prebuilt        = (row_prebuilt_t*) innobase_prebuilt;
        longlong        auto_inc;
        ulint           old_select_lock_type;
        ibool           trx_was_not_started     = FALSE;
        int             error;

        ut_a(prebuilt);
        ut_a(prebuilt->trx ==
                (trx_t*) current_thd->ha_data[innobase_hton.slot]);
        ut_a(prebuilt->table);

...

        /* Starting from 5.0.9, we use a consistent read to read the auto-inc
        column maximum value. This eliminates the spurious deadlocks caused
        by the row X-lock that we previously used. Note the following flaw
        in our algorithm: if some other user meanwhile UPDATEs the auto-inc
        column, our consistent read will not return the largest value. We
        accept this flaw, since the deadlocks were a bigger trouble. */

        /* Fetch all the columns in the key */

        prebuilt->hint_need_to_fetch_extra_cols = ROW_RETRIEVE_ALL_COLS;

        old_select_lock_type = prebuilt->select_lock_type;
        prebuilt->select_lock_type = LOCK_NONE;

        /* Eliminate an InnoDB error print that happens when we try to SELECT
        from a table when no table has been locked in ::external_lock(). */
        prebuilt->trx->n_mysql_tables_in_use++;

        error = index_last(table->record[1]);

[29 Jul 2006 11:36] Heikki Tuuri

Another solution is to place update_thd() to the function below.
Maybe best to put it to both places. When using table handles, it is essential that prebuilt is up to date!

/******************************************************************************\
*
This function initializes the auto-inc counter if it has not been
initialized yet. This function does not change the value of the auto-inc
counter if it already has been initialized. Returns the value of the
auto-inc counter in *first_value, and ULONGLONG_MAX in *nb_reserved_values (as
we have a table-level lock). offset, increment, nb_desired_values are ignored.
*first_value is set to -1 if error (deadlock or lock wait timeout)            *\
/

void ha_innobase::get_auto_increment(
/*=================================*/
        ulonglong offset,              /* in */
        ulonglong increment,           /* in */
        ulonglong nb_desired_values,   /* in */
        ulonglong *first_value,        /* out */
        ulonglong *nb_reserved_values) /* out */
{
        longlong        nr;
        int             error;

        error = innobase_read_and_init_auto_inc(&nr);

[22 Aug 2006 10:19] Tatiana Azundris Nuernberg

fixed by inno patchset merged into 5.1.12-beta (5.1-new-maint)

[13 Sep 2006 8:18] Timothy Smith

Es wird nach 5.1.12 gesendet

[14 Sep 2006 11:48] Heikki Tuuri

The fixed bug was precisely: if a partitioned InnoDB table contained an AUTO-INC column, then a SHOW command could cause an assertion failure with > 1 connection.

[14 Sep 2006 13:47] Paul DuBois

Heikki, thanks for the note.

Noted in 5.1.12 changelog.

[5 May 2010 15:23] Bugs System

Pushed into 5.1.47 (revid:[e-mail]-20100505145753-ivlt4hclbrjy8eye) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[6 May 2010 2:15] Paul DuBois

Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug. Re-closing.

[28 May 2010 5:58] Bugs System

Pushed into mysql-next-mr (revid:[e-mail]-20100524190136-egaq7e8zgkwb9aqi) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (pib:16)

[28 May 2010 6:26] Bugs System

Pushed into 6.0.14-alpha (revid:[e-mail]-20100524190941-nuudpx60if25wsvx) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[28 May 2010 6:54] Bugs System

Pushed into 5.5.5-m3 (revid:[e-mail]-20100524185725-c8k5q7v60i5nix3t) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[29 May 2010 2:49] Paul DuBois

Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.

[17 Jun 2010 12:00] Bugs System

Pushed into 5.1.47-ndb-7.0.16 (revid:[e-mail]-20100617114014-bva0dy24yyd67697) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[17 Jun 2010 12:41] Bugs System

Pushed into 5.1.47-ndb-6.2.19 (revid:[e-mail]-20100617115448-idrbic6gbki37h1c) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[17 Jun 2010 13:27] Bugs System

Pushed into 5.1.47-ndb-6.3.35 (revid:[e-mail]-20100617114611-61aqbb52j752y116) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)