Intermittent "Access denied" errors

[yashacb]

Hi Proxysql team,
I'm trying out Proxysql(v2.6.2) on GCP(MySQL v8.0.32) and sometimes, some of our E2E tests fail with the following error:

Access denied for user 'appuser'@'%' to database '<database name>'

I am able to reproduce this issue consistently in our GCP environment (but not when using proxysql in docker and mysql in docker) using a simple Python script that launches 5 threads where each thread does the following in a loop:

1. Create a database with a random name using the root user.
2. Assign SELECT, INSERT, UPDATE, DELETE permissions to the appuser using the GRANT command.
3. Run a SELECT query using the appuser.
4. Drop the database created in step 1.

The third step fails always fails in some of the threads and this intermittent error doesn't show up if multiplexing is disabled.
I'm attaching the proxysql log, but I wasn't able to find much from it. Monitor was disabled, but the error comes up even if monitor is enabled.
This is the configuration of the mysql_users table (this doesn't change during the time the test is run):

mysql> select username from mysql_users;
+-----------------+
| username        |
+-----------------+
| appuser         |
| root            |
| <other users>   |
+-----------------+

I think GCP and multiplexing are interacting in some weird way. Can I get some pointers on how to go about debugging this? TIA!

[JavierJF]

Hi @yashacb,

since it looks like you are having issues reproducing this in MySQL locally, you might be troubleshooting a MySQL (GCP) issue itself, and could be a time sensitive one. For an initial analysis I would enable both, the MySQL and ProxySQL query log, and then try to reproduce the issue. This will help to clarify the following points:

• Is there a timing issue in the reproducing script itself?
• Have MySQL acknowledge the schema creation and permissions when the 'Access denied' takes place?
• Is ProxySQL sending the correct select_db after the previous event has taken place?

Hope these points can help you to further troubleshoot the issue. This information can also provide evidence in the case there is a miss-behavior on ProxySQL side.

Thank you, Javier.