Shulou(Shulou.com)06/01 Report--

2016-06-08 04:38:11 7fa7ddd86700 InnoDB: Error: space id and page NRO stored in the page

InnoDB: read in are 4294967295:4294967295, should be 22291:4096!

InnoDB: Database page corruption on disk or a failed

InnoDB: file read of page 4096.

InnoDB: You may have to recover from a backup.

2016-06-08 04:38:11 7fa7ddd86700 InnoDB: Page dump in ascii and hex (16384 bytes):

The tools used are self-written mysqlblock and bcview.

I put it on Baidu Cloud disk.

Http://pan.baidu.com/s/1num76RJ

For everyone to download and use

MYSQL encountered the above error today:

Obviously

InnoDB: read in are 4294967295:4294967295, should be 22291:4096!

An error message is thrown here, which is translated into

There is a problem on the 4096 block of space id 22291. The message read is 4294967295

It shows that the information read by SPACE ID is incorrect 4294967295.

So how did the first 4294967295 come from?

First of all, we need to find out what kind of table SPACEID is 22291.

Select * from INNODB_SYS_TABLESPACES where space = 22291

Then take out his ibd file if it is a separate tablespace is very simple, the purpose is to analyze the reason to make sure it is really the problem with this table.

Then use the tool to view the binaries. I used the bcview tool that I wrote myself.

*

This Tool Is Uesed For Find The Data In Binary format (Hexadecimal)

Usage:./bcview file blocksize offset cnt-bytes!

File: Is Your File Will To Find Data!

Blocksize: Is N kb Block.Eg: 8 Is 8 Kb Blocksize (Oracle)!

Eg: 16 Is 16 Kb Blocksize (Innodb)!

Offset:Is Every Block Offset Your Want Start!

Cnt-bytes:Is After Offset,How Bytes Your Want Gets!

Edtor QQ:22389860!

Used gcc version 4.1.2 20080704 (Red Hat 4.1.2-46)

*

Usage:./bcview file blocksize offset cnt-bytes!

Actually, this 4294967295 comes from the 4 bytes of 34-37 of the block.

If we open the binary file and use the tool bcview (written by ourselves)

. / bcview product_info_snapshot.ibd 16 34 4 | more

Current block:00004096--Offset:00034--cnt bytes:04--data is:ffffffff

We can clearly see that the four bytes here are all ffffffff. In fact, the normal block is

Current block:00000085--Offset:00034--cnt bytes:04--data is:00005713

So where does the second 4294967295, the block number, come from?

As a matter of fact, I didn't find out where he came from.

But why can he read the right $4096?

You are the 4-11 8 bytes of each block is the current block number and the block number of the previous block.

Let's take a look.

Current block:00004090--Offset:00004--cnt bytes:08--data is:00000ffa00000ff9

Current block:00004091--Offset:00004--cnt bytes:08--data is:00000ffb00000ffa

Current block:00004092--Offset:00004--cnt bytes:08--data is:00000ffc00000ffb

Current block:00004093--Offset:00004--cnt bytes:08--data is:00000ffd00000ffc

Current block:00004094--Offset:00004--cnt bytes:08--data is:00000ffe00000ffd

Current block:00004095--Offset:00004--cnt bytes:08--data is:00000fff00000ffe

Current block:00004096--Offset:00004--cnt bytes:08--data is:0000100000000fff

Current block:00004097--Offset:00004--cnt bytes:08--data is:0000100100001000

Current block:00004098--Offset:00004--cnt bytes:08--data is:0000100200001001

Obviously, you can see here that the current block number of 0000100000000fff is 4096. The last block is 4095.

It is confirmed here that there is something wrong with this block. So how do you recover?

Of course, you can delete the import directly from the library.

If there is no backup from the library, then be prepared for the possibility of losing data.

We can create table t_bak as select * from t

This must have reported an error, that is, the block in question was read, but the t_bak came out, and at this time we took it from the increment primary key and tried to move part of it backward.

If the error is a push, such as a primary key + 10, it still has something to do with your line size. If the row of 1K is only about 10 pieces of data per block.

As long as we skip the wrong block, the reading should be normal. But it is certain that some data will be lost, and what is lost is the data of bad blocks.

If a dead horse is a doctor, you can change 34-37 bytes to a normal value. Try again.

Of course, you can also use the mysqlblock tool to see if there are any abnormal blocks.

Exception:

[root@bak tmp] #. / mysqlblock product_info_snapshotbak.ibd-t

FILE SIZE IS: 1589641216

Total Block Status:

Total block: 97024,Total size is: 1516.000000 MB

Total undo block: 0,Total size is: 0.000000 MB

Total inode block: 1,Total size is: 0.015625 MB

Total insert buffer free blocks: 0,Total size is: 0.000000 MB

Total data (index pages) block: 92434 total size is: 1444.281250 MB

Total new allocate blocks: 4540,Total size is: 70.937500 MB

Total insert buf bitmap blocks: 6,Total size is: 0.093750 MB

Total system blocks: 0,Total size is: 0.000000 MB

Total transaction system blocks: 0,Total size is: 0.000000 MB

Total file space header blocks: 1,Total size is: 0.015625 MB

Total extrenl disc blocks: 5,Total size is: 0.078125 MB

Total LOB blocks: 24,Total size is: 0.375000 MB

Total Unkown blocks: 13,Total size is: 0.203125 MB

Normal:

[root@bak tmp] #. / mysqlblock product_info_snapshot.ibd-t

FILE SIZE IS: 1589641216

Total Block Status:

Total block: 97024,Total size is: 1516.000000 MB

Total undo block: 0,Total size is: 0.000000 MB

Total inode block: 1,Total size is: 0.015625 MB

Total insert buffer free blocks: 0,Total size is: 0.000000 MB

Total data (index pages) block: 92449 Total size is: 1444.515625 MB

Total new allocate blocks: 4538,Total size is: 70.906250 MB

Total insert buf bitmap blocks: 6,Total size is: 0.093750 MB

Total system blocks: 0,Total size is: 0.000000 MB

Total transaction system blocks: 0,Total size is: 0.000000 MB

Total file space header blocks: 1,Total size is: 0.015625 MB

Total extrenl disc blocks: 5,Total size is: 0.078125 MB

Total LOB blocks: 24,Total size is: 0.375000 MB

Total Unkown blocks: 0,Total size is: 0.000000 MB

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.