MySQL Bugs: #68115: after shutdown of the server many tables are marked as crashed

Bug #68115	after shutdown of the server many tables are marked as crashed
Submitted:	18 Jan 2013 8:54	Modified:	22 Jan 2013 16:40
Reporter:	Korinna Kurrer	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	5.1.66-log, 5.5.28-log	OS:	Linux (RHEL 6.3 x64)
Assigned to:		CPU Architecture:	Any
Tags:	5.1.66-log, 5.5.28-log, corrupt, shut down, Table is marked as crashed

Description:
Hello,

I have 2 server with RHEL 6.3 x64 installed and a mysql-db with master-slave-replication between them.
If I shut down the mysql-slave-server when many users are using tha master-side many tables crashes in several databases.
I do a mysqlcheck after the new start and error messages say for instance:

CSMedBRB.Customer
warning  : 1 client is using or hasn't closed the table properly
CSMedBRB.LMSBillDetail
warning  : 1 client is using or hasn't closed the table properly
error    : Invalid key block position: 192528884070088704  key block size: 1024  file_length: 248052736
error    : key delete-link-chain corrupted
error    : Corrupt

So I have to do a repair table for several tables after that reboot.
This is not acceptable.

I tried the version 5.5.28-package and the 5.1.66-package directly distibuted by RedHat.

With both version the same behaviour.

Are there some configurations that I can set to avoid crashed tables?

We are using myisam.

Thank you.

Regards Korinna Kurrer 

How to repeat:
Install 2 server with RHEL 6.3 x64 and mysql-server.

Set up a master-slave-replication.

Do an # init 6 # on the slave-side with a load in the mysql-database on the master-side.

Control mysqld.log and control myisam-tables with mysqlcheck.

mysqld.log says:
...
InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
...

configuration file

Attachment: my.cnf (application/octet-stream, text), 3.37 KiB.

log file

Attachment: mysqld.log.20130116 (application/octet-stream, text), 32.74 KiB.

This is probably because standard shutdown script can not wait long enough for proper MySQL shutdown to happen. This is how server is stopped in 5.5:

...
'stop')
# Stop daemon. We use a signal here to avoid having to know the
# root password.

if test -s "$mysqld_pid_file_path"
then
mysqld_pid=`cat "$mysqld_pid_file_path"`

if (kill -0 $mysqld_pid 2>/dev/null)
then
echo $echo_n "Shutting down MySQL"
kill $mysqld_pid
# mysqld should remove the pid file when it exits, so wait for it.
wait_for_pid removed "$mysqld_pid" "$mysqld_pid_file_path"; return_value=$?

Note that wait_for_pid function will really wait for complete shutdown that ends with removal of pid file, but no longer than for some predefined time:

# Default value, in seconds, afterwhich the script should timeout waiting
# for server start.
# Value here is overriden by value in my.cnf.
# 0 means don't wait at all
# Negative numbers mean to wait indefinitely
service_startup_timeout=900

init itself at level 6 will NOT wait long enough after running this script with stop parameter:

...
kill_all $"Sending all processes the TERM signal..." -15 $OMITARGS
# No need to sleep and kill -9 if no processes to kill were found
if [ "$?" == 0 ]; then
sleep 2
kill_all $"Sending all processes the KILL signal..." -9 $OMITARGS
fi
...

To summarize, if you want to be sure that server stopped properly, stop it explicitly before running init 6. Alternatively you may try to increase that timeout in mysql.server script. Read http://dev.mysql.com/doc/refman/5.5/en/mysql-server.html

Thank you for the report.

This is not proper way to shutdown server, so this is technically "Not a Bug"