Bug #88548 Inconsistent behavior of myisam-recover-options when starting up MySQL

• Submitted: 20 Nov 2017 2:27
• Modified: 23 Nov 2017 5:38
• Reporter: Jaime Sicam
• Status: Verified
• Category: MySQL Server: MyISAM storage engine
• Severity: S3 (Non-critical)
• Version: 5.7.20
• OS: Any
• CPU Architecture: Any

[20 Nov 2017 2:27] Jaime Sicam

Description:
The following configurations set myisam-recover-options to OFF when placed in my.cnf:

a) myisam-recover-options="OFF"
b) myisam-recover-options=""
c) myisam-recover-options=
d) *when myisam-recover-options is not defined in my.cnf*

However, among all configurations, if the mysql.user table is corrupt or was not closed properly,  MySQL is able to start except when (a) is used. 

The error message on (a) is:

2017-11-20T02:23:18.694998Z 0 [ERROR] /usr/sbin/mysqld: Table './mysql/user' is marked as crashed and should be repaired
2017-11-20T02:23:18.695049Z 0 [ERROR] Fatal error: Can't open and lock privilege tables: Table './mysql/user' is marked as crashed and should be repaired
2017-11-20T02:23:18.695227Z 0 [ERROR] Aborting

How to repeat:
1. Create unsafe copied of mysql system directory(I'll send sample tables as well):

#!/bin/bash
backup_dir=backup
user_prefix=A
ctr=0
while [ 1 ] 
do
  cur_date=`date +%F_%H-%M-%S`
  echo "Creating user $user_prefix$ctr..."
  mysql -e "create user $user_prefix$ctr"
  ctr=`expr $ctr + 1`
  mkdir -p $backup_dir/$cur_date
  echo "Creating backup $cur_date"
  cp -v /var/lib/mysql/mysql/* $backup_dir/$cur_date
  sleep 1
done

2. Stop MySQL

3. Copy the backup mysql tables to MySQL data directory

4. Test several myisam-recover-options:
a) myisam-recover-options="OFF"
b) myisam-recover-options=""
c) myisam-recover-options=
d) *when myisam-recover-options is not defined in my.cnf*

5. If MySQL is able to start, check the value of myisam-recover-options:
mysql> show global variables like 'myisam-recover-options';
Empty set (0.00 sec)

[20 Nov 2017 2:31] Jaime Sicam

MySQL system tables

Attachment: 2017-11-20_00-59-06.tar.gz (application/x-gzip, text), 342.77 KiB.

[20 Nov 2017 4:04] Jaime Sicam

Also, I've added mysql.db for testing as well. Again, mysql will fail to start with myisam-recover-options="OFF" and complain that mysql.db is corrupt.

#!/bin/bash
backup_dir=backup
user_prefix=A
ctr=1
while [ 1 ] 
do
  cur_date=`date +%F_%H-%M-%S`
  echo "Creating user $user_prefix$ctr..."
  mysql -e "create user $user_prefix$ctr"
  mysql -e "grant select on $user_prefix.* to $user_prefix$ctr"
  ctr=`expr $ctr + 1`
  echo "Creating backup dir"
  mkdir -p $backup_dir/$cur_date
  echo "Creating backup $cur_date"
  cp -fr /var/lib/mysql/mysql/* $backup_dir/$cur_date
  sleep 1
done

[20 Nov 2017 12:35] MySQL Verification Team

Hello Jaime Sicam,

Thank you for the report and test case.
With the provided MySQL system tables(2017-11-20_00-59-06.tar.gz), confirmed with both ways i.e using conf file and using command-line format that MySQL is able to start except when (a) is used.
For (5) Can you please try 'myisam_recover_options' as variable name? 

mysql> show variables like 'myisam_recover_options';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| myisam_recover_options | OFF   |
+------------------------+-------+
1 row in set (0.01 sec)

Thanks,
Umesh

[23 Nov 2017 5:38] Jaime Sicam

Hi Umesh, 

   With just myisam-recover-options set under [mysqld] of my.cnf, the server starts up just fine.