Nullification

Suppose you have a MySQL table, that contains a field like this:

start_time datetime default NULL

As it turns out, the start_time field is never null: it always contains a value. Every record in the table has a value, and new records are never created without one. So, in the interest of good data hygiene, you do this:

alter table …
modify start_time datetime not null default ‘0000-00-00 00:00:00’;

This should be a very fast operation: the data type isn’t changing, the current values are all valid under the new schema. MySQL could just update the schema, and leave the data alone.

Alas, it does not. It insists on making a copy of the entire table. If the table is large – say, because it contains thirteen years of data – this can take a rather long time.

Annoyance, I have it now.