Mysql – How to batch a drop column operation

alter-tablemysql-5.6

I have a BLOB column in my database schema that I no longer need. I need to drop this from my production server. I'm running MYSQL-5.6

Testing this on my development copy suggests that it will take about 1.5 hours to run. Right now, it's not feasible for us to bring the production DB down for that long for maintenance. What's the best way for me to do this in batches? e.g., I can acquire a write-lock on the table for about 5-10 mins at a time without causing any production failures.

I don't have a DB administrator background so I hope this isn't a really basic question covered elsewhere.

Best Answer

I think the following thread might prove useful to you: MySQL Drop Column From Large Table

The suggestion is to copy the data to a new table MINUS the column you wish to lose, then drop the old table and rename the new one. This is if you can't secure a maintenance window, say overnight/off-production hours, to do the work instead.

From my understanding - and others, please correct me if I'm wrong - although you could clear/NULL the data in this column in batches, you couldn't drop the column in batches since a column drop implies rewriting the row without that column - and you can't have a row in a table that doesn't have an attribute column that the other rows have.

Related Solutions

Mysql – How does the alter command work internally when adding a column

See the ALTER TABLE docs here:

http://dev.mysql.com/doc/refman/5.6/en/alter-table.html

"In most cases, ALTER TABLE makes a temporary copy of the original table."

PostgreSQL – Add Numeric Version ID Column to Existing Table

This is exactly what ROW_NUMBER() window function can do:

select 
    id, version_date,
    row_number() over (partition by id order by version_date) as version
from
    your_table ;

I don't see a reason to have these version numbers stored in a column of the table. It would be fairly easy to insert the values the first time but quite complicated to keep them updated after every insert, delete and update operation (probably done via triggers or stored procedures).

It might not be that complicated if no rows are ever deleted and the version_date is never updated. That would mean that only during inserts the new (inserted) values would have to be correctly calculated.

Another option, would be to use a materialized view.

Best Answer

Related Solutions

Mysql – How does the alter command work internally when adding a column

PostgreSQL – Add Numeric Version ID Column to Existing Table

Related Question