GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

Schema mismatch when loading innobackupex backup of Percona 5.6 server to another

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Schema mismatch when loading innobackupex backup of Percona 5.6 server to another

    I'm attempting to restore an individual innodb table from one Percona 5.6 server to another, though the source database was originally Percona 5.5.

    The command used to back up the original server was :
    innobackupex --socket=/mysql/other/mysql.sock --user=xx --password=xx --no-timestamp /mysql/backup/bacula/

    After the backup was complete, it was prepared with the following command:
    innobackupex --socket=/mysql/other/mysql.sock --apply-log --export /mysql/backup/bacula/
    I then copy a tables ibd, cfg, and exp files into the data dir, and change the owner to mysql.

    I then discard the tablespace. If I import the tablespace with the cfg file present, I get : Schema mismatch (Column a precise type mismatch.) on datetime column

    I can delete the cfg file and import the tablespace without the schema verification and it imports, but there is certainly something wrong with the data. The data types aren't the same so all data up until a datetime field are just fine, but from the datetime filed on the data is actually misaligned between the columns. MySQL changed how datatime columns are stored in MySQL 5.6, and the table was originally created on a MySQL 5.5 server. That being said, this table is truncated and reloaded every day.

    Any ideas on what I have to do to get the transportable tablespace working?

  • #2
    I ran into exactly the same problem. The issue is that the manner in which temporal types (TIME/DATETIME/TIMESTAMP) are stored can vary, due to the introduction of fractional second support ...

    http://mechanics.flite.com/blog/2014...-dot-6-format/

    The following article shows a query that can be used to discern any columns that are still using the old format.

    http://www.percona.com/blog/2014/05/...al-data-types/

    You can use pt-online-schema change to upgrade a given table at source, without incurring any downtime.

    Comment

    Working...
    X