MySQL Bugs: #55832: selects crash too easily when innodb_force

Bug #55832	selects crash too easily when innodb_force_recovery>3
Submitted:	9 Aug 2010 6:32	Modified:	1 Dec 2010 0:36
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.1.49, 5.5.5	OS:	Any
Assigned to:	Marko Mäkelä	CPU Architecture:	Any
Tags:	innodb_force_recovery

Description:
selects easily crash innodb when innodb_force_recovery=6

How to repeat:
#see attached file for full session log.
#start server with clean tablespace and --innodb-force-recovery=0

set global innodb_fast_shutdown=0;  
drop table if exists t1;            
create table t1(a int)engine=innodb;
insert into t1 values (2),(1),(4);  

#restart server with innodb_force_recovery=6

select 1 from `t1` order by `a`; 

Suggested fix:
don't crash, or document the limitations. many folks are wanting to select and order data from corrupted innodb tables.  so, we should assume selecting from a non-corrupted innodb table should work...

output of logs

Attachment: bug55832_full_session.txt (text/plain), 4.87 KiB.

There are some checks in InnoDB that attempt to prevent writes when innodb_force_recovery (srv_force_recovery) is nonzero. As far as I understand, the purpose of innodb_force_recovery is to allow the data to be dumped from the clustered index, e.g., by SELECT * FROM corrupted_table or by mysqldump. In this case, the table only contains a clustered index.

Generally, I believe that we should review all innodb_force_recovery checks in InnoDB and make sure that all writes really are blocked. It would be nice if the offending operations were blocked already on the MySQL (or handler) layer with a reasonable error message. For example, if insert buffer merges are prevented, all access to secondary indexes should be blocked. Likewise, all DML and DDL should be prevented on all InnoDB tables if innodb_force_recovery is set.

In this particular case, we have to find out if the bug is in MySQL or InnoDB. I would point the finger on MySQL.

some other crashes

Attachment: bug55832_some_other_crashes.txt (text/plain), 17.43 KiB.

Thank you for the bug report.

100809  8:58:34 [Note] C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqld: ready for connections.
Version: '5.1.49-community'  socket: ''  port: 3307  MySQL Community Server (GPL)
100809  8:59:16 - mysqld got exception 0xc0000005 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

key_buffer_size=54525952
read_buffer_size=65536
max_used_connections=1
max_threads=100
threads_connected=1
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 86152 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd: 0x6318c00
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
00000001401C0ADF    mysqld.exe!make_sortkey()[filesort.cc:766]
00000001401C164C    mysqld.exe!find_all_keys()[filesort.cc:618]
00000001401C1B44    mysqld.exe!filesort()[filesort.cc:247]
0000000140170D94    mysqld.exe!create_sort_index()[sql_select.cc:13851]
000000014017530D    mysqld.exe!JOIN::exec()[sql_select.cc:2271]
000000014017576A    mysqld.exe!mysql_select()[sql_select.cc:2519]
0000000140175B66    mysqld.exe!handle_select()[sql_select.cc:269]
0000000140069AA0    mysqld.exe!execute_sqlcom_select()[sql_parse.cc:5094]
000000014006CA6C    mysqld.exe!mysql_execute_command()[sql_parse.cc:2288]
0000000140071410    mysqld.exe!mysql_parse()[sql_parse.cc:6021]
00000001400720C8    mysqld.exe!dispatch_command()[sql_parse.cc:1256]
0000000140072B57    mysqld.exe!do_command()[sql_parse.cc:882]
0000000140099AD7    mysqld.exe!handle_one_connection()[sql_connect.cc:1136]
000000014031D845    mysqld.exe!pthread_start()[my_winthread.c:85]
00000001402E78F7    mysqld.exe!_callthreadstart()[thread.c:295]
00000001402E79C5    mysqld.exe!_threadstart()[thread.c:275]
000000007792BE3D    kernel32.dll!BaseThreadInitThunk()
0000000077A66A51    ntdll.dll!RtlUserThreadStart()
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 000000000636BA10=select 1 from `t1` order by `a`
thd->thread_id=1
thd->killed=NOT_KILLED

Significance of this is that using order by pk desc is one way to try to get a mysqldump of data after damage is found that causes a deliberate crash when a damaged page is found in the asc direction. For harder cases, where clauses are also interesting.

I can repeat the simple test case on MySQL 5.1 with the built-in InnoDB, but not the InnoDB Plugin.

It looks like MySQL 5.1 could get confused because InnoDB skips the calculation of index cardinality statistics and the initialization of the auto-increment counter when innodb_force_recovery >= 4 (SRV_FORCE_NO_IBUF_MERGE).

We will have to find out what is causing the ORDER BY failure. The crash occurs in MySQL code, but InnoDB must be the culprit, delivering bad data. This simple test case does not even use a secondary index. One thing that InnoDB should do is to declare all secondary indexes disabled when innodb_force_recovery >= 4.

3 files. 1) valgrind output, 2) schema, 3) queries, which must be executed via "source ...." in command line client.

Attachment: bug55832.zip (application/octet-stream, text), 12.39 KiB.

Initial patch (simple case works with innodb_force_recovery=4 or 5)

Attachment: bug55832-1.patch (text/x-diff), 3.10 KiB.

I did not get the more complex cases to crash on my system. The simple crash is due to missing index cardinality statistics for the clustered index. The attached patch (against the built-in InnoDB in MySQL 5.1) will compute the statistics for the clustered index even if innodb_force_recovery=4 or 5. On level 6 (SRV_FORCE_NO_LOG_REDO), any access to data files is potentially crash-prone. Thus, with the patch you can still crash it with innodb_force_recovery=6.

Can you try the patch? How can InnoDB declare all secondary indexes as disabled in MySQL (needed when innodb_force_recovery>=4)?

Marko, is supplying bogus cardinality for secondary indexes practical? Say 1 cardinality?

It's nice not to completely lose the secondary indexes, again because it's of use to try different access patterns to get to data. Much less common than PK desc, but still tied occasionally. If there's a simple hack that works, that's of some value and we can document the limitation that it may be necessary to use force index to get the index used.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/116489

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/116490

Marko, thanks.

i confirm the above patch prevents crashing in a taken sample of my tests.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/116579

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/116581

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/116584

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/116585

Pushed to mysql-5.1-innodb (both built-in and plugin) and mysql-5.5-innodb.

Pushed into mysql-5.1 5.1.52 (revid:sunanda.menon@sun.com-20100928083322-wangbv97uobu7g66) (version source revid:sunanda.menon@sun.com-20100928083322-wangbv97uobu7g66) (merge vers: 5.1.52) (pib:21)

Pushed into mysql-trunk 5.6.1-m4 (revid:alik@sun.com-20100928153607-tdsxkdm5cmuym5sq) (version source revid:alik@sun.com-20100928153508-0saa6v93dinqx1u7) (merge vers: 5.6.1-m4) (pib:21)

Pushed into mysql-next-mr (revid:alik@sun.com-20100928153646-pqp8o1a92mxtuj3h) (version source revid:alik@sun.com-20100928153532-lr3gtvnyp2en4y75) (pib:21)

Pushed into mysql-5.5 5.5.7-rc (revid:alik@sun.com-20100928153459-4nudf4zgzlou4s7q) (version source revid:alik@sun.com-20100928153459-4nudf4zgzlou4s7q) (merge vers: 5.5.7-rc) (pib:21)

Pushed into mysql-5.1-telco-7.0 5.1.51-ndb-7.0.20 (revid:martin.skold@mysql.com-20101014082627-jrmy9xbfbtrebw3c) (version source revid:martin.skold@mysql.com-20101014082627-jrmy9xbfbtrebw3c) (merge vers: 5.1.51-ndb-7.0.20) (pib:21)

Pushed into mysql-5.1-telco-6.3 5.1.51-ndb-6.3.39 (revid:martin.skold@mysql.com-20101014083757-5qo48b86d69zjvzj) (version source revid:martin.skold@mysql.com-20101014083757-5qo48b86d69zjvzj) (merge vers: 5.1.51-ndb-6.3.39) (pib:21)

Pushed into mysql-5.1-telco-6.2 5.1.51-ndb-6.2.19 (revid:martin.skold@mysql.com-20101014084420-y54ecj85j5we27oa) (version source revid:martin.skold@mysql.com-20101014084420-y54ecj85j5we27oa) (merge vers: 5.1.51-ndb-6.2.19) (pib:21)

Adding to changelog:

When MySQL was restarted after a crash with the option
innodb_force_recovery=6, certain queries against InnoDB tables could
fail, depending on WHERE or ORDER BY clauses.

Usually in such a
disaster recovery situation, you dump the entire table using a query
without these clauses. During advanced troubleshooting, you might use
queries with these clauses to diagnose the position of the corrupted
data, or to recover data following the corrupted part.

Pushed into mysql-5.5 5.5.7-rc (revid:sunanda.menon@sun.com-20101109182959-otkxq8vo2dcd13la) (version source revid:vasil.dimov@oracle.com-20100928081234-22qbm6cwht521484) (merge vers: 5.1.51) (pib:21)

Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:vasil.dimov@oracle.com-20100928081234-22qbm6cwht521484) (merge vers: 5.1.51) (pib:21)

Pushed into mysql-next-mr (revid:alexander.nozdrin@oracle.com-20101113160336-atmtmfb3mzm4pz4i) (version source revid:vasil.dimov@oracle.com-20100928081234-22qbm6cwht521484) (pib:21)