MySQL Bugs: #88734: XA transactions with binlog_format=STATEMENT do not get errors or warnings

Bug #88734	XA transactions with binlog_format=STATEMENT do not get errors or warnings
Submitted:	1 Dec 2017 18:11	Modified:	3 Dec 2017 15:50
Reporter:	Kenny Gryp	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: XA transactions	Severity:	S3 (Non-critical)
Version:	5.7.19	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
As described on https://dev.mysql.com/doc/refman/5.7/en/xa-restrictions.html

```
When binlog_format=STATEMENT is set, a warning is issued for DML statements inside XA transactions. 
```

I don't see any warnings when I try this.

How to repeat:
```
mysql> set transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)

mysql> set binlog_format='statement';
Query OK, 0 rows affected (0.00 sec)

mysql>  xa start '1';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values (1512516, null);
Query OK, 1 row affected (0.00 sec)

mysql> show warnings;
Empty set (0.00 sec)

mysql> xa end '1';
Query OK, 0 rows affected (0.00 sec)

mysql> xa prepare '1';
Query OK, 0 rows affected (0.00 sec)
```

No warnings anywhere

Suggested fix:
Provide a WARNING.

Have a strict mode where an ERROR can be given when these unsafe feature combinations are used.

Hello Kenny Gryp,

Thank you for the report and test case.
This issue is not seen on 5.7.20, could you please confirm with latest 5.7.20 build?

rm -rf 88733
bin/mysqld --initialize-insecure --basedir=$PWD --datadir=$PWD/88733 
bin/mysqld --no-defaults --basedir=$PWD --datadir=$PWD/88733 --core-file --socket=/tmp/mysql_ushastry.sock --port=33061 --log_bin --server_id=1 --log-error=$PWD/88733/log.err 2>&1 &

-- 5.7.19

[umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.19: bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.19-log MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> use test
Database changed
mysql> create table t1(id int not null primary key, name varchar(100));
Query OK, 0 rows affected (0.01 sec)

mysql> set transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)

mysql> set binlog_format='statement';
Query OK, 0 rows affected (0.00 sec)

mysql> xa start '1';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values (1512516, null);
Query OK, 1 row affected (0.01 sec)

^^ No warnings

-- 5.7.20 warning is seen

--
[umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.20: bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.20-log MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> set transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)

mysql> set binlog_format='statement';
Query OK, 0 rows affected (0.00 sec)

mysql> xa start '1';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values (1512516, null);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                                                                                      |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1592 | Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it is being used inside a XA transaction. Concurrent XA transactions may deadlock on slaves when replicated using statements. |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Thanks,
Umesh

Yes it was something new in 5.7.20. Apologies, should have tested with latest first.
Duplicate of #85639

No problems, thank you for confirming.

Regards,
Umesh