Bug #106671 Contribution by Tencent: mark insert select with autoinc column unsafe

• Submitted: 8 Mar 2022 12:12
• Modified: 11 Mar 2022 7:46
• Reporter: Yin Peng (OCA)
• Status: Verified
• Category: MySQL Server: Replication
• Severity: S3 (Non-critical)
• Version: 5.7.35, 5.7.37
• OS: Any
• CPU Architecture: Any
• Tags: Contribution

[8 Mar 2022 12:12] Yin Peng

Description:
In general, insert select statement is marked unsafe. However there is an exception: insert ... select ... union all select ...

This statement is written to the binary log in statement format when binlog_format=mixed and may cause error on slave.

How to repeat:
The following test script can repeat this problem(run with option --mysqld=--innodb-autoinc-lock-mode=2):
-- source include/have_debug.inc
-- source include/have_innodb.inc
-- source include/have_binlog_format_mixed.inc
-- source include/master-slave.inc

connect (con1,127.0.0.1,root,,test,$MASTER_MYPORT,);
connection master;

CREATE TABLE `t1` (
  `cell_id` int(11) NOT NULL AUTO_INCREMENT ,
  `report_id` int(11) NOT NULL,
  PRIMARY KEY (`cell_id`),
  KEY `report_id` (`report_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7460418 DEFAULT CHARSET=utf8mb4;

SET SESSION debug="+d,innodb_simulate_concurrency_gen_autoinc";

send INSERT INTO `t1`(`report_id`)
SELECT '314838' UNION ALL
SELECT '314839' UNION ALL
SELECT '314840' UNION ALL
SELECT '314841' UNION ALL
SELECT '314842';

sleep 1;
connection con1;
echo in con1;
begin;
INSERT INTO `t1`(`report_id`) SELECT '314843';
commit;
select last_insert_id();
connection master;
reap;
echo in master;
select last_insert_id();
SET SESSION debug="-d,innodb_simulate_concurrency_gen_autoinc";
select cell_id, report_id from t1;
--source include/sync_slave_sql_with_master.inc
connection slave;
select cell_id, report_id from t1;

--source include/rpl_end.inc

=====================================

In addition, we need to add a debug point in ha_innodb.cc to simulate concurrent insert:

diff --git a/storage/innobase/handler/ha_innodb.cc b/storage/innobase/handler/ha_innodb.cc
index 808f6577635..32ab67f72f2 100644
--- a/storage/innobase/handler/ha_innodb.cc
+++ b/storage/innobase/handler/ha_innodb.cc
@@ -7645,6 +7645,9 @@ no_commit:
 
                auto_inc_used = true;
        }
+  DBUG_EXECUTE_IF("innodb_simulate_concurrency_gen_autoinc",{
+      os_thread_sleep(1000000);
+  });

Suggested fix:
Mark all insert select statements unsafe when innodb_autoinc_lock_mode=2.

[8 Mar 2022 12:15] Yin Peng

Update the version of mysql.

[8 Mar 2022 12:21] Yin Peng

Suggested fix:
Mark all insert select statements with autoinc column unsafe when innodb_autoinc_lock_mode=2.

[9 Mar 2022 6:55] MySQL Verification Team

Hello yin peng,

Thank you for the report and test case.

regards,
Umesh

[11 Mar 2022 7:46] Yin Peng

Here is a patch for this problem.

[11 Mar 2022 7:47] Yin Peng

Suggested fix

(*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: patch.txt (text/plain), 2.23 KiB.

[11 Mar 2022 8:00] MySQL Verification Team

Thank you for your contribution.

regards,
Umesh