Querying myisam table metadata may corrupt the table

[Jason Parrott]

This is related to bug #925377, but confirmed on the 5.3.6 release.

Jervin R (viiin) wrote a reproducer which I have attached (myisam_crash.rb) and confirm causes the corrupt table message. We're seeing corrupt tables on our live databases as well.

This bug appears to be present still on 5.3.6 - possibly not during ENABLE|DISABLE keys but immediately before drop. see my.cnf below and Ruby script for test case.

[mysqld]
basedir=/home/revin/Downloads/mariadb-5.3.6-Linux-x86_64/
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=revin
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

collation_server = utf8_unicode_ci
character_set_server = utf8
skip-external-locking
#fast_index_creation = false

# 10MB ramdisk - can be ignored
tmpdir = /tmpfs

key_buffer = 276M
innodb_buffer_pool_size = 276M
max_allowed_packet = 16M
thread_stack = 256K
thread_cache_size = 128
thread_concurrency = 8
max_connections = 512
table_cache = 2048
myisam-recover = BACKUP
query_cache_limit = 1M
query_cache_size = 128M
expire_logs_days = 10
max_binlog_size = 100M

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Produces the output:
120426 11:32:39 [ERROR] mysqld: Table './test/customerXX' is marked as crashed and should be repaired

[Elena Stepanova (elenst)]

The problem is also reproducible with the MTR test case provided in the original bug report (I will paste it again here).
The behavior is different on 5.1/5.2 and 5.3 now. On 5.3 the assertion failure seems to have been fixed, although the corruption messageis still reproducible. On 5.1 and 5.2 debug versions the original assertion failure (Assertion `strcmp(share->unique_file_name,filename) || share->last_version' failed) is still observed, and there is no commit for bug #925377 in the tree history, apparently it was only pushed into 5.3.

# MTR test case:

--connect(con1,localhost,root,,)
--perl
open( DATA, ">bug925377.txt" )
  || die "Couldn't open file bug925377.txt for writing: $!";
foreach my $i ( 1..100000 )
{
  print DATA "$i,line number $i\n";
}
close( DATA );
EOF
--disable_warnings
DROP TABLE IF EXISTS bug925377;
--enable_warnings
CREATE TABLE bug925377 (
  id INT PRIMARY KEY,
  a VARCHAR(100),
  INDEX(a)
) ENGINE=MyISAM;
ALTER TABLE bug925377 DISABLE KEYS;
LOAD DATA LOCAL INFILE 'bug925377.txt'
  INTO TABLE bug925377
  FIELDS TERMINATED BY ',';
--send
  ALTER TABLE bug925377 ENABLE KEYS;

--connection default
--let $wait_timeout=10
--let $show_statement= SHOW PROCESSLIST
--let $field= State
--let $condition= = 'Repair by sorting'
--source include/wait_show_condition.inc

SHOW TABLE STATUS LIKE 'bug925377';

--connection con1
--reap
DROP TABLE bug925377;

# End of MTR test case

[Peter (Stig) Edwards (thatsafunnyname)]

I created a bash version of the ruby reproducer, the table has fewer fields.
It reproduces the table marked as crashed error reliably (for me), using MariaDB 5.3.6, I will try it with the latest 5.3 next

#!/bin/sh

HOST=localhost
PORT=3306
USER=root
PASSWORD=
DATABASE=test
MYSQL_CLIENT=/usr/bin/mysql

function run_cmd {
  $MYSQL_CLIENT --host=$HOST --user=$USER --password=$PASSWORD --database=$DATABASE --exec="$1" > /dev/null
}

function dbs_size {
  for i in {1..200}
  do
    run_cmd "SELECT table_schema , data_length , index_length FROM information_schema.TABLES WHERE table_schema='$DATABASE'"
  done
}

function table_run {
    IDX="$1"
  run_cmd "CREATE TABLE table_$IDX (\`id\` int, PRIMARY KEY (\`id\`)) ENGINE=MyISAM CHARSET=latin1"
  run_cmd "alter table table_$IDX disable keys"
  run_cmd "alter table table_$IDX enable keys"
  run_cmd "drop table table_$IDX"
}

function many_table_run {
  for i in {1..20}
  do
    table_run $i &
  done
}

dbs_size &
many_table_run

# produces in mysqld error log:
# 120515 11:31:23 [ERROR] mysqld: Table './test/table_5' is marked as crashed and should be repaired
# 120515 11:31:23 [ERROR] mysqld: Table 'table_5' is marked as crashed and should be repaired

[Peter (Stig) Edwards (thatsafunnyname)]

The bash reproducer in the comment above reproduces the table marked as crashed when using:
  http://terrier.askmonty.org/archive/pack/5.3/build-2163/kvm-bintar-hardy-amd64/mariadb-5.3.7-Linux-x86_64.tar.gz
from
  http://buildbot.askmonty.org/buildbot/builders/kvm-bintar-hardy-amd64/builds/1634
which is revision 3523 of ~maria-captains/maria/5.3

[Peter (Stig) Edwards (thatsafunnyname)]

I created a MTR version of the ruby and bash reproducers. I suspect it could be simplified even more:

# MTR test case:

# https://bugs.launchpad.net/maria/+bug/989055

--disable_warnings
let $h= 200;
while ($h)
{
  eval DROP TABLE IF EXISTS t_$h;
  dec $h;
}
--enable_warnings

--connect(con1,localhost,root,,)
--connect(con2,localhost,root,,)

let $i= 200;
while ($i)
{
  eval CREATE TABLE t_$i ( id INT PRIMARY KEY, a VARCHAR(100), INDEX(a) ) ENGINE=MyISAM;
  dec $i;
}

let $j= 200;
while ($j)
{
  connection con1;
    --send
      eval ALTER TABLE t_$j DISABLE KEYS;
  connection con2;
    --send
      eval SHOW TABLE STATUS LIKE 't_$j';
  connection con1;
    reap;
    --send
      eval ALTER TABLE t_$j ENABLE KEYS;
  connection con1;
    reap;
  connection con2;
    reap;
  dec $j;
}
--connection default
disconnect con1;
disconnect con2;

let $k= 200;
while ($k)
{
  eval SHOW TABLE STATUS LIKE 't_$k';
  dec $k;
}

# Fails because of warnings found in the error log:
# YYMMDD HH:MM:SS [ERROR] mysqld: Table './test/t_XX' is marked as crashed and should be repaired

# End of MTR test case

[Elena Stepanova (elenst)]

Hi Peter,

There was an MTR test case in comment #2, does it not work for you?

[Peter (Stig) Edwards (thatsafunnyname)]

Oops, sorry, in place of the "SHOW TABLE.." I intended to use "SELECT table_schema , data_length , index_length...", so:

< eval SHOW TABLE STATUS LIKE 't_$j';
> SELECT table_schema , data_length , index_length FROM information_schema.TABLES WHERE table_schema='test';

This fails because of the the table mark as crashed error in the mysqld err log.

The MTR test case in comment #2 works for me, in that it reproduces the problem of table marked as crashed.

Even though I suspect the bug may be the same, I wanted to try and produce a MTR test case for the 2nd reproducer
as it didn't involve "LOAD DATA ..." (or even writing any data to the tables), in the #2 comment MTR the query
running while keys are disabled/enabled is SHOW TABLE STATUS LIKE 'bug925377' while in the 2nd reproducer the query
is less specific (I imagine it ends up "opening for status").

I have not run it with a debug binary, if it is the same bug then please disregard it, thanks.