Proxysql: Handler error "No database selected"

[renecannao]

Issue easy to reproduce with:

mysqlslap --auto-generate-sql --iterations=10 ...

Why

mysqlslap runs DROP DATABASE mysqlslap after every iteration, and the connections that were using schema mysqlslap are left connected to a not existing schema. Because ProxySQL filters USE schema, even if the schema is re-created, the schema with the connections left without schema is not re-initialized, and therefore you get an error: No database selected .

What

Since proxysql's connections should always have a schema connected, if the backend replies with No database selected, ProxySQL can drop the connection and recreate one. That should be a safe approach.

[ecortestws]

Any progress on this issue? Any workaround? Thanks.

[renecannao]

Hi Eduardo,

no there is no progress on this right now, and I am considering it low priority as it is unlikely that in a production environment a database is dropped and recreated.
Is this your case?

[ecortestws]

Was trying to use mysqlslap to perform several basic tests. Will find another solution :)

[renecannao]

If it is just for testing, there are 2 quick options:
a) restart proxysql :)
b) disable the backends and re-enable the backends, automatically dropping all connections:

UPDATE mysql_servers SET status='OFFLINE_HARD';
LOAD MYSQL SERVERS TO RUNTIME;
UPDATE mysql_servers SET status='ONLINE';
LOAD MYSQL SERVERS TO RUNTIME;

[MaxFlanders]

restarting proxysql between each test, but it is the equivalent of limiting your mysqlslap command to --iterations 1. Would love to see something done here since mysqlslap is a great loadtesting tool and it sucks to be limited when trying to use it and proxysql together.

[dogsbody]

We had so much hope for ProxySQL but have just been hit with this issue and it's made it unusable. Not venting, ProxySQL looks awesome on paper but wanted to let you know the cause of at least one person not adopting it. Keep up the great work.

[denghuancong]

we meet this problem too, https://github.com/pingcap/tidb/issues/7681, they say it is proxysql problem, Is there any way to fix that?

[arthurchui]

We have the same issue after re-creating the db instance before each run of CI.

[halindrome]

We actually have the same issue because we create new databases for CI and for new customers all the time. We can bypass the proxy but that feels like a hack. There should be a way to select a database.

[jrcarlsen]

Have anyone found a workaround for this that can be triggered automatically? I, like many others needs this for our CI testing suite.