KanboardのDBのSQLiteからMySQLへの変更

調査

Kanboardの公式マニュアルの「Performances — Kanboard documentation」にボトルネックの特定方法が書いてあるので，これに沿って原因を特定する。

まずはデバッグモードを有効にする。「Configuration File — Kanboard documentation」に記載がある通りに，config.default.phpをコピーして用意したconfig.phpに以下の内容を記載する。

// Enable/Disable debug
// define('DEBUG', false);
define('DEBUG', true);

// Available log drivers: syslog, stderr, stdout, system or file
// define('LOG_DRIVER', 'system');
define('LOG_DRIVER', 'file');

Kanboardでタスクを移動させてみて，出力されるdata/debug.logを確認すると大量のSQLが発行されており，以下の通り実行に30 sほどかかっていた。

SQL: total_execution_time=32.167462587357

タスクの移動程度で30秒もかかっているのはさすがに問題だ。

ローカルファイルへのIOに時間がかかっているのだろうか。公式マニュアルの「Sqlite — Kanboard documentation」にSQLiteの最適化方法の記載がある。

以下のように，[Settings]>[About]>[Database]>[Optimize the database (VACUUM command)] を選択すると，SQLiteのDBを最適化してくれるらしい。

ただし，SQLiteを最適化しても対して状況は改善されなかった。

やはり，DBにSQLiteを選択したのが原因かもしれない。そこでDBをMySQLに移行 (マイグレーション, migration) してみる。

SQLiteからMySQLへのマイグレーション

SQLiteからMySQLへのマイグレーションについて，公式マニュアルではそのものずばりの項目はない。一番近いのは「MySQL/MariaDB — Kanboard documentation」だ。

ただし，こちらに書かれているのは以下の通りの一般的なことだった。

1. MySQLのDBを作成
2. config.phpにDBの接続情報を記入
3. SQLをインポート

これで済めば簡単だったのだが，この方法ではうまくいかず，SQLiteからMySQLへのDBのマイグレーションが思いの外困難だった。

SQLiteのダンプ自体は以下のコマンドで簡単に取得できた。

cd data
sqlite3 -batch db.sqlite .dump >dump.sql

しかし，「SQLiteからMySQLへのDBの形式変換は難しい – senooken.jp」にも記載した通り，SQLiteとMySQLとで微妙にSQLの構文が異なり，MySQLへのインポートに失敗した。

「sqlite – Quick easy way to migrate SQLite3 to MySQL? – Stack Overflow」に掲載されている方法を順番に試してみたが，例えばインポート時に以下のエラーが出てしまった。

ERROR 1101 (42000) at line 2: BLOB, TEXT, GEOMETRY or JSON column 'role' can't have a default value

2020-01-13 01:15:49 ERROR    MySQL failed creating table users: 1171 (42000): All parts of a PRIMARY KEY must be NOT NULL; if you need NULL in a key, use UNIQUE instead

途方に暮れて，外部有料サービスも試して「Database conversion – RebaseData」を使うことで，インポートに成功した。しかし，インポート後にKanboardの画面を開くと以下のエラーが表示されるようになってしまった。

Internal Error: Unable to run SQL migrations: Running migration \Schema\version_1, SQLSTATE[42S01]: Base table or view already exists: 1050 Table 'users' already exists (You may have to fix it manually)
Internal Error: Unable to run SQL migrations: Running migration \Schema\version_1, SQLSTATE[42S01]: Base table or view already exists: 1050 Table 'config' already exists (You may have to fix it manually)

振り出しに戻って，KanboardのissueからSQLiteからMySQLへのマイグレーションに関しての情報を探る。

何件かヒットした中で，「SQLite to MySQL migration · Issue #2756 · kanboard/kanboard · GitHub」のissueの情報で解決した。

issue内で有志のoliviermaridatにより作られたマイグレーションツール「GitHub – oliviermaridat/kanboard-sqlite2mysql: Guidelines to migrate a Kanboard SQLite database to a MySQL database」で変換すればよいとのことだ。アカウントやリポジトリーが削除されてしまった場合に備えて，念のため自分のアカウントにもフォークした。

README.mdに記載がある通り，以下のコマンドでリポジトリーを取得してツールを実行することでMySQLに取り込める。

git clone --depth 1 https://github.com/oliviermaridat/kanboard-sqlite2mysql
cd kanboard-sqlite2mysql
git fetch --depth 1 origin tag v1.1.1
git checkout v1.1.1

# ./kanboard-sqlite2mysql.sh <kanboard path> <DB> -u <username> -p
# ./kanboard-sqlite2mysql.sh ../task.senooken.jp senooken_task -u senooken_task -p

この後説明するKanboardのDB設定を施すことで，MySQLで問題なく動作した。

DB設定

DB自体の移転が完了したので，最後にKanboardがDBにMySQLを参照するように設定する。

Kanboardのルートディレクトリー直下のconfig.phpに，以下のようにDBへの接続情報を記入する。config.phpがなければ，config.default.phpをコピーしてconfig.phpにリネームして編集する。

// Database driver: sqlite, mysql or postgres (sqlite by default)
// define('DB_DRIVER', 'sqlite');
define('DB_DRIVER', 'mysql');

// Mysql/Postgres username
define('DB_USERNAME', 'username');

// Mysql/Postgres password
define('DB_PASSWORD', 'password');

// Mysql/Postgres hostname
define('DB_HOSTNAME', 'localhost');

// Mysql/Postgres database name
define('DB_NAME', 'dbname');

これでKanboardのDBにMySQLを使えるようになる。

実際に動作を確認すると以下のように問題ない速度になっていることを確認できた。

SQL: total_execution_time=0.84600329399109

問題ないことを確認できたので，デバッグ設定を元に戻した。

結論

タスク管理WebサービスのKanboardのDBをデフォルトのSQLiteからMySQLに移転した。

SQLiteからMySQLへのデータ変換が思っていたよりも難しく，信頼性の高い情報もそこまで多くなかったので，手間取ってしまった。

長く使うサービスのDBには，SQLiteではなくパフォーマンス上有利なMySQLやPostgreSQLを使うようにしたいと感じた。