List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:January 6 2003 1:48pm
Subject:Re: Autonum broken
View as plain text  
At 12:37 +0000 1/6/03, John Morrison wrote:
>Hello everyone
>I am developing an application with MySQL v3.23.33 (MyISAM tables) using
>Microsoft Visual Basic and ODBC v3.520.6019.0.
>My OS is MS Windows 2000
>I have just had an interesting issue crop up concerning gaps in autonumbering.
>In the interests of better normalisation I decided to divide one table's data
>between two tables.  So I created another table and copied selected rows into
>This would make for more complicated SQL queries so, until I was sure the SQL
>would be manageable I did not initially delete the redundant rows in the first
>table.  I just put them out of reach of the application by renumbering their
>autonum data so they could be restored later if need be. This was simple
>because, since the autonum column was signed, I just renumbered the 
>rows by giving them a minus sign.

This is an invalid use of an AUTO_INCREMENT column; such columns are
intended only for use with positive values.  Attempts to use other values
result in trouble, as you've found.  This is not brokenness of MySQL's

>   However, this had an (to me) unexpected
>result.  The application appeared still to work OK except that autonum was
>broken in the table with the renumbered rows.
>I added one more row to this table and its autonum column was assigned the
>maximum signed INT value of 2.147 billion, (it should have been 73) preventing
>any further inserts into the table.
>I did not immediately realise where the problem lay and I dropped and rebuilt
>the table from an earlier MySQLdump, but to no effect.  When I finally deleted
>the rows with negative primary key autonum values and dumped and rebuilt the
>table again, autonum worked normally once more.  Somehow the gaps in the
>autonum series confused the autonum allocation.
>I feel that gaps of similar size could arise from the block deletion of rows
>and that this could be an issue.
>I have not heard of this before, is it an issue?
>For information, the entire series of ID numbers was as follows...
>-70, -69, -68, -67, -66, -63, -62, -61, -59, -58, 1, through 57, 60, 64, 65,
>71, 72
>John Morrison

Autonum brokenJohn Morrison6 Jan
  • Re: Autonum brokenPaul DuBois6 Jan
    • Re: Autonum brokenJohn Morrison6 Jan
  • Re: Autonum brokenMichael T. Babcock6 Jan
    • Re: Autonum brokenJohn Morrison9 Jan