Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2003
    Location
    Sinapore
    Posts
    187

    Unanswered: Problem with auto increment

    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
    Last edited by pearl2; 02-15-05 at 02:20.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the purpose of auto_increment columns is to provide a number that is unique

    it does this by incrementing

    it does not guarantee that previously used numbers won't be re-used, just that they will be unique

    it seems that both tables are doing this, even if not exactly in identical manner

    if you need the exact same number in both tables, you should copy the number from one to the other
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2003
    Location
    Sinapore
    Posts
    187
    Ah, I see.

    Great thanks, r937

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •