EmergencyEMERGENCY? Get 24/7 Help Now!

pt-online-schema-change and default values

 | June 21, 2012 |  Posted In: Insight for DBAs, MySQL


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:

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.

One Comment

  • 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.

Leave a Reply


Percona’s widely read Percona Database Performance blog highlights our expertise in enterprise-class software, support, consulting and managed services solutions for both MySQL® and MongoDB® across traditional and cloud-based platforms. The decades of experience represented by our consultants is found daily in numerous and relevant blog posts.

Besides specific database help, the blog also provides notices on upcoming events and webinars.
Want to get weekly updates listing the latest blog posts? Subscribe to our blog now! Submit your email address below and we’ll send you an update every Friday at 1pm ET.

No, thank you. Please do not ask me again.