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