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
"message":"ER_NO_SUCH_TABLE: Table 'test_db.tmp_table' doesn't exist",
This suggests that
q2 starts executing before all SQL statements in
q1 finish executing, since
q2 cannot find the temporary table created by
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!
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
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.