MySQL Bugs: #34286: Assertion failure in thread 2816 in file .\row\row0sel.c line 3500

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

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.

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;

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

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

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]);

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.

Sunny, please test with ALTER, too.

The fix is supposed to work.

--Heikki

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.

Pushed into local rep as r2279.

According to Sunny - "(the bug/fix) is not relevant to the new AUTOINC code in 5.1+".

Pushed into 6.0.7-alpha  (revid:timothy.smith@sun.com-20080731214757-65lrunl1vmnnlues) (version source revid:timothy.smith@sun.com-20080813063149-5o63l7vlvbm6nuhm) (pib:3)

Pushed into 5.0.68  (revid:timothy.smith@sun.com-20080731214757-65lrunl1vmnnlues) (version source revid:timothy.smith@sun.com-20080813061111-bt9vnarlk41r3yif) (pib:3)

Pushed into 5.1.28  (revid:timothy.smith@sun.com-20080731214757-65lrunl1vmnnlues) (version source revid:timothy.smith@sun.com-20080813061206-ew8rffhoqwi9wm5p) (pib:3)

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.

Pushed into 6.0.7-alpha  (revid:cbell@mysql.com-20080822132131-uveo6wiuecy6m2b8) (version source revid:cbell@mysql.com-20080822132131-uveo6wiuecy6m2b8) (pib:3)

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)