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.


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

  • I ran version 3 of the tool on a MySQL 5.7.21 database (master of a replication pair). All data recorded in a column of data type JSON were set to NULL, effectively losing all of the data. At first I thought that I was getting caught by the equivalent of a FULL TEXT index, but that wasn’t the case.

    So fair warning to all, you might want to be very careful when using this. In my case, I had no option as I couldn’t take the database down long enough to dump or ALTER it normally. Ironically, it still cost me the data itself.

    The tool did work as advertised and the old table was not significantly locked during the ALTER process.

Leave a Reply