List:General Discussion« Previous MessageNext Message »
From:Brent Baisley Date:June 13 2007 6:26pm
Subject:Re: Before I shoot myself in the foot...
View as plain text  
Yes, that will lock up the table while the change is being made. One 
technique you can use is to rename the table and create a new to catch 
the incoming data.
RENAME TABLE x TO y;CREATE TABLE x LIKE y;

By putting both commands on 1 line, it will execute almost immediately.

Then you can alter the table you renamed to y, since it's not being used 
it won't lock up anything. Then rename it back when you are done. If you 
have an auto increment field in the table, make sure you set the auto 
increment value to a number higher than what accumulated in the 
temporary holding table.
ALTER TABLE y AUTO_INCREMENT = ###########
RENAME TABLE y TO tmp, x TO y, tmp TO x

That will swap the tables back, y becomes x, and x become y. The you can 
do an insert select from table y, which is holding the data that 
accumulate while you were altering the other table.
It's convoluted and round about, but you won't lose any data. And it may 
not be feasible if there are data dependencies within the same table.

Baron Schwartz wrote:
> Hi Brian,
>
> Brian Dunning wrote:
>> ...if I add a column to a table with 40,000,000 records, will it 
>> cause that table to hang for any significant amount of time, and 
>> prevent other transactions? It's a MyISAM table and I was going to 
>> add a varchar(20) column, NULL. It's a very busy table, constant 
>> searches and inserts.
>
> Yes, definitely.  If you look up the ALTER TABLE documentation it will 
> explain the details -- I think all SELECTs will continue to run okay, 
> but INSERTs will wait until the ALTER is done, then they will be 
> applied to the altered table.
>
> But don't hold me to that :-)
>
> Baron
>
Thread
Before I shoot myself in the foot...Brian Dunning13 Jun
  • Re: Before I shoot myself in the foot...Baron Schwartz13 Jun
    • Re: Before I shoot myself in the foot...Brent Baisley13 Jun
  • Re: Before I shoot myself in the foot...Brian Dunning13 Jun
    • Re: Before I shoot myself in the foot...Ananda Kumar14 Jun
      • Re: Before I shoot myself in the foot...Baron Schwartz14 Jun
        • Re: Before I shoot myself in the foot...Ananda Kumar14 Jun
  • Re: Before I shoot myself in the foot...Martijn Tonies14 Jun
  • Re: Before I shoot myself in the foot...Martijn Tonies14 Jun
Re: Before I shoot myself in the foot...Baron Schwartz13 Jun