Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.
Hi!
I need to update my table by loading data from file and truncating existing data.
This is my code that loads data from file into new table which is the same as destination table, then I swap new table and destintation table. This appoach works but sometimes it hungs at rename action. Which causes parts of my site that use that table to freeze.
DROP TABLE IF EXISTS t_new;
DROP TABLE IF EXISTS t_old;
CREATE TABLE t_new LIKE t;
LOAD DATA LOCAL INFILE 'path' INTO TABLE t_new
CHARACTER SET cp1251
FIELDS TERMINATED BY '(|)'
LINES TERMINATED BY '\n';
RENAME TABLE t TO t_old, t_new TO t;
DROP TABLE t_old;
Before We used to truncate destintation table and then load data from file. But that makes the table empty for ~1.5 seconds.
Do you really need to empty the table? I would do update or create instead.
If you need to empty it why not add a column like active or some such and just add the records with active set to false then when you are done set the old ones to false and the new ones to true. Then you can delete the old ones any time you like.
Hi @tray2 ! I appreciate your answer, but I believe update is a no go for my use case since I have thousands of records in the table. And updating them in one batch often causes mysql errors. Adding active field isn't an option too since we have ~ 30 tables that are updated like this many times a day.
My assumption is that rename is not happening sometimes because of locks or transactions. Is there a way to try check if table is free now and then run rename?
If you need to do this several times a day I'd say it a bad database model. No matter which way you go you will always have a small amount of time when the table is empty. There is also other issues with dropping the table all indexes related to to it dissapears and it's better to have the table exist but empty than don't exists when called upon.
If you were using an Oracle database you could use public synonyms to switch between the tables.
The only "less bad" way to do in my opinion is to use database transactions to handle this.
That way you will get all the data more or less at once. the procedure would look something like this.
https://laravel.com/docs/6.x/database#database-transactions
However I suggest you take a look at the database model and see if you can use updateOrInsert.
@tray2, now I am testing solution where I check if table is in use before renaming it like so SHOW OPEN TABLES WHERE In_use > 0. If that doesn't work too I will try the transaction approach. I wish we could use update and insert for this but it would require us to rewrite the way out e-commerce site interacts with our accounting system which provides most of the site data.
I think a rewrite of that logic is the way to go.
Couldn't you import it to a temporary table then do an updateOrInsert to the real table?
Landed on using transactions. Works fine
Please or to participate in this conversation.