Jump to content
xisto Community
mica1405241557

Auto_increment Jumping About not sequential numbers

Recommended Posts

Hello,I've been having very strange problems with AUTO_INCREMENT. It will be sequential for the first 4 or so inserts and display them fine. After 3 or 4 or 5 inserts the FileID, which has the AUTO_INCREMENT applied to it, will then jump to a seemingly random number like 72, 49207, and so on.My SQL for the table in question is:CREATE TABLE `IPR` ( `FileID` int(11) NOT NULL auto_increment, `Filename` varchar(40) NOT NULL, `ContentType` varchar(15) NOT NULL, `Description` varchar(150) NOT NULL, `Status` char(10) NOT NULL, `ApprovedBy` varchar(30) default NULL, `Created` varchar(15) NOT NULL, `Software` varchar(25) default NULL, `Registered` varchar(15) NOT NULL, `LocationDirectory` varchar(250) NOT NULL, `CopyrightGroup` varchar(25) NOT NULL, `FileCreator` varchar(30) NOT NULL, `Ownership` int(3) NOT NULL, `CopyrightStatus` varchar(20) default NULL, `Comments` varchar(200) default NULL, `SubmittedBy` varchar(30) NOT NULL, PRIMARY KEY (`FileID`)) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;Even after these jumps, if I go in and delete or manually edit the record with the crazy FileID and then runALTER TABLE table_name AUTO_INCREMENT = 6;it will still carry on jumping numbers either immediately or a row or two later. It's driving me crazy. Any help would be very much appreciated.Thanks

Share this post


Link to post
Share on other sites

Well, sounds impossible. To prove it, I did tried it on MySQL 5.0 ran locally, well, it's not possible. I doubt it's an MySQL bug.It's very likely that you might have insert the FileID along with some random number, or file_id you get from some file function. Check your insert query. You can also post the code section that do the inserting.

Share this post


Link to post
Share on other sites

I do not know what RDBMS you are using or how the DB in question is being used so there may be a couple reasons. One is that your RDBMS just might do auto increment like that. at Siebel our records were auto incrimented by some formula I forget that ended with the millisecond. Of course systems do not repeat numbers even if the the record that previously had that number was deleted.

Share this post


Link to post
Share on other sites

Thankyou both for your responses, they're appreciated. I had been using the database with a PHP interface I hadn't tried before and that appears to be the source of the problem. I had people entering data into a form but keeping the FieldID off the form, so that they couldn't allocate one, and so that it only displays in the results where the SQL has incremented the FieldID to the next digit. It would appear there is some sort of conflict with the interface trying to make the hidden field a unique entry. Anyway, I've altered it within the interface coding and so far so good. Thankyou both for taking the time to consider the problem and reply.Best wishes,Michelle

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...

Important Information

Terms of Use | Privacy Policy | Guidelines | We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.