Hello,

We've had an ongoing problem with AUTO_INCREMENT columns. It seems to have started when we migrated from MariaDB 10.1 back in November.

We have a table, `biblio_physical` with an AUTO_INCREMENT column `id`, and the primary key of the table is composite: `id`,`library_id`. Both are unsigned integers. The primary key is composite for partitioning: we use list partitioning based on `library_id`.

Every so often, we run a statement like this:
INSERT INTO c_biblio_physical (library_id) VALUES(12345);

99+% of the time, the result is as expected: a new row is created with an incremented `id` value and the specified `library_id` value. But every so often, we get an error like "Duplicate entry '54321-12345' for key 'PRIMARY'".

When that error happens, there always exists a row in that table with the `id` value that was specified in the error (in my made-up example above, 54321) and with a different `library_id` value. So it seems that Maria is:
a) Failing to auto-increment the column as it should; somehow it is creating two rows with the same `id` value, and
b) Incorrectly believing that '54321-12345' and '54321-23456' are duplicate primary keys.

Fixing either of them would solve the problem we're seeing, but probably a) is the one to really fix, since b) couldn't ever happen with the correct auto-increment behavior.

I can't duplicate this behavior: when I start a transaction and create a row, and simultaneously start another transaction and create a row, I get the expected auto-increment behavior: the second row gets a number higher than the first, even though it hasn't been committed.

We have tried changing innodb_autoinc_lock_mode to the more conservative 0, which was the behavior for 10.1. Didn't seem to help. We've since set it back to 1, the default for 10.3.

The server is in a master/master replication configuration, which opens some doors for auto-increment problems, but the two masters each have distinct server IDs, they have auto-increment-increment set to 2, and auto-increment-offset is set to 1 on one machine and 2 on the other. Also, both of the conflicting rows (the one that did succeed in being written and the proposed one that did not) both originated on the same server. So I do not believe this is a master/master issue.

It seems to me that this must be a bug in the database, something to do with partitioning, or with composite primary keys, or both. I should note that the two offending rows do not share a partition. Well, maybe they sometimes do. In all the examples we've observed they have not. I don't know whether or not being on the same partition makes them immune to this problem; that is possible.

Most of the time we see this crop up, it's on one particular server with this particular table. But it isn't restricted to that: it does sometimes happen on the other server as well, and also it sometimes happens on other tables that are configured the same way.

Is there any more information I can provide, or anything else I can try?

Thanks for MariaDB!