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.
