TL;DR
- NULLが入っているカラムを後から
NOT NULL にしようとすると出る
- 何故出るかというと、 INSERT IGNORE INTOがNOT NULL DEFAULTを裏切る から敢えて
SHOW WARNINGS を拾ってエラーにしている
- 裏切られるのを承知の上で強行するなら
pt-online-schema-change --null-to-not-null でいける
UPDATE t1 SET val = ? WHERE val IS NULL でNULLを排除してからpt-online-schema-changeすればいいんじゃないかな
日々の覚書: INSERT IGNORE INTOがNOT NULL DEFAULTを裏切る の続き。
pt-oscは INSERT IGNORE INTO を使ってるはずなのになんでこのメッセージでエラるんだろ？ と思ったら、 ↑の裏切りを防ぐために明示的にワーニングを拾ってエラーにエスカレーションしているのであった。
https://github.com/percona/percona-toolkit/blob/v3.3.1/bin/pt-online-schema-change#L11791-L11828
mysql80 121> CREATE TABLE t1 (num int PRIMARY KEY, val varchar(32));
Query OK, 0 rows affected (0.02 sec)
mysql80 121> INSERT INTO t1 VALUES (1, 'one'), (2, NULL);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql80 121> SELECT * FROM t1;
+-----+------+
| num | val |
+-----+------+
| 1 | one |
| 2 | NULL |
+-----+------+
2 rows in set (0.00 sec)
mysql80 123> SELECT @@global.sql_mode;
+-------------------+
| @@global.sql_mode |
+-------------------+
| |
+-------------------+
1 row in set (0.00 sec)
sql_modeに関係ないことを確認するために、グローバルSQL_MODEを空っぽにしておく。
$ pt-online-schema-change -S /usr/mysql/8.0.26/data/mysql.sock -uroot --alter "MODIFY val varchar(32) NOT NULL DEFAULT 'z'" D=d2,t=t1 --execute
No slaves found. See --recursion-method if host 150-95-141-50 has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
analyze_table, 10, 1
copy_rows, 10, 0.25
create_triggers, 10, 1
drop_triggers, 10, 1
swap_tables, 10, 1
update_foreign_keys, 10, 1
Altering `d2`.`t1`...
Creating new table...
Created new table d2._t1_new OK.
Altering new table...
Altered `d2`.`_t1_new` OK.
2021-09-11T00:11:48 Creating triggers...
2021-09-11T00:11:48 Created triggers OK.
2021-09-11T00:11:48 Copying approximately 2 rows...
2021-09-11T00:11:48 Dropping triggers...
2021-09-11T00:11:48 Dropped triggers OK.
2021-09-11T00:11:48 Dropping new table...
2021-09-11T00:11:48 Dropped new table OK.
`d2`.`t1` was not altered.
(in cleanup) 2021-09-11T00:11:48 Error copying rows from `d2`.`t1` to `d2`.`_t1_new`: 2021-09-11T00:11:48 Copying rows caused a MySQL error 1048:
Level: Warning
Code: 1048
Message: Column 'val' cannot be null
Query: INSERT LOW_PRIORITY IGNORE INTO `d2`.`_t1_new` (`num`, `val`) SELECT `num`, `val` FROM `d2`.`t1` LOCK IN SHARE MODE /*pt-online-schema-change 5706 copy table*/
2021-09-11T00:11:48 Dropping triggers...
2021-09-11T00:11:48 Dropped triggers OK.
`d2`.`t1` was not altered.
とまあ転けて終わる。
…と、ジェネラルログをよく見てたら、元のsql_modeに関わらずいったんsql_modeを全OFFしていた。
2021-09-11T00:11:48.055972+09:00 132 Query /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */
まあいっか。
—null-to-not-null オプションをつけると、通る。ただし、DEFAULTに裏切られるので暗黙のフォールバックを受ける。
mysql80 136> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`num` int NOT NULL,
`val` varchar(32) NOT NULL DEFAULT 'z',
PRIMARY KEY (`num`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
mysql80 136> SELECT * FROM t1;
+-----+-----+
| num | val |
+-----+-----+
| 1 | one |
| 2 | |
+-----+-----+
2 rows in set (0.00 sec)
これが嫌な場合、 先んじて UPDATE t1 SET val = 'z' WHERE val IS NULL とでもしてNULLをつぶしておくのが正しいと思う。実際にNULLに当たらなければ、 --null-to-not-null をつけなくてもpt-oscは完走する。
ちなみにMySQLのNative ALTER TABLEはsql_modeに影響を受ける。STRICTな時はエラーになるし
mysql80 136> SELECT @@session.sql_mode;
+-----------------------------------------------------------------------------------------------------------------------+
| @@session.sql_mode |
+-----------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql80 136> SELECT * FROM t1;
+-----+------+
| num | val |
+-----+------+
| 1 | one |
| 2 | NULL |
+-----+------+
2 rows in set (0.00 sec)
mysql80 136> ALTER TABLE t1 MODIFY val varchar(32) NOT NULL DEFAULT 'z';
ERROR 1138 (22004): Invalid use of NULL value
Non-Strictな場合はDEFAULTを裏切ってALTERが走る。
mysql80 136> SET SESSION sql_mode = '';
Query OK, 0 rows affected (0.00 sec)
mysql80 136> ALTER TABLE t1 MODIFY val varchar(32) NOT NULL DEFAULT 'z';
Query OK, 2 rows affected, 1 warning (0.04 sec)
Records: 2 Duplicates: 0 Warnings: 1
mysql80 136> SHOW WARNINGS;
+---------+------+------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------+
| Warning | 1265 | Data truncated for column 'val' at row 2 |
+---------+------+------------------------------------------+
1 row in set (0.00 sec)
mysql80 136> SELECT * FROM t1;
+-----+-----+
| num | val |
+-----+-----+
| 1 | one |
| 2 | |
+-----+-----+
2 rows in set (0.00 sec)
こっちももちろんNULLに当たらなければSTRICT + ALTER TABLEでもエラーにはならないので、「NULLABLEからNOT NULLにする時はUPDATEでまずNULLを排除しましょう」という極めてフツーの感じになるのであった。
mysql80 136> SELECT @@session.sql_mode;
+-----------------------------------------------------------------------------------------------------------------------+
| @@session.sql_mode |
+-----------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql80 136> CREATE TABLE t1 (num int PRIMARY KEY, val varchar(32));
Query OK, 0 rows affected (0.01 sec)
mysql80 136> INSERT INTO t1 VALUES (1, 'one'), (2, NULL);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql80 136> UPDATE t1 SET val = 'z' WHERE val IS NULL;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql80 136> SELECT * FROM t1;
+-----+------+
| num | val |
+-----+------+
| 1 | one |
| 2 | z |
+-----+------+
2 rows in set (0.00 sec)
mysql80 136> ALTER TABLE t1 MODIFY val varchar(32) NOT NULL DEFAULT 'z';
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql80 136> SELECT * FROM t1;
+-----+-----+
| num | val |
+-----+-----+
| 1 | one |
| 2 | z |
+-----+-----+
2 rows in set (0.00 sec)