No data if queries are sent between TRUNCATE and SELECT INTO. Using MySQL innoDB
Using a MySQL DB I am having problems with the stored procedure and event timer that I created. I made an empty table that is filled with data from another via SELECT INTO. Before filling, I TRUNCATE the current data. It was used to track only log entries that occur within 2 months from the current date.
This turns the 350k + log table into about 750, which really makes reports submitting faster.
The problem is that if the client sends a query exactly between the TRUNCATE statement and the SELECT INTO statement (which has a high probability, given that EVENT is set to run every 1 minute), the query returns no rows ...
I looked at the read lock on the table while this PROCEDURE is starting, but the locks are not allowed in the WRITTEN PROCEDURES.
Can anyone come up with a workaround that (preferably) doesn't require redoing? I really need to point in the right direction.
Thanks Max
source to share
I would suggest an alternative approach instead of truncating the table and then selecting into it ...
Instead, you can select a new dataset into a new table. Then, using one command RENAME
, rename the new table to existing table and the existing table to some backup name.
RENAME TABLE existing_table TO backup_table, new_table TO existing_table;
This is a single atomic operation ... so the client will not be able to read the data after it is freed, but before it is refilled.
Alternatively, you can change TRUNCATE
to DELETE FROM
and then move that into a transaction along with SELECT INTO
:
START TRANSACTION
DELETE FROM YourTable;
SELECT INTO YourTable...;
COMMIT
source to share