Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
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
3  
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

3 Answers 3

up vote 5 down vote accepted

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.

share|improve this answer
    
Check this out percona.com/blog/2013/09/11/… –  Jonas Stensved Nov 19 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 at 21:55

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.

share|improve this answer
    
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 !!! –  RolandoMySQLDBA 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. –  RolandoMySQLDBA 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:

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.
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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