MySQL Internals Manual  /  File Formats  /  MySQL .frm File Format

11.1 MySQL .frm File Format

Regardless of the storage engine you choose, every MySQL table you create is represented on disk by a .frm file that describes the table's format (that is, the table definition). The file bears the same name as the table, with an .frm extension. The .frm format is the same on all platforms, but in the description of the .frm format that follows, the examples come from tables created under the Linux operating system.

First, let's create an example table, using the mysql client:

mysql> CREATE TABLE table1 (column1 CHAR(5)) ENGINE=MYISAM COMMENT '*';
Query OK, 0 rows affected (0.00 sec)

The .frm file associated with table1 can be found in the directory that represents the database (or schema) to which the table belongs. The datadir system variable contains the name of this directory:

mysql> SHOW VARIABLES LIKE 'datadir';
 +---------------+-----------------------+
 | Variable_name | Value                 |
 +---------------+-----------------------+
 | datadir       | /usr/local/mysql/var/ |
 +---------------+-----------------------+
 1 row in set (0.00 sec)

The DATABASE() function contains the name of the relevant database:

mysql> SELECT DATABASE();
 +------------+
 | DATABASE() |
 +------------+
 | ff         |
 +------------+
 1 row in set (0.00 sec)

Since MySQL stores .frm files in datadir/database_name, it's a simple matter to locate the corresponding .frm file for table1. For example, within a Linux shell:

shell> su root
shell> cd /usr/local/mysql/var/ff
shell> ls table1.*
  table1.frm  table1.MYD  table1.MYI
shell> ls -l table1.*
-rw-rw----  1 root root 8566 2006-09-22 11:22 table1.frm
-rw-rw----  1 root root    0 2006-09-22 11:22 table1.MYD
-rw-rw----  1 root root 1024 2006-09-22 11:22 table1.MYI

The .MYD (data) and .MYI (index) files are not our concern here; they are described at Section 21.1, “MyISAM Record Structure”. To understand the .frm format, let's look at table1.frm using a hexadecimal-dump utility:

shell> hexdump -v -C table1.frm
  00000000  fe 01 09 09 03 00 00 10  01 00 00 30 00 00 10 00  |...........0....|
  00000010  06 00 00 00 00 00 00 00  00 00 00 02 08 00 08 00  |................|
  00000020  00 05 00 00 00 00 08 00  00 00 00 00 00 00 00 10  |................|
  00000030  00 00 00 c0 c3 00 00 10  00 00 00 00 00 00 00 00  |................|
  00000040  2f 2f 00 00 20 00 00 00  00 00 00 00 00 00 00 00  |//.. ...........|
  ...                                                         | (many 0s)      |
  00001000  00 00 00 00 02 00 ff 00  00 00 00 00 00 00 00 00  |................|
  00001010  ff 20 20 20 20 20 00 00  06 00 4d 79 49 53 41 4d  |.     ....MyISAM|
  ...                                                         | (many 0s)      |
  00002000  6c 01 00 10 00 00 00 00  00 00 00 00 00 00 00 00  |l...............|
  00002010  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
  00002020  00 00 00 00 00 00 00 00  00 00 00 00 00 00 01 2a  |...............*|
  ...
  00002100  01 00 01 00 3b 00 05 00  00 00 06 00 0a 00 00 00  |....;...........|
  00002110  00 00 00 00 00 00 50 00  16 00 01 00 00 00 00 00  |......P.........|
  00002120  3b 00 02 01 02 14 29 20  20 20 20 20 20 20 20 20  |;.....)         |
  00002130  20 20 20 20 20 20 20 20  20 20 20 20 20 20 20 20  |                |
  00002140  20 20 20 20 20 20 20 20  20 20 20 20 20 20 20 00  |               .|
  00002150  04 00 08 63 6f 6c 75 6d  6e 31 00 04 08 05 05 00  |...column1......|
  00002160  02 00 00 00 80 00 00 00  fe 08 00 00 ff 63 6f 6c  |.............col|
  00002170  75 6d 6e 31 ff 00                                 |umn1..|
  00002176

The details just shown might change, especially since there is a transition underway from an old (binary) format to a new (text based) .frm format. You can confirm that the details are correct by comparing this description with the statements in sql/table.cc, create_frm(). The following tables explain the meaning of each byte in the hexadecimal dump shown in the preceding example:

  • Offset: The byte position in the file.

  • Length: The number of bytes.

  • Value: What's in the given byte position for the given length (remember that storage is low byte first, so 0010 means 0x1000, not 0x0010!).

  • Explanation: A brief explanation of the contents.

Table 11.1 .frm File Header Section

Offset Length Value Explanation

0000

1

fe

Always

0001

1

01

Always

0002

1

09

FRM_VER (which is in include/mysql_version.h) +3 +test(create_info->varchar)

0003

1

09

See enum legacy_db_type in sql/handler.h. For example, 09 is DB_TYPE_MYISAM, but 14 if MyISAM with partitioning.

0004

1

03

 ??

0005

1

00

Always

0006

2

0010

IO_SIZE

0008

2

0100

 ??

000a

4

00300000

Length, based on key_length + rec_length + create_info->extra_size

000e

2

1000

"tmp_key_length", based on key_length

0010

2

0600

rec_length

0012

4

00000000

create_info->max_rows

0016

4

00000000

create_info->min_rows

001b

1

02

Always (means use long pack-fields)

001c

2

0800

key_info_length

001e

2

0800

create_info->table_options also known as db_create_options? one possible option is HA_LONG_BLOB_PTR

0020

1

00

Always

0021

1

05

Always (means version 5 frm file)

0022

4

00000000

create_info->avg_row_length

0026

1

08

create_info->default_table_charset

0027

1

00

Always

0028

1

00

create_info->row_type

0029

6

00..00

Always (formerly used for RAID support)

002f

4

10000000

key_length

0033

4

c0c30000

MYSQL_VERSION_ID from include/mysql_version.h

0037

4

10000000

create_info->extra_size

003b

2

0000

Reserved for extra_rec_buf_length

003d

1

00

Reserved for default_part_db_type, but 09 if MyISAM with partitioning

003e

2

0000

create_info->key_block_size


Table 11.2 .frm File Key Information Section

Offset Length Value Explanation

1000

1

00

Always 00 when there are no keys (that is, indexes)

...

 ??

 ??

 ??

101a

6

"MyISAM"

Name of engine. If partitioning, the partition clauses are here


Table 11.3 .frm File Comment Section

Offset Length Value Explanation

202e

1

01

Length of comment

202f

40

"*"

The string in the COMMENT table option


Table 11.4 .frm File Column Information Section

Offset Length Value Explanation

2100

2

01

Always

2102

2

0100

share->fields (number of columns)

2104

2

3b00

pos (length of all screens). Goes up if column-name length increases. Doesn't go up if add comment.

2106

2

0500

Based on number of bytes in row.

210c

2

0500

n_length. Goes up if row length increases.

210e

2

0000

interval_count. Number of different ENUM/SET columns.

2110

2

0000

interval_parts. Number of different strings in ENUM/SET columns.

2112

2

0000

int_length

211a

2

0100

share->null_fields. Number of nullable columns.

211c

2

0000

com_length

2152

1

08

Length of column-name including '\0' termination

2153

3

"column1\0"

column-name

215b

1

04

 ??

215c

1

03

 ??

215d

1

05

Number of bytes in column

215e

1

05

Number of bytes in column

215f

4

00020000

 ??

2163

1

00

Flags for zerofill, unsigned, etc.

2164

1

80

Additional flags, and scale if decimal/numeric

2168

1

fe

Data type (fe=char, 02=smallint, 03=int, etc.) see enum field_types in include/mysql_com.h

2169

1

08

Character set or geometry type

(later)

 ??

 ??

Column names again, defaults, ENUM/SET strings, column comments ... at end of row. not shown.


The .frm file for a partitioned table contains partition information, in clear text, in addition to the usual table definition details. Let's create a partitioned table and do a hexadecimal dump of its .frm file:

mysql> CREATE TABLE table2 (column1 INT) ENGINE=MYISAM COMMENT '*'
       PARTITION BY HASH(column1) PARTITIONS 2;
Query OK, 0 rows affected (0.00 sec)

The hexadecimal dump from table2 is shown here:

00000000  fe 01 09 14 03 00 00 10  01 00 00 30 00 00 10 00  |...........0....|
00000010  05 00 00 00 00 00 00 00  00 00 00 02 08 00 08 00  |................|
00000020  00 05 00 00 00 00 08 00  00 00 00 00 00 00 00 10  |................|
00000030  00 00 00 c0 c3 00 00 3d  00 00 00 00 00 09 00 00  |.......=........|
00000040  2f 2f 00 00 20 00 00 00  00 00 00 00 00 00 00 00  |//.. ...........|
00000050  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
...
00001000  00 00 00 00 02 00 ff 00  00 00 00 00 00 00 00 00  |................|
00001010  ff 00 00 00 00 00 00 09  00 70 61 72 74 69 74 69  |.........partiti|
00001020  6f 6e 2a 00 00 00 20 50  41 52 54 49 54 49 4f 4e  |on*... PARTITION|
00001030  20 42 59 20 48 41 53 48  20 28 63 6f 6c 75 6d 6e  | BY HASH (column|
00001040  31 29 20 50 41 52 54 49  54 49 4f 4e 53 20 32 20  |1) PARTITIONS 2 |
00001050  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
...
00002000  76 01 00 10 00 00 00 00  00 00 00 00 00 00 00 00  |v...............|
00002010  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
00002020  00 00 00 00 00 00 00 00  00 00 00 00 00 00 01 2a  |...............*|
00002030  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
...
00002100  01 00 01 00 3b 00 0b 00  00 00 05 00 0a 00 00 00  |....;...........|
00002110  00 00 00 00 00 00 50 00  16 00 01 00 00 00 00 00  |......P.........|
00002120  3b 00 02 01 02 14 29 20  20 20 20 20 20 20 20 20  |;.....)         |
00002130  20 20 20 20 20 20 20 20  20 20 20 20 20 20 20 20  |                |
00002140  20 20 20 20 20 20 20 20  20 20 20 20 20 20 20 00  |               .|
00002150  04 00 08 63 6f 6c 75 6d  6e 31 00 04 08 0b 0b 00  |...column1......|
00002160  02 00 00 1b 80 00 00 00  03 08 00 00 ff 63 6f 6c  |.............col|
00002170  75 6d 6e 31 ff 00                                 |umn1..|
00002176

In the example output, notice that position 00001010 and following contains the clear text of the CREATE TABLE ... PARTITION clause and not just the MYISAM engine information, as in table1, which shows the .frm of a nonpartitioned table.

Finally, CREATE VIEW also causes creation of a .frm file, but a view .frm bears no resemblance to a base table .frm; it's purely textual. Here's an example of a .frm for a view made with:

mysql> CREATE VIEW v AS SELECT 5;
Query OK, 0 rows affected (0.00 sec)

Just looking at the text will tell you what it's about:

00000000  54 59 50 45 3d 56 49 45  57 0a 71 75 65 72 79 3d  |TYPE=VIEW.query=|
00000010  73 65 6c 65 63 74 20 35  20 41 53 20 60 35 60 0a  |select 5 AS `5`.|
00000020  6d 64 35 3d 38 64 39 65  32 62 62 66 64 35 33 35  |md5=8d9e2bbfd535|
00000030  66 35 37 39 64 34 61 39  34 39 62 39 65 62 37 64  |f579d4a949b9eb7d|
00000040  32 33 34 39 0a 75 70 64  61 74 61 62 6c 65 3d 30  |2349.updatable=0|
00000050  0a 61 6c 67 6f 72 69 74  68 6d 3d 30 0a 64 65 66  |.algorithm=0.def|
00000060  69 6e 65 72 5f 75 73 65  72 3d 72 6f 6f 74 0a 64  |iner_user=root.d|
00000070  65 66 69 6e 65 72 5f 68  6f 73 74 3d 6c 6f 63 61  |efiner_host=loca|
00000080  6c 68 6f 73 74 0a 73 75  69 64 3d 32 0a 77 69 74  |lhost.suid=2.wit|
00000090  68 5f 63 68 65 63 6b 5f  6f 70 74 69 6f 6e 3d 30  |h_check_option=0|
000000a0  0a 72 65 76 69 73 69 6f  6e 3d 31 0a 74 69 6d 65  |.revision=1.time|
000000b0  73 74 61 6d 70 3d 32 30  30 36 2d 30 39 2d 32 32  |stamp=2006-09-22|
000000c0  20 31 32 3a 31 34 3a 34  38 0a 63 72 65 61 74 65  | 12:14:48.create|
000000d0  2d 76 65 72 73 69 6f 6e  3d 31 0a 73 6f 75 72 63  |-version=1.sourc|
000000e0  65 3d 73 65 6c 65 63 74  20 35 0a                 |e=select 5.|

User Comments
Sign Up Login You must be logged in to post a comment.