レプリカにだけgenerated columnを追加したらセカンダリインデックスが更新されなかった

TL;DR

• レプリカにだけgenerated columnを足す構成 + binlog_format = ‘ROW’ かつgenerated columnを使ったインデックスがあると、そのインデックスの変更はレプリカでリプレイされない(インデックス上はgenerated columnのデータ型の暗黙のデフォルトになったり、リーフの数がそもそもおかしくなったり)

• インデックス再作成、 ALTER TABLE .. ENGINE = InnoDB でも望む状態にはならなかった。

  • generated columnを削除して再作成も絡めないとダメ

---

5.7.27のレプリケーション構成を作って、マスターにテキトーなテーブルを作る。 binlog_format, binlog_row_image はデフォルトのまま。

$ make_replication_sandbox --how_many_slaves=1 5.7.27

$ ./m

master [localhost] {msandbox} ((none)) > CREATE DATABASE d1;
Query OK, 1 row affected (0.01 sec)

master [localhost] {msandbox} ((none)) > CREATE TABLE d1.t1 (num INT PRIMARY KEY, val varchar(32));
Query OK, 0 rows affected (0.02 sec)

master [localhost] {msandbox} ((none)) > INSERT INTO d1.t1 VALUES (1, 'one'), (2, 'two');                                                                     Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

master [localhost] {msandbox} ((none)) > SELECT @@binlog_format;
+-----------------+
| @@binlog_format |
+-----------------+
| ROW             |
+-----------------+
1 row in set (0.01 sec)

master [localhost] {msandbox} ((none)) > SELECT @@binlog_row_image;
+--------------------+
| @@binlog_row_image |
+--------------------+
| FULL               |
+--------------------+
1 row in set (0.00 sec)

レプリカの側に、generated columnを作る。binlog_format= ROWでも壊れないようにテーブルの末尾に。generateの式はテキトー。

$ ./s1

slave1 [localhost] {msandbox} ((none)) > SELECT * FROM d1.t1;
+-----+------+
| num | val  |
+-----+------+
|   1 | one  |
|   2 | two  |
+-----+------+
2 rows in set (0.01 sec)

slave1 [localhost] {msandbox} ((none)) > ALTER TABLE d1.t1 ADD odd_even ENUM('even', 'odd') AS (CASE num % 2 WHEN 0 THEN 'even' WHEN 1 THEN 'odd' END) NOT NULL;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

slave1 [localhost] {msandbox} ((none)) > SELECT * FROM d1.t1;                          +-----+------+----------+
| num | val  | odd_even |
+-----+------+----------+
|   1 | one  | odd      |
|   2 | two  | even     |
+-----+------+----------+
2 rows in set (0.01 sec)

slave1 [localhost] {msandbox} ((none)) > SHOW CREATE TABLE d1.t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `num` int(11) NOT NULL,
  `val` varchar(32) DEFAULT NULL,
  `odd_even` enum('even','odd') GENERATED ALWAYS AS ((case (`num` % 2) when 0 then 'even' when 1 then 'odd' end)) VIRTUAL NOT NULL,
  PRIMARY KEY (`num`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.01 sec)

これでマスターにINSERTすると

master [localhost] {msandbox} ((none)) > INSERT INTO d1.t1 VALUES (3, 'three');
Query OK, 1 row affected (0.00 sec)

master [localhost] {msandbox} ((none)) > SELECT * FROM d1.t1;
+-----+-------+
| num | val   |
+-----+-------+
|   1 | one   |
|   2 | two   |
|   3 | three |
+-----+-------+
3 rows in set (0.00 sec)

レプリカでもちゃんと見える。ここまではOK。

slave1 [localhost] {msandbox} ((none)) > SELECT * FROM d1.t1;
+-----+-------+----------+
| num | val   | odd_even |
+-----+-------+----------+
|   1 | one   | odd      |
|   2 | two   | even     |
|   3 | three | odd      |
+-----+-------+----------+
3 rows in set (0.00 sec)

ではレプリカにこのgenerated columnを使ったインデックスを作る。

slave1 [localhost] {msandbox} ((none)) > ALTER TABLE d1.t1 ADD KEY (odd_even);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

slave1 [localhost] {msandbox} ((none)) > SELECT odd_even, COUNT(*) FROM d1.t1 GROUP BY odd_even;
+----------+----------+
| odd_even | COUNT(*) |
+----------+----------+
| even     |        1 |
| odd      |        2 |
+----------+----------+
2 rows in set (0.01 sec)

そしておもむろにマスターに100万行くらい突っ込む。

master [localhost] {msandbox} ((none)) > LOAD DATA LOCAL INFILE '/tmp/md5' INTO TABLE d1.t1;
Query OK, 999991 rows affected (4.69 sec)
Records: 999991  Deleted: 0  Skipped: 0  Warnings: 0

master [localhost] {msandbox} ((none)) > SELECT case (`num` % 2) when 0 then 'even' when 1 then 'odd' end AS odd_even, COUNT(*) FROM d1.t1 GROUP BY odd_even;
+----------+----------+
| odd_even | COUNT(*) |
+----------+----------+
| even     |   499997 |
| odd      |   499997 |
+----------+----------+
2 rows in set (0.68 sec)

レプリカではこう。

slave1 [localhost] {msandbox} ((none)) > SELECT odd_even, COUNT(*) FROM d1.t1 GROUP BY odd_even;
+----------+----------+
| odd_even | COUNT(*) |
+----------+----------+
| even     |   999992 |
| odd      |        2 |
+----------+----------+
2 rows in set (0.25 sec)

おそらく想像がつくように、このgenerated columnに触らないインデックスを使わせると正しく出る。

slave1 [localhost] {msandbox} ((none)) > SELECT odd_even, COUNT(*) FROM d1.t1 USE INDEX(PRIMARY) GROUP BY odd_even;
+----------+----------+
| odd_even | COUNT(*) |
+----------+----------+
| even     |   499997 |
| odd      |   499997 |
+----------+----------+
2 rows in set (0.80 sec)

STOREDなgenerated columnはトリガーと一緒でマスターでのみ評価されるから、STOREDなgenerated columnをレプリカにだけ追加すると値がおかしくなるのはどっかで見たことがあったんだけど、VIRTUALでもセカンダリーインデックス使ってるとこうなるのね…。残念至極。