Details
-
Bug
-
Status: Done
-
Medium
-
Resolution: Fixed
-
3.4.0
-
None
-
Yes
-
Yes
-
2
Description
Hi,
The rows which contain NULL values will be converted to the defined default value.
This part is not correct. The values will always be assigned a default value based on datatype.
Document link: https://docs.percona.com/percona-toolkit/pt-online-schema-change.html#cmdoption-pt-online-schema-change-null-to-not-null
mysql> CREATE TABLE actor ( -> actor_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, -> first_name VARCHAR(45) NOT NULL, -> last_name VARCHAR(45) NOT NULL, -> ola_breach_status int(11), -> PRIMARY KEY (actor_id), -> KEY idx_actor_last_name (last_name) -> )ENGINE=InnoDB DEFAULT CHARSET=utf8; Query OK, 0 rows affected (0.01 sec)mysql> mysql> INSERT INTO actor (first_name,last_name) VALUES ('PENELOPE','GUINESS'); Query OK, 1 row affected (0.00 sec)mysql> INSERT INTO actor (first_name,last_name) VALUES ('PENELOPE','GUINESS'); Query OK, 1 row affected (0.00 sec)mysql> INSERT INTO actor (first_name,last_name) VALUES ('PENELOPE','GUINESS'); Query OK, 1 row affected (0.00 sec)mysql> select * from actor; +----------+------------+-----------+-------------------+ | actor_id | first_name | last_name | ola_breach_status | +----------+------------+-----------+-------------------+ | 1 | PENELOPE | GUINESS | NULL | | 2 | PENELOPE | GUINESS | NULL | | 3 | PENELOPE | GUINESS | NULL | +----------+------------+-----------+-------------------+ 3 rows in set (0.00 sec)
[root@yunus-uyanik-default ~]# pt-online-schema-change h=localhost,D=test,t=actor --alter "modify column ola_breach_status int(11) unsigned NOT NULL DEFAULT 123" --null-to-not-null --execute mysql> select * from test.actor; +----------+------------+-----------+-------------------+ | actor_id | first_name | last_name | ola_breach_status | +----------+------------+-----------+-------------------+ | 1 | PENELOPE | GUINESS | 0 | | 2 | PENELOPE | GUINESS | 0 | | 3 | PENELOPE | GUINESS | 0 | +----------+------------+-----------+-------------------+ 3 rows in set (0.00 sec)
ola_breach_status fill the with int 0 instead of the default value which is 123.