Bug #67159 MySQL 5.6, GTID and performance_schema
Submitted: 9 Oct 2012 18:43 Modified: 9 Nov 2012 17:22
Reporter: Simon Mudd (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Performance Schema Severity:S3 (Non-critical)
Version:5.6.5 OS:Any
Assigned to: CPU Architecture:Any
Tags: windmill

[9 Oct 2012 18:43] Simon Mudd
Description:
While trying to analyze some information from MySQL 5.6's performance_schema I came across this issue on a MySQL 5.6.5 server configured with the new GTID functionality (see below). This wasn't expected, nor I believe is it clearly documented.

How to repeat:
Note: my configuration had the following gtid settings as currently recommended on MySQL web site.

[root@my-server ~]# grep gtid /etc/my.cnf
gtid_mode = ON
disable-gtid-unsafe-statements
[root@my-server ~]# rpm -q MySQL-server
MySQL-server-5.6.5_m8-1.rhel5
[root@my-server ~]# 

This is what fails:

root@my-server [performance_schema]> UPDATE setup_instruments set enabled = 'Yes', timed = 'yes' WHERE NAME IN ( 'wait/io/socket/sql/server_tcpip_socket', 'wait/io/socket/sql/client_connection' );
ERROR 1784 (HY000): Updates to non-transactional tables are forbidden when DISABLE_GTID_UNSAFE_STATEMENTS = 1.
root@my-server [performance_schema]>

It _does_ seem to be possible to work around this doing the following:

root@my-server [performance_schema]> set session sql_log_bin = 0;
Query OK, 0 rows affected (0.00 sec)

root@my-server [performance_schema]> UPDATE setup_instruments set enabled = 'Yes', timed = 'yes' WHERE NAME IN ( 'wait/io/socket/sql/server_tcpip_socket', 'wait/io/socket/sql/client_connection' );
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0

Suggested fix:
It may be convenient to want to _configure_ performance_schema by _replicating_ changes from a master downstream (as was the case above), so having to disable this to make the configuration change is not ideal, nor is it ideal having to configure each server individually, if this can be done "easily" via normal replication.

The documentation does not talk about this specific edge case and given the importance of using P_S for improved "monitoring" and analysis I think it should be possible to come up with a mechanism to tell mysql that it's ok to apply this sort of "change" into the binlogs without "worrying" unduly.

A reported issue with the grants tables seems similar in this respect.  If we enable this "allow special cases" functionality, the DBA needs to be aware of what he's allowing but in most cases this shouldn't really be an issue IMO.

If the disable-gtid-unsafe-statements setting is not appropriate then please add better documentation to add the appropriate caveats, but also be prepared if the code currently warns about this usage that it will annoy people who disable it. (I'm not really sure what removing this setting does). Also note that this setting is not even dynamic so I can't temporarily disable it prior to making a change such as this.
[10 Oct 2012 12:50] Sinisa Milivojevic
Verified against 5.6.7-rc
[9 Nov 2012 17:22] Jon Stephens
Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html
[9 Nov 2012 17:23] Jon Stephens
Fixed in 5.6.9: Performance Schema tables, server log tables, and replication info tables will no longer be replicated.