TL;DR
- 何かを直した結果テストケースが膨らんでいるのはわかる
- その一方で
enable_cascade_triggersなる機能が増えていて面白かった - 以下ピコ太郎風にお願いします（？）
I have a MySQL 9.7 Early Release (first) ..
ダウンロードは https://t.co/eD2BeHLWbZ からだ！
— yoku0825 (@yoku0825) March 14, 2026
MySQL Community Early Access Builds | mysqlhttps://t.co/fG8lMgfL9w
I have a MySQL 9.7 Early Release 2 ..
MySQL 9.7.0 Early Access Release "2" !?https://t.co/UYa8jvUDZS pic.twitter.com/cJWfkppVPL
— yoku0825 (@yoku0825) April 8, 2026
Ah!
＿人人人人人＿
＞ diff -r ＜
￣Y^Y^Y^Y^Y^￣
ということで
$ diff -r mysql-9.7.0-er mysql-9.7.0-er2 | tee diff.txt
..
diff -r mysql-9.7.0-er/sql/sys_vars.cc mysql-9.7.0-er2/sql/sys_vars.cc
7691a7692,7727
>
> /**
> Warn usage of enable_cascade_triggers variable. When it is set
> to false, warning should include triggers do not fire during FK cascade.
> */
> bool enable_cascade_triggers_check(sys_var *self, THD *thd, set_var *setv) {
> if (setv->save_result.ulonglong_value == 0)
> push_warning_printf(
> thd, Sql_condition::SL_WARNING, ER_WARN_DEPRECATED_WITH_NOTE,
> ER_THD(thd, ER_WARN_DEPRECATED_WITH_NOTE), self->name.str,
> "Triggers on child table will not fire during foreign key cascade.");
> else {
> if (!is_sql_fk_checks_enabled(thd)) {
> push_warning_printf(
> thd, Sql_condition::SL_WARNING, ER_WARN_DEPRECATED_WITH_NOTE,
> ER_THD(thd, ER_WARN_DEPRECATED_WITH_NOTE), self->name.str,
> "Enabling trigger execution on child table is supported only with "
> "SQL Foreign Key handling "
> "(i.e with innodb_native_foreign_keys = OFF).");
> } else {
> push_warning_printf(thd, Sql_condition::SL_WARNING,
> ER_WARN_DEPRECATED_SYNTAX_NO_REPLACEMENT,
> ER_THD(thd, ER_WARN_DEPRECATED_SYNTAX_NO_REPLACEMENT),
> self->name.str);
> }
> }
> return false;
> }
>
> Sys_var_bool Sys_enable_cascade_triggers(
> "enable_cascade_triggers",
> "Execute trigger on child tables during foreign key cascade operations for "
> "SQL Engine foreign key handling(i.e. innodb_native_foreign_keys = OFF).",
> SESSION_VAR(enable_cascade_triggers),
> CMD_LINE(OPT_ARG, OPT_CASCADE_TRIGGERS), DEFAULT(false), NO_MUTEX_GUARD,
> IN_BINLOG, ON_CHECK(enable_cascade_triggers_check), ON_UPDATE(nullptr));
..
パッと見つかったのは enable_cascade_triggers なるシステム変数が増えているところ。今までのストレージエンジンForeign Keyを「使わなかった時でも」CASCADEされた更新がTRIGGERを叩けるようにする、みたいな意味なんだろうか。
MySQL 8.4でテスト。t1の子にt2をセットしてUPDATEとDELETEをカスケードさせる。
mysql84 8> CREATE TABLE t1 (num serial, val varchar(32));
Query OK, 0 rows affected (0.01 sec)
mysql84 8> CREATE TABLE t2 (num serial, val varchar(32));
Query OK, 0 rows affected (0.01 sec)
mysql84 8> ALTER TABLE t2 ADD FOREIGN KEY (num) REFERENCES t1(num) ON UPDATE CASCADE ON DELETE CASCADE;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql84 8> INSERT INTO t1 VALUES (1, 'one'), (2, 'two'), (3, 'three');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql84 8> INSERT INTO t2 VALUES (1, 'one'), (2, 'two'), (3, 'three');
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
で、カスケードされたt2側にトリガーで記録を残すようにする。
mysql84 8> CREATE TABLE log (num serial, op varchar(32), dt datetime);
Query OK, 0 rows affected (0.01 sec)
mysql84 8> CREATE TRIGGER tr1 AFTER UPDATE ON t2 FOR EACH ROW INSERT INTO log VALUES (OLD.num, 'UPDATE', NOW());
Query OK, 0 rows affected (0.01 sec)
mysql84 8> CREATE TRIGGER tr2 AFTER DELETE ON t2 FOR EACH ROW INSERT INTO log VALUES (OLD.num, 'DELETE', NOW());
Query OK, 0 rows affected (0.00 sec)
で、DELETEしたりUPDATEしたり。
mysql84 8> SELECT * FROM log;
Empty set (0.00 sec)
mysql84 8> DELETE FROM t1 WHERE num = 1;
Query OK, 1 row affected (0.00 sec)
mysql84 8> SELECT * FROM t1;
+-----+-------+
| num | val |
+-----+-------+
| 2 | two |
| 3 | three |
+-----+-------+
2 rows in set (0.00 sec)
mysql84 8> SELECT * FROM t2;
+-----+-------+
| num | val |
+-----+-------+
| 2 | two |
| 3 | three |
+-----+-------+
2 rows in set (0.00 sec)
mysql84 8> SELECT * FROM log;
Empty set (0.00 sec)
mysql84 8> UPDATE t1 SET num = 102 WHERE val = 'two';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql84 8> SELECT * FROM t1;
+-----+-------+
| num | val |
+-----+-------+
| 3 | three |
| 102 | two |
+-----+-------+
2 rows in set (0.00 sec)
mysql84 8> SELECT * FROM t2;
+-----+-------+
| num | val |
+-----+-------+
| 3 | three |
| 102 | two |
+-----+-------+
2 rows in set (0.00 sec)
mysql84 8> SELECT * FROM log;
Empty set (0.00 sec)
空っぽ。
これがMySQL 9.7.0-er2で enable_cascade_triggers をONにしてやると
mysql97 15> SELECT @@session.enable_cascade_triggers;
+-----------------------------------+
| @@session.enable_cascade_triggers |
+-----------------------------------+
| 0 |
+-----------------------------------+
1 row in set (0.001 sec)
mysql97 15> SET SESSION enable_cascade_triggers = ON;
Query OK, 0 rows affected, 1 warning (0.000 sec)
mysql97 15> SHOW WARNINGS;
+---------+------+----------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------------------------------------------------+
| Warning | 1681 | 'enable_cascade_triggers' is deprecated and will be removed in a future release. |
+---------+------+----------------------------------------------------------------------------------+
1 row in set (0.000 sec)
mysql97 15>
mysql97 15> SELECT @@session.enable_cascade_triggers;
+-----------------------------------+
| @@session.enable_cascade_triggers |
+-----------------------------------+
| 1 |
+-----------------------------------+
1 row in set (0.000 sec)
出たー。
mysql97 15> DELETE FROM t1 WHERE num = 1;
Query OK, 1 row affected (0.002 sec)
mysql97 15> UPDATE t1 SET num = 102 WHERE val = 'two';
Query OK, 1 row affected (0.001 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql97 15> SELECT * FROM t1;
+-----+-------+
| num | val |
+-----+-------+
| 3 | three |
| 102 | two |
+-----+-------+
2 rows in set (0.000 sec)
mysql97 15> SELECT * FROM t2;
+-----+-------+
| num | val |
+-----+-------+
| 3 | three |
| 102 | two |
+-----+-------+
2 rows in set (0.000 sec)
mysql97 15> SELECT * FROM log;
+-----+--------+---------------------+
| num | op | dt |
+-----+--------+---------------------+
| 1 | DELETE | 2026-04-20 07:35:06 |
| 2 | UPDATE | 2026-04-20 07:35:08 |
+-----+--------+---------------------+
2 rows in set (0.000 sec)
使いどころがあるかどうか（インコンパチだから）は兎も角おもしろ（というか、今までできなかったこともあんまり気にしてなかった）
なお予告(?)通り、FOREIGN KEYのカスケードもトリガーの結果もちゃんとバイナリログに全部書くようになってた。
# at 3572
#260420 7:35:08 server id 1097 end_log_pos 3572 CRC32 0xf56f08ff Rows_query
# UPDATE t1 SET num = 102 WHERE val = 'two'
# at 3572
#260420 7:35:08 server id 1097 end_log_pos 3572 CRC32 0x07966e20 Table_map: `d1`.`t1` mapped to number 130
# has_generated_invisible_primary_key=0
# at 3572
#260420 7:35:08 server id 1097 end_log_pos 3572 CRC32 0x7cbbaf93 Table_map: `d1`.`t2` mapped to number 133
# has_generated_invisible_primary_key=0
# at 3572
#260420 7:35:08 server id 1097 end_log_pos 3572 CRC32 0xf3902a3a Table_map: `d1`.`log` mapped to number 128
# has_generated_invisible_primary_key=0
# at 3572
#260420 7:35:08 server id 1097 end_log_pos 3572 CRC32 0x3392556e Update_rows: table id 133 flags: NO_FOREIGN_KEY_CHECKS_F UNKNOWN_FLAG(0x10)
# at 3572
#260420 7:35:08 server id 1097 end_log_pos 3572 CRC32 0xdecdd0e3 Write_rows: table id 128 flags: UNKNOWN_FLAG(0x10)
# at 3572
#260420 7:35:08 server id 1097 end_log_pos 3572 CRC32 0x58208ae7 Update_rows: table id 130 flags: STMT_END_F UNKNOWN_FLAG(0x10)
BINLOG '
LNflaR1JBAAAQQAAAAAAAACAAABVUERBVEUgdDEgU0VUIG51bSA9IDEwMiBXSEVSRSB2YWwgPSAn
dHdvJ/8Ib/U=
LNflaRNJBAAANgAAAAAAAAAAAIIAAAAAAAMAAmQxAAJ0MQACCA8CgAACAQGAAgP8/wAgbpYH
LNflaRNJBAAANgAAAAAAAAAAAIUAAAAAAAMAAmQxAAJ0MgACCA8CgAACAQGAAgP8/wCTr7t8
LNflaRNJBAAAOQAAAAAAAAAAAIAAAAAAAAMAAmQxAANsb2cAAwgPEgOAAAAGAQGAAgP8/wA6KpDz
LNflaR9JBAAAOgAAAAAAAAAAAIUAAAAAABIAAgACAf8AAgAAAAAAAAAAZgAAAAAAAAADdHdvblWS
Mw==
LNflaR5JBAAAOAAAAAAAAAAAAIAAAAAAABAAAgAD/wACAAAAAAAAAAZVUERBVEWZuah4yOPQzd4=
LNflaR9JBAAANgAAAAAAAAAAAIIAAAAAABEAAgACAQEAAgAAAAAAAAAAZgAAAAAAAADniiBY
'/*!*/;
### UPDATE `d1`.`t2`
### WHERE
### @1=2 /* LONGINT meta=0 nullable=0 is_null=0 */
### SET
### @1=102 /* LONGINT meta=0 nullable=0 is_null=0 */
### @2='two' /* VARSTRING(128) meta=128 nullable=1 is_null=0 */
### INSERT INTO `d1`.`log`
### SET
### @1=2 /* LONGINT meta=0 nullable=0 is_null=0 */
### @2='UPDATE' /* VARSTRING(128) meta=128 nullable=1 is_null=0 */
### @3='2026-04-20 07:35:08' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
### UPDATE `d1`.`t1`
### WHERE
### @1=2 /* LONGINT meta=0 nullable=0 is_null=0 */
### SET
### @1=102 /* LONGINT meta=0 nullable=0 is_null=0 */
# at 3572
#260420 7:35:08 server id 1097 end_log_pos 3572 CRC32 0xbe416b78 Xid = 224
COMMIT/*!*/;
8.4だとこうなので、FKは書かれていないかった（し、トリガーは着火させるようにクエリを流してもトリガーぶんはバイナリログには書かれてない、これは従前といっしょ）
# at 515
#260420 7:32:22 server id 1084 end_log_pos 515 CRC32 0x46c03123 Rows_query
# UPDATE t1 SET num = 102 WHERE val = 'two'
# at 515
#260420 7:32:22 server id 1084 end_log_pos 515 CRC32 0x6de5ebb9 Table_map: `d1`.`t1` mapped to number 85
# has_generated_invisible_primary_key=0
# at 515
#260420 7:32:22 server id 1084 end_log_pos 515 CRC32 0x6a442810 Update_rows: table id 85 flags: STMT_END_F
BINLOG '
htblaR08BAAAQQAAAAAAAACAAClVUERBVEUgdDEgU0VUIG51bSA9IDEwMiBXSEVSRSB2YWwgPSAn
dHdvJyMxwEY=
htblaRM8BAAANgAAAAAAAAAAAFUAAAAAAAMAAmQxAAJ0MQACCA8CgAACAQGAAgP8/wC56+Vt
htblaR88BAAANgAAAAAAAAAAAFUAAAAAAAEAAgACAQEAAgAAAAAAAAAAZgAAAAAAAAAQKERq
'/*!*/;
### UPDATE `d1`.`t1`
### WHERE
### @1=2 /* LONGINT meta=0 nullable=0 is_null=0 */
### SET
### @1=102 /* LONGINT meta=0 nullable=0 is_null=0 */
# at 515
#260420 7:32:22 server id 1084 end_log_pos 515 CRC32 0x0f860ca6 Xid = 5
COMMIT/*!*/;
diffの全文はgistに上げたけど、WEBインターフェースだと後ろがちょん切れて見えないので（本当は33817行ある）ダウンロードしてみないといけなさそう。他に新機能っぽいものはないけどバグ直したついでのテストケースとか増えてる。
0 件のコメント :
コメントを投稿