技術めも

MariaDB10.3から導入されたsystem versioned table（正確にはTemporal Data Tablesというらしい）ですが、一見するととても便利な機能ですが、ちょっと注意が必要です。

---

（ひとつめ）

CREATE時にSQLモードの影響を受ける。

SQLモードに「NO_ZERO_DATE」（もしくはTRADITIONAL）が入っていて、「0000-00-00」などの無効な日時を弾く定義を行っているとCREATEできない。（当然ALTER時も影響を受けます）

MariaDB [test]> select @@sql_mode;
 +------------------------------------------------------------------------------------------------------------------------------------------------------+
 | @@sql_mode                                                                                                                                           |
 +------------------------------------------------------------------------------------------------------------------------------------------------------+
 | STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
 +------------------------------------------------------------------------------------------------------------------------------------------------------+

MariaDB [test]> create table t_sv (
     ->   user_id int not null,
     ->   name varchar(32) not null,
     ->   primary key (user_id)
     -> ) with system versioning;
 ERROR 1067 (42000): Invalid default value for 'row_start'

とまあ、こんな感じです。

当方の環境では、「NO_ZERO_DATE」と「TRADITIONAL」を落とすことで、CREATE可能になりました。

（ふたつめ）

row_endというカラム（レコードが切り替えられた日時であり、上のDDLだと勝手にそのような名前で作成される）は実はPRIMARY KEYである。

show create table の結果だと、

MariaDB [test]> show create table t_sv;
 +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 | Table | Create Table                                                                                                                                                                |
 +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 | t_sv  | CREATE TABLE t_sv (
   user_id int(11) NOT NULL,
   name varchar(32) NOT NULL,
   PRIMARY KEY (user_id)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 WITH SYSTEM VERSIONING |
 +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

となるのですが、show index の結果をみると、

MariaDB [test]> show index from t_sv;
 +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
 | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
 +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
 | t_sv  |          0 | PRIMARY  |            1 | user_id     | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
 | t_sv  |          0 | PRIMARY  |            2 | row_end     | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
 +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

というように、user_idとrow_endでPRIMARY KEY となっている。

show create table 上は出てこないのに、不思議。

ちまみに、information_schema.statistics のテーブルにも出てきているので、アプリケーション上で自動的にPRIMARY KEY の判定を行って、何かをしている場合は注意が必要です。

しかし、実は例外もあって、以下のようにそもそもPRIMARY KEY がないテーブルだとrow_endにPRIMARY KEY が設定されない。

CREATE TABLE t (x INT) WITH SYSTEM VERSIONING
   PARTITION BY SYSTEM_TIME INTERVAL 1 WEEK (
     PARTITION p0 HISTORY,
     PARTITION p1 HISTORY,
     PARTITION p2 HISTORY,
     PARTITION pcur CURRENT
   );

これまた超絶不思議。おそらくはclustered indexに自動的に決定されることが起因しているのかもしれない。

試しにxをPRIMARY KEY に変更したら以下のようなエラーがでた。

MariaDB [test]> alter table t add primary key (x);
 ERROR 4119 (HY000): Not allowed for system-versioned test.t. Change @@system_versioning_alter_history to proceed with ALTER.

どうやら変更できないみたい。

（みっつめ）

公式にも書いてありますが、mysqldumpを使った時に、現在のレコードのみが出力されます。

つまり、mysqldumpではsystem versioned table のバックアップはできません。

そのほか、

• 同じデータであっても隠しカラムの値のため、CHECKSUM の値が同じにならない
• データを変更してなくても、ON UPDATEが走る（row_startとかが変わるから）

といった特徴もあります。

安易に使うと、意外なところでハマりそうな気がしますが、社内システムなど限られた環境で扱う分は、威力を発揮しそうな感じがします。

system versioned table はトランザクションIDの履歴対応やら、10.4でアプリケーションタイムをサポートするなど、今後も改良が加えられていくと思いますので、個人的には期待してます。

以上

MariaDB10.3で導入されたSequenceですが、

便利な反面、注意しなければいけないことが2つありましたので、

記載しておきます。

---

（ひとつめ）

テーブルのdefault値にすると、名前次第でmysqldumpから復元できない。

これは以前書いた記事の話です。

たとえば、以下のようなテーブルシーケンスを作成します。

MariaDB [test]> create sequence t_user_seq increment by 0;

MariaDB [test]> create table t_user (
    ->   user_id int not null,
    ->   name varchar(32) not null,
    ->   seq_no bigint not null unique default nextval(t_user_seq),
    ->   primary key (user_id)
    -> );

この状態で、mysqldumpすると、以下のようにテーブルの定義が先に来てしまう関係上、リストアで失敗します。

--
-- Table structure for table `t_user`
--
:
:
:
--
-- Table structure for table `t_user_seq`
--

これは名前順で出力されてしまう仕様上、t_user_seqが後に定義されてしまうため、リストアに失敗するという現象をおこします。

（ふたつめ）

show create table 中に、シーケンスの定義にスキーマ（データベース）名が含まれてしまう。

MariaDB [test]> show create table t_user;
+--------+----------------------+
| Table  | Create Table         |
+--------+----------------------+
| t_user | CREATE TABLE `t_user` (
  `user_id` int(11) NOT NULL,
  `name` varchar(32) NOT NULL,
  `seq_no` bigint(20) NOT NULL DEFAULT nextval(`test`.`t_user_seq`),
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `seq_no` (`seq_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+--------+----------------------+

test というスキーマ名が入ってきてしまっています。

これにより、別スキーマに、

create table ~ like test.t_user;

とかしてしまうと、シーケンスの参照がコピー元となってしまいます。

うっかりやりそうなミスなので、ご注意ください。

以上