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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | root@smt2:~# pt-online-schema-change --execute --alter="add column v varchar(100) not null" D=sbtest,t=sbtest Altering `sbtest`.`sbtest`... Creating new table... Created new table sbtest._sbtest_new OK. Altering new table... Altered `sbtest`.`_sbtest_new` OK. Creating triggers... Created triggers OK. Copying approximately 10000060 rows... Dropping triggers... Dropped triggers OK. Dropping new table... Dropped new table OK. `sbtest`.`sbtest` was not altered. (in cleanup) Error copying rows from `sbtest`.`sbtest` to `sbtest`.`_sbtest_new`: Copying rows caused a MySQL error 1364: Level: Warning Code: 1364 Message: Field 'v' doesn't have a default value Query: INSERT LOW_PRIORITY IGNORE INTO `sbtest`.`_sbtest_new` (`id`, `k`, `c`, `pad`) SELECT `id`, `k`, `c`, `pad` FROM `sbtest`.`sbtest` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) AND ((`id` <= ?)) /*pt-online-schema-change 11617 copy nibble*/ Dropping triggers... Dropped triggers OK. `sbtest`.`sbtest` was not altered. |
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 | root@smt2:~# pt-online-schema-change --execute --alter="add column v varchar(100) not null default ''" D=sbtest,t=sbtest Altering `sbtest`.`sbtest`... Creating new table... Created new table sbtest._sbtest_new OK. Altering new table... Altered `sbtest`.`_sbtest_new` OK. Creating triggers... Created triggers OK. Copying approximately 10000060 rows... Copying `sbtest`.`sbtest`: 5% 08:48 remain Copying `sbtest`.`sbtest`: 10% 08:52 remain Copying `sbtest`.`sbtest`: 14% 08:42 remain Copying `sbtest`.`sbtest`: 19% 08:17 remain Copying `sbtest`.`sbtest`: 24% 07:46 remain Copying `sbtest`.`sbtest`: 29% 07:13 remain Copying `sbtest`.`sbtest`: 33% 06:48 remain Copying `sbtest`.`sbtest`: 38% 06:21 remain Copying `sbtest`.`sbtest`: 43% 05:54 remain Copying `sbtest`.`sbtest`: 47% 05:25 remain Copying `sbtest`.`sbtest`: 52% 04:57 remain Copying `sbtest`.`sbtest`: 57% 04:23 remain Copying `sbtest`.`sbtest`: 62% 03:50 remain Copying `sbtest`.`sbtest`: 67% 03:24 remain Copying `sbtest`.`sbtest`: 71% 02:56 remain Copying `sbtest`.`sbtest`: 76% 02:29 remain Copying `sbtest`.`sbtest`: 80% 01:59 remain Copying `sbtest`.`sbtest`: 85% 01:30 remain Copying `sbtest`.`sbtest`: 90% 01:01 remain Copying `sbtest`.`sbtest`: 95% 00:30 remain Copied rows OK. Swapping tables... Swapped original and new tables OK. Dropping old table... Dropped old table `sbtest`.`_sbtest_old` OK. Dropping triggers... Dropped triggers OK. Successfully altered `sbtest`.`sbtest`. |
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.