Resolve database operation not allowed when Innodb_forced_recovery > 0

Resolve database operation not allowed when Innodb_forced_recovery > 0

Please modify MY.CNF

Innodb_force_recovery = 1

Revision changed to

Innodb_force_recovery = 0

When closed, the parameter innodb_fast_shutdown affects the behavior of the table's storage engine as InnoDB.

The value of this parameter is 0, 1, 2

0 on behalf of the party when MySQL is closed, InnoDB needs to complete all full purge and merge insert buffer operations, which will take some time. The 1 represents no need to complete the above full purge, merge insert buffer operation, but some data dirty pages in the buffer pool will still be flushed to disk. 2 means that the full purge is not completed, the merge insert buffer operation, and the data dirty pages in the buffer pool are not written back to disk, but the logs are written to the log file. This will not cause anything to be lost, but the next time the MySQL database is started, it will execute recovery
The parameter innodb_force_recovery affects the recovery status of the entire InnoDB storage engine. Default 0

Test:

Environment: Innodb_fast_shutdown = 2

Innodb_flush_log_at_trx_commit = 2

Sync_binlog = 0

Innodb_force_recovery affects the recovery status of the entire InnoDB storage engine. The default is 0, which means that when recovery is required, all

Recovery operation. When a valid recovery operation is not possible, MySQL may fail to start and record the error log.
The innodb_force_recovery can be set to 1-6, and the large number contains the effect of all previous numbers.
When the set parameter value is greater than 0, the table can be select,create,drop, but the insert,update or delete operation

is not allowed.
1 (srv_force_ignore_corrupt): Ignores the corrupt page that is checked.
2 (Srv_force_no_background): Prevents the main thread from running, as the main thread needs to perform full purge operations, which can cause crash.
3 (Srv_force_no_trx_undo): Transaction rollback operation is not performed.
4 (Srv_force_no_ibuf_merge): Do not perform insert buffer merge operation.
5 (Srv_force_no_undo_log_scan): Do not look for the log, InnoDB storage engine will treat uncommitted transactions as committed.
6 (Srv_force_no_log_redo): Do not roll forward operations.



Test A
Destroy XBB5.IBD table
The data page was deleted

Innodb_force_recovery = 1-3 Table Unavailable report error 2002 (HY000): Can ' t connect to local MySQL server through socket '/TMP/MYSQLD.SOC K ' Error innodb_force_recovery = 4-6 SELECT * Available, select COUNT (*) No lack of report error (HY000): Lost connection to MySQL server Duri ng Query Error

Test Two
Create transaction, do not commit

[Email protected] 04:32:32>begin; Query OK, 0 rows affected (0.01 sec) [email protected] 04:33:14>update Test set B = b+100; Query OK, 9999 rows affected (0.18 sec) rows matched:9999 changed:9999 warnings:0

Innodb_force_recovery =0 to check for rollback operations

130626 16:32:20  Innodb:database was wasn't shut down normally! Innodb:starting crash recovery. innodb:reading tablespace information from the. ibd files ... innodb:restoring possible Half-written data pages from the Doublewriteinnodb:buffer ...  Innodb:1 transaction (s) which must be rolled back or cleaned upinnodb:in total 9999 row operations to Undoinnodb:trx ID Counter is 0 12544innodb:last MySQL binlog file position 0 920753, file Name/vobiledata/mysqllog/mysql-bin.000245innodb : Starting in background the rollback of uncommitted transactions130626 16:32:21  innodb:rolling back trx with ID 0 12032, 9999 rows to undoinnodb:progress in percents:1130626 16:32:21  innodb:started; Log sequence number 0 4330016130626 16:32:21 [Note] Recovering after a crash using/vobiledata/mysqllog/mysql-bin130626 16 : 32:21 [Note] starting crash recovery ...  130626 16:32:21 [Note] Crash Recovery finished. 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 7 1-------------------Bayi ded 0 events130626 16:32:21 [Note]/usr/local/mysql/bin/mysqld:ready for connections. Version: ' 5.1.57-log '   socket: '/tmp/mysqld.sock '   port:3306  MySQL Community Server (GPL)  98 99 100 Innodb:rolling back of Trx ID 0 12032 completed130626 16:32:21  innodb:rollback of non-prepared transactions comple Ted

If you roll back more data, recovery is relatively slow
Innodb_force_recovery = 2 prevents the main thread from running, as the main thread needs to perform full purge operations, which can cause crash.

Innodb:starting crash recovery. innodb:reading tablespace information from the. ibd files ... innodb:restoring possible Half-written data pages from the Doublewriteinnodb:buffer ...  Innodb:1 transaction (s) which must be rolled back or cleaned upinnodb:in total 9999 row operations to Undoinnodb:trx ID Counter is 0 15616innodb:last MySQL binlog file position 0 920753, file Name/vobiledata/mysqllog/mysql-bin.000245innodb : Starting in background the rollback of uncommitted transactions130626 17:05:53  innodb:rolling back trx with ID 0 15104, 9999 rows to undoinnodb:progress in percents:1130626 17:05:53  innodb:started; Log sequence number 0 13016158InnoDB:!!! Innodb_force_recovery is set to 2!!! 130626 17:05:53 [note] Recovering after a crash using/vobiledata/mysqllog/mysql-bin130626 17:05:53 [note] starting crash Recovery ...  130626 17:05:53 [Note] Crash Recovery finished. 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 3536 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 85130626 17:05:53 [note] Event scheduler:loaded 0 events130626 17:05:53 [note]/usr/local/mysql/ Bin/mysqld:ready for connections. Version: ' 5.1.57-log '   socket: '/tmp/mysqld.sock '   port:3306  MySQL Community Server (GPL)  86 87 88 8 9 94 98 100innodb:rolling back of Trx ID 0 15104 completed130626 17:05:53  innodb:rollback of non-prepared transactions completed

Innodb_force_recovery =3 does not perform a rollback operation

130626 16:33:53  Innodb:database was wasn't shut down normally! Innodb:starting crash recovery. innodb:reading tablespace information from the. ibd files ... innodb:restoring possible Half-written data pages from the Doublewriteinnodb:buffer ...  Innodb:1 transaction (s) which must be rolled back or cleaned upinnodb:in total 9999 row operations to Undoinnodb:trx ID  Counter is 0 13056innodb:last MySQL binlog file position 0 920753, file name/vobiledata/mysqllog/mysql-bin.000245130626 16:33:53  innodb:started; Log sequence number 0 6497918InnoDB:!!! Innodb_force_recovery is set to 3!!! 130626 16:33:53 [note] Recovering after a crash using/vobiledata/mysqllog/mysql-bin130626 16:33:53 [note] starting crash Recovery ... 130626 16:33:53 [note] Crash recovery finished.130626 16:33:53 [note] Event scheduler:loaded 0 events130626 16:33:53 [not E]/usr/local/mysql/bin/mysqld:ready for connections. Version: ' 5.1.57-log '   socket: '/tmp/mysqld.sock '   port:3306  MySQL CommuNity Server (GPL) 

Innodb_force_recovery =5 do not look for the log, InnoDB the storage engine will commit uncommitted transaction transactions
At this point the data has been update

+----+------+------+------+| A | B | C |  D |+----+------+------+------+|  1 |    101 |    1 |  1 | |  2 |    102 |    2 |  2 | |  3 |    103 |    3 |  3 | |  4 |    104 |    4 |  4 | |  5 |    105 |    5 |  5 | |  6 |    106 |    6 |  6 | |  7 |    107 |    7 |  7 | |  8 |    108 |    8 |  8 | |  9 |    109 |    9 | 9 | |  10 |   110 |   10 | Ten |+----+------+------+------+

Innodb_force_recovery =6 does not perform a roll forward operation, but there is a rollback operation when recovering

+----+------+------+------+| A | B | C |  D |+----+------+------+------+|  1 |    101 |    1 |  1 | |  2 |    102 |    2 |  2 | |  3 |    103 |    3 |  3 | |  4 |    104 |    4 |  4 | |  5 |    105 |    5 |  5 | |  6 |    106 |    6 |  6 | |  7 |    107 |    7 |  7 | |  8 |    108 |    8 |  8 | |  9 |    109 |    9 | 9 | |  10 |   110 |   10 | Ten |+----+------+------+------+

130626 16:44:29  Innodb:database was wasn't shut down normally! Innodb:starting crash recovery. innodb:reading tablespace information from the. ibd files ... innodb:restoring possible Half-written data pages from the Doublewriteinnodb:buffer ... Innodb:doing recovery:scanned up to log sequence number 0 8680656innodb:1 transaction (s) which must is rolled back or C Leaned Upinnodb:in total 9999 row operations to Undoinnodb:trx ID counter is 0 14080innodb:last MySQL binlog file posit Ion 0 920753, file name/vobiledata/mysqllog/mysql-bin.000245innodb:starting in background the rollback of uncommitted TR ansactions130626 16:44:29  innodb:rolling back trx with ID 0 13057, 9999 rows to undoinnodb:progress in percents:1 130626 16:44:29  innodb:started;  Log sequence number 0 8680656 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 79130626 16:44:29 [note] Event scheduler:loaded 0 events130626 16:44:29 [note]/usr/local /mysql/bin/mysqld:ready for connections. Version: ' 5.1.57-log '   socket: '/tmp/mysqld.sock '   port:3306  MySQL Community Server (GPL)  80 81 82 8  3 94----------------98 Innodb:rollback of non-prepared transactions completed130626 16:45:08 Mysqld_safe starting mysqld daemon with databases from/vobiledata/mysqldata130626 16:45:08 [Note] Plugin ' Federated ' is disabled.130626 16:45:08  InnoDB:  Initializing buffer pool, size = 2.0g130626 16:45:08  innodb:completed initialization of buffer poolinnodb:the user has set Srv_force_no_log_redo oninnodb:skipping LOG redo130626 16:45:08  innodb:started; Log sequence number 0 0InnoDB:!!! Innodb_force_recovery is set to 6!!! 130626 16:45:08 [Note] Recovering after a crash using/vobiledata/mysqllog/mysql-bin130626 16:45:08 [Note] starting crash recovery ... 130626 16:45:08 [note] Crash recovery finished.130626 16:45:08 [note] Event scheduler:loaded 0 events130626 16:45:08 [not E]/usr/local/mysql/bin/mysqld:ready for connections. Version: ' 5.1.57-log '   socket: '/tmp/mysqld.sock '   port:3306  MySQL Community Server (GPL) 130626 16:45:14  InnoDB:error:space object of table Test/test,innodb:space ID 3 did not exist in memory. Retrying an open.

-+------+------+------+|  1 |  101 |    1 |    1 | |   2 |  102 |    2 |    2 | |   3 |  103 |    3 |    3 | |   4 |  104 |    4 |    4 | |   5 |  |    5 |    5 | |   6 |  106 |    6 |    6 | |   7 |  107 |    7 |    7 | |   8 |  108 |    8 |    8 | |   9 |  109 |    9 |    9 | | Ten |  |   |   |+----+------+------+------+ 

130626 16:44:29  Innodb:database was wasn't shut down normally! Innodb:starting crash recovery. innodb:reading tablespace information from the. ibd files ... innodb:restoring possible Half-written data pages from the Doublewriteinnodb:buffer ... Innodb:doing recovery:scanned up to log sequence number 0 8680656innodb:1 transaction (s) which must is rolled back or C Leaned Upinnodb:in total 9999 row operations to Undoinnodb:trx ID counter is 0 14080innodb:last MySQL binlog file posit Ion 0 920753, file name/vobiledata/mysqllog/mysql-bin.000245innodb:starting in background the rollback of uncommitted TR ansactions130626 16:44:29  innodb:rolling back trx with ID 0 13057, 9999 rows to undoinnodb:progress in percents:1 130626 16:44:29  innodb:started;  Log sequence number 0 8680656 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 79130626 16:44:29 [note] Event scheduler:loaded 0 events130626 16:44:29 [note]/usr/local /mysql/bin/mysqld:ready for connections. Version: ' 5.1.57-log '   socket: '/tmp/mysqld.sock '   port:3306  MySQL Community Server (GPL)  80 81 82 8  3 94----------------98 Innodb:rollback of non-prepared transactions completed130626 16:45:08 Mysqld_safe starting mysqld daemon with databases from/vobiledata/mysqldata130626 16:45:08 [Note] Plugin ' Federated ' is disabled.130626 16:45:08  InnoDB:  Initializing buffer pool, size = 2.0g130626 16:45:08  innodb:completed initialization of buffer poolinnodb:the user has set Srv_force_no_log_redo oninnodb:skipping LOG redo130626 16:45:08  innodb:started; Log sequence number 0 0InnoDB:!!! Innodb_force_recovery is set to 6!!! 130626 16:45:08 [Note] Recovering after a crash using/vobiledata/mysqllog/mysql-bin130626 16:45:08 [Note] starting crash recovery ... 130626 16:45:08 [note] Crash recovery finished.130626 16:45:08 [note] Event scheduler:loaded 0 events130626 16:45:08 [not E]/usr/local/mysql/bin/mysqld:ready for connections. Version: ' 5.1.57-log '   socket: '/tmp/mysqld.sock '   port:3306  MySQL Community Server (GPL) 130626 16:45:14  InnoDB:error:space object of table Test/test,innodb:space ID 3 did not exist in memory. Retrying an open.

Resolve database operation not allowed when Innodb_forced_recovery > 0