Autoincrement field does NOT use the next available value

[phdphd]

 

Hi All,

 

I defined an autoincrement ID field with 7 digits (int(7) ZEROFILL), so as to get 0000001, 0000002, 0000003, etc., enough for the table to contain as many as 10 000 000 rows.

At the beginning all was OK. Then I noticed that the field would "skip" entire ranges. For example, it would directly go from 0000100 to 0000200.

Things became very complicated when it went directly to 9999999, which is the last value allowed by int(7), because the next time it set the "10000000" value, which contains 8 digits, whereas it had so far used only 99.9999% of the values available with int(7).

 

The problem is that I really need an 7-digit format for this field.

 

Is there a way to really force the field to use the next available value?

 

Thanks for your help!

 

 

[phdphd]

Oops, please read 0.005% instead of 99.9999% !

[Christian F.]

It sounds like AUTO_INCREMENT might not have been the best choice in this case, as you don't exactly release a previous value to be reused when deleting rows. Doing so could very well introduce concurrency errors in the database, and is the reason why it's not done. To prevent this from happening when you delete the last row, MySQL stores the offset in the meta-information for the table.

Now, OPTIMIZE can (and will) roll back the offset to the last available, but it will not fill out empty columns in the middle. So if this is something you want to do, then I recommend perhaps looking at other solutions for your system, perhaps a trigger or a Stored Procedure.

[PFMaBiSmAd]

The times I have seen an auto-increment field 'skip' values is when an insert query either produces an error or you have a query that intentionally ignores errors. The query still gets the next available auto-increment value, but does not use it.

 

Any chance your code is intentionally setting the auto-increment value in a query and you have a logic error that is causing this or perhaps you are mass inserting data that already exists and the insert query is getting auto-increment values but not using them (the next successful insert would use the next available one and would appear to skip values) or perhaps a hacker has been feeding your script data values that cause the query to fail and get/use up auto-increment values.

[phdphd]

Thank you for your answer. Here are my comments.

 

Any chance your code is intentionally setting the auto-increment value in a query and you have a logic error that is causing this

 

The insert SQL/PHP instruction does not set the ID table field. Basically I have an array containing data gathered from a form like name, address, zip code, city,  etc. Then I send this data to the table, each piece in the corresponding field, and let the database set the value in the ID field.

 

you are mass inserting data that already exists

 

I do not insert data massively. And should data already exist in the DB there would be no conflict because I test (SELECT) whether data exists before sending (INSERT) it to the DB.

 

perhaps a hacker has been feeding your script data values that cause the query to fail and get/use up auto-increment values.

 

No such a risk yet , my website is still under development on my PC.

 

 

[PFMaBiSmAd]

my website is still under development on my PC

 

Then the most likely cause is -

you have a logic error that is causing this

 

What's your code that is forming the SELECT query through to the point where you are executing the insert query?

 

Do you have error checking logic in your code to detect and report when a query fails?

 

What approximate quantity of queries are you executing and are they enough to account for the number of auto-increment values that are used/skipped? 10s, 100s, 1000s?

 

It's also possible that your code is being requested multiple times by the browser and/or you have code that is inserting, then deleting rows, thereby using up auto-increment values. Do you have any header redirect statements that don't have exit/die statements after them to prevent the remainder of your code from running while the browser requests the target of the redirect? You could be failing to stop the execution of your php code and it goes on to delete rows that were just inserted.

 

The short answer is, there are multiple things that could be causing such a symptom and to either confirm or eliminate the code, the most common cause of unexplained computer behavior, as the cause, it takes seeing all the code that reproduces the symptom.

[fenway]

Please recognize that it's called 'auto'-increment -- which means you're not supposed to give it a value.

 

If you are, you've chosen the wrong field -- but I could have told you that from your choice of ZEROFILL.

[phdphd]

Thanks all of you for your answers.

 

Please recognize that it's called 'auto'-increment -- which means you're not supposed to give it a value.

 

My PHP code never sets a value for the ID field. It fills only the other fields.

 

What's your code that is forming the SELECT query through to the point where you are executing the insert query?

 

Basically the code puts all the data entered by the user in an array where keys are named according to the field names in the DB table, then it builds the insert condition by simulating SQL language and by using array key names as table fields and array values as the table values to be inserted.

 

Do you have error checking logic in your code to detect and report when a query fails?

 

Should the query fail for any reason (no connection to server/database, wrong table name/field, etc.), and since my website is still under development, typical PHP error messages display. There are anyway no automatic retries in my php code, since it is designed to stop running in such situations and to display a message to the user.

 

What approximate quantity of queries are you executing and are they enough to account for the number of auto-increment values that are used/skipped? 10s, 100s, 1000s?

Definetely not enough to account for the values skipped. For example, the autoinc counter jumped from 2000203 directly to 9999999 while just a few minutes/hours elapsed between the 2 inserts. I never manually tested 7 millions queries in the meantime, and my code is not designed to do that automatically anyway.

 

Do you have any header redirect statements that don't have exit/die statements after them to prevent the remainder of your code from running while the browser requests the target of the redirect?

 

All header instructions are followed by exit() instructions.

 

All in all I just wonder whether the "guilty" is not the ZEROFILL setting. It is very strange that the autoinc counter jumped from 2000203 to 9999999. I have the feeling that if I had chosen INT(12) instead of INT (7), it would have jumped from 000002000203 to 999999999999.

 

[Pikachu2000]

I realize this doesn't address the problem you've described, but you seem to have a major misconception of what the value in the parentheses of INT(7) actually does. It does not limit the field to 7 digits at all; the field will still hold ~4.3 trillion values.

 

MySQL supports an extension for optionally specifying the display width of integer data types in parentheses following the base keyword for the type. For example, INT(4) specifies an INT with a display width of four digits. This optional display width may be used by applications to display integer values having a width less than the width specified for the column by left-padding them with spaces. (That is, this width is present in the metadata returned with result sets. Whether it is used or not is up to the application.)

 

The display width does not constrain the range of values that can be stored in the column. Nor does it prevent values wider than the column display width from being displayed correctly. For example, a column specified as SMALLINT(3) has the usual SMALLINT range of -32768 to 32767, and values outside the range permitted by three digits are displayed in full using more than three digits.

 

When used in conjunction with the optional (nonstandard) attribute ZEROFILL, the default padding of spaces is replaced with zeros. For example, for a column declared as INT(4) ZEROFILL, a value of 5 is retrieved as 0005.

[fenway]

MySQL won't skip them -- unless you add them, then delete them -- or specify a value.