日々の覚書: 複数のテーブルのON UPDATE current_timestampなカラムの値を揃える方法を考える の派生形なんですが、 "CURRENT_TIMESTAMP および CURRENT_TIMESTAMP() は NOW() のシノニムです。" なので、ちょっとだけ篠田さんの「使い慣れたSQLに潜む実装依存」に対する補足でもあったりします。
NOW関数は デフォルトでは 「そのステートメントの開始時刻」を返します。
この動作の検証としては以下のステートメントが有名でしょう。
mysql57> SELECT NOW(6), SLEEP(1), NOW(6); +----------------------------+----------+----------------------------+ | NOW(6) | SLEEP(1) | NOW(6) | +----------------------------+----------+----------------------------+ | 2016-12-01 10:16:16.596803 | 0 | 2016-12-01 10:16:16.596803 | +----------------------------+----------+----------------------------+ 1 row in set (1.00 sec) mysql57> SELECT SYSDATE(6), SLEEP(1), SYSDATE(6); +----------------------------+----------+----------------------------+ | SYSDATE(6) | SLEEP(1) | SYSDATE(6) | +----------------------------+----------+----------------------------+ | 2016-12-01 10:16:29.699001 | 0 | 2016-12-01 10:16:30.699117 | +----------------------------+----------+----------------------------+ 1 row in set (1.00 sec)
NOW関数はステートメントの中で2回呼ばれていますが、同じ値を返します。
それに対しSYSDATE関数は「関数が実行された時点の時刻」を返すため、SLEEP(1) をはさんでもう一度実行された時には違う結果を返します。
さてさて、これがレプリケーションをはさむとどうなるか。
master [localhost] {msandbox} (d1) > SELECT @@binlog_format;
+-----------------+
| @@binlog_format |
+-----------------+
| STATEMENT |
+-----------------+
1 row in set (0.00 sec)
master [localhost] {msandbox} (d1) > INSERT INTO sbr VALUES ('NOW', NOW(6)), ('SYSDATE', SYSDATE(6));
Query OK, 2 rows affected, 1 warning (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 1
master [localhost] {msandbox} (d1) > SHOW WARNINGS;
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note | 1592 | Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave. |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
master [localhost] {msandbox} (d1) > SELECT * FROM sbr;
+---------+----------------------------+
| func | dt |
+---------+----------------------------+
| NOW | 2016-12-01 11:35:23.865477 |
| SYSDATE | 2016-12-01 11:35:23.871343 |
+---------+----------------------------+
2 rows in set (0.00 sec)
slave1 [localhost] {msandbox} (d1) > SELECT * FROM sbr;
+---------+----------------------------+
| func | dt |
+---------+----------------------------+
| NOW | 2016-12-01 11:35:23.865477 |
| SYSDATE | 2016-12-01 11:35:23.874431 |
+---------+----------------------------+
2 rows in set (0.00 sec)
SBRの場合、NOW関数はマスターと同じ値が記録されるけれど、SYSDATE関数は同じ値が記録されない。「スレーブでSQLをリプレイした時の現在時刻」になってしまう。
master [localhost] {msandbox} (d1) > SELECT @@binlog_format;
+-----------------+
| @@binlog_format |
+-----------------+
| ROW |
+-----------------+
1 row in set (0.00 sec)
master [localhost] {msandbox} (d1) > INSERT INTO rbr VALUES ('NOW', NOW(6)), ('SYSDATE', SYSDATE(6));
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
master [localhost] {msandbox} (d1) > SELECT * FROM rbr;
+---------+----------------------------+
| func | dt |
+---------+----------------------------+
| NOW | 2016-12-01 11:36:06.626105 |
| SYSDATE | 2016-12-01 11:36:06.626309 |
+---------+----------------------------+
2 rows in set (0.00 sec)
slave1 [localhost] {msandbox} (d1) > SELECT * FROM rbr;
+---------+----------------------------+
| func | dt |
+---------+----------------------------+
| NOW | 2016-12-01 11:36:06.626105 |
| SYSDATE | 2016-12-01 11:36:06.626309 |
+---------+----------------------------+
2 rows in set (0.00 sec)
それに対してRBRは関数をリプレイしないのでどちらでも問題なく動く（そもそも「関数の結果」をバイナリーログに書き込むため、スレーブではリプレイされない）
MIXEDの場合、SYSDATE関数がレプリケーションアンセーフな関数（非決定性関数）のためRBRにフォールバックする。
NOW関数の仕掛けはこうだ。
- クエリー開始時にTIMESTAMPセッション変数の中に値が入っているかどうかを探す
- ちなみにTIMESTAMP変数は32ビット符号つきかつ負値をバリデーションではじいている、つまりunixtime
- TIMESTAMP変数が0ならば、thd->query_start() を読んでTIMESTAMP変数に入れる
- TIMESTAMP変数をunixtimeからDATETIMEに変換して返す
- クエリー終了まで、一度セットされたTIMESTAMP変数を使い続ける
- クエリー終了後、もとのTIMESTAMP変数が0だった場合は0に戻す
mysqlbinlogを出すと、しょっちゅう SET TIMESTAMP= .. で指定されているのを見ることができる。これは、そういう（NOW関数をスレーブでも決定性にするための）意味だったのだ。
# at 6439
#161201 11:35:23 server id 1 end_log_pos 6526 CRC32 0x2e37a667 Query thread_id=9 exec_time=0 error_code=0
SET TIMESTAMP=1480559723.865477/*!*/;
BEGIN
/*!*/;
# at 6526
#161201 11:35:23 server id 1 end_log_pos 6671 CRC32 0xab51a6f0 Query thread_id=9 exec_time=0 error_code=0
SET TIMESTAMP=1480559723.865477/*!*/;
INSERT INTO sbr VALUES ('NOW', NOW(6)), ('SYSDATE', SYSDATE(6))
/*!*/;
# at 6671
#161201 11:35:23 server id 1 end_log_pos 6702 CRC32 0xa975b625 Xid = 73
COMMIT/*!*/;
豆知識でした。
明日 12/2 は @kakuka4430 さんです！
0 件のコメント :
コメントを投稿