MySQL Bugs: #69892: innodb stats interferes with innodb force recovery and drop/create tables

Bug #69892	innodb stats interferes with innodb force recovery and drop/create tables
Submitted:	1 Aug 2013 8:23	Modified:	7 Oct 2013 13:25
Reporter:	Shane Bester (Platinum Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	5.6.13	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Read the last paragraph of:
http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html

Attempting to drop/create a table under forced recovery mode doesn't work as advertised.

The DROP TABLE fails with:
InnoDB: unknown error code 57 while dropping table:`test`.`t1`.

The CREATE TABLE crashes like:
InnoDB: Assertion failure in thread 8604 in file fsp0fsp.cc line 1882
InnoDB: Failing assertion: inode

mysqld.exe!my_sigabrt_handler()[my_thr_init.c:499]
mysqld.exe!raise()[winsig.c:593]
mysqld.exe!abort()[abort.c:81]
mysqld.exe!fseg_inode_get()[fsp0fsp.cc:1883]
mysqld.exe!fseg_n_reserved_pages()[fsp0fsp.cc:2213]
mysqld.exe!btr_get_size()[btr0btr.cc:1221]
mysqld.exe!dict_stats_update_transient_for_index()[dict0stats.cc:792]
mysqld.exe!dict_stats_update_transient()[dict0stats.cc:878]
mysqld.exe!dict_stats_update()[dict0stats.cc:3112]
mysqld.exe!ha_innobase::open()[ha_innodb.cc:4765]
mysqld.exe!handler::ha_open()[handler.cc:2451]
mysqld.exe!open_table_from_share()[table.cc:2341]
mysqld.exe!open_table()[sql_base.cc:3072]
mysqld.exe!open_and_process_table()[sql_base.cc:4541]
mysqld.exe!open_tables()[sql_base.cc:4981]
mysqld.exe!mysql_create_table()[sql_table.cc:4956]
mysqld.exe!mysql_execute_command()[sql_parse.cc:3031]
mysqld.exe!mysql_parse()[sql_parse.cc:6209]
mysqld.exe!dispatch_command()[sql_parse.cc:1335]
mysqld.exe!do_command()[sql_parse.cc:1042]
mysqld.exe!do_handle_one_connection()[sql_connect.cc:977]
mysqld.exe!handle_one_connection()[sql_connect.cc:895]
mysqld.exe!pfs_spawn_thread()[pfs.cc:1861]
mysqld.exe!pthread_start()[my_winthread.c:63]
mysqld.exe!_callthreadstartex()[threadex.c:314]
mysqld.exe!_threadstartex()[threadex.c:292]

How to repeat:
# start server with --innodb-force-recovery=0 --innodb_stats_persistent=0 --innodb-stats-auto-recalc=0 --innodb-stats-on-metadata=0

drop table if exists t1;
create table t1(a int)engine=innodb;
start transaction;
insert t1 values (1),(1),(1),(1),(1),(1),(1);

# shutdown server
# restart server with --innodb-force-recovery=3 --innodb_stats_persistent=0 --innodb-stats-auto-recalc=0 --innodb-stats-on-metadata=0

drop table t1; #error
create table t1(a int)engine=innodb; #crash

Suggested fix:
Some thoughts:

Do not read or update innodb statistics (transient or persistent) when innodb_force_recovery is enabled.

Add mtr testcases for the drop/create of tables during force recovery mode.

Update the manual to reflect the actual situation, in case it is wrong now.

Noted in 5.6.15, 5.7.3 changelogs:

 Due to a regression in MySQL 5.6, creating or dropping tables with
"innodb_force_recovery" set to "3" ("SRV_FORCE_NO_TRX_UNDO") would fail.
Additionally, this bug fix includes a code modification that sets "InnoDB"
to read-only when "innodb_force_recovery" is set to a value greater than
"3" ("SRV_FORCE_NO_TRX_UNDO"). 

Also updated innodb_force_recovery option information to mention that InnoDB is placed in read-only state for "innodb_force_recovery" options 4,5, and 6. The change should appear soon, with the next published documentation build.

http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html
http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html

5.6$ bzr log -r 5486
------------------------------------------------------------
revno: 5486
committer: Annamalai Gurusami <annamalai.gurusami@oracle.com>
branch nick: mysql-5.6
timestamp: Thu 2013-10-03 15:17:17 +0530
message:
  Bug #17253499 INNODB STATS INTERFERES WITH INNODB FORCE RECOVERY
  AND DROP/CREATE TABLES
  
  Problem:
  
  During force recovery (SRV_FORCE_NO_TRX_UNDO), create and drop table is
  not working. 
  
  Solution:
  
  The semantics of the force recovery level SRV_FORCE_NO_TRX_UNDO has been
  misinterpreted.  The force recovery level SRV_FORCE_NO_TRX_UNDO means that
  rollback of recovered transactions must not be done.  This should not prevent
  other transactions being assigned a rollback segment.  So while assigning
  rollback segments, do not check the level of innodb_force_recovery.
  
  Also, when the innodb_force_recovery is greater than SRV_FORCE_NO_TRX_UNDO
  we now make InnoDB to become read only. 
  
  rb#3473 approved by Marko