Add column on huge InnoDB table in MySQL 4.1

Question

I need to add a column on a huge InnoDB table (200GB) which resides on MySQL 4.1.21 (SunOS).

CPU has 16GB RAM and 200GB of more disk space.

Is there any quick way of adding this rather than ALTER TABLE? Because, ALTER TABLE as far as I know would take some months to complete.

Any thoughts experts?

• Matteo Tassinari: Create temporary table with new column, import all data in temp table and then drop old table? No other way as far as I know.
• Abhijit Buchake: Matteo, the problem with this approach is the table keeps on getting new records and it could not go offline for much time.
• Matteo Tassinari: If this is not feasible, and you do not want to ALTER TABLE, I really do not see how you could do this...
• Maxim Krizhanovsky: What about creating a new table with the PK of the old one and the new column, renaming the old table and creating a view with the name of the old table, that joins the 2 together?
• Abhijit Buchake: That's what I had suggested but from the application point of view, it was a tedious process to stop accessing the database even momentarily. Anyways, dev team decided to rework the code and skip this ALTER.

Answer 1

You can use statement-based replication and add the column to a slave first. Any pending modifications will queue up on the slave, and apply once the alter has complete. When the slave is fully up to date with the new schema, you can effectively 'promote' it.

I am sure you are aware, but MySQL 4.1 is quite old. Triggers were not introduced until MySQL 5.0 (2005), so this limits your options to use tools like pt-online-schema-change.

Answer 2

Percona toolkit has an online schema changer that may be of use.

http://www.percona.com/software/percona-toolkit

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

• Abhijit Buchake: Thanks, Mark. But the solution needed to be really quick and without substantial downtime. Moreover, it's 4.1 version of MySQL which they are planning to move to Oracle. So no migrations in near future :(