MySQLの更新をホームページに即時反映

投稿：2023-09-06

背景

MySQLにデータを収集して自分専用のホームページに表示するシステムがあり、クライアントからのリクエストでページを生成する際にMySQLから取り出して見せています。データの収集は自動的に行われていますけれど、一度表示したページはそのままなので直近の収集したデータが反映されません。ページ内のJavaScriptでタイマー使い定期的にリロードしてできそうだけど何かつまらない。 MySQLのデータ更新を検知してクライアントへ通知してリロードするようにならないものか。

レコードの更新日時

元々MySQLのカラムに更新日時(update_time)を入れてあり、この属性は追加やカラムを更新した際の日時が自動的に設定されるものにしてあります。詳しくは次のサイトをご覧ください。
MySQL :: MySQL 8.0 リファレンスマニュアル :: 11.2.5 TIMESTAMP および DATETIME の自動初期化および更新機能
こんな感じのコマンドで更新日時のカラムを追加しました。

ALTER TABLE mytable ADD update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

TIMESTAMP型のフォーマットはstrftimeの%Fと同じです。

2023-09-06 23:36:10

レコードが更新された日時ですから全レコード内の最新日時がテーブル全体の更新日時になります。最新日時を取得するSQLは次の通り。

SELECT update_time FROM mytable ORDER BY update_time DESC LIMIT 1;

この記事書いて思い付いたSQLは次の通り。 mysqlクライアントで上手く動作するのを確認済です。

SELECT MAX(update_time) FROM mytable;

更新を検知

MySQLには更新した契機にプログラムを起動してくれる仕組みがない（正しくは、見つけられない）ので、１分間隔で上記の更新日時を求めて日時に変更があれば更新したことが分かります。ただしMySQLへのレコードを追加する側は１レコードにつき数秒かけているので、更新が分かっても最後のレコードを更新し終わるまで様子を見なくてはなりません。そこで更新後１分間待ち、その間に更新されなければ更新完了と判断します。コードの中にあるget_dbh、load_recordは自作の共用関数、emitは後述のEventSourceの関数でイベントupdateをクライアントに渡します。イベント名は自分で好きに決めます。

# データーベース更新通知
my $update_time;
my $update_start = 0;
my $update_id = Mojo::IOLoop->recurring(60 => sub {
	my $dbh = get_dbh($self);
	my @record = load_record($self, $dbh,
		"SELECT update_time FROM slot ORDER BY update_time DESC LIMIT 1",
		[],
		[qw/ update_time /]
	);
	if (!defined $update_time) {
		# 初期化
		$update_time = $record[0]->{update_time};
	} elsif ($update_time lt $record[0]->{update_time}) {
		# 更新中
		$update_time = $record[0]->{update_time};
		$update_start = 1;
	} elsif ($update_start) {
		# 更新完了
		$update_start = 0;
		$self->emit("update");
	}

	$dbh->disconnect;
});

EventSourceで通知

Webサーバーからクライアント（ブラウザ）へ更新を通知する方法としてEventSourceを利用します。詳細は下記からどうぞ。
EventSource - Web API | MDN
Mojoliciousにプラグインがあるのでそれを使います。
Mojolicious::Plugin::EventSource - A plugin to make it eazy to use EventSource with Mojolicious - metacpan.org
URLとコールバックを指定するとクライアントからEventSourceの開始要求が来た際にコールバックが呼ばれます。その中で切断防止用にpingを打つのと、上記の方法でデータベース更新を検知した場合にクライアントへ通知します。変数$notice_uriにはページのURLに/noticeを追加したEventSource用のURLを入れてあります。

$r->event_source($notice_uri => sub {
	# この$selfはEventSource
	my $self = shift;

	# txは弱いリファレンスなのでtxをコールバックで参照して延命を図る
	my $tx = $self->render_later->tx;

	my $ping_id = Mojo::IOLoop->recurring(30 => sub {
		$self->emit("ping", "");
	});

	# データーベース更新通知
	my $update_time;
	my $update_start = 0;
	my $update_id = Mojo::IOLoop->recurring(60 => sub {
		my $dbh = get_dbh($self);
		my @record = load_record($self, $dbh,
			"SELECT update_time FROM pachinko ORDER BY update_time DESC LIMIT 1",
			[],
			[qw/ update_time /]
		);
		if (!defined $update_time) {
			# 初期化
			$update_time = $record[0]->{update_time};
		} elsif ($update_time lt $record[0]->{update_time}) {
			# 更新中
			$update_time = $record[0]->{update_time};
			$update_start = 1;
		} elsif ($update_start) {
			# 更新完了
			$update_start = 0;
			$self->emit("update");
		}

		$dbh->disconnect;
	});

	# 終了
	$self->on(finish => sub {
		Mojo::IOLoop->remove($ping_id);
		Mojo::IOLoop->remove($update_id);
		$tx;
	});

	# 応答を抑止
	$self->render_later;
});

通知を受信してリロード

ページの末尾にJavaScriptを置いてサーバーからの通知を受け取ります。 location.hrefはページのURLで、/noticeを追加してEventSource用のURLにしています。上記のemitで送るイベントupdateを待つように指定します。

<!-- ページを読み終わってから実行 -->
<script>
// イベントを受信
var es = new EventSource(location.href + "/notice");

// 更新イベント
es.addEventListener("update", (e) => {
	location.reload();
}, false);
</script>

おまけ：hypnotoadで失敗

開発と本番の違い

新しい機能を入れる際はmorboを使います。エディタのコードを保存したのを検知してプログラムを再起動する便利な開発版です。本番稼働はhypnotoadを使っています。管理プロセスが起動されるとそこからワーカープロセスを複数個起動します。クライアントからアクセスすると待機中のワーカープロセスが受信してレスポンスを返します。

失敗の肝

データーベース更新通知で使う変数$update_time、$update_startをevent_sourceのコールバックの外に置いてしまいました。

# ここに置いちゃった
my $update_time;
my $update_start = 0;
$r->event_source($notice_uri => sub {
	# 本当はここに置く
});

hyptonoadで失敗した訳

変数$update_timeに更新日時が入りますが、そのタイミングに問題があります。クライアントからページを開いた際のワーカープロセス#1のタイマーで更新日時Aが入り初回は通知なしです。その後データベースが更新され更新日時Bとなりクライアントへ通知するとリロードします。リロードの延長でEventSourceが切断されます(finishコールバックのトコ)。リロードのリクエストを受けた別のワーカープロセス#2が更新日時Bを入れ初回は通知なしです。その後データベースが更新され更新日時Cとなりクライアントへ通知するとリロードします。これを何度か繰り返すとリロード時のワーカープロセスは#1になります。未確認ですがラウンドロビン式に交代していると思います。このときの変数に入っている更新日時は、例えばワーカープロセスが４個あるとして#1(A)、#2(B)、#3(C)、#4(D)となります。 #1にAが入っている状態でタイマーのコールバックで更新日時がAからDに変わったと検知して更新を通知します。そう、全部のワーカプロセスの更新日時がDになるまで更新を通知してくるのでした。変数$update_timeをEventSourceのコールバックの中に置くとリロード時にEventSourceが接続され変数ができるけど中身は未定義値、最初のタイマーでDに設定されるので通知されません。