I have a query q1 that consists of multiple MySQL statements, some of which create temporary tables. The query has On success trigger defined which is another query q2 that reads from a temporary table tmp_table created by query q1. Sometimes (non-deterministically), I get the following error when q2 runs:
{
"status": 400,
"message":"ER_NO_SUCH_TABLE: Table 'test_db.tmp_table' doesn't exist",
"error": true,
"source": "resource"
}
This suggests that q2 starts executing before all SQL statements in q1 finish executing, since q2 cannot find the temporary table created by q1.
Has anyone experienced a similar problem? Is there some setting somewhere that I need to tweak to make this work properly?
I should add that sometimes the table that displays the data returned by query q2 doesn't update when q2 does happen to finish without errors. Rerunning the query by clicking on Refresh button always refreshes the data in the table.
Clearly, I'm running into some asynchronous issues here, but I haven't been able to figure out what they are. Any help would be greatly appreciated!
@polsar88,
There are two possible solutions depending on what you are allowed to do with your db server.
-
If you have create permissions to your MySQL server the best thing is to create a stored procedure to run all of your queries. This is by far the best solution. I find stored procedures (especially in SQL Server) quite painful, but I am getting better at sucking it up and doing it anyway.
-
If you cannot do 1) then you can split q1 into multiple queries and run them sequentially using the technique shown here: A pattern for triggering many async queries
2 Likes
It turns out that the issue was that the query that creates temporary tables got triggered twice. Depending on the timings, one of the queries' execution dropped a temporary table that had just been created by the other query.