Shulou(Shulou.com)05/31 Report--

This article will explain in detail what to do about Space id in fsp header,but in the page header errors in MYSQL. The editor thinks it is very practical, so I share it with you for reference. I hope you can get something after reading this article.

The following problems were found when starting MYSQL:

2015-12-14 20:51:59 2098 [ERROR] InnoDB: Space id in fsp header 131225 but in the page header 65

2015-12-14 20:51:59 2098 [ERROR] InnoDB: inconsistent data in space header in tablespace. / test/oneblock.ibd (table test/oneblock)

The storage of innodb contains

Tablespace--segment--extent--block (pages)

While tablespace has a space id and a unique number to describe the tablespace, the space id of ibdata is 0, and the ibdata is

SYSTEM SPACE, which contains a lot of data segments, or blocks, such as rollback segments that are not included in a normal SPACE_ID.

If innodb_file_per_table is turned on, every innodb table file has space id, where the file contains table data and index data.

A normal space contains less information.

Our main analysis here is only to analyze the ordinary space, that is, the ibd file established by innodb_file_per_table.

Use tools (tools written by yourself):

[root@hadoop1 test] # mysqlblock c1.ibd-t

* * *

USEAGE: mysqlblock datafile-tmomomod

This small tool used in study and test database,not

Uesd on online database!

This tool is used to find how many blocks and types

In specified datafile,Exp:how many undo block in d

Ata file!

QQ:22389860

* * *

-t Only Total blocks types in ibdata!

-d Blocks types detail in ibdata!

* * *

FILE SIZE IS: 10485760

Total Block Status:

Total block: 640,Total size is: 10.000000 MB

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

Total index node block: 1,Total size is: 0.015625 MB

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

Total data (leaf node) block: 113 total size is: 1.765625 MB

Total new allocate blocks: 524,Total size is: 8.187500 MB

Total insert buf bitmap blocks: 1,Total size is: 0.015625 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: 0,Total size is: 0.000000 MB

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

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

Here we can clearly see the number of blocks of each type, and pay attention to a special block

Total file space header blocks and FSP_HDR BLOCK, this block is required for every space

Even if SYSTEM SPACE contains it, it is always the first block of SPACE.

Each block also contains file header.

Space id in fsp header 131225,but in the page header 65

The fsp header here is file space header, and the last 38 to 150 bytes of the first block

And page header here is actually file header and FIL header, which is the first 38 bytes inherent in the block.

Here's a bit of a twist:

One is file header (the first 38 bytes inherent in each block)

One is file space header (38-150 bytes of the first block of space)

File header (FIL header)

Contains the following 0-37 bytes

Checksum (4) 0-3

Offset (4) 4-7

Previous page (4) 8-11

Next page (4) 12-15

LSN for last page modification (8) 16-23

Page type (2) 24-25

FLUSH LSN (8) 26-33

SPACE ID (4) 34-37

Some of the meanings of each of these values are self-explanatory, while others require more in-depth study, but a node where a block is a two-way pointer is certain.

And record the LSN record of each block, which should be used for block recovery. This part is the same for every piece. And here SPCAE ID is.

It is stated in page header that there will be a value in every block. What's recorded is that SPACE_ID doesn't have to explain too much.

Next, let's take a look at the unique features of file space header block

38-150byte: file space header (FSP HEADER)

SPACE_ID (4)

Unused (4)

Highest page number in file (size) (4)

Highest page number initialized (free limit) (4)

Flags (4)

Number of pages uesd in "free_frag" list (4)

List base node for "free" list (16)

List base node for "free_frag" list (16)

List base node for "full_frag" list (16)

Nex unused segment id (8)

List base node for "full_nodes" list (16)

List base node for "free_nodes" list (16)

Many of these values need to be studied in depth, but we found that SPACE_ID is contained in 38-41 bytes.

Then we found it.

Space id in fsp header 131225,but in the page header 65

Where does fsp header come from, and he comes from the 38-41 bytes of the first block of the FSP HEADER and ibd files

And page header 65, 34-37 bytes from each block.

In that case, we can test and simulate this phenomenon, and we modify the 4 bytes manually. Using the tools I wrote myself, I wrote two because I couldn't find a suitable tool under LINUX.

Bctool and bcview

Bctool is used to modify

Bcview is used to view

Main test mode

1. Modify the 38-41 bytes of FSP HEADER to check whether MYSQL INNODB reports an error, and then modify it back to see whether it can start smoothly.

2. Modify the 34-37 bytes of the first block file space header to see if MYSQL INNODB reports an error, and then modify it back to see if it can start smoothly.

3. Modify the 34-37 bytes of the file space header of any block to see if the MYSQL INNODB reports an error, and then modify it back to see if it can start smoothly.

We still use the C1 table under the test database and its innodb data file is c1.ibd.

1. Modify the 38-41 bytes of FSP HEADER to check whether MYSQL INNODB reports an error, and then modify it back to see if it can be started smoothly.

View 34-37 bytes of a block through bcview

[root@hadoop1 test] # bcview c1.ibd 16 34 4 | more

*

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)

*

-Current file size is: 10.000000 Mb

-Current use set blockszie is 16 Kb

Current block:00000000--Offset:00034--cnt bytes:04--data is:00000048

Current block:00000001--Offset:00034--cnt bytes:04--data is:00000048

Current block:00000002--Offset:00034--cnt bytes:04--data is:00000048

Current block:00000003--Offset:00034--cnt bytes:04--data is:00000048

Current block:00000004--Offset:00034--cnt bytes:04--data is:00000048

Current block:00000005--Offset:00034--cnt bytes:04--data is:00000048

Current block:00000006--Offset:00034--cnt bytes:04--data is:00000048

Current block:00000007--Offset:00034--cnt bytes:04--data is:00000048

Current block:00000008--Offset:00034--cnt bytes:04--data is:00000048

Current block:00000009--Offset:00034--cnt bytes:04--data is:00000048

Current block:00000010--Offset:00034--cnt bytes:04--data is:00000048

.

Of course, there are a lot of blocks behind, but here bcview c1.ibd 1634 4 is the 16KB size of 34 bytes starting with a total of 4 bytes of output.

We found that it was all 00000048, because every space_id is recorded in page header.

So his space id is 0X48 10, which is 72. Let's verify it in MYSQL:

Mysql > select * from INNODB_SYS_TABLESPACES where space=72

+-+

| | SPACE | NAME | FLAG | FILE_FORMAT | ROW_FORMAT | PAGE_SIZE | ZIP_PAGE_SIZE | |

+-+

| | 72 | test/c1 | 0 | Antelope | Compact or Redundant | 16384 | 0 |

+-+

1 row in set (0.01 sec)

As you can see, there is no problem.

So let's look at 38 to 41 bytes next. For better illustration, I use the d option of mysqlblock c1.ibd-d | more to view detailed block information.

And use bcview c1.ibd 16 38 4 | more

Current block:00000000--Offset:00038--cnt bytes:04--data is:00000048

Current block:00000001--Offset:00038--cnt bytes:04--data is:00000000

Current block:00000002--Offset:00038--cnt bytes:04--data is:ffffffff

Current block:00000003--Offset:00038--cnt bytes:04--data is:000601c8

Current block:00000004--Offset:00038--cnt bytes:04--data is:00343b26

Current block:00000005--Offset:00038--cnt bytes:04--data is:00663b26

Current block:00000006--Offset:00038--cnt bytes:04--data is:00663b26

Current block:00000007--Offset:00038--cnt bytes:04--data is:00663b26

Current block:00000008--Offset:00038--cnt bytes:04--data is:00663b26

Current block:00000009--Offset:00038--cnt bytes:04--data is:00663b26

Current block:00000010--Offset:00038--cnt bytes:04--data is:00663b26

Current block:00000011--Offset:00038--cnt bytes:04--data is:00663b26

Current block:00000012--Offset:00038--cnt bytes:04--data is:00663b26

Current block:00000013--Offset:00038--cnt bytes:04--data is:00663b26

Current read blocks is: 0-- This Block is file space header blocks!

Current read blocks is: 1-- This Block is insert buffer bitmap blocks!

Current read blocks is: 2-- This Block is index node blocks!

Current read blocks is: 3-This Block is data blocks (Tree leaf node)!

Current read blocks is: 4-This Block is data blocks (Tree leaf node)!

Current read blocks is: 5-This Block is data blocks (Tree leaf node)!

Current read blocks is: 6-This Block is data blocks (Tree leaf node)!

Current read blocks is: 7-This Block is data blocks (Tree leaf node)!

Current read blocks is: 8-This Block is data blocks (Tree leaf node)!

Current read blocks is: 9-This Block is data blocks (Tree leaf node)!

Current read blocks is: 10-This Block is data blocks (Tree leaf node)!

Current read blocks is: 11-This Block is data blocks (Tree leaf node)!

Current read blocks is: 12-This Block is data blocks (Tree leaf node)!

Current read blocks is: 13-- This Block is data blocks (Tree leaf node)!

Current read blocks is: 14-This Block is data blocks (Tree leaf node)!

Now we can clearly see that the only four bytes of 38-41 is fsp_header block 00000048, which is space_id.

Other types of blocks are not.

Next we use bctool to modify the 38-41 bytes of FSP HEADER. Or make changes after closing the database

Make a backup first for safety.

[root@hadoop1 test] # cp c1.ibd c1.ibdbak

Then:

[root@hadoop1 test] # bctool c1.ibd 0 38 000000ee

*

This tool is uesed to check data ues binary format,no Big-Endian

Or Little-Endian diff,this tool is base one byte on byte to change

! block is 16k.if want change other block eg:8k! Please set blocks

0 and offset blocks*8192+offset!

Usage:./bctool yfile blocks offset yourdata (XX)!

Warings:backup file frist!

Editor QQ:22389860

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

*

0-0-0-ee

Check again:

Bcview c1.ibd 16 38 4 | more

Current block:00000000--Offset:00038--cnt bytes:04--data is:000000ee

Current block:00000001--Offset:00038--cnt bytes:04--data is:00000000

Current block:00000002--Offset:00038--cnt bytes:04--data is:ffffffff

Current block:00000003--Offset:00038--cnt bytes:04--data is:000601c8

I have changed it to ee.

Then start MYSQL, although the database is up, but

Mysql > select count (*) from C1

ERROR 2013 (HY000): Lost connection to MySQL server during query

Database downtime immediately

[root@hadoop1 test] # service mysqldefault start

Starting MySQL..The server quit without updating PID file (/ mysql/data/hadoop1.pid). [FAILED]

It is useless to start again. Check the log:

2015-12-26 01:05:38 5074 [ERROR] InnoDB: Space id in fsp header 238 but in the page header 72

2015-12-26 01:05:38 5074 [ERROR] InnoDB: checksum mismatch in tablespace. / test/c1.ibd (table test/c1)

2015-12-26 01:05:38 5074 [Note] InnoDB: Page size:1024 Pages to analyze:64

2015-12-26 01:05:38 5074 [Note] InnoDB: Page size: 1024, Possible space_id count:0

2015-12-26 01:05:38 5074 [Note] InnoDB: Page size:2048 Pages to analyze:64

2015-12-26 01:05:38 5074 [Note] InnoDB: Page size: 2048, Possible space_id count:0

2015-12-26 01:05:38 5074 [Note] InnoDB: Page size:4096 Pages to analyze:64

2015-12-26 01:05:38 5074 [Note] InnoDB: Page size: 4096, Possible space_id count:0

2015-12-26 01:05:38 5074 [Note] InnoDB: Page size:8192 Pages to analyze:64

2015-12-26 01:05:38 5074 [Note] InnoDB: Page size: 8192, Possible space_id count:0

2015-12-26 01:05:38 5074 [Note] InnoDB: Page size:16384 Pages to analyze:64

.

We found that error reproduction 238 conversion to hexadecimal is our modified 0XEE.

Innodb_force_recovery > 0 may be useful in this case, but I didn't experiment with it.

Then I'll change the 38-41 bytes of fsp header back to 0X48 and start it.

[root@hadoop1 test] # bctool c1.ibd 0 38 00000048

Check again

[root@hadoop1 test] # bcview c1.ibd 16 38 4 | more

Current block:00000000--Offset:00038--cnt bytes:04--data is:00000048

Start:

[root@hadoop1 test] # service mysqldefault start

Starting MySQL... [OK]

Startup complete.

Mysql > select count (*) from C1

+-+

| | count (*) |

+-+

| | 8192 |

+-+

1 row in set (0.01 sec)

There is no problem with the query.

Second, modify the 34-37 bytes of the first block file space header to see if the MYSQL INNODB reports an error, and then modify it back to see if it can start smoothly.

Close the data again

Use bcview to view

[root@hadoop1 test] # bcview c1.ibd 16 34 4 | more

Current block:00000000--Offset:00034--cnt bytes:04--data is:00000048

Modify:

[root@hadoop1 test] # bctool c1.ibd 0 34 00000099

View

[root@hadoop1 test] # bcview c1.ibd 16 34 4 | more

Current block:00000000--Offset:00034--cnt bytes:04--data is:00000099

Start the database and view the data

Mysql > select count (*) from C1

ERROR 2013 (HY000): Lost connection to MySQL server during query

View the log:

2015-12-26 01:17:46 6122 [ERROR] InnoDB: Space id in fsp header 72 but in the page header

The 153 here is the 0X99 compared to the one just now.

2015-12-26 01:05:38 5074 [ERROR] InnoDB: Space id in fsp header 238 but in the page header 72

You can see from the log that fsp header is normal and file header is wrong this time.

So let's revise it again:

[root@hadoop1 test] # bctool c1.ibd 0 34 00000048

View

Current block:00000000--Offset:00034--cnt bytes:04--data is:00000048

It has been modified back, and then started.

Mysql > select count (*) from C1

+-+

| | count (*) |

+-+

| | 8192 |

+-+

1 row in set (0.01 sec)

No questions to inquire about

Third, modify the 34-37 bytes of the file space header of any block to see if the MYSQL INNODB reports an error, and then modify it back to see if it can start smoothly.

Since the file header of each data block contains space id, we can change the

Current read blocks is: 0-- This Block is file space header blocks!

Current read blocks is: 1-- This Block is insert buffer bitmap blocks!

Current read blocks is: 2-- This Block is index node blocks!

Current read blocks is: 3-This Block is data blocks (Tree leaf node)!

Current read blocks is: 4-This Block is data blocks (Tree leaf node)!

Current read blocks is: 5-This Block is data blocks (Tree leaf node)!

Here, select Block 5 (0 is actually the sixth), and this block is the data block.

The specific process is no longer given, the order is given.

[root@hadoop1 test] # bctool c1.ibd 5 34 00000088

[root@hadoop1 test] # bcview c1.ibd 16 34 4 | more

Current block:00000005--Offset:00034--cnt bytes:04--data is:00000088

There is no problem with viewing the data, but there is an error in the log

2015-12-26 01:25:58 403be940 InnoDB: Error: space id and page NRO stored in the page

InnoDB: read in are 136:5, should be 72:5!

You can see that innodb said that the space id of block 5 is 0X88, which should be 72. Although the data can still run, this error is in the end.

The impact is unknown, but we know why, modify it.

Shut down the database:

[root@hadoop1 test] # bctool c1.ibd 0 34 00000048

[root@hadoop1 test] # bcview c1.ibd 16 34 4 | more

Current block:00000005--Offset:00034--cnt bytes:04--data is:00000048

The error disappears

Summary:

1. We can know what space id,space id is, that is, the unique identification of the table space.

2. We can know what FSP HEADER BLOCK is and the first block of each space file

The FSP HEADER is stored in 38-150 bytes of the first block of each SPACE file. In fact, this block also stores XDS information, that is, EXTENTS information.

Reintroduction

3. When accessing the table, if the 34-37-byte SPCAE_ID of the FIL HEADER of the first block does not match the 38-41 bytes of the FSP HEADER, the database will

Immediate downtime.

Error report:

2015-12-26 01:05:38 5074 [ERROR] InnoDB: Space id in fsp header 238 but in the page header 72

4. We also know that if there is a problem with the FIL HEADER 34-37 byte space id that is not the first block, the log will only report an error, but it will not affect the use.

Error report:

2015-12-26 01:25:58 403be940 InnoDB: Error: space id and page NRO stored in the page

InnoDB: read in are 136:5, should be 72:5!

5. In order to complete this experiment, I used three tools, all of which were written for myself. Without these three tools, the experiment could not have been completed, or it would be very difficult.

-mysqlblock: used to view the type of block in the data file

* * *

USEAGE: mysqlblock datafile-tmomomod

This small tool used in study and test database,not

Uesd on online database!

This tool is used to find how many blocks and types

In specified datafile,Exp:how many undo block in d

Ata file!

QQ:22389860

* * *

-t Only Total blocks types in ibdata!

-d Blocks types detail in ibdata!

* * *

USEAGE: mysqlblock datafile-tmomomod

-bcview: used to view the fixed byte output of a block

*

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!

-bctool: used to modify a byte of a block (16K is now supported)

*

This tool is uesed to check data ues binary format,no Big-Endian

Or Little-Endian diff,this tool is base one byte on byte to change

! block is 16k.if want change other block eg:8k! Please set blocks

0 and offset blocks*8192+offset!

Usage:./bctool yfile blocks offset yourdata (XX)!

Warings:backup file frist!

Editor QQ:22389860

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

*

Usage:./bctool file block offset yourdata (XX)!

Of course, bctool and bcview can modify and view any file, not just INNODB BLOCK.

This is the end of the article on "what to do if there are Space id in fsp header,but in the page header errors in MYSQL". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it out for more people to see.

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.