Closing database connection while query is still pending about mysql

[trowski]

Hi @LouisTSpi!

Graceful closing of the connection requires sending a command to the server, which as you noticed needs to go into the queue. Cancelling a MySQL query requires issuing another query on a different connection, KILL QUERY process_id. Unfortunately we don't have an API exposed for this, and one would not be trivial to design or implement. I'm considering adding support in v3, but it's certainly not something that will be added to v2.

The other option is to abruptly close the socket connection, but this will leave the table in a metadata lock state again.

I suspect the table is being left in a lock state because the close operation is not being allowed to complete before the script is terminated. Instead of using Amp\Promise\wait(), wrap everything into Amp\Loop::run() to ensure any further tasks are executed before the script ends.

I'd recommend upgrading to v3 if you're able. The API is much cleaner and the use of fibers would have avoided the close issue, as the fiber will suspend until the connection closes.

[LouisTSpi]

Hello @trowski,

Thank you for your reply.

I think I was not clear enough about the metadata lock state, let me rephrase to try and clear things up.
My practical case arises when the table on one server is already in a metadata lock state (due to reasons independent from my app or amphp/mysql) when I issue the queries using amphp/mysql. It is not the queries made with amphp/mysql that render the table in the lock state, so I'm not expecting amphp/mysql to change the lock state of the table.

For example the situation would be:

• there are 2 db servers (server1 and server2)
• On server2, the table the query is selecting from is already in a "Waiting for table metadata lock" state
• while server2 table is in this lock state, the app uses amphp/mysql as in the example above to query both servers in parallel
• server1 promise resolves with its results
• server2 does not respond in time due to the lock: its timeout promise fails and execution for this server goes through the catch and finally statements
• $db->close() is called for connection to server2 but does not actually close the connection
• php returns the results of server1 and execution stops.
• the query issued on server2 stays in MySQL's processlist until the table metadata lock is released, even long after php execution stops

The other option is to abruptly close the socket connection, but this will leave the table in a metadata lock state again.
I suspect the table is being left in a lock state because the close operation is not being allowed to complete before the script is terminated.

I'll try force closing the socket. As said above, since the table was already in the lock state before issuing the queries, it does not really matter if the table is left in the lock state afterwards. What matters is that the connection is closed, so that mysql can accept new connections to answer other queries (not all queries use the locked table).

I'd recommend upgrading to v3 if you're able. The API is much cleaner and the use of fibers would have avoided the close issue, as the fiber will suspend until the connection closes.

I've tested an implementation using v3 beta but I get the same result. I'm considering migrating the production app once amphp/sql, amphp/sql-common and amphp/mysql are stable. As far as I can tell they are still in beta.