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.
phpmyadmin
Perhaps you could just select the phpMyAdmin Operations tab:
- In phpMyAdmin, click on the table you want to reset or change the AUTO_INCREMENT value
- Click on the Operations Tab
- In the Table Options box find the auto_increment field.
- Enter the new auto_increment starting value
- Click on the Go button for the Table Options box.
Since this one of the most frequently asked questions for phpmyadmin, you can learn more about this in this blog : http://trebleclick.blogspot.com/2009/01/mysql-set-auto-increment-in-phpmyadmin.html
Supplemental Info
For an empty table, another way to reset the auto_increment attribute is to run
TRUNCATE TABLE mydb.tablename;
Don't run this if you have data in it. If you want to hose the data, then be my guest.
In phpmyadmin, just click the SQL
tab, enter the command, and run it.
For a nonempty table, you may want to adjust the auto_increment attribute to the highest existing id in use in case higher entries were deleted.
First, optimize the table
OPTIMIZE TABLE mydb.mytable;
Next, locate the highest value for the auto_increment column (say it is id
)
SELECT MAX(id) maxid FROM mydb.mytable;
Suppose the answer returns 27. Goto the Operations tab and enter 28.
Best Answer
The problem is that you can not create AUTO_INCREMENT without a key so try something like this:
Hope this helps