Linux MySQL5InnoDB system error code 0

6 (SRV_FORCE_NO_LOG_REDO) do not roll logs before restoring the connection. The database cannot be used with the allowed options in these options. As

6 (SRV_FORCE_NO_LOG_REDO) do not roll logs before restoring the connection. The database cannot be used with the allowed options in these options. As

Mysql5.1.37 has an error in the replication environment. The error is as follows: An slave database is used for routine backup.

Version: '5. 1.37max-debug' socket: '/var/lib/mysql. sock' port: 3306 Source distribution
InnoDB: Error: tried to read 524288 bytes at offset 0 212992.
InnoDB: Was only able to read 69632.
100903 0:10:18 InnoDB: Operating system error number 0 in a file operation.
InnoDB: Error number 0 means 'success '.
InnoDB: Some operating system error numbers are described
InnoDB:
InnoDB: File operation call: 'read '.
InnoDB: Cannot continue operation.
100903 00:10:19 mysqld_safe Number of processes running now: 0
100903 00:10:19 mysqld_safe mysqld restarted
100903 0:10:20 [Note] Plugin 'federated 'is disabled.
100903 0:10:20 [Note] Plugin 'ndbcluster' is disabled.
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
100903 0:10:21 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the. ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: Error: tried to read 557056 bytes at offset 0 212992.
InnoDB: Was only able to read 69632.
100903 0:10:37 InnoDB: Operating system error number 0 in a file operation.
InnoDB: Error number 0 means 'success '.
InnoDB: Some operating system error numbers are described
InnoDB:
InnoDB: File operation call: 'read '.
InnoDB: Cannot continue operation.
100903 00:10:41 mysqld_safe mysqld from pid file/dbdata/mysql. pid ended

This happened at night and showed that the database was closed at. The error code 0 given above had no explanation.

In retrospect, the daily database full backup is also at, and the sequence shown above is that InnoDB fails to read, but the operating system returns 0 values and cannot continue to read files.

Then the system automatically restarts and verifies the logs after the restart. the idb file reads table space information and restores data to the site. However, the same reading error occurs, but the operating system normally reads the data at the specified location.

The database is so big that marketing department personnel need to use it immediately. Fortunately, it is not online to provide services directly. I can back up data with other nodes and perform the following operations first.

# Innodb_force_recovery = 4
# Skip-slave-start

Innodb_force_recovery = 4 and then start the server. The problem is that there is an insert statement that cannot be used and restarted constantly. Of course, this level does not allow updates, so I can only disable mysql, however, the replication also stops, and then mysql is started for check. check will also skip the unavailable data. check is complete. I know I can use it, comment out the two rows, and restart, wait until synchronization is complete. I know that I lost data, but I don't know how much I lost.

In order to solve the problem, the method used is not good. In fact, later I thought it would be okay if I wanted to get up immediately. I could just change the level to 3, so I don't need to stop copying, the reason is that the site cannot be recovered and the site cannot be recovered. The site is replied to for Redo or rollback of unfinished tasks. Therefore, the larger the log buffer, the better. The larger the log buffer, the longer the recovery time. The running speed is indeed much faster, but the recovery will be extended if there is a problem.

The following is the InnoDB crash recovery solution provided in the MySQL manual. If there is no backup, other nodes do not need to do so, even if they do so in their own way, data is also lost, and it is better to restore data from other nodes.

If the database page is damaged, you may want to use select into outfile to dump your table from the database. Generally, most of the data obtained in this way is intact. Even so, the damage may cause the SELECT * FROM tbl_name or InnoDB background operation to crash or assert, or even cause the InnoDB roll-back recovery to crash. However, you can use it to force the InnoDB Storage engine to start and prevent background operations from running, so that you can dump your tables. For example, you can add the following lines in the [mysqld] section of the Option file before restarting the server:
[Mysqld] innodb_force_recovery = 4innodb_force_recovery the following non-zero values are allowed. A larger number contains all smaller numbers. If you can use an option value of 4 to dump your table, you are safe, and only some data on damaged individual pages will be lost. A value of 6 is even more exaggerated because the database page is left in an old state, which in turn can cause more damage to the B tree and other database structures.
1 (srv_force_ignore_0000upt)
Even if the server detects a corrupted page, it also causes the server to run. Try to let SELECT * FROM tbl_name skip the damaged index records and pages, which will help dump the table.
2 (SRV_FORCE_NO_BACKGROUND)
Prevents the main thread from running. If the crash may occur during the cleaning operation, this will block it.
3 (SRV_FORCE_NO_TRX_UNDO)
Do not run transaction rollback after recovery.
4 (SRV_FORCE_NO_IBUF_MERGE)
It also prevents insert buffer merge operations. If you may cause a crash. We recommend that you do not perform these operations or calculate statistical tables.
5 (SRV_FORCE_NO_UNDO_LOG_SCAN)
When starting the database, do not view unfinished logs: InnoDB regards unfinished transactions as committed.
6 (SRV_FORCE_NO_LOG_REDO)
Do not roll logs before restoring the connection.
The database cannot be used with the allowed options in these options. As a security measure, when innodb_force_recovery is set to a value greater than 0, InnoDB prevents users from performing INSERT, UPDATE, or DELETE operations.
Even if force recovery is used, you can DROP or CREATE a table. If you know that a given table is causing a rollback crash, you can remove it. You can also use this to stop the out-of-control rollback caused by failed large imports or failed ALTER tables. You can kill the mysqld process and set innodb_force_recovery to 3, so that the database is suspended and does not need to be rolled back. Then, you can discard the tables that lead to out-of-control rollback.