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 run
ALTER 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

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.

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.

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