[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 341
  • Last Modified:

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.
0
fpoyavo
Asked:
fpoyavo
  • 6
  • 5
  • 3
  • +1
1 Solution
 
Randy PooleCommented:
why do you want to do this?  do you not need the data in the table?
0
 
GaryCommented:
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

0
 
fpoyavoAuthor Commented:
Ok if I lock this table will it cause exceptions in INSERT transactions ? If yes how to avoid it ?
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
GaryCommented:
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
0
 
fpoyavoAuthor Commented:
backup yep. LOCK did not work as you said. I will try ALTER now.
0
 
Ray PaseurCommented:
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?
0
 
GaryCommented:
He already said - archiving
0
 
Ray PaseurCommented:
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.
0
 
GaryCommented:
As I said
0
 
fpoyavoAuthor Commented:
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.
0
 
GaryCommented:
FLUSH is no use as new queries are coming in.
You obviously have some kind of ID column?
Do a select based on the last ID in the table, copy to the new table, delete from current table
This won't affect new inserts, it may slow down other queries while it happens but at least they would be queued and not dropped (unless it takes an inordinate amount of time)
If it does take too long maybe you should be running this query on a regular basis
0
 
fpoyavoAuthor Commented:
Does ALTER actually blocks new data coming in ?
0
 
GaryCommented:
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
0
 
fpoyavoAuthor Commented:
Not me :))) I would not do it at all ... but you know we follow orders :) Ok thanks for all your feedback.
0
 
Ray PaseurCommented:
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!
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

  • 6
  • 5
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now