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.

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.

share|improve this question
    
Please post SHOW CREATE TABLE of both tables. –  RolandoMySQLDBA Feb 11 '13 at 20:17
    
You said you have an archive table. Does that table use the MyISAM storage engine or the ARCHIVE storage engine ??? –  RolandoMySQLDBA Feb 11 '13 at 21:05
    
(using main post for better code formatting) –  Svein Tjønndal Feb 11 '13 at 23:50
    
Do x and xhistory have identical layouts ? –  RolandoMySQLDBA Feb 12 '13 at 0:02
    
x and xhistory are identical except xhistory does not have auto increment on xid –  Svein Tjønndal Feb 12 '13 at 0:19

1 Answer 1

You can check the AUTO_INCREMENT value from INFORMATION_SCHEMA like this:

SELECT AUTO_INCREMENT FROM information_schema.tables
WHERE table_schema='mydb' AND table_name='x';

you should also see the AUTO_INCREMENT with

SHOW CREATE TABLE mydb.x\G

SUGGESTION

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:

USE mydb
CREATE TABLE x
(
    id INT NOT NULL AUTO_INCREMENT,
    ...
    PRIMARY KEY (id)
);

Try doing OPTIMIZE TABLE manually as follows:

InnoDB or MyISAM tables with no non-unique keys

USE mydb
CREATE TABLE x_new LIKE x;
INSERT INTO x_new SELECT * FROM x ORDER BY id;
ALTER TABLE x RENAME x_old;
ALTER TABLE x_new RENAME x;
DROP TABLE x_old;

MyISAM tables with non-unique keys

USE mydb
CREATE TABLE x_new LIKE x;
ALTER TABLE x_new DISABLE KEYS;
INSERT INTO x_new SELECT * FROM x ORDER BY id;
ALTER TABLE x_new ENABLE KEYS;
ALTER TABLE x RENAME x_old;
ALTER TABLE x_new RENAME x;
DROP TABLE x_old;

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:

mysqldump -uroot -p... --triggers mydb x > mydb_x.sql
mysql -uroot -p... -Dmydb < mydb_x.sql

With a table of 1000 rows, this should

  • go pretty quickly
  • replicate and produce an exact copy on the slave
  • restore auto_increment behavior to Master and Slave

Give it a Try !!!

UPDATE 2013-02-11 19:30 EDT

Let's assume that x and xhistory have identical layouts. Let's also add some columns:

CREATE TABLE x
(
    xid int(10) unsigned NOT NULL AUTO_INCREMENT,
    col1 ... ,
    col2 ... ,
    col3 ... ,
    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',
    col1 ... ,
    col2 ... ,
    col3 ... ,
    PRIMARY KEY (xid)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Perhaps you could just use the REPLACE command. It mechanically operates as either an INSERT or UPDATE via DELETE and INSERT.

REPLACE INTO xhistory SELECT * FROM x;
TRUNCATE TABLE x;

Previously existing rows get deleted from xhistory and then inserted from x into xhistory. New rows are simply inserted.

share|improve this answer
    
This works but we need an automatic way of achieving this. Otherwise I'll be verifying multiple DBs manually each weekend. –  Svein Tjønndal Feb 12 '13 at 0:04
    
@Svein - hope you heared of scripting and crond..!! –  Mannoj Jun 12 '13 at 12:19

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.