Bug #87008 Becuse using "xa commit one phase" in master DB, slave have memory leak

• Submitted: 11 Jul 2017 9:23
• Modified: 14 Jul 2017 10:20
• Reporter: sunghun kim
• Status: Duplicate
• Category: MySQL Server: Replication
• Severity: S3 (Non-critical)
• Version: 5.7.18
• OS: Any
• CPU Architecture: Any
• Tags: memory leak, replication, xa commit one phase, XA Transaction

[11 Jul 2017 9:23] sunghun kim

Description:
When master DB queried "xa commit ... one phase" , in slave DB "innodb monitor" TRANSACTION  is increased like below.

---TRANSACTION 421949033065200, not started
0 lock struct(s), heap size 1136, 0 row lock(s)

And in slave DB memory leak occurred .

Repeate it too many times, slave DB mysqld process is killed by OOM killer.

How to repeat:
In Slave DB > 
$ mysql -uroot -prootpassword -e"show engine innodb status\G" | grep "TRANSACTION" | grep "not started" | wc -l
3

In Master DB >
mysql> create database if not exists test; 
mysql> use test;
mysql> create table if not exists g4 (seq int not null primary key, val1 int );
mysql> insert g4 values (1,10);
mysql> xa start 'test';
mysql> update g4 set val1=12 where seq=1;
mysql> xa end 'test';
mysql> xa commit'test' one phase;

In Slave DB >
$ mysql -uroot -prootpassword -e"show engine innodb status\G" | grep "TRANSACTION" | grep "not started" | wc -l
4

In start phase slave db TRANSACTION count is 3.
After master db "xa commit one phase " is queried, slave db TRANSACTION count  become 4.

A system with 16GB of memory is destroyed when it is repeated 260 million times.

[11 Jul 2017 9:27] sunghun kim

A system with 16GB of memory is destroyed when it is repeated about 2,600,000 times, not 260 million times.

[12 Jul 2017 10:44] MySQL Verification Team

Hello sunghun kim,

Thank you for the report.
May I request you to please attach configuration files from both the master/slave environment to repeat the issue at our end? Thank you!

regards,
Umesh

[13 Jul 2017 8:36] MySQL Verification Team

Thank you for providing requested details, is Semisynchronous replication enabled? Is the slave acting as both master and slave? Please let us know.
Also, is this reproducible on latest GA i.e 5.7.18?

[14 Jul 2017 8:12] sunghun kim

Slave is acting as both master and slave. (in use master-master replication)

MySQL 5.7.18 no longer has problems like slave DB memory leak,

but "xa commit one phase" with "0 row affected" still occured replication error like below.

In Master DB >
mysql> xa start 'test';
mysql> update g4 set val1=12 where seq=1;
Query OK, 0 rows affected (0.00 sec)
mysql> xa end 'test';
mysql> xa commit'test' one phase;

In Slave DB >
mysql> show slave status;
...
               Last_SQL_Errno: 1399
               Last_SQL_Error: Error 'XAER_RMFAIL: The command cannot be executed when global transaction is in the  ACTIVE state' on query. Default database: 'test'. Query: 'COMMIT'
...

[14 Jul 2017 8:19] sunghun kim

Sorry, example is wrong.

In Master DB >
mysql> xa start 'test';
mysql> update g4 set val1=12 where seq=10;
Query OK, 0 rows affected (0.00 sec)
mysql> xa end 'test';
mysql> xa commit'test' one phase;

In Slave DB >
mysql> show slave status;
...
       Last_SQL_Errno: 1399
       Last_SQL_Error: Error 'XAER_RMFAIL: The command cannot be executed when global transaction is in the  ACTIVE state' on query. Default database: 'test'. Query: 'COMMIT'
...

[14 Jul 2017 10:20] MySQL Verification Team

Thank you sunghun kim, for confirming that memory leak issue is not repeatable on 5.7.18. Coming to your newer error on slave, this is most likely duplicate of Bug #83295, please see Bug #83295

regards,
Umesh