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
it.
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 appropriate
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 part.
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
--------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
