List:General Discussion« Previous MessageNext Message »
From:Johan De Meersman Date:July 8 2013 12:17pm
Subject:Re: ALTER TABLE - correct way of adding columns?
View as plain text  
----- Original Message -----
> From: "Rafał Radecki" <radecki.rafal@stripped>
> 
> I would like to change the layout of my production database, I would
> like to add a column to an existing table. As I think before the ALTER
> TABLE statement all access to the database should be denied/ended, then the
> ALTER TABLE should be performed, and then user/applications should be able
> to use the database once again.

Quite right, but ALTER TABLE will acquire a full table lock all by itself - and in 5.6,
it's actually getting pretty smart about wether or not it's needed. If it does take a
lock, any clients trying to access the table will simply wait for the lock to release,
just like happens on other locking queries.

The pt-schema-upgrade tool you found is a big help if you need to do long-running changes
but want to keep the table online during the operation.

> My tables is quite small ~4MB data & indexes.

In this case, however, I shouldn't expect the change to take more than a few seconds in
any case; so if your clients won't need to be updated because of the new layout, you
could probably just run it and nobody will notice. Given that this is production, though,
it is good practice to inform the (human) clients about your changes beforehand :-)


-- 
Unhappiness is discouraged and will be corrected with kitten pictures.
Thread
ALTER TABLE - correct way of adding columns?Rafał Radecki8 Jul
  • Re: ALTER TABLE - correct way of adding columns?Rafał Radecki8 Jul
  • Re: ALTER TABLE - correct way of adding columns?Johan De Meersman8 Jul