Bug #87300 | mysql-shell dba.checkInstanceConfiguration() | ||
---|---|---|---|
Submitted: | 3 Aug 2017 10:18 | Modified: | 9 Nov 2017 11:24 |
Reporter: | forthedream for | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | Shell General / Core Client | Severity: | S2 (Serious) |
Version: | mysqlsh Ver 1.0.10 for Linux on x86_64 | OS: | Any |
Assigned to: | CPU Architecture: | Any |
[3 Aug 2017 10:18]
forthedream for
[8 Aug 2017 12:59]
MySQL Verification Team
Hello! Thank you for the report. I'm not seeing the reported issue while trying at my end with default settings: ==== -- build used mysql-5.7.19-linux-glibc2.12-x86_64.tar.gz mysql-shell-1.0.10-linux-glibc2.12-x86-64bit.tar.gz -- conf in use [mysqld] socket=/tmp/mysql_ushastry.sock basedir=/export/umesh/server/binaries/InnoDBCluster/mysql-5.7.19-linux-glibc2.12-x86_64 datadir=/export/umesh/server/binaries/InnoDBCluster/mysql-5.7.19-linux-glibc2.12-x86_64/85567 log_slave_updates = ON server_id = 598492703 relay_log_info_repository = TABLE master_info_repository = TABLE transaction_write_set_extraction = XXHASH64 binlog_format = ROW disabled_storage_engines = MyISAM,BLACKHOLE,FEDERATED,CSV,ARCHIVE report_port = 3306 binlog_checksum = NONE enforce_gtid_consistency = ON log_bin gtid_mode = ON group_replication_start_on_boot = OFF group_replication = ON rm -rf 87300 bin/mysqld --initialize-insecure --basedir=$PWD --datadir=$PWD/87300 -v bin/mysqld --defaults-file=./my.cnf --basedir=$PWD --datadir=$PWD/87300 --core-file --socket=/tmp/mysql_ushastry.sock --port=3306 --log-error=$PWD/87300/log.err 2>&1 & [umshastr@hod03]/export/umesh/server/binaries/InnoDBCluster/mysql-shell-1.0.10-linux-glibc2.12-x86-64bit: bin/mysqlsh MySQL Shell 1.0.10 Copyright (c) 2016, 2017, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type '\help' or '\?' for help; '\quit' to exit. Currently in JavaScript mode. Use \sql to switch to SQL mode and execute queries. mysql-js> dba.configureLocalInstance() Please provide the password for 'root@localhost:3306': Detecting the configuration file... Found configuration file at standard location: /etc/my.cnf Do you want to modify this file? [Y|n]: [Y|n]: n Default file not found at the standard locations. Please specify the path to the MySQL configuration file: /export/umesh/server/binaries/InnoDBCluster/mysql-5.7.19-linux-glibc2.12-x86_64/my.cnf MySQL user 'root' cannot be verified to have access to other hosts in the network. 1) Create root@% with necessary grants 2) Create account with different name 3) Continue without creating account 4) Cancel Please select an option [1]: 3 Validating instance... The instance 'localhost:3306' is valid for Cluster usage You can now use it in an InnoDB Cluster. { "status": "ok" } mysql-js> dba.checkInstanceConfiguration('root@localhost:3306') Please provide the password for 'root@localhost:3306': Validating instance... The instance 'localhost:3306' is valid for Cluster usage { "status": "ok" } mysql-js> Please could you share the exact conf file used in your environment to see if that helps here to reproduce the issue? Thanks, Umesh
[9 Aug 2017 6:37]
OpenSource DBA
Hi Umesh, I can repeat the same behavior. The user account used to administer an instance is not root in my case though. It is as per below link. Ref: https://dev.mysql.com/doc/refman/5.7/en/mysql-innodb-cluster-working-with-production-deplo... Test Case: [root@innodb1 ~]# mysql -V mysql Ver 14.14 Distrib 5.7.18, for Linux (x86_64) using EditLine wrapper [root@innodb1 ~]# --Issue [root@innodb1 ~]# mysqlsh MySQL Shell 1.0.10 Copyright (c) 2016, 2017, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type '\help' or '\?' for help; '\quit' to exit. Currently in JavaScript mode. Use \sql to switch to SQL mode and execute queries. mysql-js> mysql-js> dba.verbose = 1 1 mysql-js> dba.checkInstanceConfiguration('cluster_admin@localhost:3306') Please provide the password for 'cluster_admin@localhost:3306': Validating instance... =========================== MySQL Provision Output =========================== Enter the password for server (cluster_admin@localhost:3306): Running check command. Checking Group Replication prerequisites. * Comparing options compatibility with Group Replication... PASS Server configuration is compliant with the requirements. * Checking server version... PASS Server is 5.7.18 * Checking that server_id is unique... PASS The server_id is valid. * Checking compatibility of Multi-Threaded Slave settings... PASS Multi-Threaded Slave settings are compatible with Group Replication. * Checking compliance of existing tables... FAIL ERROR: 1 table(s) do not have a Primary Key or Primary Key Equivalent (non-null unique key). sys.sys_config Group Replication requires tables to use InnoDB and have a PRIMARY KEY or PRIMARY KEY Equivalent (non-null unique key). Tables that do not follow these requirements will be readable but not updateable when used with Group Replication. If your applications make updates (INSERT, UPDATE or DELETE) to these tables, ensure they use the InnoDB storage engine and have a PRIMARY KEY or PRIMARY KEY Equivalent. You can retry this command with the --allow-non-compatible-tables option if you'd like to enable Group Replication ignoring this warning. ERROR: Error checking instance: The operation could not continue due to the following requirements not being met: Non-compatible tables found in database. ============================================================================== The instance 'localhost:3306' is not valid for Cluster usage. The following issues were encountered: - 1 table(s) do not have a Primary Key or Primary Key Equivalent (non-null unique key). Please fix these issues and try again. { "errors": [ "1 table(s) do not have a Primary Key or Primary Key Equivalent (non-null unique key)." ], "restart_required": false, "status": "error" } mysql-js> \q Bye! --sys.sys_config table details & grants [root@innodb1 ~]# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 15 Server version: 5.7.18-log MySQL Community Server (GPL) Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> desc sys.sys_config; +----------+--------------+------+-----+-------------------+-----------------------------+ | Field | Type | Null | Key | Default | Extra | +----------+--------------+------+-----+-------------------+-----------------------------+ | variable | varchar(128) | NO | PRI | NULL | | | value | varchar(128) | YES | | NULL | | | set_time | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | | set_by | varchar(128) | YES | | NULL | | +----------+--------------+------+-----+-------------------+-----------------------------+ 4 rows in set (0.00 sec) mysql> show grants for cluster_admin; +--------------------------------------------------------------------------------------------------------------------------------------------------+ | Grants for cluster_admin@% | +--------------------------------------------------------------------------------------------------------------------------------------------------+ | GRANT RELOAD, SHUTDOWN, PROCESS, FILE, SUPER, REPLICATION SLAVE, REPLICATION CLIENT, CREATE USER ON *.* TO 'cluster_admin'@'%' WITH GRANT OPTION | | GRANT SELECT, INSERT, UPDATE, DELETE ON `mysql`.* TO 'cluster_admin'@'%' WITH GRANT OPTION | | GRANT SELECT ON `performance_schema`.* TO 'cluster_admin'@'%' WITH GRANT OPTION | | GRANT ALL PRIVILEGES ON `mysql_innodb_cluster_metadata`.* TO 'cluster_admin'@'%' WITH GRANT OPTION | +--------------------------------------------------------------------------------------------------------------------------------------------------+ 4 rows in set (0.00 sec) mysql> \q Bye --Config [root@innodb1 ~]# cat /etc/my.cnf # For advice on how to change settings please see # http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html [mysqld] # # Remove leading # and set to the amount of RAM for the most important data # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%. # innodb_buffer_pool_size = 128M # # Remove leading # to turn on a very important data integrity option: logging # changes to the binary log between backups. # log_bin # # Remove leading # to set options mainly useful for reporting servers. # The server defaults are faster for transactions and fast SELECTs. # Adjust sizes as needed, experiment to find the optimal values. # join_buffer_size = 128M # sort_buffer_size = 2M # read_rnd_buffer_size = 2M datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid #InnoBD Cluster server-id=1 log-bin log-slave-updates = ON binlog-format=row gtid-mode=ON enforce-gtid-consistency=ON master-info-repository=TABLE relay-log-info-repository=TABLE transaction-write-set-extraction=XXHASH64 slave-parallel-workers=2 slave-preserve-commit-order=1 slave-parallel-type=LOGICAL_CLOCK slave-preserve-commit-order=1 disabled_storage_engines = MyISAM,BLACKHOLE,FEDERATED,CSV,ARCHIVE report_port = 3306 binlog_checksum = NONE [root@innodb1 ~]# Regards, Sameer
[9 Aug 2017 7:24]
forthedream for
hi Umesh Shastry when i use dba.checkInstanceConfiguration('root@localhost:3307') ,sucess but when i use dba.checkInstanceConfiguration('root@10.201.143.242:3307') ,failed localhost ip is 10.201.143.242 like this mysql-js> dba.configureLocalInstance('root@localhost:3307') Please provide the password for 'root@localhost:3307': Detecting the configuration file... Found configuration file at standard location: /etc/my.cnf Do you want to modify this file? [Y|n]: [Y|n]: n Default file not found at the standard locations. Please specify the path to the MySQL configuration file: /mysql/3307.cnf Validating instance... The instance 'localhost:3307' is valid for Cluster usage You can now use it in an InnoDB Cluster. { "status": "ok" } mysql-js> dba.checkInstanceConfiguration('root@10.201.143.242:3307') Please provide the password for 'root@10.201.143.242:3307': Validating instance... =========================== MySQL Provision Output =========================== Enter the password for server (root@10.201.143.242:3307): Running check command. Checking Group Replication prerequisites. * Comparing options compatibility with Group Replication... PASS Server configuration is compliant with the requirements. * Checking server version... PASS Server is 5.7.19 * Checking that server_id is unique... PASS The server_id is valid. * Checking compatibility of Multi-Threaded Slave settings... PASS Multi-Threaded Slave settings are compatible with Group Replication. * Checking compliance of existing tables... FAIL ERROR: 11 table(s) do not have a Primary Key or Primary Key Equivalent (non-null unique key). sys.sys_config, test.sbtest1, test.sbtest10, test.sbtest2, test.sbtest3, test.sbtest4, test.sbtest5, test.sbtest6, test.sbtest7, test.sbtest8, test.sbtest9 Group Replication requires tables to use InnoDB and have a PRIMARY KEY or PRIMARY KEY Equivalent (non-null unique key). Tables that do not follow these requirements will be readable but not updateable when used with Group Replication. If your applications make updates (INSERT, UPDATE or DELETE) to these tables, ensure they use the InnoDB storage engine and have a PRIMARY KEY or PRIMARY KEY Equivalent. You can retry this command with the --allow-non-compatible-tables option if you'd like to enable Group Replication ignoring this warning. ERROR: Error checking instance: The operation could not continue due to the following requirements not being met: Non-compatible tables found in database. ============================================================================== The instance '10.201.143.242:3307' is not valid for Cluster usage. The following issues were encountered: - 11 table(s) do not have a Primary Key or Primary Key Equivalent (non-null unique key). Please fix these issues and try again. { "errors": [ "11 table(s) do not have a Primary Key or Primary Key Equivalent (non-null unique key)." ], "restart_required": false, "status": "error" } mysql-js> dba.verbose = 1 1 mysql-js> dba.checkInstanceConfiguration('root@localhost:3307') Please provide the password for 'root@localhost:3307': Validating instance... =========================== MySQL Provision Output =========================== Enter the password for server (root@localhost:3307): Running check command. Checking Group Replication prerequisites. * Comparing options compatibility with Group Replication... PASS Server configuration is compliant with the requirements. * Checking server version... PASS Server is 5.7.19 * Checking that server_id is unique... PASS The server_id is valid. * Checking compatibility of Multi-Threaded Slave settings... PASS Multi-Threaded Slave settings are compatible with Group Replication. * Checking compliance of existing tables... PASS ============================================================================== The instance 'localhost:3307' is valid for Cluster usage { "status": "ok" } my conf file is [mysqld] datadir=/mysql/3307 socket=/mysql/3307/mysql.sock basedir = /usr/local/mysql #skip-grant-tables port = 3307 user=mysql log-error=/mysql/3307/mysqld.log pid-file=/mysql/3307/mysqld.pid server_id=22 slow_query_log=1 long_query_time=3 slow_query_log_file=/mysql/3307/slow-query.log binlog_format = row gtid_mode = ON enforce-gtid-consistency = ON log-slave-updates = ON log-bin=53_3307-bin relay_log =/mysql/3307/mysql-relay-bin log_bin_trust_function_creators=1 #innodb-buffer-pool-size=2048M #max-connections=1000 #innodb_log_file_size=200M expire_logs_days=7 sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES relay_log_info_repository = TABLE master_info_repository = TABLE transaction_write_set_extraction = XXHASH64 disabled_storage_engines = MyISAM,BLACKHOLE,FEDERATED,CSV,ARCHIVE report_port = 3307 binlog_checksum = NONE group_replication_start_on_boot = OFF group_replication = ON
[10 Aug 2017 6:11]
MySQL Verification Team
Thank you for replying back. It seems to me that due to lack of privileges false error is reported here i.e even though sys.sys_config has PK/Key etc but still it complains instead of actual privileges issue. ## Created cluster specific user CREATE USER 'cluster_admin'@'%'; GRANT RELOAD, SHUTDOWN, PROCESS, FILE, SUPER, REPLICATION SLAVE, REPLICATION CLIENT, CREATE USER ON *.* TO 'cluster_admin'@'%' WITH GRANT OPTION; GRANT SELECT, INSERT, UPDATE, DELETE ON `mysql`.* TO 'cluster_admin'@'%' WITH GRANT OPTION; GRANT SELECT ON `performance_schema`.* TO 'cluster_admin'@'%' WITH GRANT OPTION; GRANT ALL PRIVILEGES ON `mysql_innodb_cluster_metadata`.* TO 'cluster_admin'@'%' WITH GRANT OPTION ; [umshastr@hod03]/export/umesh/server/binaries/InnoDBCluster/mysql-shell-1.0.10-linux-glibc2.12-x86-64bit: bin/mysqlsh MySQL Shell 1.0.10 Copyright (c) 2016, 2017, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type '\help' or '\?' for help; '\quit' to exit. Currently in JavaScript mode. Use \sql to switch to SQL mode and execute queries. mysql-js> dba.checkInstanceConfiguration('cluster_admin@localhost:3306') Please provide the password for 'cluster_admin@localhost:3306': Validating instance... The instance 'localhost:3306' is not valid for Cluster usage. The following issues were encountered: - 1 table(s) do not have a Primary Key or Primary Key Equivalent (non-null unique key). Please fix these issues and try again. { "errors": [ "1 table(s) do not have a Primary Key or Primary Key Equivalent (non-null unique key)." ], "restart_required": false, "status": "error" } mysql-js> mysql-js> dba.verbose = 1 1 mysql-js> mysql-js> dba.checkInstanceConfiguration('cluster_admin@localhost:3306') Please provide the password for 'cluster_admin@localhost:3306': Validating instance... =========================== MySQL Provision Output =========================== Enter the password for server (cluster_admin@localhost:3306): Running check command. Checking Group Replication prerequisites. * Comparing options compatibility with Group Replication... PASS Server configuration is compliant with the requirements. * Checking server version... PASS Server is 5.7.19 * Checking that server_id is unique... PASS The server_id is valid. * Checking compatibility of Multi-Threaded Slave settings... PASS Multi-Threaded Slave settings are compatible with Group Replication. * Checking compliance of existing tables... FAIL ERROR: 1 table(s) do not have a Primary Key or Primary Key Equivalent (non-null unique key). sys.sys_config Group Replication requires tables to use InnoDB and have a PRIMARY KEY or PRIMARY KEY Equivalent (non-null unique key). Tables that do not follow these requirements will be readable but not updateable when used with Group Replication. If your applications make updates (INSERT, UPDATE or DELETE) to these tables, ensure they use the InnoDB storage engine and have a PRIMARY KEY or PRIMARY KEY Equivalent. You can retry this command with the --allow-non-compatible-tables option if you'd like to enable Group Replication ignoring this warning. ERROR: Error checking instance: The operation could not continue due to the following requirements not being met: Non-compatible tables found in database. ============================================================================== The instance 'localhost:3306' is not valid for Cluster usage. The following issues were encountered: - 1 table(s) do not have a Primary Key or Primary Key Equivalent (non-null unique key). Please fix these issues and try again. { "errors": [ "1 table(s) do not have a Primary Key or Primary Key Equivalent (non-null unique key)." ], "restart_required": false, "status": "error" } mysql-js> root@localhost [(none)]> show create table sys.sys_config\G *************************** 1. row *************************** Table: sys_config Create Table: CREATE TABLE `sys_config` ( `variable` varchar(128) NOT NULL, `value` varchar(128) DEFAULT NULL, `set_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `set_by` varchar(128) DEFAULT NULL, PRIMARY KEY (`variable`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.01 sec) ###### GRANT 'ALL' to cluster_admin@localhost root@localhost [(none)]> GRANt ALL ON *.* TO 'cluster_admin'@'%'; Query OK, 0 rows affected (0.00 sec) ### Attempt to confirm mysql-js> mysql-js> dba.checkInstanceConfiguration('cluster_admin@localhost:3306') Please provide the password for 'cluster_admin@localhost:3306': Validating instance... =========================== MySQL Provision Output =========================== Enter the password for server (cluster_admin@localhost:3306): Running check command. Checking Group Replication prerequisites. * Comparing options compatibility with Group Replication... PASS Server configuration is compliant with the requirements. * Checking server version... PASS Server is 5.7.19 * Checking that server_id is unique... PASS The server_id is valid. * Checking compatibility of Multi-Threaded Slave settings... PASS Multi-Threaded Slave settings are compatible with Group Replication. * Checking compliance of existing tables... PASS * Verifying Group Replication plugin for server 'localhost:3306' ... WARNING: The group_replication plugin has not been installed/loaded in 'localhost:3306' Group Replication plugin: Not loaded ============================================================================== The instance 'localhost:3306' is valid for Cluster usage { "status": "ok" }
[5 Sep 2017 13:07]
Shrikant Singh
I am facing the same issue, tried different combination of users like 'root@%' or 'clusteradmin@%' but every time facing the same table " ERROR: 1 table(s) do not have a Primary Key" when creating cluster with the command "var cluster = dba.createCluster('user@host:port')". All though dba.checkInstanceConfiguraion('user@host:port') is successful if I use 'root@localhost:3306' but failing when using 'root@IP:3306'. To overcome grant related issue I have create user "Grant all on *.* to 'root@'%' with grant option" on two machines for the cluster. With this user cluster is created but when adding Instance to the cluster it is failing with following error: "Group Replication join failed. ERROR: Error joining instance to cluster: '10.1.10.236:3306' - Query failed. 3092 (HY000): The server is not configured properly to be an active member of the group. Please see more details on error log.. Query: START group_replication (RuntimeError)" I am using ========== CentOS Linux release 7.3.1611 mysql Ver 14.14 Distrib 5.7.19, for Linux (x86_64) mysqlsh Ver 1.0.10 for Linux on x86_64 Either it is a bug or documentation is missing some information.
[19 Sep 2017 16:40]
Daisuke Shimizu
I think that you probably need the following user accounts. GRANT SELECT ON sys.* TO your_user@'%' WITH GRANT OPTION; When using "dba.configureLocalInstance ()" to create InnoDB cluster administration account, I think that the InnoDB cluster administration account may not have privilege to "sys schema".
[9 Nov 2017 11:24]
David Moss
Posted by developer: Thank you for your feedback, this has been fixed in upcoming versions and the following was added to the 1.0.11 / 8.0.4 changelog: When using the dba.checkInstanceConfiguration() and dba.configurelocalinstance() commands, the account being used was not being checked if it had enough privileges to actually execute the command. The fix ensures that account has the required privileges before proceeding. This also required a change of the privileges given to clusterAdmin users.