FreeBSD 13.1-RELEASE-p8 - Mariadb 10.6.14

Can you provide a way to reproduce it?

We are looking to see if wee can replicate the issue on a newly built / empty database. We encountered the problem in our production database on multiple tables with unique indexes on two fields and we rolled back to 10.6.11.

We are not able to replicate the issue on a minimal new build where we install 10.6.14 and dump one table that had the issue with.
At the moment, we have binary logs from the production databases showing successful insert statements with duplicate unique indices as well as the slave logs showing the errors which allowed us to catch the issue. If this or any other thing from our logs would of any help, please let me know.

Can you show the SHOW CREATE TABLE definition (even if table/column names are changed)?

Do you have a snapshot on which binary logs can be applied to create the duplicate?

What events are in the binary log indicative of the SQL statements? Are there types of SQL statements that occur very close to each other potentially allowing the duplicate?

What non-default configuration options are used my_print_defaults --mysqld?

So this is occurring on the master. Do occasionally duplicate find themselves in the replca tables or does the duplicate key error occur in replication predictably?

To explicitly use the secondary unique index can you try the query to validate the duplicate is in the secondary index.

SELECT dupfield, COUNT(*) cnt FROM tbl GROUP BY dupfiled HAVING cnt > 1;

Thank you kindly.

One final note regarding #6: I have also tried to re-create the unique index and it failed and reported that there are duplicates. After removing the duplicates manually, I have recreated the index, but the duplicates continued to appear with the recreated index.

Does CHECK TABLE .. EXTENDED show anything other than OK when duplicates occur? And are there any errors or warnings which seem to be relevant or at least unexpected in the server's error (not slave's log, but the one which has duplicates)?

I did not check that statement when the duplicates occurred and we have already downgraded.
Regarding the logs: I do not recall anything in master's error log when duplicates occurred, but we unfortunately rolled back the filesystem that contains the error log, so I am not able to double check that.

One thing that looked off after the upgrade was following statement at the top of the binlogs created with 10.6.14 and opened with 10.6.14's mariadb-binlog binary. This warning was appearing even after I restart the engine to move to new binary.

1. Warning: this binlog is either in use or was not closed properly....

By the way, someone else seems to have experienced the same issue when moving from 10.3 to 10.6. She does not state the minor version, and she thinks she is solving it by fiddling with innodb parameters and does not file a bug report.

https://dba.stackexchange.com/questions/327421/mariadb-10-3-mariadb-10-6innodb-duplicate-entry-for-key-unique-ind

If you are completely stuck, we can try to clone our production database and repeat the upgrade to 10.6.14, however this will be a lengthy process and I also do not know exactly what to look for if I can get a problematic system up.

I expect it will be tracked further in MDEV-31120, but I'll leave it to marko to decide.

You probably don't need a workaround if you rolled back anyway, but if you do, please try innodb_change_buffering=none, at least for now I suspect non-none-values to be causing the problem.

Thank you. We have already rolled back, and will upgrade to a release with a fix for this issue.

I think that this must duplicate MDEV-31120, which is a regression that was introduced in MariaDB Server 10.6.12.