MikeJacobs, SHOW CREATE TABLE should roughly display the AUTO_INCREMENT value that would be used for the next insert. It is not exactly that simple, but close to the truth if you are using the default auto_increment_increment=1 and auto_increment_offset=0. If some operations that ‘consumed’ or ‘allocated’ AUTO_INCREMENT values were never committed (for example due to a deadlock of transactions, or due to a duplicate key error), and if the server was restarted, then MDEV-6076 could start to make trouble for you. Before MDEV-6076, on server restart, InnoDB would basically do SELECT MAX(auto_increment_column) to initialize the counter. Some applications require a guarantee that AUTO_INCREMENT values are never reused or reissued, and such applications must obviously be tolerant of any ‘gaps’ in the sequence.

In InnoDB, you should be able to reset the AUTO_INCREMENT counter to the ‘minimal’ value (in this case 10001) by executing something like this:

ALTER TABLE hash_chain AUTO_INCREMENT=0, ALGORITHM=NOCOPY;

The ALGORITHM=NOCOPY is just a safety measure to refuse an expensive operation. Normally, the native algorithm inside InnoDB should be chosen.

My interest in this ticket was to check if there is a bug in InnoDB. I will let my colleagues judge whether this qualifies as a bug in LOAD DATA or INSERT.

bah!

just tried
SET GLOBAL innodb_autoinc_lock_mode=0;
within the Heidi UI

and it responded with:
SQL Error (1238): Variable 'innodb_autoinc_|ock_mode' is a read only variable

so I've added it as a line in the mysqld section of my.ini
innodb_autoinc_lock_mode=0

If that's wrong, please let me know

Yes, that is right. Sorry for the incorrect advice. I should have remembered that there are good reasons why if SET GLOBAL is not accepted for it.

OK, just restarted from scratch, after adding the new line to my.ini and rebooting (to ensure that the Maria service reads the new .ini and that nothing in windoze is likely to be interfering with the operation.

Imported the 1000200 rows without issue. But despite the new line, got precisely the same result. All ROWUNQ had the correct value, but the final autoinc value was offset by 6383.

Frankly, unless you say otherwise, its a non issue for me because I've created a workaround (which you can see if you follow the EE link) which evades the problem. But I can imagine that, if I'm the only one on the planet getting this behaviour you might advise me that something must be seriously screwed with (both of) my installations which I shouldn't ignore. That's why I want to know if, when you follow the steps to recreate the issue, what final autoinc value do you see (before any tweaks)

I did consider the possibility that it might be a bug within the Heidi UI, but I can rule that out because I don't need to use the Heidi UI while I'm creating the table. And, if I don't, I still get the offset at the end of the first import where I don't reset the autoinc value.

just spotted your additional comment above:

>>In InnoDB, you should be able to reset the AUTO_INCREMENT counter to the ‘minimal’ value (in this case 10001) by executing something >>like this:

>>ALTER TABLE hash_chain AUTO_INCREMENT=0, ALGORITHM=NOCOPY;

that's similar to what I'm running from VFP

my fix is slightly more complex. Before each import, I interrogate the count to calculate what the autoinc value should be and assign that value to NEXTNUM

then I issue ALTER TABLE `hash_chain` AUTO_INCREMENT="+TRANSFORM(NEXTNUM)

then I import and the ROWUNQ values follow on as they should.

Doing it that way allows me to store the start and end values that should emerge for ROWUNQ, then check, after the import, that they match.