Re: Before I shoot myself in the foot...

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
>

-- 
MySQL General Mailing List
For list archives: 
http://lists.mysql.com/mysql
To unsubscribe:    
http://lists.mysql.com/mysql?unsub=lists@pantek.com