| Bug #86155 | InnoDB Cluster doen not work with sql_mode="ORACLE,TRADITIONAL" | ||
|---|---|---|---|
| Submitted: | 2 May 2017 6:56 | Modified: | 30 Jun 2017 9:58 |
| Reporter: | Ted Wennmark | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | Shell AdminAPI InnoDB Cluster / ReplicaSet | Severity: | S2 (Serious) |
| Version: | 1.0.9 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | sql_mode mysql_innodb_cluster_metadata | ||
[3 May 2017 6:06]
Ted Wennmark
Problem is that sql_mode ORACLE contains ANSI_QUOTES and SQL statement being executed as part of populating InnoDB Cluster meta data tables sometime uses ' and sometime uses ", by replacing " with ' all works fine, see bellow. Default sql_mode in MySQL 5.7.18: mysql-sql> select @@sql_mode\G *************************** 1. row *************************** @@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) Problem query: mysql-sql> SELECT COUNT(*) as count FROM mysql_innodb_cluster_metadata.instances WHERE replicaset_id = 1 AND addresses->"$.mysqlClassic" = 'Node1:63310'; +-------+ | count | +-------+ | 1 | +-------+ 1 row in set (0.00 sec) mysql-sql> set sql_mode="ORACLE"; Query OK, 0 rows affected (0.00 sec) mysql-sql> SELECT COUNT(*) as count FROM mysql_innodb_cluster_metadata.instances WHERE replicaset_id = 1 AND addresses->"$.mysqlClassic" = 'Node1:63310'; ERROR: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"$.mysqlClassic" = 'Node1:63310'' at line 1 Change "$.mysqlClassic" to '$.mysqlClassic' and all works: mysql-sql> SELECT COUNT(*) as count FROM mysql_innodb_cluster_metadata.instances WHERE replicaset_id = 1 AND addresses->'$.mysqlClassic' = 'Node1:63310'; +-------+ | count | +-------+ | 1 | +-------+ 1 row in set (0.00 sec) I see no problems only using ' for strings and then it should work independent of sql_mode configured.
[3 May 2017 6:23]
MySQL Verification Team
Hello Ted, Thank you for the report. Verified as described. Thanks, Umesh
[30 Jun 2017 9:58]
David Moss
Posted by developer: Thanks for your feedback Ted, this was fixed in bug#25966057.
Description: MySQL Shell fails to create cluster when sql_mode="ORACLE,TRADITIONAL" I have installed latest version of MySQL Shell and MySQL. [ted@Node1 ~]$ mysqlsh --version MySQL Shell Version 1.0. [ted@Node1 ~]$ mysql --version mysql Ver 14.14 Distrib 5.7.18, for linux-glibc2.5 (x86_64) using EditLine wrapper If I remove sql_mode="ORACLE,TRADITIONAL" everything works! Information in mysqlsh debug log ==================================== ..... 2017-05-02 06:51:13: Debug: DBA: execute_sql('DROP USER IF EXISTS mysql_innodb_cluster_rp468461848@'%'' 2017-05-02 06:51:13: Debug: DBA: execute_sql('CREATE USER IF NOT EXISTS mysql_innodb_cluster_rp468461848@'%' IDENTIFIED BY '****************'' 2017-05-02 06:51:13: Debug: DBA: execute_sql('GRANT REPLICATION SLAVE ON *.* to mysql_innodb_cluster_rp468461848@'%'' 2017-05-02 06:51:13: Debug: Created replication user 'mysql_innodb_cluster_rp468461848@'%'' 2017-05-02 06:51:13: Debug: DBA: execute_sql('INSERT INTO mysql_innodb_cluster_metadata.clusters (cluster_name, description, options, attributes) VALUES ('mycluster', 'Defaul t Cluster', '{\"adminType\":\"local\"}', '{\"default\":true}')' 2017-05-02 06:51:13: Debug: DBA: execute_sql('INSERT INTO mysql_innodb_cluster_metadata.replicasets (cluster_id, replicaset_type, topology_type, replicaset_name, active, attr ibutes) VALUES (1, 'gr', 'pm', 'default', 1, IF('0', JSON_OBJECT('adopted', 'true'), '{}'))' 2017-05-02 06:51:13: Debug: DBA: execute_sql('UPDATE mysql_innodb_cluster_metadata.clusters SET default_replicaset = 1 WHERE cluster_id = 1' 2017-05-02 06:51:13: Debug: DBA: execute_sql('SELECT COUNT(*) as count FROM mysql_innodb_cluster_metadata.instances WHERE replicaset_id = 1' 2017-05-02 06:51:13: Debug: DBA: execute_sql('SELECT COUNT(*) as count FROM mysql_innodb_cluster_metadata.instances WHERE replicaset_id = 1 AND addresses->"$.mysqlClassic" = 'Node1:63310'' 2017-05-02 06:51:13: Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"$.mysqlCla ssic" = 'Node1:63310'' at line 1 2017-05-02 06:51:13: Debug: MySQL Error (1064): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to u se near '"$.mysqlClassic" = 'Node1:63310'' at line 1 2017-05-02 06:51:13: Debug: Removing replication user 'mysql_innodb_cluster_rp468461848@'%'' 2017-05-02 06:51:13: Debug: DBA: execute_sql('DROP USER IF EXISTS mysql_innodb_cluster_rp468461848@'%'' 2017-05-02 06:51:13: Error: Dba.createCluster: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to us e near '"$.mysqlClassic" = 'Node1:63310'' at line 1 ..... How to repeat: mysqlsh --log-level=debug3 mysql-js> shell.connect('ted@Node1:63310'); mysql-js> dba.checkInstanceConfiguration('ted@Node1:63310'); mysql-js> dba.configureLocalInstance('ted@Node1:63310',{password:'ted', mycnfPath:'/home/ted/my.cnf'}); mysql-js> var cluster=dba.createCluster('mycluster'); A new InnoDB cluster will be created on instance 'ted@Node1:63310'. Creating InnoDB cluster 'mycluster' on 'ted@Node1:63310'... Dba.createCluster: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"$.mysqlClassic" = 'Node1:63310'' at line 1 (MySQL Error 1064) Suggested fix: Create standard SQL when creating and populating mysql.mysql_innodb_cluster_metadata tables with data that is not affected by sql_mode settings. Detect this limitation in dba.checkInstanceConfiguration(...) Workaround: If you disable sql_mode="ORACLE,TRADITIONAL" when creating cluster you can later on add setting again once cluster is up and running.