Hi,
I'm seeing a rather strange behaviour with auto increment in two of the tables I've created. These tables have the following specs:
Code:
CREATE TABLE table_myisam (
id mediumint(8) unsigned NOT NULL auto_increment,
) TYPE=MyISAM;
CREATE TABLE table_innodb (
id mediumint(8) unsigned NOT NULL auto_increment,
) TYPE=InnoDB;
I've a perl script to interact with the database.The desired behaviour is as follows:
When I insert in entry into table_myisam, a corresponding entry is also inserted into table_innodb. These two entries have the same id's i.e. if the entry in table_myisam is inserted with an entry of id 28, table_innodb will also be inserted with an entry of the same id.
Similarly, when an entry is deleted from table_myisam, a corresponding entry is deleted from table_innodb.
After some time, both tables should look as follows:
table_myisam
id
2
5
10
11
table_innodb
id
2
5
10
11
However, on two occasions, an entry was deleted from table_myisam so that the two tables appeared as follows:
table_myisam
id
2
5
10
table_innodb
id
2
5
10
That is, entry 11 was deleted and everything was okay.
This was followed by a new insertion. What happened was that while table_myisam was inserted with the next value after 11, table_innodb was inserted with the deleted value 11:
table_myisam
id
2
5
10
12
table_innodb
id
2
5
10
11
What could have gone wrong? Isn't table_innodb supposed to be inserted with the auto increment value 12?
I'm at my wit's end and I hope someone can enlighten me.
Thanks in anticipation
dBforums
>
Problem with auto increment
02-15-05, 01:16
Linear Mode
