Description:
Manual (https://dev.mysql.com/doc/refman/5.6/en/check-table.html) has the following paragraphs describing how it works for InnoDB tables:
" The following notes apply to InnoDB tables:
If CHECK TABLE finds a problem for an InnoDB table, the server may shut down to prevent error propagation. Details of the error will be written to the error log.
If CHECK TABLE encounters corruptions or errors in InnoDB tables or indexes, it reports an error. It does not shut down the server. Starting with MySQL 5.5, CHECK TABLE usually marks the index and sometimes marks the table as corrupted, preventing further use of the index or table."
As "problem" and "corruptions" are NOT specifically defined, one can even assume that second paragraph contradicts the first one. To be precise: it seems there are some problems identified by CHECK TABLE when server may be shut down, but we do not know what are these problems. On the other hand, it seems there are "corruptions" that leads just to marking some index as corrupted, but there is no shut down.
Real question is: is it safe to run CHECK TABLE for InnoDB table (that we suspect as corrupted for some reason)? Manual says nothing about it.
Moreover, it's not correct to speak about "shut down of the server", as attempts to use CHECK TABLE with a single byte changed on the secondary index page lead to assertion failure and crash 9see details below), not clean shut down.
How to repeat:
Create the table as follows:
create table tt(id varchar(10) primary key, val varchar(10), key(val)) engine=InnoDB;
insert into tt values('aaaa', 'bbbb'), ('cccc', 'dddd');
alter table tt add column val2 varchar(10);
update tt set val2='zzzz';
mysql> select * from tt;
+------+------+------+
| id | val | val2 |
+------+------+------+
| aaaa | bbbb | zzzz |
| cccc | dddd | zzzz |
+------+------+------+
2 rows in set (0.01 sec)
Then shut server down, find the tt.ibd file and change one byte at the secondary index page (change 'aaaa' to 'aXaaa', for example). This is how it looks like in bvi:
00010060 02 00 1D 69 6E 66 69 6D 75 6D 00 03 00 0B 00 00 ...infimum......
00010070 73 75 70 72 65 6D 75 6D 04 04 00 00 00 10 00 10 supremum........
00010080 62 62 62 62 61 58 61 61 04 04 00 00 00 18 FF E0 bbbbaXaa........
00010090 64 64 64 64 63 63 63 63 00 00 00 00 00 00 00 00 ddddcccc........
Then start server, make sure the table "looks OK":
mysql> select * from tt;
+------+------+------+
| id | val | val2 |
+------+------+------+
| aaaa | bbbb | zzzz |
| cccc | dddd | zzzz |
+------+------+------+
2 rows in set (0.00 sec)
and then try to run CHECK TABLE. You'll end up with server crashed and restarted, and the following messages in the error log:
...
page dump etc
...
2015-05-17 12:43:41 7fbcc827b700 InnoDB: uncompressed page, stored checksum in field1 2972981636, calculated checksums for field1: crc32 213674153, innodb 3424031611, none 3735928559, stored checksum in field2 82984784, calculated checksums for field2: crc32 213674153, innodb 82984784, none 3735928559, page LSN 0 1641382, low 4 bytes of LSN at page end 1641382, page number (if stored to page already) 4, space id (if created with >= MySQL-4.1.1 and stored already) 7
InnoDB: Page may be an update undo log page
InnoDB: Page may be an index page where index id is 25
InnoDB: (index "val" of table "test"."tt")
InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 4.
InnoDB: You may have to recover from a backup.
InnoDB: It is also possible that your operating
InnoDB: system has corrupted its own file cache
InnoDB: and rebooting your computer removes the
InnoDB: error.
InnoDB: If the corrupt page is an index page
InnoDB: you can also try to fix the corruption
InnoDB: by dumping, dropping, and reimporting
InnoDB: the corrupt table. You can use CHECK
InnoDB: TABLE to scan your table for corruption.
InnoDB: See also http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
InnoDB: Error: Unable to read tablespace 7 page no 4 into the buffer pool after 100 attempts
InnoDB: The most probable cause of this error may be that the table has been corrupted.
InnoDB: You can try to fix this problem by using innodb_force_recovery.
InnoDB: Please see reference manual for more details.
InnoDB: Aborting...
2015-05-17 12:43:41 7fbcc827b700 InnoDB: Assertion failure in thread 140448788625152 in file buf0buf.cc line 2741
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.6/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
09:43:41 UTC - mysqld got signal 6 ;
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.
Please help us make Percona Server better by reporting any
bugs at http://bugs.percona.com/
key_buffer_size=8388608
read_buffer_size=131072
max_used_connections=1
max_threads=153
thread_count=1
connection_count=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 69184 K bytes of memory
Hope that's ok; if not, decrease some variables in the equation.
Thread pointer: 0x7fbcb6744000
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...
stack_bottom = 7fbcc827adc8 thread_stack 0x40000
/home/openxs/dbs/p5.6/bin/mysqld(my_print_stacktrace+0x35)[0x8e9a15]
/home/openxs/dbs/p5.6/bin/mysqld(handle_fatal_signal+0x4b4)[0x66a0d4]
/lib64/libpthread.so.0(+0xf710)[0x7fbcc7c8a710]
/lib64/libc.so.6(gsignal+0x35)[0x7fbcc6935625]
/lib64/libc.so.6(abort+0x175)[0x7fbcc6936e05]
/home/openxs/dbs/p5.6/bin/mysqld[0xa5f33d]
/home/openxs/dbs/p5.6/bin/mysqld[0xa367ab]
/home/openxs/dbs/p5.6/bin/mysqld[0xa36ea4]
/home/openxs/dbs/p5.6/bin/mysqld[0xa3a98a]
/home/openxs/dbs/p5.6/bin/mysqld[0x946ba8]
/home/openxs/dbs/p5.6/bin/mysqld(_ZN7handler8ha_checkEP3THDP15st_ha_check_opt+0x6a)[0x5adb6a]
/home/openxs/dbs/p5.6/bin/mysqld[0x82b1d3]
/home/openxs/dbs/p5.6/bin/mysqld(_ZN19Sql_cmd_check_table7executeEP3THD+0xc2)[0x82be42]
/home/openxs/dbs/p5.6/bin/mysqld(_Z21mysql_execute_commandP3THD+0x16f5)[0x6eb5c5]
/home/openxs/dbs/p5.6/bin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_state+0x658)[0x6ef618]
/home/openxs/dbs/p5.6/bin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x14cf)[0x6f0b6f]
/home/openxs/dbs/p5.6/bin/mysqld(_Z24do_handle_one_connectionP3THD+0x16f)[0x6bd0bf]
/home/openxs/dbs/p5.6/bin/mysqld(handle_one_connection+0x47)[0x6bd197]
/home/openxs/dbs/p5.6/bin/mysqld(pfs_spawn_thread+0x12a)[0xb11f5a]
/lib64/libpthread.so.0(+0x79d1)[0x7fbcc7c829d1]
/lib64/libc.so.6(clone+0x6d)[0x7fbcc69eb8fd]
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (7fbcaeb2d010): check table tt
Connection ID (thread ID): 1
Status: NOT_KILLED
Now, tell me how one could suspect such an outcome from the manual page mentioned above?
Suggested fix:
Please, specify what exact corruptions lead to marking index and/or table as corrupted (with example to prove the statement), what exact corruptions (like wrong checksum, see the example above) lead to assertion failures and what (if any) lead to clean shut down of server.
Make sure that paragraphs/statements in the manual are specific and do not contradict each other.
Make sure to add a WARNING that running CHECK TABLE on the InnoDB table may crash the server, even if you are able to read all the data from the table (if corruption affects secondary index).
