Hello Paul Many thanks. I won't try that again, then.
John In article <p05111b50ba3f391357f4@[192.168.0.33]>, Paul DuBois wrote: > 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 > --------------------------------------------------------------------- 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
