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

In this article Xiaobian for you to introduce in detail "mysql frm file error repair", the content is detailed, the steps are clear, the details are handled properly, I hope this "mysql frm file error report how to repair" article can help you solve doubts, the following follow the editor's ideas slowly in-depth, together to learn new knowledge.

In mysql, frm means "table definition" and is a file that describes the structure of a data table. The frm file is used to store the metadata information of each data table, including the definition of the table structure, etc. The frm file has nothing to do with the database storage engine, that is, the data table of any storage engine must have a frm file, named as "data table name .frm".

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

In mysql, frm means "table definition" and is a file that describes the structure of a data table.

When any data table is established in MYSQL, there is a .frm file corresponding to the database directory of its data catalog. The .frm file is used to save the meta information of each data table, including the definition of the table structure.

The .frm file has nothing to do with the database storage engine, that is, the data table of any storage engine must have a .frm file, named as the data table name .frm, such as user.frm. The .frm file can be used to recover the table structure when the database crashes.

Generally speaking, the frm file will not be corrupted, but do not give up hope if the frm file is corrupted under special circumstances, such as the following error:

150821 16:31:27 [ERROR] / usr/local/mysql51/libexec/mysqld: Incorrect information in file:'. / usr/local/mysql51/libexec/mysqld Test1.frm'

When repairing the MyISAM and InnoDB tables, the MySQL service invokes the frm file first, so we can only repair the frm file for later data recovery.

MySQL creates frm files through the create_frm () function of sql/table.cc, and the created frm files are binary files that need to be parsed into hexadecimal by hexdump.

The code defined by the create_frm () function to the header of the frm file

/ * Create a .frm file * / File create_frm (THD * thd, const char * name, const char * db, const char * table, uint reclength, uchar * fileinfo, HA_CREATE_INFO * create_info, uint keys, KEY * key_info) {register File file; ulong length; uchar fill [IO _ SIZE]; int create_flags= O_RDWR | Orange TRUNC; ulong key_comment_total_bytes= 0; uint I If (create_info- > options & HA_LEX_CREATE_TMP_TABLE) create_flags | = O_EXCL | Fix this when we have new .frm files; Current limit is 4G rows (QQ) * / if (create_info- > max_rows > UINT_MAX32) create_info- > max_rows= UINT_MAX32; if (create_info- > min_rows > UINT_MAX32) create_info- > min_rows= UINT_MAX32 If ((file= mysql_file_create (key_file_frm, name, CREATE_MODE, create_flags, MYF (0)) > = 0) {uint key_length, tmp_key_length, tmp, csid; bzero ((char*) fileinfo,64); / * header * / fileinfo [0] = (uchar) 254; fileinfo [1] = 1; fileinfo [2] = FRM_VER+3+ test (create_info- > varchar) Fileinfo [3] = (uchar) ha_legacy_type (ha_checktype (thd,ha_legacy_type (create_info- > db_type), 0)); fileinfo [4] = 1; int2store (fileinfo+6,IO_SIZE) / * Next block starts here * / Keep in sync with pack_keys () in unireg.cc For each key: 8 bytes for the key header 9 bytes for each key-part (MAX_REF_PARTS) NAME_LEN bytes for the name 1 byte for the NAMES_SEP_CHAR (before the name) For all keys: 6 bytes for the header 1 byte for the NAMES_SEP_CHAR (after the last name) 9 extra bytes (padding for safety? Alignment?) * / for (I = 0; I

< keys; i++) { DBUG_ASSERT(test(key_info[i].flags & HA_USES_COMMENT) == (key_info[i].comment.length >

If (key_ info.flags & HA_USES_COMMENT) key_comment_total_bytes + = 2 + key_ info.comment.length;} key_length= keys * (8 + MAX_REF_PARTS * 9 + NAME_LEN + 1) + 16 + key_comment_total_bytes Length= next_io_size ((ulong) (IO_SIZE+key_length+reclength+ create_info- > extra_size)); int4store (fileinfo+10,length); tmp_key_length= (key_length

< 0xffff) ? key_length : 0xffff; int2store(fileinfo+14,tmp_key_length); int2store(fileinfo+16,reclength); int4store(fileinfo+18,create_info->

Max_rows); int4store (fileinfo+22,create_info- > min_rows); / * fileinfo [26] is set in mysql_create_frm () / fileinfo [27] = 2; / / Use long pack-fields / * fileinfo [28 & 29] is set to key_info_length in mysql_create_frm () * / create_info- > table_options | = HA_OPTION_LONG_BLOB_PTR; / / Use portable blob pointers int2store (fileinfo+30,create_info- > table_options) Fileinfo [32] = 0; / / No filename anymore fileinfo [33] = 5; / / Mark for 5.0frm file int4store (fileinfo+34,create_info- > avg_row_length); csid= (create_info- > default_table_charset? Create_info- > default_table_charset- > number: 0), fileinfo [38] = (uchar) csid; / * In future versions, we will store in fileinfo [39] the values of the TRANSACTIONAL and PAGE_CHECKSUM clauses of CREATE TABLE. * / fileinfo [39] = 0; fileinfo [40] = (uchar) create_info- > row_type; / * Next few bytes where for RAID support * / fileinfo [41] = (uchar) (csid > > 8); fileinfo [42] = 0; fileinfo [43] = 0; fileinfo [44] = 0; fileinfo [45] = 0; fileinfo [46] = 0; int4store (fileinfo+47, key_length); tmp= MYSQL_VERSION_ID / / Store to avoid warning from int4store int4store (fileinfo+51, tmp); int4store (fileinfo+55, create_info- > extra_size); / * 59-60 is reserved for extra_rec_buf_length, 61 for default_part_db_type * / int2store (fileinfo+62, create_info- > key_block_size); bzero (fill,IO_SIZE); for (; length > IO_SIZE) Length-= IO_SIZE) {if (mysql_file_write (file, fill, IO_SIZE, MYF (MY_WME | MY_NABP)) {(void) mysql_file_close (file, MYF (0)); (void) mysql_file_delete (key_file_frm, name, MYF (0)); return (- 1) } else {if (my_errno = = ENOENT) my_error (ER_BAD_DB_ERROR,MYF (0), db); else my_error (ER_CANT_CREATE_TABLE,MYF (0), table,my_errno);} return (file);} / * create_frm * /

The open_binary_frm () function pairs the code defined on the frm index section

For (iTun0; I)

< keys ; i++, keyinfo++) { keyinfo->

Table= 0; / / Updated in open_frm if (new_frm_ver > = 3) {keyinfo- > flags= (uint) uint2korr (strpos) ^ HA_NOSAME; keyinfo- > key_length= (uint) uint2korr (strpos+2); keyinfo- > key_parts= (uint) strpos [4]; keyinfo- > algorithm= (enum ha_key_alg) strpos [5]; keyinfo- > block_size= uint2korr (strpos+6); strpos+=8 } else {keyinfo- > flags= ((uint) strpos [0]) ^ HA_NOSAME; keyinfo- > key_length= (uint) uint2korr (strpos+1); keyinfo- > key_parts= (uint) strpos [3]; keyinfo- > algorithm= HA_KEY_ALG_UNDEF; strpos+=4;} keyinfo- > key_part= key_part; keyinfo- > rec_per_key= rec_per_key; for Key_part++) {* rec_per_key++=0; key_part- > fieldnr= (uint16) (uint2korr (strpos) & FIELD_NR_MASK); key_part- > offset= (uint) uint2korr (strpos+2)-1; key_part- > key_type= (uint) uint2korr (strpos+5); / / key_part- > field= (Field*) 0 / / Will be fixed later if (new_frm_ver > = 1) {key_part- > key_part_flag= * (strpos+4); key_part- > length= (uint) uint2korr (strpos+7); strpos+=9;} else {key_part- > length= * (strpos+4); key_part- > key_part_flag=0; if (key_part- > length > 128) {key_part- > length&=127 / * purecov: inspected * / key_part- > key_part_flag=HA_REVERSE_SORT; / * purecov: inspected * /} strpos+=7;} key_part- > store_length=key_part- > length;}} keynames= (char*) key_part; strpos+= (strmov (keynames, (char*) strpos)-keynames) + 1; / / reading index comments for (keyinfo= share- > key_info, iTuno; I

< keys; i++, keyinfo++) { if (keyinfo->

Flags & HA_USES_COMMENT) {keyinfo- > comment.length= uint2korr (strpos); keyinfo- > comment.str= strmake_root (& share- > mem_root, (char*) strpos+2, keyinfo- > comment.length); strpos+= 2 + keyinfo- > comment.length;} DBUG_ASSERT (test (keyinfo- > flags & HA_USES_COMMENT) = (keyinfo- > comment.length > 0);}

Hexdump is a binary file viewer under Linux that converts binaries to ASCII, 10, hexadecimal, or octal for viewing.

The hexdump parameter-C displays each byte in hexadecimal, a line of 16 bytes, showing the text content stored in hexadecimal-b each byte in octal, a line of 16 bytes, and a line beginning with an offset value in hexadecimal; 0000000 177 105 114 106 002 001 000000 000000 000000 000Mustc each byte is shown in ASCII characters, the rest as above 0000000 177 E L F 002 001 001\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0 explain only the specified length byte units: default decimal, hexadecimal for 0x or 0X, and octal for 0. The default is bytes, b is 512 bytes, k is 1024 bytes, m is 1048576 bytes-d double byte decimal display-o double byte octal display-v minus the middle display "*" character-x double byte hexadecimal display-e format parameter

Instance version and table character set:

Reference: https://www.percona.com/blog/2015/07/09/obtain-mysql-version-frm-file/

The instance version of 0x033 statement hexdump-s 0x33-n 2-v-d table.frm [root@test1 ~] # hexdump-s 0x33-n 2-v-d / data/3308/test/test1.frm0000033 501530000035 is 5.1.53, because 5.1 0x026 21=utf808=latin11c=GBK 5.5 and 5.6 have different field type definitions, so it is important to determine the instance version of a good table. Field types are defined in the following table character set 0x026 21=utf808=latin11c=GBK statement hexdump-s 0x26-N1 table.frm.

Frm column properties:

, column serial number (initial column serial number is 4), field length, shaping length, field length, latin1 character set character type length, GBK character set character type varchar length * 2 dint varchar (30) is equivalent to 60 bytes long, converted to hexadecimal is 3cmeme utf8 character set character type varchar length * 3meme varchar (30) is equivalent to 90 bytes long Changing to hexadecimal is 5a, Flags for zerofill, unsigned, etc. (int 1b), Additional flags,and scale if decimal/numeric (DEFAULT NULL 80 not NULL 40 fear default 'VALUE' 00), code definition unireg_type,AUTO_INCREMENT of, code definition interval_nr, field type, character set, memo length, memo length

Field type (note that the field type in version 5.6 is different, which will affect data recovery):

Data type for v5.1&v5.5 (v5.6) fe=charfa=mediumtextf6=decimalfc=textof=varchar01=tinyint02=smallint03=int04=float05=real07=timestamp (v5.6 11=timestamp) 08=bigint09=mediumint10=bitob=time (v5.6 13=time) oc=datetime (v5.6 12=datetime) 0d=year0e=date

Indexes contained in the table:

The section after the offset of 0x1000 is the frm index, which is easy to find after opening it with hexdump-C. there are several indexes 0x1001: all indexes contain several fields whose index names are plaintext. The specific index structure is shown in the example.

Table:

CREATE TABLE `test3` (`a` int (11) NOT NULL, `b` varchar (10) DEFAULT NULL, `c` int (11) NOT NULL, PRIMARY KEY (`a`), UNIQUE KEY `uniq_ 1` (`b`, `c`), KEY `test1` (`c`, `b`), KEY `idx_ 2` (`c`) ENGINE=InnoDB DEFAULT CHARSET=utf8

Hexadecimal file open:

[root@test1] # hexdump-C / data/3308/test/test3.frm 00000000 fe 01 0a 0c 03 01 0000 30 0000 74 05 | .0. T. | 00000010 28 00000000000000000000000000000000000000000000000000000000000000000021000000000074 |. .t | # Table character set 00000030 05 0000 E9 c3 0000 10 000000 000000 0000 |. The standard red is the sample version number 00000040 2f 0000 20000000000000000000000 | /. | | 00000050 000000 000000 000000 00000000 |. | * 00001000 0060000 1D 000000040000000000080 |. | 0000101002 00001b 40 0000068 00 0200000002 | @. H. ". | 00001020 80 06 0000 00 80 1e 00 03 80 25 0000 1b 40 04 |.%. @. | 00001030 00 69 00 22 00 02 0000 00 03 80 25 0000 1b 40 | .i. ".%. @ | 00001040 04 00 02 80 06 0000 00 80 1e 0001 00 04 0001 | 00001050000 00 03 8025000 1b 40 04 00 ff 50 52 49 |. %... @... PRI | 00001060 4d 41 52 59 ff 75 6e 69 71 5f 31 ff 69 64 78 5f 31 ff 69 64 78 5f | MARY.uniq_1.idx_ | 00001070 31 ff 69 64 78 5f 32 ff 0000 0000 0000 | 1.idx_2. | 000010800000000000000000000000000000 ff 00000000000000000000000000 |. | | 00001580 0000 0000 0000 |. | 00001590000 0000 0000 0000 06 00 |. | 000015a0 49 6e 6f 44 42 0000 0000 0000 | InnoDB. | 000015b0 0000 0000 0000 0000 0000 0000 00 |. | * 00002000 9a 01 00 10 0000 0000 0000 0000000 |. | 00002010 0000 0000 0000 0000000 0000 0000 |. | 00002100 01 00 03 003f 00 34 0000 00 28 08 0000 00 |. 4. (. | 00002110 0000 0000 0000 50 00 16 00 01 0000 0 00 | .p. | 00002120 3f 00 04 03 0214 29 20 20 20 | | 00002150 04 0002 61 00 05 0002 62 00 06 0002 63 00 04 |. A. B.c.c. | 00002160 02 0b 0b 0002 0000 1b 400 0000 00 03 3f 0000 |. @.?. | 00002170 05 051e 00 06 0000 00 800000 00 0f 21 00 |. | 0000218000 06 00b 0b 0b 00 250000 1b 400 0000 | 00 03 3f |.%. @? | 00002190 0000 ff 61 ff 62 ff 63 ff 00 | a.b.c.. |

By the color distinction above, the yellow part circled is the index attribute, and the red, blue and green tricolor below is the three-column attribute.

Column attribute structure:

Red part: field serial number (starting with 4, 4, 5, 6 is the first, second and third of the field)

Blue: field length

Brown part: whether it is empty

Green section: field typ

Yellow: character set

Index attribute structure:

Index header:

Light blue part: index statistics

Pink section: how many columns are there in the index

Index body:

Brown part: whether the index is unique

Red part: the serial number of the column in the table

Green section: properties of the corresponding column in the table

Field default value:

The default value of the field is not saved in the field properties, but is saved in the section describing the table engine in which the default value of int type is saved as hexadecimal, and the default value of char type is saved as hexadecimal text. You can see directly through hexdump-C that if there is no index segment, the default value is after 0x1011, if there is an index segment. The location is extended, such as table CREATE TABLE `test1` (`a` int (11) NOT NULL DEFAULT '2010mm, `b` varchar (10) NOT NULL DEFAULT' 2011', `c` int (11) default '30mm, `d`varchar (10) NOT NULL DEFAULT' Yes') engine=innodb default charset=utf8 * 00001000 0000 0000 02 00 ff 0000 0000 0000 00 |. | 00001010 fe da 07 0000 04 32 30 31 0000 0000 0000 | .2011. | 00001020000 0000 0000 0000 00 03 59 65 730000 0000 |. .Yes.... | 00001040 0000 0000 0000 000 |. | 00001050 0000 0000 0000 00 06 00 49 6e 6f 44 | .InnoD | 00001060 42 0000 0000 0000 00 0 00 00 00 |. | * column a:da 07 00 00column baster 04 32 30 31 00 00 00 00column dvv 03 59 65 73 00 00 00 need to note that the default value of the char field is based on the field length and character set related As shown in table varchar (10) above, utf8 is 3bit, which is 30 hexadecimal lengths. Read here, this "mysql frm document error report how to fix" article has been introduced, want to master the knowledge of this article also need to practice and use in order to understand, if you want to know more about the article, welcome to follow the industry information channel.

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.