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.