GA

2021/09/11

pt-online-schema-changeがColumn 'xx' cannot be nullで止まる

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)
​
投稿者 時刻:
ラベル: ,

0 件のコメント :

コメントを投稿

登録: コメントの投稿 ( Atom )