Description:
When I create a cluster using the shell, if I use the shell to configure the local instance and let the shell creates the 'root'@'%' with the necessary grants, later I can see that the user is missing the EXECUTE on sys:
execute command denied to user 'root'@'%' for routine 'sys.version_major' (MySQL Error 1370)
This happens when I try to remove an instance using cluster.removeInstance()
How to repeat:
The full procedure to create a cluster from scratch (I do it on all servers part of the cluster):
[root@mysql1dc1 mysql]# mysqld --initialize-insecure -u mysql --datadir /var/lib/mysql
[root@mysql1dc1 mysql]# systemctl start mysqld
[root@mysql1dc1 mysql]# mysqlsh
MySQL Shell 8.0.4-rc
Copyright (c) 2016, 2018, 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.
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]: y
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]: 1
Password for new account:
Confirm password:
Validating instance...
The instance 'localhost:3306' is valid for Cluster usage
You can now use it in an InnoDB Cluster.
{
"status": "ok"
}
MySQL JS \c root@mysql1dc1:3306
Creating a session to 'root@mysql1dc1:3306'
Enter password:
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 132
Server version: 8.0.4-rc-log MySQL Community Server (GPL)
No default schema selected; type \use <schema> to set one.
Then on only one node I create the cluster:
MySQL mysql1dc1:3306 JS cluster=dba.createCluster('MyGroupDC1');
A new InnoDB cluster will be created on instance 'root@mysql1dc1:3306'.
Creating InnoDB cluster 'MyGroupDC1' on 'root@mysql1dc1:3306'...
Adding Seed Instance...
Cluster successfully created. Use Cluster.addInstance() to add MySQL instances.
At least 3 instances are needed for the cluster to be able to withstand up to
one server failure.
<Cluster:MyGroupDC1>
and I add instances:
MySQL mysql1dc1:3306 JS cluster.status()
{
"clusterName": "MyGroupDC1",
"defaultReplicaSet": {
"name": "default",
"primary": "mysql1dc1:3306",
"ssl": "REQUIRED",
"status": "OK_NO_TOLERANCE",
"statusText": "Cluster is NOT tolerant to any failures.",
"topology": {
"mysql1dc1:3306": {
"address": "mysql1dc1:3306",
"mode": "R/W",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
}
}
},
"groupInformationSourceMember": "mysql://root@mysql1dc1:3306"
}
MySQL mysql1dc1:3306 JS cluster.addInstance('root@mysql2dc1:3306')
A new instance will be added to the InnoDB cluster. Depending on the amount of
data on the cluster this might take from a few seconds to several hours.
Please provide the password for 'root@mysql2dc1:3306':
Adding instance to the cluster ...
The instance 'root@mysql2dc1:3306' was successfully added to the cluster.
... more intances ...
MySQL mysql1dc1:3306 JS cluster.status()
{
"clusterName": "MyGroupDC1",
"defaultReplicaSet": {
"name": "default",
"primary": "mysql1dc1:3306",
"ssl": "REQUIRED",
"status": "OK",
"statusText": "Cluster is ONLINE and can tolerate up to 2 failures.",
"topology": {
"mysql1dc1:3306": {
"address": "mysql1dc1:3306",
"mode": "R/W",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
},
"mysql2dc1:3306": {
"address": "mysql2dc1:3306",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
},
"mysql3dc1:3306": {
"address": "mysql3dc1:3306",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
},
"mysql4dc2:3306": {
"address": "mysql4dc2:3306",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
},
"mysql5dc2:3306": {
"address": "mysql5dc2:3306",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
},
"mysql6dc2:3306": {
"address": "mysql6dc2:3306",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
}
}
},
"groupInformationSourceMember": "mysql://root@mysql1dc1:3306"
}
But when I try to remove an instance:
MySQL mysql1dc1:3306 JS cluster.removeInstance('root@mysql6dc2:3306')
The instance will be removed from the InnoDB cluster. Depending on the
instance being the Seed or not, the Metadata session might become invalid.
If so, please start a new session to the Metadata Storage R/W instance.
execute command denied to user 'root'@'%' for routine 'sys.version_major' (MySQL Error 1370)
The grants are:
MySQL mysql1dc1:3306 SQL show grants\G
*************************** 1. row ***************************
Grants for root@%: GRANT SELECT, RELOAD, SHUTDOWN, PROCESS, FILE, SUPER, REPLICATION SLAVE, REPLICATION CLIENT, CREATE USER ON *.* TO `root`@`%` WITH GRANT OPTION
*************************** 2. row ***************************
Grants for root@%: GRANT INSERT, UPDATE, DELETE ON `mysql`.* TO `root`@`%` WITH GRANT OPTION
*************************** 3. row ***************************
Grants for root@%: GRANT ALL PRIVILEGES ON `mysql_innodb_cluster_metadata`.* TO `root`@`%` WITH GRANT OPTION
3 rows in set (0.00 sec)
Suggested fix:
Add the EXECUTE privilege for sys
Description: When I create a cluster using the shell, if I use the shell to configure the local instance and let the shell creates the 'root'@'%' with the necessary grants, later I can see that the user is missing the EXECUTE on sys: execute command denied to user 'root'@'%' for routine 'sys.version_major' (MySQL Error 1370) This happens when I try to remove an instance using cluster.removeInstance() How to repeat: The full procedure to create a cluster from scratch (I do it on all servers part of the cluster): [root@mysql1dc1 mysql]# mysqld --initialize-insecure -u mysql --datadir /var/lib/mysql [root@mysql1dc1 mysql]# systemctl start mysqld [root@mysql1dc1 mysql]# mysqlsh MySQL Shell 8.0.4-rc Copyright (c) 2016, 2018, 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. 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]: y 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]: 1 Password for new account: Confirm password: Validating instance... The instance 'localhost:3306' is valid for Cluster usage You can now use it in an InnoDB Cluster. { "status": "ok" } MySQL JS \c root@mysql1dc1:3306 Creating a session to 'root@mysql1dc1:3306' Enter password: Fetching schema names for autocompletion... Press ^C to stop. Your MySQL connection id is 132 Server version: 8.0.4-rc-log MySQL Community Server (GPL) No default schema selected; type \use <schema> to set one. Then on only one node I create the cluster: MySQL mysql1dc1:3306 JS cluster=dba.createCluster('MyGroupDC1'); A new InnoDB cluster will be created on instance 'root@mysql1dc1:3306'. Creating InnoDB cluster 'MyGroupDC1' on 'root@mysql1dc1:3306'... Adding Seed Instance... Cluster successfully created. Use Cluster.addInstance() to add MySQL instances. At least 3 instances are needed for the cluster to be able to withstand up to one server failure. <Cluster:MyGroupDC1> and I add instances: MySQL mysql1dc1:3306 JS cluster.status() { "clusterName": "MyGroupDC1", "defaultReplicaSet": { "name": "default", "primary": "mysql1dc1:3306", "ssl": "REQUIRED", "status": "OK_NO_TOLERANCE", "statusText": "Cluster is NOT tolerant to any failures.", "topology": { "mysql1dc1:3306": { "address": "mysql1dc1:3306", "mode": "R/W", "readReplicas": {}, "role": "HA", "status": "ONLINE" } } }, "groupInformationSourceMember": "mysql://root@mysql1dc1:3306" } MySQL mysql1dc1:3306 JS cluster.addInstance('root@mysql2dc1:3306') A new instance will be added to the InnoDB cluster. Depending on the amount of data on the cluster this might take from a few seconds to several hours. Please provide the password for 'root@mysql2dc1:3306': Adding instance to the cluster ... The instance 'root@mysql2dc1:3306' was successfully added to the cluster. ... more intances ... MySQL mysql1dc1:3306 JS cluster.status() { "clusterName": "MyGroupDC1", "defaultReplicaSet": { "name": "default", "primary": "mysql1dc1:3306", "ssl": "REQUIRED", "status": "OK", "statusText": "Cluster is ONLINE and can tolerate up to 2 failures.", "topology": { "mysql1dc1:3306": { "address": "mysql1dc1:3306", "mode": "R/W", "readReplicas": {}, "role": "HA", "status": "ONLINE" }, "mysql2dc1:3306": { "address": "mysql2dc1:3306", "mode": "R/O", "readReplicas": {}, "role": "HA", "status": "ONLINE" }, "mysql3dc1:3306": { "address": "mysql3dc1:3306", "mode": "R/O", "readReplicas": {}, "role": "HA", "status": "ONLINE" }, "mysql4dc2:3306": { "address": "mysql4dc2:3306", "mode": "R/O", "readReplicas": {}, "role": "HA", "status": "ONLINE" }, "mysql5dc2:3306": { "address": "mysql5dc2:3306", "mode": "R/O", "readReplicas": {}, "role": "HA", "status": "ONLINE" }, "mysql6dc2:3306": { "address": "mysql6dc2:3306", "mode": "R/O", "readReplicas": {}, "role": "HA", "status": "ONLINE" } } }, "groupInformationSourceMember": "mysql://root@mysql1dc1:3306" } But when I try to remove an instance: MySQL mysql1dc1:3306 JS cluster.removeInstance('root@mysql6dc2:3306') The instance will be removed from the InnoDB cluster. Depending on the instance being the Seed or not, the Metadata session might become invalid. If so, please start a new session to the Metadata Storage R/W instance. execute command denied to user 'root'@'%' for routine 'sys.version_major' (MySQL Error 1370) The grants are: MySQL mysql1dc1:3306 SQL show grants\G *************************** 1. row *************************** Grants for root@%: GRANT SELECT, RELOAD, SHUTDOWN, PROCESS, FILE, SUPER, REPLICATION SLAVE, REPLICATION CLIENT, CREATE USER ON *.* TO `root`@`%` WITH GRANT OPTION *************************** 2. row *************************** Grants for root@%: GRANT INSERT, UPDATE, DELETE ON `mysql`.* TO `root`@`%` WITH GRANT OPTION *************************** 3. row *************************** Grants for root@%: GRANT ALL PRIVILEGES ON `mysql_innodb_cluster_metadata`.* TO `root`@`%` WITH GRANT OPTION 3 rows in set (0.00 sec) Suggested fix: Add the EXECUTE privilege for sys