0

My problem is MySQL runs into a 'Too many connections' error when I add a new column to my live database.

The database has 1 million records so it takes a while to add this.

  • What is the reason the table locks?
  • What to do to keep table running?

The query I execute on the table with 1 million records (InnoDB)

ALTER TABLE table_name 
ADD COLUMN new_table_column INT(11) NULL 
DEFAULT NULL ;

Thanks!

Lion
  • 18,729
  • 22
  • 80
  • 110
directory
  • 3,093
  • 8
  • 45
  • 85

2 Answers2

0

Try running show processlist; to see what the connections are. http://dev.mysql.com/doc/refman/5.1/en/show-processlist.html

I would also take a look at you mysql configuration as well, specifically checking the max_connections setting. Changing that setting will require a mysql server restart. http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html

InnoDB requires a table lock, there isn't much you can do about that.

Take a look at these for some ideas on how to do this:

Optimizing MySQL for ALTER TABLE of InnoDB

and

ALTER TABLE without locking the table?

Community
  • 1
  • 1
thaspius
  • 1,135
  • 3
  • 17
  • 33
  • Thanks for you answer and for the links. I think it's not the good option to reach the maximum of connections of MySQL, you will run into the same problem pretty fast (I guess). Thanks for the answer anyway :) – directory Nov 06 '12 at 19:21
0

mysql is notoriously slow at adding columns.

As far as I know, all databases lock the table while adding columns, because they are changing the layout on disk and it requires heavy I/O.

Often, it is so slow (especially with mysql), the way it's done in production is to create a new table with the extra column, copy the data across, renamed the tables and start using the new table. You of course would stop write-access while you did this, but it's much faster to get the job done.

Bohemian
  • 412,405
  • 93
  • 575
  • 722
  • Yes, I think there is not really another kind of solution to keep the tables alive in a process like this. Thanks for the answer! – directory Nov 06 '12 at 19:21