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.