A hosted server is running "maintenance" each weekend. I am not privy to the details.
In a database on this server there is a MyISAM table. This table never holds more than 1000 rows and usually much less. It is MyISAM so that the auto increment does not reset (and with so few rows it really doesn't matter). Rows are regluarly deleted from this table and moved to an archive table (1M rows).
The problem is lately the auto increment has "rolled back" slightly after each maintenance.
Is there any easy way to verify the auto increment of the insert table by reading the max id from both the insert and the archive table?
I'd rather not verify before each insert unless that is the only solution.
Here are the basic table layouts:
CREATE TABLE x
(
xid int(10) unsigned NOT NULL AUTO_INCREMENT, //snip
PRIMARY KEY (xid)
) ENGINE=MyISAM AUTO_INCREMENT=124 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE xhistory
(
xid int(10) unsigned NOT NULL DEFAULT '0', //snip
PRIMARY KEY (xid)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Far from perfect workaround: (this was somewhat urgent, I had to manually update over 100 rows)
select xid from xhistory where x=?
Check if just inserted row in x exists in history. If it does:
select greatest(max(x.xid),max(xhistory.xid)) as newval from x,xhistory
Find a new id.
INSERT INTO x SELECT * FROM x AS iv WHERE iv.xid=? ON DUPLICATE KEY UPDATE xid=?
And update our row with this id.
Best Answer
You can check the AUTO_INCREMENT value from INFORMATION_SCHEMA like this:
you should also see the
AUTO_INCREMENT
withSUGGESTION
If the table has barely 1000 rows, you could manually compress it and force to have MAX(id) each time. Suppose the table looks something like this:
Try doing
OPTIMIZE TABLE
manually as follows:InnoDB or MyISAM tables with no non-unique keys
MyISAM tables with non-unique keys
This should preserve all id's and assign AUTO_INCREMENT appropriately.
It should work just fine with MySQL Replication.
CAVEAT
If the ids are different for any reason between Master and Slave, blame MySQL (I mean blame Oracle) : http://dev.mysql.com/doc/refman/5.5/en/replication-features-auto-increment.html
If the aforementioned suggestion does not rectify this, there is only one thing left to do and it is guaranteed to work. What is it?
Run this on the Master Only:
With a table of 1000 rows, this should
Give it a Try !!!
UPDATE 2013-02-11 19:30 EDT
Let's assume that
x
andxhistory
have identical layouts. Let's also add some columns:Perhaps you could just use the REPLACE command. It mechanically operates as either an INSERT or UPDATE via DELETE and INSERT.
Previously existing rows get deleted from
xhistory
and then inserted fromx
intoxhistory
. New rows are simply inserted.