Any better way out of MySQL InnoDB log "in the future"?

Question

I've got this InnoDB error in MySQL 5.0. Mysqld was stopped cleanly, but I managed to lose ib_logfile0 & ib_logfile1 afterward. Now after a clean startup, InnoDB has done its "crash recovery". I went through the innodb_force_recovery=4 business, repaired a hung MyISAM table, and now replication is ready to go, apart from this. Big numbers commified:

111116 15:49:36  InnoDB: Error: page 393457 log sequence number 111 561,760,232
InnoDB: is in the future! Current system log sequence number 70 3,946,969,851.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files. See
InnoDB: http://dev.mysql.com/doc/refman/5.0/en/forcing-recovery.html
InnoDB: for more information.

This is on a slave server. The above error spews by the hundreds. I found this answer: "insert and delete > 64 GB worth of data, so that the log sequence number becomes inflated big enough".

http://forums.mysql.com/read.php?22,50163,50163#msg-50163

That magic number of 64GB comes from 4GB*16 where that guy's innodb log "major number" needed to increase from 0 to 15. Mine's going from 70 to 111 = 164 GB. This will take 5 days. I'll keep working on speeding up my script, and running it in parallel to speed this up. In the meantime, I'm hoping someone else has a better answer. This is silly.

Answer 1

This was a pretty rare situation. I hope to never end up there again, with an InnoDB "log sequence number is in the future!" error. Because of my particular details, rebuilding/restoring my server's data was a last resort. Some cheats in helping that were good ideas, but in the end, I decided to just keep improving my Perl script to play this silly game and churn through as many gigs/hour as I could. What the heck, it's a good system stress test.

Remember: the goal is to increase a single counter ("log sequence number") which is stored somewhere in the headers of ib_logfile0 and ib_logfile1. This is to fake out InnoDB so it will ignore an apparent time warp and get on with life. But no one knows how to edit that number. Or if they do know, no one's talking.

Here's my final product. YMMV, but using mysql's REPEAT function to generate the data internally is highly efficient.

 #!/usr/bin/perl
 use DBI;
 $table = shift || die;
 $dbh = DBI->connect("DBI:mysql:junk:host=localhost", "user", "pass"); #Edit "junk" (DB name), user, and pass to suit.
 $dbh->do("DROP TABLE IF EXISTS $table");
 $dbh->do("CREATE TABLE $table (str TEXT) ENGINE=INNODB");
 $sth = $dbh->prepare("INSERT INTO $table (str) VALUES (REPEAT(?,1000000))");
 foreach (1..50) {
    $sth->execute('0123456789');   # 10 MB
 }
 $dbh->do("DELETE FROM $table");

My suggested recipe:

1. Create a 'junk' database
2. Save the above perl script as junk.pl.
3. Run junk.pl data1, and junk.pl data2, and junk.pl data3, etc. all at once, for as many CPU cores as your database server has, to start. Open multiple shells and wrap each run in a Bash loop: while true; do date; junk.pl dataX; done.

Watch your LSN grow, perhaps in another loop:

 silly# echo "SHOW INNODB STATUS \G" | mysql -p'xxxxxx' | grep '^Log seq'
 Log sequence number 124 3871092821
 silly# echo "SHOW INNODB STATUS \G" | mysql -p'xxxxxx' | grep '^Log seq'
 Log sequence number 124 4209892586
 silly# echo "SHOW INNODB STATUS \G" | mysql -p'xxxxxx' | grep '^Log seq'
 Log sequence number 125 85212387

The big number is an unsigned 32-bit INT which will wrap at 4GB, increasing the smaller number every time. In this case above it just rolled from 124 to 125. Your goal is hidden in the mysqld.log that sent you Googling for this ridiculous solution in the first place. Once you cross that finish line, that's it! Blow the horns! Release the confetti!

Sidebar: This uncovered an interesting bug in mysqld 5.0 w/REPEAT: if you go to 20 MB, it flips some internal counter and rolls over to ~ 96 KB. No warning or error anywhere. I wasn't about to waste time tracking that down. 10 MB works great. If you do hit some other limit, it may complain. I have various innodb buffers increased from default. Season to taste. As always, watch mysqld.log in one window.

Answer 2

You have three(3) options:

OPTION 01 : Perform rsync of Master to Slave (Downtime on the Master)

• Step 01 : Run reset master; on the master (Zaps Binary Logs)
• Step 02 : service mysql stop on the master
• Step 03 : service mysql stop on the slave
• Step 04 : rsync /var/lib/mysql from the master to the slave
• Step 05 : service mysql start on the master
• Step 06 : Use first binary log on the master as the log to start replication from. Use the filesize of that log as the position to start replication from
• Step 07 : service mysql start --skip-slave-start on the slave
• Step 08 : Run CHANGE MASTER TO command to setup replication from the log and position ascertained from Step 06
• Step 09 : Run start slave; on the slave and let replication catch up

OPTION 02 : Perform rsync of Master to Slave (Minimal Downtime on the Master)

• Step 01 : Run reset master; on the master (Zaps Binary Logs)
• Step 02 : service mysql stop on the slave
• Step 03 : rsync /var/lib/mysql from the master to the slave
• Step 04 : Repeat Step 03 until two consecutive rsyncs take the same amount of time
• Step 05 : service mysql stop on the master
• Step 06 : rsync /var/lib/mysql from the master to the slave
• Step 07 : service mysql start on the master
• Step 08 : Use first binary log on the master as the log to start replication from. Use the filesize of that log as the position to start replication from
• Step 09 : service mysql stop --skip-slave-start on the slave
• Step 10 : Run CHANGE MASTER TO command to setup replication from the log and position ascertained from Step 08
• Step 11 : Run start slave; on the slave and let replication catch up

OPTION 03 : Use XtraBackup

This software tool will not only make a non-obtrusive copy of a running master, but will also create the corresponding ib_logfiles for you. You would have to setup replication

I have posted to the StackExchange before on this subject

• How can I move a database from one server to another?
• MySQL Community Edition: Can I replicate my entire server with another server in one go?
• How do you re-sync the Master MySQL DB with Slave DB changes if the Master goes offline?
• https://serverfault.com/questions/257394/create-a-mysql-slave-from-another-slave-but-point-it-at-the-master/257426#257426

I have done these things many times for my employer's web hosting company. One client had 3.7TB to move and it took about 16 hours. 64GB is very small in comparison.

Answer 3

I found out there is perhaps a cooler way to solve this problem working on partitioned tables. I needed to drop partitions from some years back, and had to add some for 2014. Almost all partitions report this error, so also old ones. Very nasty crash.

So while DROPPING old and using REORGANISE of the MAXVALUE partition(the last one), It will create new files who are ok, so I get less and less warnings. In the mean time, it helps incrementing the log sequence counter, so I don't need to insert bogus data. I have this happening on a master server btw...

So this:

ALTER TABLE Events DROP PARTITION p1530 , p1535 , p1540 , p1545 , 
p1550, p1555 , p1560 , p1565 , p1570 , p1575 , p1580 , p1585 , p1590 , 
p1595 , p1600 , p1605 , p1610 , p1615 , p1620 , p1625 , p1630 , p1635 , 
p1640 , p1645 , p1650 , p1655 , p1660 , p1665 , p1670 , p1675 , p1680 , 
p1685 , p1690 , p1695 , p1700 , p1705 , p1710 , p1715 , p1720 , p1725 , 
p1730 , p1735 , p1740 , p1745 , p1750 , p1755 , p1760 , p1765 , p1770 , 
p1775 , p1780 , p1785 , p1790 , p1795 , p1800 , p1805 , p1810 , p1815 , 
p1820 , p1825 , p1830 , p1835 , p1840;

And this:

ALTER table Events REORGANIZE PARTITION p3000 INTO (
PARTITION p3500 VALUES LESS THAN (TO_DAYS('2013-01-01')),
PARTITION p3510 VALUES LESS THAN (TO_DAYS('2013-01-04')),
PARTITION p3520 VALUES LESS THAN (TO_DAYS('2013-01-07')),
PARTITION p3530 VALUES LESS THAN (TO_DAYS('2013-01-10'))
...
PARTITION p4740 VALUES LESS THAN (TO_DAYS('2014-01-08')),
PARTITION p9000 VALUES LESS THAN MAXVALUE)

That will effectively drop each partition in the change and recreate it with a temp copy of the content of what was in there. You can do this per table if you want, my application allows for that to happen, so no need to worry about synced backups etc.

Now for the rest of the table, since I haven't touched all partitions in the process some will be left with the log sequence warning, for those ones that are broken but and covered by this reorganize action I will probably run this:

ALTER TABLE Events REBUILD PARTITION p0, p1;

or that

ALTER TABLE Events OPTIMIZE PARTITION p0, p1;

So, that got me thinking, You could do this with plain vanilla tables, temporary add partitions by hash and later remove it (or keep them, I can strongly recommend partitions).

I'm using mariadb however, not mysql (so XtraDB)

Perhaps this helps someone. I'm still running it, so far so good. Changing ENGINE seems to do the job as well, so I bring it back/forth between MyIsam and them back to InnoDB.

It's fairly logical, if you change ENGINE, the table disappears from innodb, so it will not be a problem anymore.

ALTER TABLE Events ENGINE=MyISAM;
ALTER TABLE Events ENGINE=InnoDB;

it seems to work here. I can confirm a few things on partitioned tables:

• ALTER TABLE xyz ENGINE=InnoDB is very slow, to Aria (mariadb) twice as fast, but in general a slow way to increment the log sequence counter
• ALTER TABLE xyz REBUILD PARTITION ALL is the fastest way to 'fix' the tables and help increment the counter
• ALTER TABLE xyz ANALYZE PARTITION ALL is slow compaired to the former and doesn't rewrite partitions that check out to be ok. REBUILD assures a rewrite to a temp table schema.

I used the last ones on several tables. The warnings happen when it's trying to open the files and there is one for every partition definition it opens with counter issues. Almost rolled over the counter today for the last tables. I think once it's all processed one needs to flush the binary logs.

update: I can conclude a few things now I managed to sort this problem out.

• My crash was caused by reorganising partitions on a table in the Aria format (MariaDB).
• (for me) doing an rebuild of the partitions worked the best and fastest to get the sequence counter up. Altering the engine is slow and you need to do it twice to affect innodb. altering to innoDB is quite slow vs. to MyIsam or Aria.
• I upgraded to MariaDB 5.3 and not to 5.5 (was:5.2) and it works fine. I think there are way too many problems with aria, partitions in 5.5 (and confirmed bugs) to be using that combination.
• There really should be a better way to reset the log sequence counter.