Link to home
Start Free TrialLog in
Avatar of fpoyavo
fpoyavoFlag for United States of America

asked on

MYSQL RENAME or ALTER

Hi Experts,

I have a table which is used every second pretty much and at some point of a day I need to do the following in my php script:

1. Block any incoming queries [SELECT, INSERT [most of them inserts 99%] etc]
2. Do table RENAME while no app can modify the table
3. After RENAME I create table like the one I renamed
4. Let apps access it again

I read a lot about different approaches but not sure which one to stick to.

Thanks.
Avatar of Randy Poole
Randy Poole
Flag of United States of America image

why do you want to do this?  do you not need the data in the table?
Avatar of Gary
Only real way is to lock the table

LOCK TABLES table WRITE; # still allow reads since that shouldn't affect anything
...
UNLOCK TABLES;

Open in new window

Avatar of fpoyavo

ASKER

Ok if I lock this table will it cause exceptions in INSERT transactions ? If yes how to avoid it ?
Actually LOCK will block a rename - forget that

ALTER TABLE table1 RENAME TO table2;
Will lock the table anyway and should take a fraction of a second and any queries will be dropped.

Is this for a backup/archive?
It may be better to move the rows and delete
Avatar of fpoyavo

ASKER

backup yep. LOCK did not work as you said. I will try ALTER now.
I'm with Randy Poole here -- please tell us what you're trying to do.  Do you want to add or remove columns from the table?  Or what, exactly?
He already said - archiving
Sorry, I must have missed the part about archiving.  I don't think ALTER TABLE fits with making backup copies -- it's more for changing the structure of the table.  

If you want to backup a table, simply copy the original table to the backup.  Then remove all rows of the original table, except for the new rows that were inserted after the copy.  If you have a TIMESTAMP column in the table it's very easy to get this right, but you could probably do just as well with an AUTO_INCREMENT key.
As I said
Avatar of fpoyavo

ASKER

Ray,

The problem is that this table is huge and its not an option to copy that is why I am asking how to make sure table does not get new data until I finish ALTER RENAME. Also I originally tried just RENAME without alter and do you guys know if there is a difference ?

I found some article stating that they are in many ways ... also I found that FLUSH should be used to eliminate any issues.

Anyone knows anything about it ?

Thanks.
ASKER CERTIFIED SOLUTION
Avatar of Gary
Gary
Flag of Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of fpoyavo

ASKER

Does ALTER actually blocks new data coming in ?
Yes, any new queries coming in will fail while the process happens and the new table is created
And I will go back to Randy's initial question as well - why do you need to do this?
MySQL can handle millions of rows in a table quite easily

Normally archiving happens on a maybe yearly basis and you want to do this on a daily basis
Avatar of fpoyavo

ASKER

Not me :))) I would not do it at all ... but you know we follow orders :) Ok thanks for all your feedback.
table is huge and its not an option to copy
Eh?  Why not do this in steps?  Just copy the first million (or so) rows, then delete the first million, then go on to the second million, etc?  It's not rocket science - but it pays to read and understand the manuals!