A Critical Warning If You Are Using InnoDB Hot Backup

Feb 12, 2009 / By Sheeri Cabral

Tags:

If you are using InnoDB Hot Backup and a recent version of mysqld (at least 5.0.67 or higher, including 5.1.30, though it may be later versions), your backup will run fine and output OK! at the end, as it should.

Except for one thing.

The binary log file and position do not appear in their rightful place. Here’s a snippet of the output from the backup:

innobackup: MySQL binlog position: filename 'Warning', position (Code 1287):
'TYPE=storage_engine' is deprecated; use 'ENGINE=storage_engine' instead
090210 03:55:04  innobackup: innobackup completed OK!

That’s pretty misleading — looks like the backup completed OK, but it did not show us the binary log position. What about the ibbackup_binlog_info file?

[mysql@db3:~] more ibbackup_binlog_info 
Warning (Code 1287): 'TYPE=storage_engine' is deprecated; use 'ENGINE=storage_engine' instead
[mysql@db3:~]

Yikes! What happened to the binary log position and file information?

Have no fear, it’s actually in another file.


I was extremely happy to find the information in mysql-stdout:

[mysql@db3:~] more mysql-stdout 
innobackup hello 3
innobackup hello 3
innobackup hello 4
innobackup hello 4
innobackup hello 5
innobackup hello 5
Note (Code 1051): Unknown table 'ibbackup_binlog_marker'
innobackup hello 6
innobackup hello 6
Warning (Code 1287): 'TYPE=storage_engine' is deprecated; use 'ENGINE=storage_engine'
instead
innobackup hello 7
innobackup hello 7
innobackup hello 8
innobackup hello 8
innobackup hello 9
innobackup hello 9
innobackup hello 10
innobackup hello 10
innobackup hello 11
innobackup hello 11
File    Position        Binlog_Do_DB    Binlog_Ignore_DB
db2-binlog.001980       495985099               
innobackup hello 12
innobackup hello 12
innobackup hello 13
innobackup hello 13
innobackup hello 14
innobackup hello 14

I filed a bug report here:
http://bugs.mysql.com/bug.php?id=42812

The bug is an artifact of the eventual deprecation of TYPE in favor of ENGINE. There’s no reason for ibbackup to continue to use TYPE; while the product works well, it is much more expensive than it warrants.

(While I searched for this bug before creating it, I would like to say that it’s completely possible that this is “well-known” and “well-documented”, and I did not find it. Or, that this client is using an old version of ibbackup. However, there may be many folks in this same position, and this is a very subtle bug — the backup looks fine, it’s a full backup, it will restore properly, etc. but if you want to start replicating the backed up server, you will have a hard time finding the binary log information.

3 Responses to “A Critical Warning If You Are Using InnoDB Hot Backup”

  • Keith Murphy says:

    Thanks Sheeri. That is a great catch!

  • Ken Jacobs says:

    Sheeri,

    Thanks for fully describing the issue here and explaining that the backups were performed correctly and where people can find the binary log file and position information.

    As you say, the problem you experienced is due to the de-support of the keyword TYPE in the innobackup script. Instead of

    CREATE TABLE ibbackup_binlog_marker(a INT) TYPE=INNODB;

    it should use

    CREATE TABLE ibbackup_binlog_marker(a INT) ENGINE=INNODB;

    The technical issues are addressed in the bug report you posted (Bug #42812). We thank you for filing the bug report and posting this note, as well as your comment that the “product works well”.

    However, I’d like to briefly address a couple of related things.

    1. MySQL does not distribute or support InnoDB Hot Backup. You won’t find a “category” for it in the bug database.

    2. Please use the InnoDB Forums at http://forums.innodb.com/ to interact with the InnoDB team, especially about products like InnoDB Hot Backup and the InnoDB Plugin, which MySQL is not (yet?) distributing or supporting. [Besides, since we know you ;-) you could also contact us directly by email.]

    3. I think it worth mentioning that the real “bug” here was caused by a rather unnecessary change in the syntax MySQL supports. You said, “There’s no reason for ibbackup to continue to use TYPE”. It could equally well be said, “There was no reason to deprecate this syntax.” In fact, Mark Callaghan said it very well in a post to the MySQL internals mail list back in November 2007: http://lists.mysql.com/internals/35180. This sort of gratuitous change causes hassle and expense for everyone. Upward compatibility is a really good idea.

    4. Lastly, I don’t understand your comment that InnoDB Hot Backup “is much more expensive than it warrants”. InnoDB Hot Backup is the only product available today that can backup MySQL/InnoDB databases (including MyISAM tables) with little to no disruption of on-going operations. It is feature-rich, reliable and fast. It is selling well as a commercially-licensed product (and obviously the buyers don’t agree that it is “much more expensive” than it warrants. ;-)

    Thanks for listening!

  • Ken Jacobs says:

    I’ve closed the bug, and we have posted the corrected Perl script on our website: http://www.innodb.com/downloads/.

    Thanks again for reporting this …

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>