top button
Flag Notify
    Connect to us
      Facebook Login
      Site Registration Why to Join

Facebook Login
Site Registration
Print Preview

ALTER TABLE - correct way of adding columns using MySQL

0 votes
218 views

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.

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

So is the ALTER TABLE on a running/opened to clients database/table desirable or should it be run when all access to the database/table is forbidden?

posted Jul 8, 2013 by anonymous

Share this question
Facebook Share Button Twitter Share Button Google+ Share Button LinkedIn Share Button Multiple Social Share Button

2 Answers

+1 vote
 
Best answer

When you execute ALTER command on a table, MySQL create a new table with new format, copy all rows, then switch over. During this time the table is completely locked.

There are tools which allow you to do online alter table for MySQL.

You can use any of these -
http://www.percona.com/doc/percona-toolkit/2.1/pt-online-schema-change.html
http://www.facebook.com/notes/mysql-at-facebook/online-schema-change-for-mysql/430801045932
http://openarkkit.googlecode.com/svn/trunk/openarkkit/doc/html/oak-online-alter-table.html

answer Jul 9, 2013 by Sudheendra
0 votes

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.

answer Jul 8, 2013 by anonymous
Similar Questions
+1 vote

I'm trying to use a very basic alter table command to position a column after another column.

mysql> describe car_table;
+---------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+----------------+
| car_id | int(11) | NO | PRI | NULL | auto_increment |
| vin | varchar(17) | YES | | NULL | |
| color | varchar(10) | YES | | NULL | |
| year | decimal(4,0) | YES | | NULL | |
| make | varchar(10) | YES | | NULL | |
| model | varchar(20) | YES | | NULL | |
| howmuch | decimal(5,2) | YES | | NULL | |
+---------+--------------+------+-----+---------+----------------+

I am trying to position the 'color' column after the 'model' column with the following command:

mysql> alter table car_table modify column color after model;

And I'm getting the following error:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'after model' at line 1

I'm just wondering what I'm doing wrong here

+5 votes

How can I insert the content of excel file into MySQL Table, any pointer?


Useful Links with Similar Problem
Contact Us
+91 9880187415
sales@queryhome.net
support@queryhome.net
#470/147, 3rd Floor, 5th Main,
HSR Layout Sector 7,
Bangalore - 560102,
Karnataka INDIA.
QUERY HOME
...