0

I have inherited a large number of backups (MySQL 5.5) created with innobackupex as partial backups. In order to restore one of them, I have to do it table by table; as part of this process, I need to extract the table definitions from the .frm files, among other because the databases have evolved over time, and the current tables are not necessarily the same shape as originally.

This turns out to be a problem - I am using mysqlfrm to extract the table definitions from the .frm files, but for the partitioned ones I get:

$ mysqlfrm --diagnostic user_transaction.frm
# WARNING: Cannot generate character set or collation names without the --server option.
# CAUTION: The diagnostic mode is a best-effort parse of the .frm file. As such, it may not identify all of the components of the table correctly. This is especially true for damaged files. It will also not read the default values for the columns and the resulting statement may not be syntactically correct.
# Reading .frm file for user_transaction.frm:
# The .frm file is a TABLE.
ERROR: Cannot read column data.

Is there a way to get this to work?

Edit 1

I discovered the debug option:

root@db3:/mnt/mysql/gameiom# mysqlfrm --diagnostic -vvv game_action.frm
# WARNING: Cannot generate character set or collation names without the --server option.
# CAUTION: The diagnostic mode is a best-effort parse of the .frm file. As such, it may not identify all of the components of the table correctly. This is especially true for damaged files. It will also not read the default values for the columns and the resulting statement may not be syntactically correct.
# Reading .frm file for game_action.frm:
# The .frm file is a TABLE.
# Skipping to header at : 2
# General Data from .frm file:
{'IO_SIZE': 4096,
 'MYSQL_VERSION_ID': 50562,
 'avg_row_length': 0,
 'charset_low': 0,
 'create_options': 9,
 'db_create_pack': 2,
 'default_charset': 33,
 'default_part_eng': 12,
 'extra_size': 5038,
 'frm_file_ver': 5,
 'frm_version': 10,
 'key_block_size': 0,
 'key_info_length': 215,
 'key_length': 2086,
 'legacy_db_type': 'PARTITION',
 'length': 16384,
 'max_rows': 0,
 'min_rows': 0,
 'rec_length': 130,
 'row_type': 0,
 'table_charset': 33,
 'tmp_key_length': 2086}
# Skipping to key data at : 1000
# Reading key part 0.
# Reading key part 1.
# Reading key part 0.
# Reading key part 0.
# Reading key part 0.
# Reading key part 0.
# Reading key part 0.
# Reading key part 1.
# Reading key part 2.
# Index (key) Data from .frm file:
{'key_names': ['PRIMARY',
               'GA_IX01',
               'GA_IX02',
               'GA_IX03',
               'game_action_created_timestamp',
               'ga_id_cur_tstmp'],
 'keys': [{'algorithm': 0,
           'block_size': 0,
           'comment': '',
           'flags': 0,
           'key_length': 12,
           'key_parts': [{'field_num': 1,
                          'key_part_flag': 0,
                          'key_type': 16896,
                          'length': 8,
                          'offset': 1},
                         {'field_num': 8,
                          'key_part_flag': 31,
                          'key_type': 15872,
                          'length': 4,
                          'offset': 126}],
           'num_parts': 2},
          {'algorithm': 0,
           'block_size': 0,
           'comment': '',
           'flags': 1,
           'key_length': 4,
           'key_parts': [{'field_num': 2,
                          'key_part_flag': 64,
                          'key_type': 6912,
                          'length': 4,
                          'offset': 9}],
           'num_parts': 1},
          {'algorithm': 0,
           'block_size': 0,
           'comment': '',
           'flags': 1,
           'key_length': 4,
           'key_parts': [{'field_num': 3,
                          'key_part_flag': 64,
                          'key_type': 6912,
                          'length': 4,
                          'offset': 13}],
           'num_parts': 1},
          {'algorithm': 0,
           'block_size': 0,
           'comment': '',
           'flags': 65,
           'key_length': 8,
           'key_parts': [{'field_num': 4,
                          'key_part_flag': 128,
                          'key_type': 16896,
                          'length': 8,
                          'offset': 17}],
           'num_parts': 1},
          {'algorithm': 0,
           'block_size': 0,
           'comment': '',
           'flags': 1,
           'key_length': 4,
           'key_parts': [{'field_num': 8,
                          'key_part_flag': 31,
                          'key_type': 15872,
                          'length': 4,
                          'offset': 126}],
           'num_parts': 1},
          {'algorithm': 0,
           'block_size': 0,
           'comment': '',
           'flags': 41,
           'key_length': 53,
           'key_parts': [{'field_num': 2,
                          'key_part_flag': 64,
                          'key_type': 6912,
                          'length': 4,
                          'offset': 9},
                         {'field_num': 7,
                          'key_part_flag': 64,
                          'key_type': 0,
                          'length': 45,
                          'offset': 80},
                         {'field_num': 8,
                          'key_part_flag': 31,
                          'key_type': 15872,
                          'length': 4,
                          'offset': 126}],
           'num_parts': 3}],
 'num_key_parts': (9,),
 'num_keys': 6}
# Skipping to default data at : 1827
# Skipping to keys at : 18aa
# Engine string: INNODB
# Partition string:  PARTITION BY RANGE ( UNIX_TIMESTAMP(created_timestamp)) (PARTITION pMIN VALUES LESS THAN (1388534400) ENGINE = InnoDB,  PARTITION p2014_01 VALUES LESS THAN (1391212800) ENGINE = InnoDB,  PARTITION p2014_02 VALUES LESS THAN (1393632000) ENGINE = InnoDB,  PARTITION p2014_03 VALUES LESS THAN (1396310400) ENGINE = InnoDB,  PARTITION p2014_04 VALUES LESS THAN (1398902400) ENGINE = InnoDB,  PARTITION p2014_05 VALUES LESS THAN (1401580800) ENGINE = InnoDB,  PARTITION p2014_06 VALUES LESS THAN (1404172800) ENGINE = InnoDB,  PARTITION p2014_07 VALUES LESS THAN (1406851200) ENGINE = InnoDB,  PARTITION p2014_08 VALUES LESS THAN (1409529600) ENGINE = InnoDB,  PARTITION p2014_09 VALUES LESS THAN (1412121600) ENGINE = InnoDB,  PARTITION p2014_10 VALUES LESS THAN (1414800000) ENGINE = InnoDB,  PARTITION p2014_11 VALUES LESS THAN (1417392000) ENGINE = InnoDB,  PARTITION p2014_12 VALUES LESS THAN (1420070400) ENGINE = InnoDB,  PARTITION p2015_01 VALUES LESS THAN (1422748800) ENGINE = InnoDB,  PARTITION p2015_02 VALUES LESS THAN (1425168000) ENGINE = InnoDB,  PARTITION p2015_03 VALUES LESS THAN (1427846400) ENGINE = InnoDB,  PARTITION p2015_04 VALUES LESS THAN (1430438400) ENGINE = InnoDB,  PARTITION p2015_05 VALUES LESS THAN (1433116800) ENGINE = InnoDB,  PARTITION p2015_06 VALUES LESS THAN (1435708800) ENGINE = InnoDB,  PARTITION p2015_07 VALUES LESS THAN (1438387200) ENGINE = InnoDB,  PARTITION p2015_08 VALUES LESS THAN (1441065600) ENGINE = InnoDB,  PARTITION p2015_09 VALUES LESS THAN (1443657600) ENGINE = InnoDB,  PARTITION p2015_10 VALUES LESS THAN (1446336000) ENGINE = InnoDB,  PARTITION p2015_11 VALUES LESS THAN (1448928000) ENGINE = InnoDB,  PARTITION p2015_12 VALUES LESS THAN (1451606400) ENGINE = InnoDB,  PARTITION p2016_01 VALUES LESS THAN (1454284800) ENGINE = InnoDB,  PARTITION p2016_02 VALUES LESS THAN (1456790400) ENGINE = InnoDB,  PARTITION p2016_03 VALUES LESS THAN (1459468800) ENGINE = InnoDB,  PARTITION p2016_04 VALUES LESS THAN (1462060800) ENGINE = InnoDB,  PARTITION p2016_05 VALUES LESS THAN (1464739200) ENGINE = InnoDB,  PARTITION p2016_06 VALUES LESS THAN (1467331200) ENGINE = InnoDB,  PARTITION p2016_07 VALUES LESS THAN (1470009600) ENGINE = InnoDB,  PARTITION p2016_08 VALUES LESS THAN (1472688000) ENGINE = InnoDB,  PARTITION p2016_09 VALUES LESS THAN (1475280000) ENGINE = InnoDB,  PARTITION p2016_10 VALUES LESS THAN (1477958400) ENGINE = InnoDB,  PARTITION p2016_11 VALUES LESS THAN (1480550400) ENGINE = InnoDB,  PARTITION p2016_12 VALUES LESS THAN (1483228800) ENGINE = InnoDB,  PARTITION p2017_01 VALUES LESS THAN (1485907200) ENGINE = InnoDB,  PARTITION p2017_02 VALUES LESS THAN (1488326400) ENGINE = InnoDB,  PARTITION p2017_03 VALUES LESS THAN (1491004800) ENGINE = InnoDB,  PARTITION p2017_04 VALUES LESS THAN (1493596800) ENGINE = InnoDB,  PARTITION p2017_05 VALUES LESS THAN (1496275200) ENGINE = InnoDB,  PARTITION p2017_06 VALUES LESS THAN (1498867200) ENGINE = InnoDB,  PARTITION p2017_07 VALUES LESS THAN (1501545600) ENGINE = InnoDB,  PARTITION p2017_08 VALUES LESS THAN (1504224000) ENGINE = InnoDB,  PARTITION p2017_09 VALUES LESS THAN (1506816000) ENGINE = InnoDB,  PARTITION p2017_10 VALUES LESS THAN (1509494400) ENGINE = InnoDB,  PARTITION p2017_11 VALUES LESS THAN (1512086400) ENGINE = InnoDB,  PARTITION p2017_12 VALUES LESS THAN (1514764800) ENGINE = InnoDB,  PARTITION p2018_01 VALUES LESS THAN (1517443200) ENGINE = InnoDB,  PARTITION p2018_02 VALUES LESS THAN (1519862400) ENGINE = InnoDB,  PARTITION p2018_03 VALUES LESS THAN (1522540800) ENGINE = InnoDB,  PARTITION p2018_04 VALUES LESS THAN (1525132800) ENGINE = InnoDB,  PARTITION p2018_05 VALUES LESS THAN (1527811200) ENGINE = InnoDB,  PARTITION p2018_06 VALUES LESS THAN (1530403200) ENGINE = InnoDB,  PARTITION p2018_07 VALUES LESS THAN (1533081600) ENGINE = InnoDB,  PARTITION p2018_08 VALUES LESS THAN (1535760000) ENGINE = InnoDB,  PARTITION p2018_09 VALUES LESS THAN (1538352000) ENGINE = InnoDB,  PARTITION p2018_10 VALUES LESS THAN (1541030400) ENGINE = InnoDB,  PARTITION p2018_11 VALUES LESS THAN (1543622400) ENGINE = InnoDB,  PARTITION p2018_12 VALUES LESS THAN (1546300800) ENGINE = InnoDB,  PARTITION p2019_01 VALUES LESS THAN (1548979200) ENGINE = InnoDB,  PARTITION p2019_02 VALUES LESS THAN (1551398400) ENGINE = InnoDB,  PARTITION p2019_03 VALUES LESS THAN (1554076800) ENGINE = InnoDB,  PARTITION p2019_04 VALUES LESS THAN (1556668800) ENGINE = InnoDB,  PARTITION p2019_05 VALUES LESS THAN (1559347200) ENGINE = InnoDB,  PARTITION p2019_06 VALUES LESS THAN (1561939200) ENGINE = InnoDB,  PARTITION p2019_07 VALUES LESS THAN (1564617600) ENGINE = InnoDB,  PARTITION p2019_08 VALUES LESS THAN (1567296000) ENGINE = InnoDB,  PARTITION p2019_09 VALUES LESS THAN (1569888000) ENGINE = InnoDB,  PARTITION p2019_10 VALUES LESS THAN (1572566400) ENGINE = InnoDB,  PARTITION p2019_11 VALUES LESS THAN (1575158400) ENGINE = InnoDB,  PARTITION p2019_12 VALUES LESS THAN (1577836800) ENGINE = InnoDB,  PARTITION pMAX VALUES LESS THAN MAXVALUE ENGINE = InnoDB)
# Skipping to column data at : 2102
{'com_length': 20297,
 'int_length': 21586,
 'interval_count': 8202,
 'interval_parts': 16720,
 'n_length': 11330,
 'null_fields': 24374,
 'num_cols': 18720,
 'pos': 28270,
 'unknown': 17519}
# Fields per screen = 69
EXCEPTION: unpack requires a string argument of length 1
ERROR: Cannot read column data.

This is what I seem to get on all partitioned tables. Is there a problem with the setup of mysql? But it runs fine as it is.

Edit 2

Mysteriouser and mysteriouser - with a hex editor, I can see the names of the columns at 0x3153:

...
00003100   01 00 08 00  9F 00 BF 00  00 00 82 00  59 00 00 00  ............Y...
00003110   00 00 00 00  7F 00 50 00  16 00 01 00  00 00 00 00  ......P.........
00003120   9F 00 09 08  02 14 29 20  20 20 20 20  20 20 20 20  ......)
00003130   20 20 20 20  20 20 20 20  20 20 20 20  20 20 20 20
00003140   20 20 20 20  20 20 20 20  20 20 20 20  20 20 20 00                 .
00003150   04 00 0F 67  61 6D 65 5F  61 63 74 69  6F 6E 5F 69  ...game_action_i
00003160   64 00 05 00  08 67 61 6D  65 5F 69 64  00 06 00 08  d....game_id....
00003170   75 73 65 72  5F 69 64 00  07 00 11 67  61 6D 65 5F  user_id....game_
00003180   69 6E 73 74  61 6E 63 65  5F 69 64 00  08 00 05 74  instance_id....t
00003190   79 70 65 00  09 00 07 61  6D 6F 75 6E  74 00 0A 00  ype....amount...
---  game_action.frm       --0x3153/0x32A0-------------------------------------

So, mysqlfrm looks for the column data in the wrong place, it seems.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.