MySQL Bugs: #684: InnoDB and auto_increment inconsistancy

Bug #684	InnoDB and auto_increment inconsistancy
Submitted:	19 Jun 2003 12:32	Modified:	25 Jun 2003 1:17
Reporter:	Robin Johnson	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	4.0.13	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any

Description:
Presently auto_increment with InnoDB does not behave like any other table type. 
There is a page in the documentation about it: 
http://www.mysql.com/doc/en/InnoDB_auto-increment_column.html 
 
At the bottom of this page, there is a mention of a workaround: 
http://www.mysql.com/doc/en/InnoDB_restrictions.html 
(create a row and then delete it again). 
 
This allows one to increase the auto_increment value, but there is NO way to lower 
the value short of recreating the table fully from mysqldump output. 
 
Furthermore, the fix does NOT seem to replicate accross servers properly, and is 
NOT preserved in mysqldump output. 

How to repeat:
 

Suggested fix:
As a possible fix, the conceptual query: 
"SELECT MAX(auto-inc-column) FROM T" 
might become: 
"SELECT MAX(auto-inc-column,@auto-increment-base) FROM T"

Hi!

The command ALTER TABLE ... AUTO_INCREMENT=... will come to InnoDB in a version 4.1.x.

The method of inserting a dummy row and deleting it should work also in replication because MySQL writes to the binlog

SET INSERT_ID=...

before every INSERT. Thus, the auto-inc counter of the table in the slave actually has no effect on what value the auto-inc column gets in the insert in the slave.

Regards,

Heikki

I replicate databases manually as my sites run from dial and sync every 12 hours using mysqldump output.

on each side I do:
mysql --no-create-info --no-create-db --complete-insert PRIVATEDATA | sed -e 's/INSERT INTO/INSERT IGNORE INTO/g'

The major problem here is that no matter what I dump to an SQL file, the insert IDs are _not_ preserved on InnoDB.

Hi!

Sorry, I do not understand how you do manual replication. If you dump whole rows to a file, those rows contain also the value of the auto-inc column. See below.

Why does the internal auto-inc counter of the table make a difference? Are you doing two-way replication? Or are there other tables where you use the auto-inc column value as a foreign key?

Regards,

Heikki

heikki@hundin:~/mysql-4.0/client> mysql test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 4.0.14-debug-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show create table robin;
+-------+-----------------------------------------------------------------------
---------------------------+
| Table | Create Table
                           |
+-------+-----------------------------------------------------------------------
---------------------------+
| robin | CREATE TABLE `robin` (
  `a` int(11) NOT NULL auto_increment,
  PRIMARY KEY  (`a`)
) TYPE=InnoDB |
+-------+-----------------------------------------------------------------------
---------------------------+
1 row in set (0.00 sec)

mysql> select * from robin;
+---+
| a |
+---+
| 1 |
| 2 |
| 3 |
+---+
3 rows in set (0.00 sec)

mysql>

heikki@hundin:~/mysql-4.0/client> mysqldump --no-create-info --no-create-db --co
mplete-insert test robin
-- MySQL dump 9.08
--
-- Host: localhost    Database: test
---------------------------------------------------------
-- Server version       4.0.14-debug-log

--
-- Dumping data for table 'robin'
--

INSERT INTO robin (a) VALUES (1);
INSERT INTO robin (a) VALUES (2);
INSERT INTO robin (a) VALUES (3);

> Sorry, I do not understand how you do manual replication. 
See the script snippet below.

> If you dump whole rows to a file, those rows contain
> also the value of the auto-inc column. See below.
> Why does the internal auto-inc counter of the table make a difference?
> Are you doing two-way replication? 
> Or are there other tables where you use the auto-inc 
> column value as a foreign key?

All of the above actuallly. It's more than 2 way replication. 
The db layout is a number of tables, with the auto-inc column as the foreign key.
Each site submits it's dump firstly (having different insert ids is needed here so that records don't conflict) to the master server. The master server combines the data, and sends the combined set back to all of the clients.

The script I use for it is roughly this:
#!/bin/sh
D=`date +%Y%m%d%h%m%s`
B=`uname -n`.$D
DUMP=$B.submit
RESULT=$B.result
umask 077
mysql -q --complete-insert PRIVATEDATA | sed -e
's/INSERT INTO/INSERT IGNORE INTO/g' -e 's/CREATE TABLE/CREATE TABLE IF NOT EXISTS/g' | gzip -9c >/tmp/$DUMP
# A key is used here automatically
scp /tmp/$DUMP server-host:/submit/$DUMP
# security
rm /tmp/$DUMP
# wait for the server to process it
sleep 60s; 
# grab the answer
scp server-host:/result/$RESULT /tmp/$RESULT 
# process it
gunzip -c /tmp/$RESULT | mysql -u... -p... DB 
# cleanup
rm /tmp/$RESULT 

The server process just reads in the submitted file, and does a mysqldump to a new file for the client to fetch.

As such, each file passed over the network is a snapshot of the data at that moment in time.

The rest of my point is that running 'mysqldump' on a database should produce an accurate snapshot of the DB, including the current auto-insert ids.

Robin,

why not let each of the slaves use the dummy row trick to raise the auto-inc counter to a value suitable for that slave? You have to change the counter anyway to ensure auto-inc values in slaves do not overlap.

Regards,

Heikki

Admittedly, that is a solution for my setup. But anybody that does a mysqldump of a InnoDB database and expects it to preserve the state of the auto-inc id is in for a shock.