MySQL Internals: screens (or .frm files)

Inspired by http://blogs.sun.com/thava/entry/dump_mysql_frm_file_header I jumped into http://forge.mysql.com/wiki/MySQL_Internals_File_Formats and tried to write a decoder for the .frm files. Sadly the internals document is missing all the interesting parts.

So it was time to get the hands dirty and get into the code ... it got really dirty. But I found a little gem in there.

If you are interested take a look at open_binary_frm() or create_frm() in sql/table.cc or mysql_create_frm() in sql/unireg.cc. It has all the glory. You may have to wipe off the dust a bit has this code is (I bet) as old as MySQL is.

.frm-files are from a time when Monty wrote Unireg (the http://www.zhaophp.com/mysql/manual_Unireg.html says "written in 1979, rewritten in C in 1986"). Back then a CRT was green or amber. Well, the .frm files have their name that time: Form. They stored everything needed to bring up the form on the on the screen:

• Fields with types
• Field-Length
• row and column to position a field on the screen

In case the screen was "full" (80 columns, 22 rows) another screen was stored.

I bet you guess why I tell this story: They are still in there. You can still store the position of fields on a screen in your .frm files. Well, MySQL does that for you. Even in MySQL 5.1.

Take a look yourself:

$ ./src/mysql-myisam-dump --frm-file=.../mysql/user.frm

frm-Header
  frm_version           : 9
  db_type               : 9
  flags                 : 0
  key_info_offset       : 4096 -- absolute offset of the key-info-block
  key_info_length       : 42
  names_len             : 3
  names_count           : 1
  length                : 12288
...
  mysqld_version        : 50119
...
  forminfo_offset       : 8192 -- absolute offset of the form-info-block
  forminfo_len          : 2274 -- len of the form-info-block
Screens
  screens_len           : 777 -- length of the screens block
  screens_count         : 3
...
  [0] start_row         : 2
  [0] fields_on_screen  : 19
  [0] col_1             : 41
    [0] field_name      : 'Host'
    [0] row             : 4
    [1] field_name      : 'User'
    [1] row             : 5
    [2] field_name      : 'Password'
...
Colums
  col_count             : 39 -- number of fields on the table
  col_names_len         : 518 -- length of the col_names block
...
  [0] name              : 'Host'
  [0] field_len         : 180
  [0] rec_pos           : 1
  [0] field_type        : 254
  [0] charset           : 83
  [0] unireg_type       : 0
  [0] pack_flags        : 1
  [0] comment_len       : 0
  [0] col_values_ndx    : 0
...
Keys
  key_num               : 1
  key_parts             : 2 -- has to be >= than the keyparts of each key
  key_extra_length      : 10
  [0] name              : 'PRIMARY'
  [0] flags             : 2
  [0] key_length        : 228
  [0] key_parts         : 2
  [0] algorithm         : 0
  [0] block_size        : 0
...
Extra
  connect_string        : ''
  se_name               : 'MyISAM'
  comment               : 'Users and global privileges'

As the feature is still in there and is written whenever a table is created, what is it useful for ? How can we turn this liitle gem back into a shiny feature.

Don't be shy: MySQL 5.1 just skips the block when it is read back. Feel free to play with it.

(Sorry, for the text that was written there before. Thanks Mark for the heads-up).