MDEV-18466 Unsafe to log updates on tables referenced by foreign keys with triggers in statement format

ignore FK-prelocked tables when looking for write-prelocked tables
with auto-increment to complain about "Statement is unsafe because
it invokes a trigger or a stored function that inserts into an
AUTO_INCREMENT column"

3 files changed, 47 insertions, 0 deletions

diff --git a/mysql-test/suite/binlog/r/binlog_innodb_stm.result b/mysql-test/suite/binlog/r/binlog_innodb_stm.result
new file mode 100644
index 00000000000..829ed7d3c61
--- /dev/null
+++ b/mysql-test/suite/binlog/r/binlog_innodb_stm.result
@@ -0,0 +1,17 @@
+create table categories(
+cat_id int not null primary key,
+cat_name varchar(255) not null,
+cat_description text
+) engine=innodb;
+create table products(
+prd_id int not null auto_increment primary key,
+prd_name varchar(355) not null,
+prd_price decimal,
+cat_id int not null,
+foreign key fk_cat(cat_id)
+references categories(cat_id)
+on update cascade
+) engine=innodb;
+insert into categories values (1, 'drinks', 'drinks');
+update categories set cat_description=2 where cat_id=1;
+drop table products, categories;
diff --git a/mysql-test/suite/binlog/t/binlog_innodb_stm.test b/mysql-test/suite/binlog/t/binlog_innodb_stm.test
new file mode 100644
index 00000000000..4dfa7a76584
--- /dev/null
+++ b/mysql-test/suite/binlog/t/binlog_innodb_stm.test
@@ -0,0 +1,26 @@
+source include/have_innodb.inc;
+source include/have_binlog_format_statement.inc;
+
+#
+# MDEV-18466 Unsafe to log updates on tables referenced by foreign keys with triggers in statement format
+#
+
+create table categories(
+   cat_id int not null primary key,
+   cat_name varchar(255) not null,
+   cat_description text
+) engine=innodb;
+ 
+create table products(
+   prd_id int not null auto_increment primary key,
+   prd_name varchar(355) not null,
+   prd_price decimal,
+   cat_id int not null,
+   foreign key fk_cat(cat_id)
+   references categories(cat_id)
+   on update cascade
+) engine=innodb;
+
+insert into categories values (1, 'drinks', 'drinks');
+update categories set cat_description=2 where cat_id=1;
+drop table products, categories;
diff --git a/sql/sql_class.cc b/sql/sql_class.cc
index 02e76520cb4..639c7c1784a 100644
--- a/sql/sql_class.cc
+++ b/sql/sql_class.cc
@@ -5542,6 +5542,9 @@ int xid_cache_iterate(THD *thd, my_hash_walk_action action, void *arg)
     Call this function only when you have established the list of all tables
     which you'll want to update (including stored functions, triggers, views
     inside your statement).
+
+    Ignore tables prelocked for foreign key cascading actions, as these
+    actions cannot generate new auto_increment values.
 */
 
 static bool
@@ -5551,6 +5554,7 @@ has_write_table_with_auto_increment(TABLE_LIST *tables)
   {
     /* we must do preliminary checks as table->table may be NULL */
     if (!table->placeholder() &&
+        table->prelocking_placeholder != TABLE_LIST::FK &&
         table->table->found_next_number_field &&
         (table->lock_type >= TL_WRITE_ALLOW_WRITE))
       return 1;