2010-10-26

『売上猫くん on MySQL』開発日記 - 番外17 - FMで大きなSQLテーブルは扱えるのか? ― その2

前回の続き

問題4.検索実行時のデータの並び

郵便番号レイアウトの住所で「北海道」を検索すると、MySQL側では以下のようなクエリが走る。

SELECT ID FROM vw_zips WHERE `住所` LIKE '%北海道%'

MySQLは検索対象となったフィールドの文字コード順にデータを返し、それがそのまま表示される(ORDER BY PrimayKey は実行されない)。 この点、FileMakerのデータベースエンジンはテーブル内の並び順(作成順)にデータを返すので、この差異には注意を要す。 尚、その1で書いたように、SQLデータは一旦ロードされると取り込んだ順で保持されるので、プライマリキーにも文字コードにも関係なく、検索結果がぐちゃぐちゃに表示される可能性があることにも注意を要す。


問題5.新規レコード作成時の不審な挙動
a)検索がかかった状態で新規レコードを作成すると、レコードを確定した瞬間にその作成したレコードから新規レコード作成コマンド実行時点で選択されていたレコードに移動してしまう。レコードは作成されているのだが、ブックツールが正しく動作せず、そのままでは作成したレコードに移動できない。 これは単純にバグと思われる。 すぐに気付きそうなものだが、FileMaker社はなぜこのバグを放置したままでいるのだろう?

b)だらだらクエリ---新規レコード作成後のコミット時に、その1で書いた『だらだらクエリ』が走る(レコード数が10万位あると、顕著にだらだら加減が解る)。 我慢できずにエスケープすると、作成したレコードが消失する(FileMaker上でロールバックされる)。


以上で見た問題1~5のようにSQLデータベースの大きなテーブルを扱う際は、レコード(レイアウト)表示、レコード間移動、検索といった基本操作に問題があり、開発者はなんらかの回答を用意しなければならない。 尚、削除、更新については今のところ大きな問題は見当たらない。

問題6 ソートが著しく遅い
FileMaker 側でのソートは著しく遅く、少数のレコードが対象というのでなければ、実用的ではない。 バックエンド側のビューで ORDER BY しておくことは可能だが、解決策とは言えない。


(土屋)

2010/11/05 問題6を追記


関連リンク:『売上猫くん on MySQL』開発日記の記事一覧

2010-10-21

『売上猫くん on MySQL』開発日記 - 番外16 - FMで大きなSQLテーブルは扱えるのか? ― その1

MySQLを使用するメリットの一つは、何百万、何千万もの大量のレコードを持つ巨大なテーブルを扱えること。 ところが、そうした巨大テーブルをFileMaker アプリから操作しようとすると、大きな問題にぶつかる。(尚、下記の環境は、クライアントからFileMaker Server上のアプリファイル(NekoApp50.fp7)にアクセスしている。ローカルでNekoApp50.fp7を実行すれば、実行速度は改善される。)

問題1. だらだらクエリ
例えば、12万件のレコードを持つ郵便番号テーブルをFileMaker のレイアウト上に表示し、一番最後のレコードに移動してみる。移動すると「検索実行中...  クエリーを処理中」と数十秒またされることになる。これはMySQLのログを見てみるとわかるが、

SELECT DISTINCT ID FROM vw_zips WHERE ID>19 ORDER BY ID
SELECT DISTINCT ID FROM vw_zips WHERE ID>1019 ORDER BY ID
SELECT DISTINCT ID FROM vw_zips WHERE ID>2019 ORDER BY ID
SELECT DISTINCT ID FROM vw_zips WHERE ID>3019 ORDER BY ID
・・・
・・・ 中略
・・・
SELECT DISTINCT ID FROM vw_zips WHERE ID>119019 ORDER BY ID

といったクエリをテーブルの全レコード件数(この例では12万件)に達するまで延々と発行し続け、FileMakerにこれまた延々とロードし続ける、という仕様なのである。 実務上、これではユーザから苦情が来るのは必至。 さらに、テーブルデータが100万、1000万件に達する場合は、実用に耐えない。

尚、一度ロードしてしまえば、アプリを閉じるまで、上記の『だらだらクエリ』は実行しないようである。


問題2. 検索を実行すればするほどデータの並びがグチャグチャに?
FileMakerはクエリを実行した順にデータをロードし、一旦ロードされると再ロードは行わない。ユーザの検索実行数が増せば増すほど、レコードは作成順に並ばず、グチャグチャに並んでるように見える。

例えば、郵便番号レイアウトを開くと、FileMakerはそのレイアウトに割り当てられたテーブル(レイアウトテーブル)のレコードの1番目から数十番目までをSELECTする以下のようなクエリを実行し、レイアウト上に表示する(SELECTされるレコード数は、レイアウトの形状、ウインドウの大きさにより異なる)。 

SELECT ID,`〒`,`住所` FROM 郵便番号 WHERE ID IN (1,2,3,4, ~中略~ ,26,27)

次に、ユーザ自ら郵便番号の「9071801」(沖縄県)を検索し、次に「2010004」を検索し、その後レコードメニューから「全レコードを表示」を行うと、以下のような並びになってしまう。


郵便番号「2010004」の後に「0600012」移行が並ぶのは、上記の「全レコード表示」の直後にスクロールダウンした為、SELECT ID,`〒`,`住所` FROM 郵便番号 WHERE ID IN (28,29,~,40,41)というクエリが実行され、該当するレコードがロードされた結果だ。 

FileMaker は内部の SELECTクエリを実行した順にレコードをロードする。 再度検索を行っても、プライマリキーが同じものは再ロードされない。 ユーザが該当件数が少ない検索条件を実行すればする程、レコードは作成順には並ばないため、ユーザからみればグチャグチャに見える。


問題3. レコード総数取得クエリ
SQLテーブルをレイアウトに最初にロードすると以下のクエリが走る。

SELECT COUNT(*) FROM (SELECT DISTINCT ID FROM vw_salesdtls) COUNTER_TABLE 

この SELECT DISTINCT には時間がかかり、180万件のテーブルだとレイアウトにレコードを表示するだけで30秒程度はかかってしまう(但し、一旦レイアウトテーブルをロードすると、アプリファイルを閉じるまで、このクエリは再発行されない)。巨大なテーブルを扱う場合は、VIEWによりレコード総数を制限する必要がある。


問題3については、数百万レコード程度であれば、ユーザも我慢できるかもしれないが、1と2ついてはなんらかの対応が必要だろう。


土屋 


関連リンク:『売上猫くん on MySQL』開発日記の記事一覧

2010-10-13

『売上猫くん on MySQL』開発日記 - 番外15 - 再び権限(私的メモ)

特定のテーブルにGRANTできる権限/グローバルにしかGRANTできない権限
できる権限は以下。
SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, GRANT OPTION, INDEX, and ALTER

つまり、上記以外の権限はテーブル毎に設定することはできない。

  • " The EXECUTION, FILE, PROCESS, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, SHOW DATABASES, SHUTDOWN, and SUPER privileges are administrative privileges that can only be granted globally (using ON *.* syntax)."
参考URL
Re: Grant Execute on selected procedures
MySQL - How to grant FILE privilege?

Privileges Provided by MySQL 権限の詳細説明

(土屋)


関連リンク:『売上猫くん on MySQL』開発日記の記事一覧

2010-10-12

『売上猫くん on MySQL』開発日記 - 番外14 - テーブル毎に権限を設定する(ほぼ私的メモ)

MySQLにロールやグループは無い
かねがね疑問だったんだけど、権限をまとめて管理する機能(FileMakerのアクセス権限セット/グループ定義のようなもの)はMySQL本体にはないことがわかった。 コミュニティーで「他のDBにあんだから、付けてよー」って要望はあるけど、Priority は LOW なので期待薄。 Ver5.5 ではどうなんだろうか。

参考URL:
Creation of user groups http://bugs.mysql.com/bug.php?id=13131


ちなみに、MySQL Workbench には、DBA、BackupAdmin といった権限の“Role”が用意されており、これを選択することにより User に対して簡単に Role を割り当てることができる。 また、下図のように、Role そのものをユーザが定義することができる。


GRANT ~ ON scheme.* すると、 Revoke ~ ON scheme.table できない
あるデータベース内のすべてのテーブルに権限を与え、その後、そのデータベースの特定のテーブルから与えた権限を剥奪=revoke しようとしても、

Error Code: 1147 There is no such grant defined for user 'testuser' on host '%' on table 'infos'

とエラーになってしまう。


GRANTでテーブルの列記はできない
GRANT ~ on dbname.table1, dbname.table2 ~ のようなテーブルの列記はできない 。



ということで、多数のユーザ登録が必要で、且つテーブル毎/コラム毎に権限を設定する場合は、大量のGRANT文を書かなければならない。


(土屋)


関連リンク:『売上猫くん on MySQL』開発日記の記事一覧

2010-10-07

『売上猫くん on MySQL』開発日記 - 番外13 - MySQL Workbenchの憂鬱(ほぼ私的メモ)

『売上猫くん on MySQL』の開発もようやく終盤戦に突入。いままでは root 権限を主に使用して開発をすすめてきたが、リリース版のテストを行うにあたり、できるだけリスクを伴わないユーザ権限を作成し、これによりテストを行わなければならない。

そこでユーザ・権限を“お手軽に”管理・変更できる筈の、MySQL Workbench の Server Administration 機能を使うことにしたが、実はこれが全く“お手軽”ではなかったという御笑いの一席。

MySQL Workbench Ver5.2.16 OSS Beta→5.2.28 CE へアップグレード
このアップグレードを行った途端、Object Browser に mysql / information_schema が表示されなくなる。 「なんかとんでもないことやったかなぁ…?」と思いつつも、 これは以下を見つけて解決。

Information_Schema and mysql databases not shown(http://bugs.mysql.com/bug.php?id=53154)

Please, check "Show Metadata Schemata" checkbox in the Query Editor group of the SQL Editor tab in the Workbench Preferences dialog box. Use Edit > Preferences... menu item to open it. Don't forget to click "Refresh all" in the Object Browser's context menu after that.

Server Administration機能を“リモートから使用するにはSSHサーバが必要
5.2.28CEへアップグレード直後、元々Connection登録してあったリモートサーバについては、SSHサーバが無くても、Server Administration 機能を使用できた(ここが不思議なところ)。 ところが新規にConnection登録したリモートサーバについては、SSHサーバがないとServer Administration 機能は利用できない。
ちなみに、元々あったConnectionについても、“Store in Vault...”ボタンで登録してあるパスワードを消去してしまうと、それ以降はリモート接続できなくなってしまった。

FreeSSHd=SSHサーバを入れる
そんなわけで、MySQLを積んだリモートサーバにSSHサーバを入れることにする。
Windowsでフリーで利用できるなSSHを探してみると、FreeSSHd (FreeSSHd.exeのダウンロード)というのが簡単・お手軽らしい。 インストール方法はここ
インストール時の注意は、
  • 「Shell」ととももに「SFTP」も選択すること。
  • SFTPのタブの「SFTP Home Path」にmy.iniが入っているボリュームを指定する。例えば、my.iniがEドライブに入っているなら、「E:\」と指定する。
New Server Instance でエラー
さて、準備完了となり、“New Server Instance”を実行。ところが途中で下図のエラーが発生する。


Operation failed: File %ProgramFiles%\MySQL\MySQL Server 5.1\my.ini doesn't exist

というエラーが出る。 例によってググり、下記のサイトを見つける。

参考URL:
Workbench doesn't autodetect my.ini properly if %programfiles% not on C:
The second thing which was wrong, was the path to the my.ini file in MySQL Workbench. I have changed it from C:\Programme\mysql\MySQL Server 5.1\my.ini to /Programme/mysql/MySQL Server 5.1/my.ini
OS Language small problem - Server Administration
...someone pointed out that I could ignore this error and continue. This is not obvious. The error message should probably say "Warning", not "Error" and it should indicate that you can specify the exact location in a later step.

つまり上図のエラーが出たら、構わず“Next”をクリック。続くダイアログで[Change Parameters]ボックスをチェックして“Next”。 次のウインドウで「my.ini」の場所を“...”ボタンをクリックして指定する。


次に“OK”して、以下、指示に従いつつ最後までいく。
尚、ここでmy.ini を正しく指定しなくても最後まで一応たどり着くが、その場合は、“Manage Import / Export” を実行した時に、「Workbench "AttributeError:NoneType object has no attribute parametervalues」というエラーが出てしまった。 これでとりあえずは、Server Administration 機能をリモートからも使用できるようになった。

ここでは略したがその他にもエラーが出て散々。またまた2日を潰してた。Workbenchもそうだが、MySQL 関連を動かすのはやはり大変だわ。

土屋


関連リンク:『売上猫くん on MySQL』開発日記の記事一覧