Bug #73338 error message is not readable when setting innodb-force-recovery=6

• Submitted: 21 Jul 2014 8:56
• Modified: 21 Jul 2014 9:05
• Reporter: zhai weixiang (OCA)
• Status: Verified
• Category: MySQL Server: Errors
• Severity: S3 (Non-critical)
• Version: 5.6, 5.7, 5.6.21
• OS: Any
• CPU Architecture: Any

[21 Jul 2014 8:56] zhai weixiang

Description:
While starting mysql instance using --innodb-force-recovery=6. The innodb is setting to read only. So you can't do any DML or DDL. And a more  readable error message should be printed....

For example:

mysql> show global variables like 'innodb_force_recovery';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_force_recovery | 6     |
+-----------------------+-------+
1 row in set (0.00 sec)

mysql> show create table t2;
+-------+-------------------------------------------------------------------------------------+
| Table | Create Table                                                                        |
+-------+-------------------------------------------------------------------------------------+
| t2    | CREATE TABLE `t2` (
  `a` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> drop table t2;
ERROR 1051 (42S02): Unknown table 'test.t2'

mysql> create table t3 (a int);
ERROR 1874 (HY000): InnoDB is in read only mode.

How to repeat:
described above

Suggested fix:
report ER_INNODB_READ_ONLY error when drop table..

[21 Jul 2014 9:05] MySQL Verification Team

Hello Zhai,

Thank you for the report.
Verified as described.

Thanks,
Umesh

[21 Jul 2014 9:06] MySQL Verification Team

// 5.6.21

mysql-advanced-5.6.21]$ bin/mysqld_safe --defaults-file=./my.cnf --innodb-force-recovery=6 --user=mysql &
mysql-advanced-5.6.21]$ bin/mysql -u root -p -S /tmp/mysql.sock
Enter password:

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1             |
| t2             |
| t3             |
+----------------+
3 rows in set (0.00 sec)

mysql> drop table t2;
ERROR 1051 (42S02): Unknown table 'test.t2'
mysql> create table t4 (a int);
ERROR 1874 (HY000): InnoDB is in read only mode.
mysql>
mysql> show variables like '%version%';
+-------------------------+---------------------------------------------------------+
| Variable_name           | Value                                                   |
+-------------------------+---------------------------------------------------------+
| innodb_version          | 5.6.21                                                  |
| protocol_version        | 10                                                      |
| slave_type_conversions  |                                                         |
| version                 | 5.6.21-enterprise-commercial-advanced-log               |
| version_comment         | MySQL Enterprise Server - Advanced Edition (Commercial) |
| version_compile_machine | x86_64                                                  |
| version_compile_os      | Linux                                                   |
+-------------------------+---------------------------------------------------------+
7 rows in set (0.00 sec)

[22 Jul 2014 10:23] Naga Satyanarayana Bodapati

InnoDB returns HA_ERR_TABLE_READONLY when recovery mode >= 4. The error is not propagated properly. Changing the Category.