Provisioning blocked by mysql 8 credentials user error

[timmc-edx]

Early in the provisioning process, users get the following error:

Ensuring MySQL 8.0 databases and users exist...
+ docker-compose exec -T mysql80 bash -e -c 'mysql -uroot mysql'
ERROR 1396 (HY000) at line 2: Operation CREATE USER failed for 'credentials001'@'%'

Acceptance criteria:

• Provisioning no longer blocked by this error

Notes:

• MySQL doesn't give any sort of hint about why creation failed -- existing user? bad auth?
• This appears to have been introduced by feat: Added mysql 8 container in docker-compose #1097

[timmc-edx]

That PR introduces a new SQL file with commented out instructions on how to run it: docker-compose exec -T mysql80 bash -e -c "mysql -uroot mysql" < drop-mysql-user.sql -- running this does appear to allow the provisioning script to proceed. Not sure if/how/when it should be run during provisioning. Alternatively, maybe we are failing to destroy this container when we normally would.

[timmc-edx]

If I run make dev.remove-containers dev.up.mysql57+mysql80 and then make dev.shell.mysql57 or make dev.shell.mysql80 I find that the databases already contain the necessary users. And the two provisioning scripts differ in one important way: provision.sql (for 5.7) uses GRANT ALL ON credentials.* TO 'credentials001'@'%' IDENTIFIED BY 'password'; all as one statement, whereas provision-mysql80.sql has two statements:

CREATE USER 'credentials001'@'%' IDENTIFIED BY 'password';
GRANT ALL ON credentials.* TO 'credentials001'@'%';

Implicit vs. explicit user creation?

[timmc-edx]

OK yeah, auto-creation of users via GRANT statements was already deprecated as of 5.7, and doesn't exist as of 8.0. So we'll want to switch this over to CREATE USER IF NOT EXISTS.

[github-actions]

Follow-up checklist (for Arch-BOM usage)

• Is the issue flaky or consistent?
• Does it affect multiple people or multiple types of systems?
• Update the devstack troubleshooting documentation page if necessary
  • Do we need a new troubleshooting section?
  • Did a troubleshooting section already exist, but it wasn't easy to find given the symptoms?
  • If a recurring issue, should we ticket an automated resolution in place of the doc?

[timmc-edx]

#1112 should take care of it, though we may also want to figure out why those users are present in the first place.

[timmc-edx]

Answer: I didn't delete the old mysql80_volume, and must have provisioned before. The images (well really, the mounted volumes) do start off without users, but we don't have a way of cleaning up when resetting devstack. Filed an issue for that: #1113