At 12:37 +0000 1/6/03, John Morrison wrote:
>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,