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