mroonga_command()で検索時の時刻がおかしい件

2020年5月8日Mroonga/Groongagroonga,mroonga

ここのところMySQL+mroonga、若しくはMariaDB+mroongaでWEBアプリをゴニョゴニョしておりますが

mroongaに入れたdatetime型のカラムデータをgroongaコマンドで取り出すと、+9時間されているという罠。

PHPのタイムゾーン、MySQLのタイムゾーン、OSのタイムゾーンと色々調べたが全てJST Asia/Tokyoで統一されている。

MySQL経由(mroonga経由)でSELECTすると正しくdatetimeで取得できるが、mroongaコマンドで直接groongaからJSONを取得すると、リザルトのunixtimeが既に+9時間されている。

そういうものかとフロント側で時間を修正してしまってもいいが、方法の違いで同じ様な事になると面倒なので検証。

尚、この問題の出力、諸所の事情から、MyISAMで作ったテーブルをmroongaに変換している。

この辺りで変な事になっているのではないかと思い、これも含めて検証を行う

環境

MariaDB 10.1.23 + Mroonga プラグイン

若しくは

MySQL 5.6.36 + Mroonga 7.03

どちらもストレージモード

アジェンダ? なんていうの?w

MyISAMにデータを挿入し、mroongaに変換したテーブルの出力(test_a)と、
はじめからmroongaで作成したテーブルの出力(test_b)を比較し検証する

(test_a)
MyISAMでテーブル作成
データ挿入
mroongaにテーブル変更
SQLでSELECT
mroongaコマンドで取得

(test_b)
mroongaでテーブル作成
データ挿入
SQLでSELECT
mroongaコマンドで取得

DB作成

CREATE DATABASE test_db DEFAULT CHARSET=utf8mb4;

検証:test_a

まずMyISAMでテーブルを作成し、データを挿入する

CREATE TABLE `test_a` (
	`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
	`jikan` datetime NOT NULL DEFAULT 0 COMMENT '日付',
	PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

INSERT INTO test_a SET jikan='2017-07-20 00:00:00';

一応ここでもSQLでSELECT

mysql> SELECT * FROM test_a;
+----+---------------------+
| id | jikan               |
+----+---------------------+
|  1 | 2017-07-20 00:00:00 |
+----+---------------------+
1 row in set (0.00 sec)

テーブルをmroongaテーブルに変換

ALTER TABLE test_a ENGINE=mroonga;

SQLでSELECT

mysql> SELECT * FROM test_a;
+----+---------------------+
| id | jikan               |
+----+---------------------+
|  1 | 2017-07-20 00:00:00 |
+----+---------------------+
1 row in set (0.00 sec)

ここまではいい。

mroonga_commandでもSELECT

mysql> SELECT mroonga_command("select --command_version 3 test_a");
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| mroonga_command("select --command_version 3 test_a")                                                                                                                                  |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {"n_hits":1,"columns":[{"name":"_id","type":"UInt32"},{"name":"_key","type":"UInt32"},{"name":"id","type":"UInt32"},{"name":"jikan","type":"Time"}],"records":[[1,1,1,1500508800.0]]} |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

groongaコマンドのリザルトは、+9時間された秒数だった。

尚、"2017-07-20 00:00:00″のunixtimeは、1500476400(JSTの環境で取得)

検証:test_b

続いて初めからmroongaで作成、データ挿入

CREATE TABLE `test_b` (
	`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
	`jikan` datetime NOT NULL DEFAULT 0 COMMENT '日付',
	PRIMARY KEY (`id`)
) ENGINE=mroonga AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

INSERT INTO test_b SET jikan='2017-07-20 00:00:00';

SQLでSELECT

mysql> SELECT * FROM test_b;
+----+---------------------+
| id | jikan               |
+----+---------------------+
|  1 | 2017-07-20 00:00:00 |
+----+---------------------+
1 row in set (0.00 sec)

mroonga_commandでSELECT

mysql> SELECT mroonga_command("select --command_version 3 test_b");
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| mroonga_command("select --command_version 3 test_b")                                                                                                                                  |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {"n_hits":1,"columns":[{"name":"_id","type":"UInt32"},{"name":"_key","type":"UInt32"},{"name":"id","type":"UInt32"},{"name":"jikan","type":"Time"}],"records":[[1,1,1,1500508800.0]]} |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

おんなじやないけ!

仮説は半分間違っていたらしぃ

てなわけで、わかったこと

mroongaテーブル(mysql)を通すと、groonga側には+9時間されたTime型(unixtime)で格納するらしい

さて、、、

mroongaテーブル(mysql)でSELECTすると、groonga側の+9時間されたunixtimeをmroongaが-9時間して返すのか。

ストレージモードだから、mroonga側はgroongaにSQL文をgroongaコマンドに変換して投げているのではないのか。

しかしSELECT文で対象カラムの全文検索を行わない場合、MySQLの特性が出る。

100万件くらいあるデータのoffsetなんかすると酷いものだ。

だから初めはMySQL側にもデータがあり、普通のSQL文のリザルトはMySQL側のものを普通にMySQLが返しているものだとばかり思っていたのだけど、、、

いまいちmroongaが何をしているのか判らないでござる

ソース見てる人、教えてクレメンス

懸念があるとすれば、mroongaをインストールした時の環境がUTCであった事。

最初にググってみた時に見つけた、この方の記事に近いかもしれない。

初めからバッチリとJSTにした環境からmroongaをインストールしたら、こんな変な事にはならないのかもしれない。

かもしれない。

そこまでやる気力が無いので放置。

誰か教えてクレメンス