I use temporary MySQL tables in my Stored Procedures from time to time. A side effect I have is it breaks replication for my backup database.
For some background, the Backup Database is supposed to be an exact mirror of the Production Database. The only difference is a delay in replicating data, because I shut it down once a day to archive the database files elsewhere. The backup copy consists of compressing each file and this takes at least 12 hours. After the 12+ hour backup is complete, the script starts up the MySQL backup server, and it resumes replication from the Production Database.
The issue I have is if a stored procedure has a long running INSERT or UPDATE query that relies on a temporary in memory table. When the MySQL Backup Server is shut down, so the archive can begin, the stored procedure is still running and relies on the temporary table for further processing. When the MySQL Backup Server starts up again, the temporary table is no longer there, since it was in memory before the shut down.
Here are the steps this might occur:
- Your Slave MySQL Server is processing queries from a Stored Procedure
- A temporary (in memory) table is created
- MySQL is shut down
- MySQL starts up
- Temporary table does not exist anymore
- The next query in the Stored Procedure is run that tries to access the temporary table.
- Because the temporary table is removed from memory when MySQL was shut down, an error is returned.
The is an example error I receive when the MySQL Backup servers starts back up after archiving, and it can't find the in memory temporary table that existed before the archive started:
[ERROR] Slave: Error 'Table 'dbName.InMemoryTemporaryTableName' doesn't exist' on query. Default database: 'myLocateadoc3'.
Query: 'INSERT INTO dbName.TableCopyingTo SELECT * from dbName.InMemoryTemporaryTableName',
[ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'db2-bin.005555' position 1788476
To fix the problem, I've had to go back and modify the affected Stored Procedures to not create in memory temporary tables, and I instead created a separate temporary database to create my temporary table in and on disk, instead of in memory.
Trying to Access the Same Temporary Table More than Once
A side effect to this solution is if the Stored Procedure is called and then another call is made while the last call is still running. This could cause a conflict if the first call is still using the temporary on disk table and the 2nd call wants to create the same one. This wouldn't occur with In-Memory tables, because they are created per session.
Here's how it might look:
StoredProcedure Call #1 DROP TABLE IF EXISTS dbNameTemp.OnDiskTempTableName; CREATE TABLE dbNameTemp.OnDiskTempTableName LIKE dbName.TemplateTableName; Performing operations on temporary table
StoredProcedure Call #2 DROP TABLE IF EXISTS dbNameTemp.OnDiskTempTableName;
StoredProcedure Call #1 then fails because call #2 removes the table it was working on.
A couple solutions include:
- Create a Timestamped temporary table with a random number added to it for each procedure
- Add an advisory lock around the entire Stored Procedure or just the sections of code that use the temporary table
I personally use the advisory lock, mainly because I never get to use that functionality and was excited to finally find a use for it.
There are probably other solutions to this problem and I hope to hear them.