Windows 10 64 bit Pro Desktop (V21H2). Running on a i9 mini with 2x2 Tb M2SSDs and 32 Gb RAM

Yes, please provide something that would reproduce the problem.

Will do. But it might take me a week or so.

I'm in the middle of dealing with a few emergencies and may not be able to get back to the question for a while.

I have rewritten the bit of code that creates the CSVs. It WAS overwriting the previous one each time (because I had no reason to expect to need to re-use them or pass them along) And as soon as I had the workaround working, I let it chunter away to produce the first million rows. And I've only got one CSV to show for it!

As soon as I restart the generation, the code will put copies of the CSVs in a safe place, from where I can send you copies. But I can't do that until I've fully tested the first million to check that my logic is working. And I can't get back to that until I've got the emergencies out of the way.

So, apologies for the delay. (didn't expect anyone to call my bluff that quickly

Mike

Having solved the Maria Root password problem, I was able to return to the auto_inc issue.

The attached zip file contains all the files you need to recreate the issue (in a WINDOWS environment only I'm afraid)

The text file "to recreate issue.txt" does what it says on the tin. AUTOINCBUG.zip

there is clearly something wrong with the forum software but all my attempts to communicate with board admins are blocked.

the substantive issue that I'm concerned about is the lack of response to this issue as it clearly represents a (potentially) major bug.

My speculation is that I'm not getting a response because the issue isn't visible to other users on the board. I tried to get around the problem by reviving the issue with a new post (MDEV-29788). But that duplication was spotted by the board admins, who marked it as a duplicate of this issue and Closed it. This one remains Open but I don't see it listed as an open issue even within my own account.

When I tried reporting this through the Jira Server Feedback icon, it opened up a web form and allowed me to tell the story, but on Submit, came back with the error:

Oops! Something went wrong...
There was a problem submitting your feedback, likely due to the configuration of this form. You might want to contact the site owner to let them know about this issue.

I do indeed want to contact the site owner but they've blocked all obvious avenues of contact. If anyone knows how to break this logjam, I'd be grateful.

Using the file sources.csv in AUTOINCBUG.zip and the following SQL, I can observe something:

--source include/have_innodb.inc

CREATE TABLE `hash_chain` (

	`ROWUNQ` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,

	`HASH_SOURCE` VARBINARY(32) NULL DEFAULT '0',

	`HASH_LINKED` VARBINARY(32) NOT NULL DEFAULT '0',

	`HASH_SALTED` VARBINARY(32) NOT NULL DEFAULT '0',

	`HASH_PREVIOUS` VARBINARY(32) NOT NULL DEFAULT '0',

	`HASH_CONFIRMED_BY` VARBINARY(32) NULL DEFAULT 'UNCONFIRMED',

	`BLOCK_NUM` INT(10) UNSIGNED NOT NULL DEFAULT '0',

	`BLOCK_PREV` INT(11) NOT NULL DEFAULT '0',

	`BLOCK_NEXT` INT(10) UNSIGNED NOT NULL DEFAULT '0',

	`CODEL_TIME` TIMESTAMP NULL DEFAULT NULL,

	`CHAIN_TIME` TIMESTAMP NOT NULL DEFAULT current_timestamp(),

	UNIQUE INDEX `HASH_LINKED` (`HASH_LINKED`) USING BTREE,

	UNIQUE INDEX `HASH_SALTED` (`HASH_SALTED`) USING BTREE,

	UNIQUE INDEX `ROWUNQ` (`ROWUNQ`) USING BTREE,

	INDEX `HASH_SOURCE` (`HASH_SOURCE`) USING BTREE,

	INDEX `HASH_CONFIRMED_BY` (`HASH_CONFIRMED_BY`) USING BTREE,

	INDEX `BLOCK_NUM` (`BLOCK_NUM`) USING BTREE

)

COMMENT='CODEL 1.5 '

COLLATE='utf8_general_ci'

ENGINE=InnoDB

AUTO_INCREMENT=1

;

LOAD DATA

  LOCAL INFILE "/dev/shm/sources.csv"

  INTO TABLE `hash_chain`

  FIELDS TERMINATED BY ','

  OPTIONALLY ENCLOSED BY '"'

  LINES TERMINATED BY '\r\n'

  IGNORE 1 LINES

  (@hash_source,@hash_linked,@hash_salted,@hash_previous,

@hash_confirmed_by,`block_num`,`block_prev`,`block_next`,`codel_time`)

  SET

    `hash_source` = UNHEX(@hash_source),

    `hash_linked` = UNHEX(@hash_linked),

    `hash_salted` = UNHEX(@hash_salted),

    `hash_previous` = UNHEX(@hash_previous),

    `hash_confirmed_by` = UNHEX(@hash_confirmed_by);

SELECT min(rowunq),max(rowunq),count(*) FROM hash_chain;

SHOW CREATE TABLE hash_chain;

DROP TABLE hash_chain;

Note: I added PRIMARY KEY, which you had forgotten. I also made the LOAD statement use the same table name that was specified in CREATE TABLE. The relevant part of the output is:

SELECT min(rowunq),max(rowunq),count(*) FROM hash_chain;

min(rowunq)	max(rowunq)	count(*)

1	10000	10000

SHOW CREATE TABLE hash_chain;

Table	Create Table

…

) ENGINE=InnoDB AUTO_INCREMENT=16384 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci COMMENT='CODEL 1.5 '

The input file contains 10,001 rows, the first one being the title of each column.

I debugged this with ./mtr --rr:

break ha_innobase::delete_table

continue

next

next

next

next

next

next

next

watch -l table->autoinc

reverse-continue

continue

backtrace

The auto-increment sequence will be updated here:

(rr) continue

Continuing.

Thread 2 hit Hardware watchpoint 2: -location table->autoinc

Old value = 8192

New value = 16384

ha_innobase::get_auto_increment (this=0x7f25e4086e40, offset=1, increment=1, nb_desired_values=8192, first_value=0x7f25f2eb1800, nb_reserved_values=0x7f25f2eb17f8) at /mariadb/10.6/storage/innobase/handler/ha_innodb.cc:16751

16751		m_prebuilt->autoinc_offset = offset;

(rr) backtrace

#0  ha_innobase::get_auto_increment (this=0x7f25e4086e40, offset=1, increment=1, nb_desired_values=8192, first_value=0x7f25f2eb1800, nb_reserved_values=0x7f25f2eb17f8)

    at /mariadb/10.6/storage/innobase/handler/ha_innodb.cc:16751

#1  0x00005616801262b4 in handler::update_auto_increment (this=0x7f25e4086e40) at /mariadb/10.6/sql/handler.cc:3974

#2  0x000056168061b4a9 in ha_innobase::write_row (this=0x7f25e4086e40, record=0x7f25e41247e0 <incomplete sequence \370>) at /mariadb/10.6/storage/innobase/handler/ha_innodb.cc:7885

#3  0x000056168012c533 in handler::ha_write_row (this=0x7f25e4086e40, buf=0x7f25e41247e0 <incomplete sequence \370>) at /mariadb/10.6/sql/handler.cc:7574

#4  0x000056168034c695 in write_record (thd=thd@entry=0x7f25e4000c68, table=table@entry=0x7f25e41243a8, info=info@entry=0x7f25f2eb1aa0, sink=sink@entry=0x0) at /mariadb/10.6/sql/sql_insert.cc:2156

#5  0x00005616803740f1 in read_sep_field (thd=thd@entry=0x7f25e4000c68, 

The auto_inc_interval_for_cur_row that controls this in handler::update_auto_increment() appears to be updated in powers of 2. That is, assuming the default values of auto_increment_increment=1 and auto_increment_offset=0, the next AUTO_INCREMENT value after inserting anything between 8,192 and 16,383 rows should be 16,384.

I do not see anything really broken here. I do not think that there is any guarantee that no gaps will be left in the AUTO_INCREMENT sequences. Gaps would also be left if an INSERT statement ends up being rolled back. Such gaps at the end of the sequence could also be preserved across server restarts, ever since MDEV-6076 was fixed.

If you use innodb_autoinc_lock_mode=0, then the SHOW CREATE TABLE will report AUTO_INCREMENT=10001. That might be another reason not to proceed with MDEV-27844.

Marko, I'm having difficulty understanding your conclusion.

It's not even clear to me whether you were able to recreate the problem. i.e., when you imported the csv, what was the resulting auto_inc value, post import?

MikeJacobs, I am not sure if I understood the problem correctly. If you search my replies for the string AUTO_INCREMENT=, you may or may not conclude that I reproduced it. Would the following fix your problem?

SET GLOBAL innodb_autoinc_lock_mode=0;

>>would the following fix your problem?
>>SET GLOBAL innodb_autoinc_lock_mode=0;

dunno. Will test as soon as poss and feedback.

The problem, though, is real and repeatable. The latest instance involved my transfering the first million rows of the table I'm building to another workstation with a fresh install of the same version of Maria (10.9.3) I used mysqldump to export the database from workstation 1 and to import it to workstation 2. There were a couple of issues with that, which I may raise elsewhere on the board, but the one that's germaine here is that, on successful completion of the import to W 2, the autoinc value was offset by exactly the same amount reported in my initial post on Experts Exchange (6383 - not the 6183 which I mistyped in my opening post on this thread) i.e on W1, the Autoinc value for the table holding 1000200 rows was, as we'd expect, 1000201, whereas, post import on W 2, the value was 1006584. Which tells me its not a progressive error, where the gap widens, but a one off error which is added, wrongly only at the end of the import.

In other words, my impression to date, is that it does NOT happen during the import. It's almost like its a consequence of the import. So if I suck in 10000 records, all the ROWUNQ values are as expected but instead of an autoinc val of 10001, I see 106384. If I suck in 50000, again, all the ROWUNQ values will be correct but the final autoinc val is 506384. AND, it only happens ONCE. I only spotted it because I was uploading rows in batches of 10k (and didn't think to check the autoinc value after each import, which is what I'm doing now) The gap appeared ONLY after the first import. Thereafter, every ROWUNQ value was offset by 6383. It didn't add another 6383 with the next import.

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.