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. 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
