8

I have a table with 21 million row.I have to change one of the row name.When I try with the query "alter table company change id new_id int(11)"; query never ends.

Is there a simple way to change big mysql table's field name?

demircan
  • 231
  • 4
  • 9
  • Have you tried doing it in PHPMyAdmin? That might use a more efficient method – Swadq Dec 07 '12 at 21:35
  • I have tried with both PHPMyAdmin and commandline. – demircan Dec 07 '12 at 21:36
  • 1
    You could do a mySqldump and edit the dump file. Then reimport the data. – ethrbunny Dec 07 '12 at 21:37
  • Is the column defined as int(11) with no other attributes? If you are only changing the name MySQL should just update the metadata without touching the rows. What storage engine are you using? – Joni Dec 07 '12 at 21:41
  • I am sure your suggestion will end but I just want to change the field name. The dump of table will be nearly 10 GB. It does not seem to be an efficient way. – demircan Dec 07 '12 at 21:41
  • Actually dump / import is quite fast. Surprisingly so. – ethrbunny Dec 07 '12 at 21:42
  • Joni yes it is just int(11). – demircan Dec 07 '12 at 21:43
  • If you are using InnoDB I guess you'll just have to wait for it to finish: "Alterations that modify only table metadata and not table data can be made immediately by altering the table's .frm file and not touching table contents. The following changes are fast alterations that can be made this way: Renaming a column, except for the InnoDB storage engine." http://dev.mysql.com/doc/refman/5.5/en/alter-table.html – Joni Dec 07 '12 at 21:45
  • The storage engine is MYISAM. – demircan Dec 07 '12 at 21:48
  • 1
    Documentation says Renaming a column, except for the InnoDB storage engine modifies only table metadata and not table data can be made immediately by altering the table's .frm file and not touching table contents. But this is not the case for me. – demircan Dec 07 '12 at 21:51
  • Are u able to solve this? – subodh May 01 '15 at 07:41

5 Answers5

5

First, before anything, backup the table:

mysqldump -uroot -p db big_table > /tmp/big_table.backup.sql

One thing to keep in mind when doing an ALTER command is to absolutely make sure that you have the same column details for the alter that you would like to not change.

So for example:

ALTER TABLE big_table MODIFY COLUMN id INT(11)

Would skip anything else like AUTO_INCREMENT and NOT NULL. So best to include that also into the alter statement.

ALTER TABLE big_table MODIFY COLUMN id INT(11) NOT NULL AUTO_INCREMENT;

Basically just copy over the create statement area for the ID column and replace what you want and include that into the modify statement.

matsko
  • 21,895
  • 21
  • 102
  • 144
  • Also if the alter messes up for some reason then you can search and replace the `id INT(11)` field inside of the `CREATE TABLE` statement within the `big_table.backup.sql` backup file to match what you want and then import it normally using `mysql -uroot -p db < /tmp/big_table.backup.sql`. – matsko Dec 07 '12 at 22:05
  • Thanks.I am trying.I will post the result. – demircan Dec 07 '12 at 22:06
  • I have tried the recommendation.Yes it works as expected.I have used split and cat in order to edit big sql file. – demircan Dec 09 '12 at 08:44
  • Great. Perhaps add the changes and steps you had to do to make it fully work and then lets close off the question :) – matsko Dec 09 '12 at 20:30
5

Use pt-online-schema-change to make long-running ALTER TABLE changes, without locking yourself out of the table.

http://www.percona.com/doc/percona-toolkit/pt-online-schema-change.html

It usually takes longer to complete the change, but you can still read from and write to the table while it's doing its work.

You can also listen to a webinar from the author, talking about how it works and some of its caveats:

http://www.percona.com/webinars/2012-05-02-zero-downtime-schema-changes-in-mysql

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
2

You could simply do the following:

MyISAM table

CREATE TABLE big_table_new LIKE big_table;
ALTER TABLE big_table_new MODIFY COLUMN id INT(11);
ALTER TABLE big_table_new DISABLE KEYS;
INSERT INTO big_table_new SELECT * FROM big_table;
ALTER TABLE big_table_new ENABLE KEYS;
ALTER TABLE big_table RENAME big_table_bak;
ALTER TABLE big_table_new RENAME big_table;

InnoDB table

CREATE TABLE big_table_new LIKE big_table;
ALTER TABLE big_table_new MODIFY COLUMN id INT(11);
INSERT INTO big_table_new SELECT * FROM big_table;
ALTER TABLE big_table RENAME big_table_bak;
ALTER TABLE big_table_new RENAME big_table;

When you are done, big_table_bak is your backup.

If you are satisfied with the new big_table you can run

DROP TABLE big_table_bak;

If you want to revert back you can run

DROP TABLE big_table;
ALTER TABLE big_table_bak RENAME big_table;

Give it a Try !!!

RolandoMySQLDBA
  • 43,883
  • 16
  • 91
  • 132
1

The less time consuming method to change name of a field in a huge Mysql table is to :

CREATE TABLE new_big_table SELECT id as new_id,field1,field2,field3 FROM big_table.

demircan
  • 231
  • 4
  • 9
0

What do You think about:

  1. export all data and truncate table
  2. alter table
  3. change name of row in exported file
  4. import file

It's just an fast idea, but it won't be fast (exporting/importing).

Or Just try to do ALTER during night few hour maintenance? :)

DarkAjax
  • 15,955
  • 11
  • 53
  • 65
Adam Bieńkowski
  • 585
  • 4
  • 21