Not the answer you need?
Register and ask your own question!

Restoring tables with innobackupex resulting in bad data

crosmacrosma EntrantInactive User Role Beginner
I have backups created with
innobackupex --host=$DB_HOST --user=$DB_USER --password=$DB_PASS --include='db1,db2,db3' --use-memory=10G --rsync --slave-info $BACKUP_DIR

I am attempting to restore a few specific tables from our latest 5 monthly backups using the instructions found here but most of the tables have bad data. All of the rows expected appear to be present, but the data is corrupt after a particular column in every row. One table in particular is heavily damaged in 4 of 5 backups.

The table structure used to restore the tables was exported via "mysqldump --no-data" at the time of the backups, so should be correct.

Here's an example of the bad data:
mysql> SELECT * FROM `table1-2014-10-09` WHERE col1= 1 \G
*************************** 1. row ***************************
col0: 1
col1: 8
col2: Agoodstringvalue1
col3: enval2
col4: 2008-01-19 14:28:32
col5: 2010-07-25 01:43:04
col6: 10
col7: 282624
col8: 1
col9: Agoodstringvalue2
col10: 20
col11: Agoodstringvalue3
col12: 0000-00-09 05:05:08
col13: 2206-12-10 24:00:00
col14: 8640-03-26 01:50:09
col15: 2717-07-00 01:09:14
col16: 4.939e-29
col17: 1.79366e-43
col18: 0
col19: 0
col20: -128
col21: 31301
col22: -128
col23: 16474
col24: -989815040
col25: -128
col26: 8257568
col27: 109059715
col28: -16637
col29: ;?    €   € 1405718411
col30: 32
col31: 32
col32: -96
col33: 32
col34: -96
col35: 2.815312038823503e-250
col36: 1.062147479e-314
col37: 0
col38: 0

Every row in the table is like this, data starting at col12 is bad. Note the lack of year on col12 and the years 1000's in the future in 13, 14 and 15. Cols 16 and 17 are floats that should be between 0 and 10,000. col29 is a char value that should be a small string in english. (All columns 12+ are incorrect, these are just examples)

The table's structure
CREATE TABLE IF NOT EXISTS `table1-2014-10-09` (
  `col0` mediumint(8) unsigned NOT NULL,
  `col1` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `col2` char(20) NOT NULL DEFAULT '',
  `col3` enum('enval1','enval2','enval3','enval4') NOT NULL,
  `col4` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `col5` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `col6` tinyint(2) NOT NULL DEFAULT '0',
  `col7` int(10) unsigned NOT NULL DEFAULT '0',
  `col8` tinyint(1) NOT NULL DEFAULT '0',
  `col9` char(30) NOT NULL,
  `col10` tinyint(2) unsigned NOT NULL DEFAULT '0',
  `col11` char(30) NOT NULL DEFAULT 'Human',
  `col12` datetime NOT NULL,
  `col13` datetime DEFAULT '0000-00-00 00:00:00',
  `col14` datetime DEFAULT '0000-00-00 00:00:00',
  `col15` datetime DEFAULT '0000-00-00 00:00:00',
  `col16` float NOT NULL DEFAULT '0',
  `col17` float unsigned NOT NULL DEFAULT '0',
  `col18` smallint(5) unsigned NOT NULL DEFAULT '0',
  `col19` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `col20` tinyint(1) NOT NULL DEFAULT '0',
  `col21` smallint(5) unsigned NOT NULL,
  `col22` tinyint(1) NOT NULL DEFAULT '0',
  `col23` smallint(5) NOT NULL,
  `col24` int(10) NOT NULL DEFAULT '0',
  `col25` tinyint(1) NOT NULL DEFAULT '0',
  `col26` int(10) unsigned NOT NULL DEFAULT '0',
  `col27` int(10) NOT NULL,
  `col28` smallint(3) NOT NULL DEFAULT '-75',
  `col29` char(24) NOT NULL,
  `col30` tinyint(2) unsigned NOT NULL DEFAULT '0',
  `col31` tinyint(2) unsigned NOT NULL DEFAULT '0',
  `col32` tinyint(1) NOT NULL DEFAULT '0',
  `col33` tinyint(1) unsigned NOT NULL,
  `col34` tinyint(1) NOT NULL,
  `col35` double unsigned NOT NULL DEFAULT '0',
  `col36` double NOT NULL DEFAULT '0',
  `col37` tinyint(2) unsigned NOT NULL DEFAULT '0',
  `col38` int(10) unsigned NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=875492 DEFAULT CHARSET=latin1;


Of the 5 copies of this table I have restored, the first 3 are just like this example. The fourth backup seems to be OK, no corrupt data. The fifth is similar but the corruption starts at col16.

I'm not even sure what to search for for this issue. When I search for anything about corrupt mysql data, all I find is to do with being unable to even load the data.

If any more info is needed, please ask.

Any suggestions or help would be appreciated.

Comments

  • niljoshiniljoshi MySQL Sage Inactive User Role Beginner
    Hi,

    Can you tell us which MySQL/Percona Server version you are using because above process is only possible for 5.6 version.
    Also is this possible for you to check that whole backup is corrupted or this problem is only with selected tables which you are trying to import?
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.