Bug #34286 Assertion failure in thread 2816 in file .\row\row0sel.c line 3500
Submitted: 4 Feb 2008 15:14 Modified: 13 Aug 2008 15:54
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S1 (Critical)
Version:5.0.46, 5.0.48, 5.0.54, 5.1.22 OS:Windows (windows)
Assigned to: Timothy Smith CPU Architecture:Any
Tags: auto_increment

[4 Feb 2008 15:14] Shane Bester
Description:
when doing binary upgrade from earlier versions of 5.0 to 5.0.54 there are some conditions that cause a repeatable crash when trying to alter/optimize an innodb table:

080204 17:05:53  InnoDB: Started; log sequence number 0 46916
080204 17:05:53 [Note] mysqld-debug: ready for connections.
Version: '5.0.54-enterprise-gpl-debug'  socket: ''  port: 3307  MySQL Enterprise Server - Debug (GPL)
InnoDB: Error: MySQL is trying to perform a consistent read
InnoDB: but the read view is not assigned!
TRANSACTION 0 1281, ACTIVE 0 sec, OS thread id 2816 starting index read, thread declared inside InnoDB 499
mysql tables in use 2, locked 1
1 lock struct(s), heap size 320
MySQL thread id 1, query id 4 127.0.0.1 root setup
optimize table t1

080204 17:05:59InnoDB: Assertion failure in thread 2816 in file .\row\row0sel.c line 3500
InnoDB: Failing assertion: 0
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/refman/5.0/en/forcing-recovery.html
InnoDB: about forcing recovery.
InnoDB: Thread 2588 stopped in file .\os\os0sync.c line 389
InnoDB: Thread 1836 stopped in file .\os\os0sync.c line 269
InnoDB: Thread 1816 stopped in file .\os\os0sync.c line 572

How to repeat:
clean 5.0.24a datadir.
start 5.0.24a.
execute the following sql:

<cut>
set foreign_key_checks=0;
drop table if exists `t1`;
create table `t1`(`a` int not null auto_increment primary key,`b` int not null default '0',key(`b`),
constraint `report_ibfk_1` foreign key(`b`) references `somewhere`(`b`) on update no action
)engine=innodb default charset=utf8;
insert into `t1` set `a`=-1,`b`=1;
</cut>

shutdown 5.0.24a.
clean 5.0.54 datadir
copy ibdata* ib_log* and *.frm to from 5.0.24a to 5.0.54 datadir
start 5.0.54. It should start without a problem.

execute this:  optimize table t1;

crash happens.
[4 Feb 2008 15:29] MySQL Verification Team
simpler testcase!!

drop table if exists `t1`;
create table `t1`(`a` int not null auto_increment primary key)engine=innodb;
insert into `t1` set `a`=-1;

<shutdown mysqld and restart it>

optimize table t1;
[4 Feb 2008 15:49] MySQL Verification Team
the stack trace:
mysqld-debug.exe!row_search_for_mysql
mysqld-debug.exe!ha_innobase::index_read
mysqld-debug.exe!ha_innobase::index_last
mysqld-debug.exe!ha_innobase::innobase_read_and_init_auto_inc
mysqld-debug.exe!ha_innobase::info
mysqld-debug.exe!ha_innobase::update_create_info
mysqld-debug.exe!mysql_alter_table
mysqld-debug.exe!mysql_recreate_table
mysqld-debug.exe!mysql_admin_table
mysqld-debug.exe!mysql_optimize_table
mysqld-debug.exe!mysql_execute_command
mysqld-debug.exe!mysql_parse
mysqld-debug.exe!dispatch_command
mysqld-debug.exe!do_command
mysqld-debug.exe!handle_one_connection
mysqld-debug.exe!pthread_start
mysqld-debug.exe!_callthreadstart
mysqld-debug.exe!_threadstart
[4 Feb 2008 17:34] Heikki Tuuri
Sunny,

please look at this. Looks like the auto-inc init code behaves badly in OPTIMIZE TABLE, if OPTIMIZE TABLE is run as the FIRST statement on the table.

Workaround: before OPTIMIZE, first run some other statement that initializes the auto-inc counter of the table. I think SHOW TABLE STATUS does that.

Regards,

Heikki
[5 Feb 2008 13:50] Heikki Tuuri
mysqld-debug.exe!row_search_for_mysql
mysqld-debug.exe!ha_innobase::index_read
mysqld-debug.exe!ha_innobase::index_last
mysqld-debug.exe!ha_innobase::innobase_read_and_init_auto_inc
mysqld-debug.exe!ha_innobase::info

I guess ::info() does not have the 'prebuilt' of the table handle in the proper state:

row0sel.c in 5.0:
        if (!prebuilt->sql_stat_start) {
                /* No need to set an intention lock or assign a read view */

                if (trx->read_view == NULL
                    && prebuilt->select_lock_type == LOCK_NONE) {

                        fputs(
"InnoDB: Error: MySQL is trying to perform a consistent read\n"
"InnoDB: but the read view is not assigned!\n", stderr);
                        trx_print(stderr, trx, 600);
                        fputc('\n', stderr);
                        ut_a(0);
                }

Suggested fix:

ha_innodb.cc in 5.0:
        /* 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++;
##### FIX:
        /* Since we will perform a MySQL SELECT query to determine the auto-inc value, set prebuilt->sql_stat_start = TRUE so that it is performed like any normal SELECT, regardless of the context we come here. */

        prebuilt->sql_stat_start = TRUE;
##### ONLY AFTER THAT CALL:
        error = index_last(table->record[1]);
[5 Feb 2008 13:55] MySQL Verification Team
The same crash is also caused by running alter table instead of optimize table. I guess the fix should be tested for both those scenarios.
[5 Feb 2008 14:13] Heikki Tuuri
Sunny, please test with ALTER, too.

The fix is supposed to work.

--Heikki
[28 Feb 2008 10:59] Andrii Nikitin
Customer confirmed workaround - ensure that at least one row has positive value in autoinc column. (insert fake value or update existing (disable foreign key may be required)).

But this is not good workaround because if customer deals with multiple DB instances (customers ofour customer have own installations) it is pain to check that every single table has positive values in autoinc field.
[21 May 2008 23:39] Calvin Sun
Pushed into local rep as r2279.
[21 May 2008 23:41] Calvin Sun
According to Sunny - "(the bug/fix) is not relevant to the new AUTOINC code in 5.1+".
[13 Aug 2008 8:29] Bugs System
Pushed into 6.0.7-alpha  (revid:timothy.smith@sun.com-20080731214757-65lrunl1vmnnlues) (version source revid:timothy.smith@sun.com-20080813063149-5o63l7vlvbm6nuhm) (pib:3)
[13 Aug 2008 8:35] Bugs System
Pushed into 5.0.68  (revid:timothy.smith@sun.com-20080731214757-65lrunl1vmnnlues) (version source revid:timothy.smith@sun.com-20080813061111-bt9vnarlk41r3yif) (pib:3)
[13 Aug 2008 8:38] Bugs System
Pushed into 5.1.28  (revid:timothy.smith@sun.com-20080731214757-65lrunl1vmnnlues) (version source revid:timothy.smith@sun.com-20080813061206-ew8rffhoqwi9wm5p) (pib:3)
[13 Aug 2008 15:54] Paul DuBois
Noted in 5.0.68 changelog (only; the 5.1 and 6.0 pushes were null merges):

Using OPTIMIZE TABLE as the first statement on an InnoDB table with
an AUTO_INCREMENT column could cause a server crash.
[28 Aug 2008 20:15] Bugs System
Pushed into 6.0.7-alpha  (revid:cbell@mysql.com-20080822132131-uveo6wiuecy6m2b8) (version source revid:cbell@mysql.com-20080822132131-uveo6wiuecy6m2b8) (pib:3)
[13 Sep 2008 23:56] Bugs System
Pushed into 6.0.7-alpha  (revid:timothy.smith@sun.com-20080731214757-65lrunl1vmnnlues) (version source revid:lars-erik.bjork@sun.com-20080718081554-1cq4mw3bk21fzol6) (pib:3)