October 1, 2014

pt-online-schema-change and default values

When I’m doing conventional ALTER TABLE in MySQL I can ignore default value and it will be assigned based on the column type. For example this alter table sbtest add column v varchar(100) not null would work even though we do not specify default value. MySQL will assign empty string as default default value for varchar column. This however does not work for pt-online-schema-change:

pt-online-table-change does not try to guess the default value from column type and so it will fail unless default value is specified. To make pt-online-table-change we need to make a small change to our ALTER TABLE statement to explicitly specify default value:

About Peter Zaitsev

Peter managed the High Performance Group within MySQL until 2006, when he founded Percona. Peter has a Master's Degree in Computer Science and is an expert in database kernels, computer hardware, and application scaling.

Comments

  1. To add even more detail about this, the issue is that MySQL (as usual!) behaves differently from some database servers and assigns a default value to the column in an ALTER, even though you didn’t specify one:

    alter table sbtest add v varchar(100) not null;
    Query OK… Warnings: 0

    It doesn’t even give a warning. Many other database servers would throw an error on the ALTER, because you haven’t specified what should go into column v. MySQL will let the ALTER proceed, and then during the copy-table-to-new-table process, assigns an empty string to column v. But, if you then do an INSERT into the table afterwards without specifying a value for column v, it changes its mind and decides to be strict. This is what’s causing pt-online-schema-change to have a problem.

Speak Your Mind

*