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".


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.

  • One promising answer: "If it's a slave server, the best solution really would be to move the database aside and install a new snapshot from the master." Unfortunately there's 20,000 tables across 25 databases, a mix of MyISAM and InnoDB, in production 24x7. It would take too long to shut all that down and do a fresh full replication before starting up replication again.
    – IcarusNM
    Nov 17 '11 at 6:23
  • 4
    I now have this 8-core machine on its knees in a pointless race to create and delete 164 gigs of data. The only alternative I'm hearing is to nuke everything on this slave and start over from scratch. All to effectively change one number in two files. Surely there's some InnoDB engineer out there with a pro tip. Has anyone ever opened ib_logfile0 in Emacs, found the magic number in hex, and just changed it?
    – IcarusNM
    Nov 17 '11 at 16:28
  • Here's a great article on a few ways to do it. Percona is definitely the authority on MySQL. percona.com/blog/2013/09/11/…
    – jbrahy
    May 13 '16 at 18:40

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.

 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.

  • Check this out percona.com/blog/2013/09/11/… Nov 19 '14 at 21:18
  • Thanks Jonas; that's interesting. I think I may stick with my method above. He shows using gdb against the running mysqld which I would probably never risk. But good information there too.
    – IcarusNM
    Nov 21 '14 at 21:55
  • For some strange reason, using MariaDB, I don't get the 'small number [space] big number' log sequence numbers — but just a 'big number', so sadly this method didn't work for me. Well, of course, the log gets updated, I just don't know when to stop! Nov 2 '16 at 10:56

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 stop --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

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.

  • In OPTION 02 Step 05 you say to start the master. When was it stopped? Rsync on a live master is gutsy. I'm impressed. And luckily I'm using innodb_file_per_table. But eventually you have to bite the bullet and stop the master long enough for one final rsync to run before starting replication. That's a possibility I may resort to, but this is a very active DBMS. And I'll look at XtraBackup for my info.
    – IcarusNM
    Nov 17 '11 at 15:40
  • @IcarusNM : Ah, typo. I corrected it. Thank You !!! Nov 17 '11 at 16:02
  • OPTION 02 could probably still use some work. E.g. You should do step 2 before step 1. You probably want a RESET SLAVE in there somewhere. Typo in step 4. And you say "first binary log" in step 5 but you really mean "only" or "last" binary log. And you should use mysqlbinlog to ascertain log positions, not file size. And all this still won't work unless you stop the master at some point. Basing a log position/time on when an rsync finished is risky at best.
    – IcarusNM
    Nov 17 '11 at 16:41
  • I have been doing OPTION 2 for the past 4 years with DB Hosting clients who have data in the TeraByte Range. It works every time against a running server. The only real mistake you could make is on the slave. That mistake would be in whether replication was setup properly or not. Additionally, RESET SLAVE is useful, especially if you have piled up many GB of relay logs. After the rsync process and reestablishment of replication, please remember the CHANGE MASTER TO command will also wipe out relay logs for you as well. Nov 17 '11 at 18:09
  • mmm ... strange. i set up my slave using xtrabackup (as always) and still got these log errors (percona mysql 5.5.x) ... seems there went something wrong on this slave and i have to do it again.
    – harald
    Mar 14 '12 at 9:57

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:


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:


or that


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.


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.
  • Under MariaDB, you can quickly alter all tables using USE INFORMATION_SCHEMA; SELECT CONCAT("ALTER TABLE `", TABLE_SCHEMA,"`.`", TABLE_NAME, "` REBUILD PARTITION ALL;") AS MySQLCMD AS MySQLCMD FROM TABLES; (source: dba.stackexchange.com/questions/35073/…) and tee it to a file to be executed as a series of commands. Nov 2 '16 at 11:29

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.