Bug #50771 Altering column default value causes table rebuild
Submitted: 31 Jan 2010 22:00 Modified: 1 Mar 2010 6:43
Reporter: Michael Thompson Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: MyISAM storage engine Severity:S3 (Non-critical)
Version:5.1.31 OS:Linux (ubuntu 9.04)
Assigned to: CPU Architecture:Any
Tags: alter column, ALTER TABLE, set default, slow

[31 Jan 2010 22:00] Michael Thompson
Description:
Since setting the default value of a column should only affect *future* rows,
it should not require a table rebuild (copy to tmp, repair by sorting).
However, at least in 5.1.31, this is exactly what happens:

+----+------+-----------+----------------+---------+------+-------------------+------------------------------------------------------------------------------+
| Id | User | Host      | db             | Command | Time | State             | Info                                                                         |
+----+------+-----------+----------------+---------+------+-------------------+------------------------------------------------------------------------------+
| 90 | root | localhost | mog_production | Query   |   48 | copy to tmp table | ALTER TABLE medianet_media_files ALTER COLUMN feed_created_id SET DEFAULT 64 | 
| 91 | root | localhost | mog_production | Query   |    0 | NULL              | show full processlist                                                        | 
+----+------+-----------+----------------+---------+------+-------------------+------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

Here is what my table looks like:

mysql> describe medianet_component_rights;
+-------------------+-------------+------+-----+---------+----------------+
| Field             | Type        | Null | Key | Default | Extra          |
+-------------------+-------------+------+-----+---------+----------------+
| id                | bigint(21)  | NO   | PRI | NULL    | auto_increment | 
| comp_id           | int(11)     | YES  | MUL | NULL    |                | 
| asset_code        | varchar(3)  | YES  |     | NULL    |                | 
| authorization     | varchar(32) | YES  |     | NULL    |                | 
| inception         | date        | YES  | MUL | NULL    |                | 
| created_date      | date        | YES  |     | NULL    |                | 
| last_updated_date | date        | YES  |     | NULL    |                | 
| feed_id           | int(11)     | YES  | MUL | NULL    |                | 
| feed_created_id   | int(11)     | NO   | MUL | 64      |                | 
| revoked           | tinyint(1)  | NO   | MUL | 0       |                | 
+-------------------+-------------+------+-----+---------+----------------+
10 rows in set (0.00 sec)

And this is a profile of that alter table statement:

Status                  Duration
starting                0.000037
checking permissions    0.000006
checking permissions    0.000022
init                    0.000063
Opening tables          0.000016
setup                   0.000166
creating table          0.008746
After create            0.000243
copy to tmp table       266.418131
Creating index          0.000019
Repair by sorting       999.999999
Saving state            0.011707
Creating index          0.000005
copy to tmp table       0.000031
rename result table     3.841147
System lock             0.000016
Table lock              0.000023
end                     0.000014
query end               0.000008
freeing items           0.000154
cleaning up             0.000011

How to repeat:
Create a MyISAM table with a lot of rows (mine had >30M)
Alter the default value of a column using ALTER TABLE {tablename} ALTER COLUMN {columnname} SET DEFAULT {value};

This should be pretty much instantaneous, but took over 20 minutes for my table.
[1 Feb 2010 6:43] Valeriy Kravchuk
Plese, check with a newer version, 5.1.42 or 5.1.43, and inform about the results.
[2 Mar 2010 0:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".