long uniques break old->new replication

--source include/have_innodb.inc

drop database test; create database test character set utf8mb3;

binlog 'aRf2ZA8BAAAA/AAAAAABAAAAAAQAMTAuNS4xNS1NYXJpYURCLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAABpF/ZkEzgNAAgAEgAEBAQEEgAA5AAEGggAAAAICAgCAAAACgoKAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAEEwQADQgICAoKCgFRmTlk';

create table test.t1 (id int not null, b1 varchar(255) not null, b2 varchar(2550) not null, unique (id), unique key (b1,b2) using hash) engine=innodb;

binlog 'bBf2ZBMBAAAANAAAAJgHAAAAAHEAAAAAAAEABHRlc3QAAnQxAAQDDw8IBP0C4h0AeqMD4A==bBf2ZBcBAAAANAAAAMwHAAAAAHEAAAAAAAEABP/wj6QAAAEAYgEAZa6/VU0JAAAANteqUw==';

binlog 'bBf2ZBMBAAAANAAAAHUkAAAAAHEAAAAAAAEABHRlc3QAAnQxAAQDDw8IBP0C4h0AaTGFIg==bBf2ZBgBAAAASAAAAL0kAAAAAHEAAAAAAAEABP//8I+kAAABAGIBAGWuv1VNCQAAAPBuWwAAAQBiAQBlrr9VTQkAAADxS9Lu';

These events were generated by MariaDB 10.5.15 — before MDEV-27653. So they have old hash values. When replayed on a newer server, the table will be created with the new hash function. After-image (second event is insert, third event is update) contains the old hash value, which isn't recalculated and gets inserted. Pre-image (in the third event) contains the old hash value, which is recalculated in index_read(). And it cannot be found in the index, that contains the old hash value.

The hash value calculation was changed in the following versions:
10.11.2, 10.4.28, 10.5.19, 10.6.12, 10.7.8, 10.8.7, 10.9.5, 10.10.3

If the master would be before any of the above versions and slave newer than any of the above versions or vice versa, then the reported problem can happen if the master and slave was of different versions when the table was created.

After the "Fix version" this cannot happen anymore as the slave will always recalculate the hash on the slave on insert and update.

However if the mismatch of master and slave version have ever been in effect, the slaves hash values can be corrupted (as they contain a mismatch of old and new values).

The effect of this bug is that if one has a table with an utf8 column and a UNIQUE HASH index like in:

CREATE TABLE
...
xxx VARCHAR(255) COLLATE 'utf8mb3_general_ci',
...
UNIQUE INDEX `xxx` (column,...) USING HASH...

Then the following problems may occur on slave where the master & slave have conflicting version as described above:

• Rows inserted with the new master will have a different hash value than old rows. If there was a duplicate value between an old row and new row, it will not be noticed.
• If the slave is promoted to a master, inserts into the tables may not notice duplicates as the hash from the old master will be different to the newly generated onces.

To fix this, one has to drop the unique index and add it back on the affected server or alternatively run ALTER TABLE xxx ENGINE=InnoDB; (In case of InnoDB tables).