0

I am having a strange issue with MySQL Query, i am having a table with the fields

slno,mobileno , contractor with slno as primary key and auto increment sequence 1

, while test say uptil 100 records the count and the autoincrement values are same ,

so i truncated the table to reset the autoincrement and inserted a huge excel file with around 40k data via php, then issued select query which yields

the max of slno is 40000 as expected but the count shows 39920

, I am just amused and tried to find over google , may be my lack of keyword search ability prevented me from finding result, so i am posting in here, for ref added screen shot, Any ideas and clarifications. Thanks

enter image description here

EDIT: min slno is 1

enter image description here

EDIT : A related question with solution to find gap in auto number in mysql has been asked and solved here.

Community
  • 1
  • 1
sansknwoledge
  • 4,209
  • 9
  • 38
  • 61

1 Answers1

2

There are specific cases in which auto-incremented values can be lost. One example is if you roll back an insertion. As per the doco:

"Lost" auto-increment values and sequence gaps

In all lock modes (0, 1, and 2), if a transaction that generated auto-increment values rolls back, those auto-increment values are "lost". Once a value is generated for an auto-increment column, it cannot be rolled back, whether or not the "INSERT-like" statement is completed, and whether or not the containing transaction is rolled back. Such lost values are not reused. Thus, there may be gaps in the values stored in an AUTO_INCREMENT column of a table.

In that case, although the insert is backed out, the auto-increment may not be. That would certainly allow for the possibility that your bulk insertion from Excel is occasionally failing and retrying, with the subsequent retry working. It really depends on how your insertion process works.

In any case, assuming those values will always be contiguous is actually a bad assumption to make.

This is because, even if insertions were guaranteed to be contiguous, it's possible to delete rows which would result in gaps appearing. You can certainly fix this each time you delete (or bulk insert for that matter) but the workload is high - you basically have to find gaps and then "move" higher entries into those gaps.

This movement is likely to be non-trivial as it's most likely that there will be other tables holding key look-ups to that column, and each of those will need to be changed as well.

So the best use case for an auto-increment field is simply to provide a unique identifier for the row where no other one exists and not to be necessarily contiguous.

Community
  • 1
  • 1
paxdiablo
  • 854,327
  • 234
  • 1,573
  • 1,953
  • Thanks, paxdiablo, so in my case 80 random records from excel was tried to insert and failed so the auto increment skipped, is that correct. which subsequently leads to another question, shall i try catch the failed record and try to re insert them and remove the auto increment and use a manual count +1 something like that. – sansknwoledge Jan 24 '17 at 04:17
  • @sansknwoledge, see my final paragraph. Just assume they *won't* necessarily be contiguous. Once you let that go, your spirits can soar :-) In any case, short of an intermittent error, trying to insert them again won't work any better than the first time. – paxdiablo Jan 24 '17 at 04:21
  • sorry english is not my mother tongue, so i am having some difficulty in understanding your last paragraph and comment. can you tell in simple terms what should i do ,my apologies. – sansknwoledge Jan 24 '17 at 04:29
  • 1
    As simple as I can put it: Totally ignore the fact that there may be gaps. Don't make assumptions that the auto-incrementing field will always have contiguous values. – paxdiablo Jan 24 '17 at 04:34