cf unbind-service or delete-service-key fails for a MySQL 8.0 plan with a service broker 500/502 error

Products

• VMware Tanzu SQL

Issue/Introduction

As of MySQL 8.0.22, DROP USER will fail if the user is the definer of any stored objects. This includes Events, Routines, Triggers, and Views. For a MySQL 3.0 tile service instance created for MySQL 8.0, there is a user created when a service instance is bound to an app and also when a service key is created. It is a different user for each of these. If this user has created a stored object, and the stored object still exists, the unbind of the service instance and the delete of the service key will fail 500 or 502 error. For the "cf delete-service-key" case, this will look something like:

$ cf dsk smallDb8 smallDb8_key 

Really delete the service key smallDb8_key?> y
Deleting key smallDb8_key for service instance smallDb8 as admin...
FAILED
Server error, status code: 502, error code: 10001, message: Service broker failed to delete service binding for instance smallDb8: Service broker error: There was a problem completing your request. Please contact your operations team providing the following information: service: p.mysql, service-instance-guid: a584b54d-de7f-408c-aa0e-10b79b015512, broker-request-id: 3a5fd262-4e56-4928-806b-1b258e88a587, operation: unbind
$

Environment

Product Version: Other

Resolution

As a workaround, an operator can grant the SET_USER_ID privilege to the mysql-agent@localhost user. This will result in behavior similar to that seen with MySQL 5.7, but may result in orphaned objects. To do this, SSH into the service instance deployment's mysql instance(s), login to the database as the MySQL admin user, and run:

GRANT SET_USER_ID ON *.* TO 'mysql-agent'@'localhost';

To see stored objects created by the non system user, an operator can login to the database as the MySQL admin user and run the following four queries:

SELECT DISTINCT DEFINER, EVENT_NAME FROM INFORMATION_SCHEMA.EVENTS WHERE DEFINER NOT LIKE 'mysql.sys%';

SELECT DISTINCT DEFINER, ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE DEFINER NOT LIKE 'mysql.sys%';

SELECT DISTINCT DEFINER, TRIGGER_NAME FROM INFORMATION_SCHEMA.TRIGGERS WHERE DEFINER NOT LIKE 'mysql.sys%';

SELECT DISTINCT DEFINER, TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE DEFINER NOT LIKE 'mysql.sys%';

If any of these queries returns data for a User not in the mysql.user table, then this is an orphaned stored object. The user table can be queries with "select User from mysql.user;". It is possible to recreate the user owning a stored object so that the stored object remains accessible.


If consistent user names are needed because of objects that are specified with a specific DEFINER, then we suggest using the “Custom Username” feature of bindings. This is described in the documentation at https://docs.vmware.com/en/VMware-SQL-with-MySQL-for-Tanzu-Application-Service/3.0/mysql-for-tas/customize-access.html#create-custom-username-credentials-1


That is during “cf bind-service” or “cf create-serivce-key” an option can be passed to specify a specific database username:

cf bind-service APP_NAME SERVICE_INSTANCE_NAME -c '{ "username": "my-app-user" }'

This does not allow overriding any existing users and is limited to valid usernames for a MySQL database.


This is being investigated for improvement in the MySQL tile with regard to MySQL 8.0 service instances. At a minimum we will likely add this “SET_USER_ID” privilege to the mysql-agent user automatically for MySQL 8.0 instances to avoid this “unbind-service” problem.