0

MySQL documentation for v5.7 states about the replication of variables:

In MySQL 5.7, the following session variables are written to the binary log and honored by the replication slave when parsing the binary log, regardless of the logging format: - sql_mode, (etc.)

However this feature does not seem to be working. I noticed it in my development setup and double checked it with a fresh install of 1 master and 2 slaves with mysql-sandbox.

Data replication works:

$HOME/sandboxes/rsandbox_mysql-5_7_13/m

master [localhost] {msandbox} ((none)) > use test;
master [localhost] {msandbox} (test) > CREATE TABLE test1 (id INT(11) UNSIGNED NOT NULL);
master [localhost] {msandbox} (test) > INSERT INTO test1 VALUES(1);
master [localhost] {msandbox} (test) > SELECT * FROM test1;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

$HOME/sandboxes/rsandbox_mysql-5_7_13/s1

slave1 [localhost] {msandbox} (test) > SELECT * FROM test1;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

However replication of the session variable sql_mode does not work:

$HOME/sandboxes/rsandbox_mysql-5_7_13/m

master [localhost] {msandbox} (test) > SET @@session.sql_mode = '';
master [localhost] {msandbox} (test) > SELECT @@SESSION.sql_mode;
+--------------------+
| @@SESSION.sql_mode |
+--------------------+
|                    |
+--------------------+
1 row in set (0.00 sec)

$HOME/sandboxes/rsandbox_mysql-5_7_13/s1

slave1 [localhost] {msandbox} (test) > SELECT @@SESSION.sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@SESSION.sql_mode                                                                                                                        |
+-------------------------------------------------------------------------------------------------------------------------------------------+
|ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

I searched for related bugs in the MySQL Bug tracker, but did not find any issues.

I know that it is possible for the database administrator to set the variable on a global basis on each database server. However this is not an option at the moment. I need this feature as part of an OpenSource PHP framework, which should have as less requirements as possible.

Robert Blank
  • 1
  • `when parsing the binary log`. That'd only happen when you're replaying the log. doesn't say ANYTHING about honoring it in operational use. – Marc B Jul 25 '16 at 15:33
  • So you mean, that this sentence is about manually replaying the binary_log on the slave. This would be confusing, because the context is the "replication of variables", but of course there is a chance that some documentation can be confusing. – Robert Blank Jul 27 '16 at 12:35

0 Answers0