MySQL for TAS - ERROR 1227 (42000): Access denied

Products

• VMware Tanzu SQL

Issue/Introduction

This KB article addresses the issue of running into a "1227 (42000): Access Denied" error when trying to run commands on a MySQL service instance from a Jumpbox (or Ops Man VM).

A good example of this could be trying to run a mysql restore from that MySQL service instance on a Jumpbox (Or from Ops Man VM) and getting this error.

Refer to this documentation for the complete MySQL restore process for more context. 

Example use case: 

mysql --user=USERNAME --password=PASSWORD --host=MYSQL-IP < BACKUP-FILE 

USERNAME is the username retrieved from the output of cf service-key.
PASSWORD is the password retrieved from the output of cf service-key.
MYSQL-IP is the MySQL service instance IP address.
BACKUP-FILE is the name of your backup artifact.


Error: 

ERROR 1227 (42000)
at line 48633: Access denied; you need (at least one of) the SUPER privileges) for this operation

Environment

Product Version: 2.9

Resolution

The resolution to this issue lies in granting permission to the user that is trying to run the MySQL command. In our case, this is a MySQL restore, and the service key user for the MySQL service instance needs to have admin permissions to complete the MySQL restore or running any other MySQL commands.

Refer to this documentation for more details regarding the MySQL restore process

As a resolution, we can grant admin permissions to the service key user by following the steps below: 

STEP 1
Log into Apps Manager

STEP 2
Browse to the space and org that houses the MySQL service instance.

STEP 3
Find the Service key associated with the MySQL service instance. If not, refer to this documentation to create a service key for the MySQL service instance. 

STEP 4
After the MySQL service instance and corresponding service key has been created or located, take note of the username and password fields in the service key output: 

  
We can also find the username field in our service key by running the CF command cf service-key $MYSQL_SERVICE_INSTANCE_NAME $SERVICE_KEY_NAME. As we can see in the above screenshot, our username is 061c6################. 

The $MYSQL_SERVICE_INSTANCE_NAME can be found by running the command cf services | grep p.mysql. This command will print out a list of service instances that are using the p.mysql offering. As we can see below, our mysql2 service instance appears in the list, and this is the name we will be using in the following cf service-key command. For our example, our $MYSQL_SERVICE_INSTANCE_NAME is mysql2

The $SERVICE_KEY_NAME is the name of the service key that was created for the MySQL service. In our case, our $SERVICE_KEY_NAME is mysql-2. We can also reference the screenshot from Apps Manager as well and see that our $SERVICE_KEY_NAME is indeed mysql-2. 

We can even check the service keys that are associated with our MySQL service instance mysql2 via the CF CLI, and we see that service key mysql-2 is associated with MySQL service instance mysql2: 

We can now print out the contents of the service key mysql-2 in the CF CLI as well by running the command cf service-key mysql2 mysql-2: 

ubuntu@ubuntu:~$ cf service-key mysql2 mysql-2
Getting key mysql-2 for service instance mysql2 as admin...

{
  "credentials": {
    "hostname": "xxxxxxxxxxxxxxxxxxxx.mysql.service.internal",
    "jdbcUrl": "jdbc:mysql://xxxxxxxxxxxxxxxxxxx.mysql.service.internal:3306/service_instance_db?user=061c6c3xxxxxxxxxxxxxxxxxx&password=xxxxxxxxxxxxxxxxxxx&sslMode=VERIFY_IDENTITY&useSSL=true&requireSSL=true&enabledTLSProtocols=TLSv1.2&serverSslCert=/etc/ssl/certs/ca-certificates.crt",
    "name": "############",
    "password": "#################",
    "port": 3306,
    "tls": {
      "cert": {
        "ca": "-----BEGIN CERTIFICATE-----\nxxxxxxxxxxxxxxxxxxxxxx\n-----END CERTIFICATE-----\n"
      }
    },
    "uri": "mysql://xxxxxxxxxxxxxxxxxxxxxxxxxxxxx:[email protected]:3306/service_instance_db?reconnect=true",
    "username": "061c6####################"
  }
}

 

STEP 5

From here, we can work towards finding our $MYSQL_SERVICE_DEPLOYMENT_NAME (Which is the deployment name of  MySQL service instance found in step 4) for MySQL service mysql2 by running the command cf service $MYSQL_SERVICE_INSTANCE_NAME, and grabbing the GUID. In our example below, we see that our GUID begins with 9893. We will need this GUID to find the $MYSQL_SERVICE_DEPLOYMENT_NAME. 

One additional step we have to do is to search for the GUID inside of our list of bosh deployments. The $MYSQL_SERVICE_DEPLOYMENT_NAME will contain our GUID that starts with 9893. To do this, we can run the command bosh deps | grep 9893:

As we can see, our $MYSQL_SERVICE_DEPLOYMENT_NAME is service-instance_9893xxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx

STEP 6
We can now SSH into the MySQL service instance VM. 

bosh -d $MYSQL_SERVICE_DEPLOYMENT_NAME ssh 

# In our case, this would be this command:
bosh -d service-instance_9893xxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx ssh

STEP 7
Log into the MySQL database as the admin user:

# Change to root user 
sudo -i 

# Initiate MySQL as admin user
mysql --defaults-file=/var/vcap/jobs/mysql/config/mylogin.cnf

STEP 8
Confirm that the service key username exists as a user in the user table in the mysql database, and grant Admin permissions to the service key user. In our case, this is user 061c6######################## per the username grabbed from step 4. 

# Use mysql table
use mysql; 

# Confirm that the service key user exists in the user table
select * from user where User='061c6########################';

# Grant admin privileges to the service key user.
GRANT ALL ON *.* TO '061c6####################'@'%' WITH GRANT OPTION;

STEP 9
Return to the jump box VM or the Ops Man VM, and attempt to run the MySQL restore command referenced in this documentation . The initial error should no longer appear.

NOTE: We can substitute the --user variable with the service key username and the --password with the service key password we grabbed in step 4. 

NOTE: We can grab the MYSQL-IP value by taking note of our service instance deployment name we gathered from step 6, and list the VMs within that deployment to display the IP address. In our case, we can run this following command: bosh -d service-instance_9893xxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx vms. In our case, the MYSQL-IP value that we use in the following command would be 10.225.x.x

Now, we can run our MySQL command targeted that MySQL service instance

# MySQL Service instance restore
mysql --user=USERNAME --password=PASSWORD --host=MYSQL-IP < BACKUP-FILE 

# Running general command as an example
mysql --user=USERNAME --password=PASSWORD --host=MYSQL-IP --silent --execute='show databases'