Bug #98799 Replication failing on valid scenario
Submitted: 2 Mar 2020 12:08 Modified: 4 Mar 2020 8:22
Reporter: David Ducos Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Replication Severity:S2 (Serious)
Version:5.7, 5.7.29 OS:Any
Assigned to: CPU Architecture:Any

[2 Mar 2020 12:08] David Ducos
Description:
I found an issue that breaks the replication when the primary key type of a table is different on the master (mediumint) and slave(int), we send some inserts inside a transaction and auto_increment value is in the unsigned zone ( values > 8388607 on mediumint )

How to repeat:
I have a replication between nodeA (Master) and nodeB (Slave).

On nodeB I configured:
| slave_type_conversions | ALL_NON_LOSSY |

On nodeA I have:
CREATE TABLE `table1` (
  `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=latin1

And this 2 tests:
test1:
INSERT INTO table1 VALUE();

test2:
BEGIN
INSERT INTO table1 VALUE();
INSERT INTO table1 VALUE();
INSERT INTO table1 VALUE();
COMMIT;

On nodeB
ALTER TABLE bug MODIFY COLUMN id INT unsigned AUTO_INCREMENT;

If I executed test 1 and test2 they work.

But if I execute on nodeA:
ALTER TABLE table1 AUTO_INCREMENT=12093336;
test1 works but test2 breaks the replication with:
               Last_SQL_Error: Could not execute Write_rows event on table test.table1; Duplicate entry '0' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log nodeA-bin.000001, end_log_pos 4245

Doesn't make sense to set 0 as primary key when the real value is there, but it is not working when the values are in the unsigned part.
[4 Mar 2020 8:22] MySQL Verification Team
Hello David Ducos,

Thank you for the report and feedback!

Thanks,
Umesh
[4 Mar 2020 8:23] MySQL Verification Team
Test results - 5.7.29

Attachment: 98799_5.7.29.results (application/octet-stream, text), 25.46 KiB.

[4 Mar 2020 8:23] MySQL Verification Team
I'll add 8.0.19 results later on
[14 Jan 8:43] Jan Kahoun
Still present in 5.7.30, any estimation when this crucial bug will get fixed?