ラベル 開発日記番外 の投稿を表示しています。 すべての投稿を表示
ラベル 開発日記番外 の投稿を表示しています。 すべての投稿を表示

2012-06-16

『売上猫くん on MySQL』開発日記 - 番外25 - 行ロックについて

小社主催の「FileMaker+MySQL開発の講習」の受講者の方から「FileMakerとMySQLのシステムで、FileMaker(単体システム)のような行ロックってできないの?」との質問を講習前に頂いた。
FileMaker単体のシステムでは、ユーザがあるレコードを編集中に他のユーザが同レコードを編集しようとしても「他のユーザが編集中」旨が表示され、他のユーザは先行するユーザが編集を終了するまで編集することができなくなる。質問の趣旨はこれをFileMaker/MySQLのシステムでも実現したいということ。 
FileMaker標準機能では、FileMaker/MySQL 環境下で排他的行ロックはできない。 が、方法としては2つある。一つはMySQLのロック機能(select for update)を使用する方法(MySQLの排他的行ロック)、もう一つはMySQLテーブルにロックフラグを用意し、最初に編集を開始したユーザがそのフラグに自分のIDを書き込み、後続の他ユーザの編集を禁じるようにごりごりスクリプトで制御方法(FileMaker単体の行ロックと似ているという意味で、FileMakerライクなロック)。 以下はこの2つのロックの実装方法について。

1.MySQLの排他的行ロック
まず、MySQLの排他的行ロック機能を使うと、FileMakerはどのように反応するのか、試してみる。

MySQLコマンドラインで、

  begin;
  select 売上No from 売上ヘッダ where 売上No=1 for update;


を実行する。 次にFileMakerの売上画面で売上No=1のレコードを開いてみると、何事もなく表示される。次にこのレコードの任意のフィールド値を変更してみると普通に変更できる。 さらに、レコードをCommitすると… 出た、必殺のコーヒーマーク! これがMySQLの排他的行ロックである。 ここで、MySQLコマンドラインで、


 commit;


を実行すると、コヒーマークは消えて、レコードがCommitされた。 MySQLの排他的行ロック(select for update)は他ユーザがレコードをselectすることを妨げず、よってFileMakerのレイアウト上にも表示される。 また一旦表示されたレコードは、他のユーザが編集していたとしても、後発ユーザの編集を妨げるよなFileMaker的ロックは行われない。 しかし、いざCommitを実行しようとすると、コーヒーマックが出て、select for update を実行したユーザのトランザクションがCommitかRollbackされるまで、コヒーマークが出たまま、つまりロックされた状態となる。
【FileMakerとMySQLコマンドライン】
select ~ for update 後にFMで編集→確定ボタンすると、コーヒーカップが… 
注:
通常、ロックが発生から60秒後に、「[MySQL][ODBC 5.1 Driver][mysqld-5.1.58-community]Lock wait timeout exceeded; try restarting transaction」というエラーが返る。レコード確定ステップ実行後に後処理がある場合は、このエラーをトラップして処理を中止するなど適切な処理を行わないと、システムが暴走する可能性があるので要注意
FileMakerからも「SQLを実行」スクリプトステップに上記内容を直書きしたり、ストアドプロシージャをCALLすれば、ロックやトランザクションを利用できる。試しに


BEGIN
start transaction;
 select * from 売上ヘッダ where 売上No=1 for update;
 select sleep(60);
 rollback;
END;


というストアドプロシージャを作成して、「SQLを実行」ステップからこれをCALLしてみる。 問題なく実行でき、結果、60秒の間そのレコードは更新できなくなる。しかし 「SQLを実行」 ステップはクライアントPCにDSN登録がないと実行できない、という問題があるため、FileMakerからMySQLトリガを発生させてストアドを実行したい(この方法については『売上猫くん on MySQL』開発日記 - 3 - FMからストアド、トリガを参照)。 ということでトリガから上記のストアドを実行しようとすると、「 Not allowed to return a result set from a trigger」というエラーが出るので、レコードセットが返らないように下記のようにストアドを書きなおした。

BEGIN
    declare temp int;
    start transaction;
        select Count(*) into temp from 売上ヘッダ where  売上No =1 for update;
        select sleep (60) into temp;
        update appliasons set ret="OK" where ID=appId;
    commit;
END
ところが残念なことが重なる。今度は「Explicit or implicit commit is not allowed in stored function or trigger.」というエラーが返ってくる。 これでMySQLのトリガではトランザクションが使用できないことがわかった。まえに小生が書いた記事『売上猫くん on MySQL』開発日記 - 番外12 - トリガの制約に、この件をちらっと書いていた(恥)。 
結論としては、ストアドを直接実行すればMySQLのロックを実行できるが、トリガを介したストアド実行によるロックは不可、ということになる。

尚、FM12の新関数ExecuteSQL関数では、MySQLのロックは利用できない(つか、この関数、相当中途半端な上、意味不明なクエリを発する。書いた通りの動きをしないこともあるし…。この辺りは後日機会があれば)。


2.FileMakerライクなロック
次にFileMakerライクなロック。これは開発者が自分でゴリゴリ書かなくてはならないのでかなり面倒だ。 以下は小社のFileMaker/MySQL講習テキストから実装方法を引用する。
【実装】
“編集”ボタンを用意し、レコードを変更する必要があるユーザには“編集”ボタンを実行して「編集モード」にすることを義務付ける。編集モードでなければレコードの変更は実行できないよう制御するとともに、“編集”ボタン実行時には、当該レコードのロックフラグフィールド(cntLockId)にユーザ固有のIDをセット。このフラグが解除されない間は他ユーザが当該レコードの変更を行えないように制御。ユーザがレコードの更新を終えCommitした瞬間にこのロックフラグフィールドを解除するように設計する。
名称
種類
仕様
cntLockId
フィールド
(
テキスト)
編集ボタンを実行するとこのフィールドにユーザ固有のID=Get ( 持続 ID )が入力され、このIDを持たない他ユーザは編集不可となる。 
cntLockTiime
フィールド
(
タイムスタンプ)
編集ボタン実行時に[cntLockId]とともに本フィールドにはサーバのタイムスタンプ(現日時)が入力される。 このタイムスタンプによりデッドロックになっていないかを判断し、管理者は適切な処理をおこなう。
cntLockAC
フィールド
(
テキスト)
必要に応じて作成。編集ボタン実行時にGet ( アカウント名 )が入力される。 これを利用すれば、編集実行者を画面上に表示することができる。
(ユーザが手入力可能なフィールド)
フィールド
OnObjectEnterスクリプトトリガを用いて、[cntLockId<> Get ( 持続 ID )の場合は、フィールドに入れない(フィールド値を変更できない)ように制御。
編集
ボタン
上記のcntLockId/ cntLockTiime/ cntLockAC参照。左記3フィールドに値を入力後にレコードを確定する。これによりそのレコードは他ユーザによる編集が不可となる。
確定
ボタン
上記のcntLockId/ cntLockTiime/ cntLockACの値をクリア(ロックを解除)後、レコードを確定する。
復帰
ボタン
デッドロックしないよう([cntLockId]の値が残らない)ように制御。
他のボタン
ボタン
削除ボタン、削ボタン(売上明細削除用)など、当該レコードを変更する可能性があるボタン、スクリプトは[cntLockId]に他ユーザのIDがある場合は、実行出来ないように制御する。

【編集ボタンによるFileMakerライクなロック】

[cntLockId]と同じGet(持続ID)を持つユーザのみが編集可となり、確定ボタンが完了すると[cntLockId][cntLockTime][cntLockAC]はnullとなり、他のユーザも編集可能となる。


注:
サーバのRemote Desktop Service を介して複数人でFileMakerを使用する場合、Get(持続ID)は重複すると思われる。この場合、他の方法で個別IDを取得すること。

(土屋)

2012-06-08

SQL関連記事と『売上猫くん on MySQL』開発日記の一覧








【FileMaker/MySQL環境での大容量データ取扱(英語のみ/動画による実例あり)】
Big Table Handling Model in FileMaker/MySQL Environment ― Tentative one
FileMaker vs Access 実行速度比較検証


【FMEasy在庫 on MySQL関連】
『FMEasy在庫』 のバックエンドを MySQL に変える (1) ― 概要
『FMEasy在庫』 のバックエンドを MySQL に変える (2) ― 在庫1
 


【その他のFileMaker/MySQL関連】
 ファイル起動時にFileMakerからMySQLに投げられるクエリ



【MySQL Workbench関連】
MySQL Workbench の EER モデリングツールでリレーションシップ検証 1 (Identifying と Non identifying)
MySQL Workbench の EER モデリングツールでリレーションシップ検証 2 (外部キー制約の種類と動作)

MySQL Workbench の EER モデリングツールでリレーションシップ検証 3 (リレーションシップアイコンの種類と使い方)
 



【売上猫くん開発日記 ― 番外編】
番外1 -全データベース削除→リストア
番外2 - MySQLミラーサイト
番外3 - 0を書き込みできない
番外4 - mysqldump で文字化け?
番外5 - ポータルで検索できない
番外6 - リモートのログを mysqlbinlog できない
番外7 - FileMaker内にMySQLの一般ログを表示する
番外8 - insert...select構文とODBC
番外9 - 外部キー制約設定でのたうちまわる
番外10 - 開発再開
番外11 - 外部キー ってさぁ… orz
番外12 - トリガの制約
番外13 - MySQL Workbenchの憂鬱(ほぼ私的メモ)
番外14 - テーブル毎に権限を設定する(ほぼ私的メモ)
番外15 - 再び権限(私的メモ)
番外16 - FMで大きなSQLテーブルは扱えるのか? ― その1
番外17 - FMで大きなSQLテーブルは扱えるのか? ― その2
番外18 - FMで大きなSQLテーブルは扱えるのか? ― その3
番外19 - FMで大きなSQLテーブルは扱えるのか? ― その4
番外20 - ウィンドウ内容の再表示ステップ実行時のクエリ
番外21 - βリリースしまつた
番外22 -SQL SECURITY の DEFINER/INVOKER指定
番外23 - FileMaker vs Access ~ 実行速度編 ~
番外24 - MySQL 5.5は使用できるのか?
番外25 - 行ロックについて


【売上猫くん開発日記 ― 本篇】(←メインになる筈が、見捨てられた哀れな本編)
1 (題目無)
2 - 開発環境
3 - FMからストアド、トリガ



2012-06-05

『売上猫くん on MySQL』開発日記 - 番外24 - MySQL 5.5は使用できるのか?

FileMakerとMySQL 5.0/5.1で開発してきた人へ:

『売上猫くん on MySQL R5.0β』は、FileMaker 11(FM11)/MySQL 5.1で開発した。 そのDBダンプをMySQL5.5上 にリストアして、FM11フロントエンド(NekoApp.fp7)からアクセスしようとすると、以下のような状況となり、アクセスできない。



このNekoApp.fp7をFileMaker 12(FM12)でNekoApp.fmp12に変換し、MySQL5.5にアクセスするとどうか?



と、さらに悲惨なこととなる。


NekoApp.fp7のリレーションシップ画面を開いてみると、下図のように一見すると正しくテーブルは認識されているようだが、TOをダブルクリックしてみるとTOとテーブルのリンクが消失していて、テーブルを指定し直す必要がある。


また、その指定し直しの過程でリレーションシップが飛んでしまったりもする。 TOとリレーションシップをすべて再設定・確認するだけども大変な作業になるし、検証作業も含めると… 膨大な作業になる。


注:
  • 使用しているODBCのバージョンは、5.1.11。 
  • FM社が公式サポートするMySQLは現時点でMy SQL 5.1 Community Editionのみ。
  • MySQL5.1を介さず、最初からFM11/12とMySQL5.5でリレーションシップ(TO)を作成しておけば、とりあえずは動作するようである(要検証)。

2011-11-03

『売上猫くん on MySQL』開発日記 - 番外23 … FileMkaer vs Access ~ 実行速度編 ~

FileMaker と Access をフロントエンドアプリとして 、MySQL 上のデータベースを操作した場合の実行速度を比較テストしてみた。 比較テストの動画は→こちら(Youtubeへ)。 


テスト方法:
FileMaker Pro 11 で開発し、今年1月にリリースした『売上猫くん on MySQL R5.0β』を使用。 さらに今回のテストのため、本製品のAccess版を作成した。 といっても、機能は売上・請求機能に限定し、その機能のいくつかも削られたプロトタイプとなっている。 システム構成は以下の通り。


MySQL上のデータベース「neko」には、FM猫からもAccess猫からも同時に接続・操作が可能となっている。

テスト結果
以下がテスト結果である。 

テスト内容Access FileMaker
1. プログラム起動 (Program startup)54
2. レコード表示 (Records display)54
3. レコード検索 (Records find)35
4. レコードソート (Records sort)52
5. レコード入力 (Record input)53
合計点(Total)2318
注:各項目を1~5点で評価


表にあるテスト項目の他にも、80ページ程度の請求書の一括印刷テストも行ったが、FM/ACCESSいずれもストレスを感じること無く、印刷(PDF化)できた。今回のテストは、売上テーブルで11.6万件、売上明細テーブルで170万件を入れて行っているが、数百万件、数千万件のレコードを扱うのであれば、開発者によるカスタムのSQLクエリを発行できるAccessの優位度が増すことになる。


ダウンロード:
本テストで使用した『売上猫くん on MySQL R5.0β』のDOWNLOADは→こちら
尚、今回使用したAccess版は非公開。

以上


【変更履歴】
12/06/05 記事タイトル変更

2011-01-25

『売上猫くん on MySQL』開発日記 - 番外22 -SQL SECURITY の DEFINER/INVOKER指定

ようやく再リリース!、と思ったのもつかの間。 MySQLデータベース neko のインストール後、テーブル(実際はビュー)にアクセスできない現象が発生。 CREATE VIEW するときの SQL SECURITY [DEFINER | INVOKER]に問題があることがわかったので、以下、そのメモ。
(ということで、1/25現在、ダウンロード停止中 )
(1/25 18:00~、ダウンロード再開)

DEFINER/INVOKER

DEFINER指定:
Stored/Viewに対して実行アカウントがExecute/Selectできる権限さえあれば、Stored/Viewの内容はDEFINERの権限で実行される→Stored/Viewが実行できない可能性は少ない(DEFINERがroot@localhost なら、ほぼ確実に実行できる筈。)

INVOKER指定:
Stored/Viewに対してExecute/Selectできる権限があっても、Stored/Viewの内容は実行アカウントの権限に基づき実行される→Stored/Viewが実行できない可能性は、DEFINER指定に比し大きい。

例:
CREATE DEFINER = 'admin'@'localhost' PROCEDURE p1()
SQL SECURITY DEFINER (or INVOKER)
BEGIN
UPDATE t1 SET counter = counter + 1;
END;


DEFINER指定:実行アカウントに Execute権限さえあれば、t1テーブルのUPDATE権限は不要( 'admin'@'localhost'の権限で実行される)
INVOKER指定:INVOKER(実行者) は Execute権限とUPDATE権限の両方必要。 

尚、下記サイトでは、セキュリティ上、INVOKER の使用を推奨している。


参考サイト
http://dev.mysql.com/doc/refman/5.0/en/stored-programs-security.html
(対応の日本語頁は無く、DEFINER | INVOKERに関する日本語サイトも少なし)

以上

2011-01-17

『売上猫くん on MySQL』開発日記 - 番外21 - βリリースしまつた

ダウンロード再開しまつた。 MySQL 5.5 に対応させようとしましたが、簡単には修正できなさそう。当面は諦めモード。 ということで、本製品は、現段階では、MySQL 5.5 には非対応!ですので、ご注意ください。 (11/01/21 土屋追記)



最新版のMySQL 5.5で動作しないことが判明したため、一時ダウンロード停止! トホホ…(11/01/20 土屋追記)



ようやく「売上猫くん on MySQL 5.0」のβ版(フリーウェア)をリリースすることになりました。
売上明細のテーブルに200万程度のレコードを入れてテストを行っています。
現時点でできるかぎりの巨大テーブル対応と、ストアドプロシジャやトリガを使用した高速化を行っています。 200万行のテーブルがあっても、起動が若干遅いこと、起動後最初に売上明細テーブルにアクセスにいくと10秒位コーヒーマークがでる位で、使用できるレベルではないか、と思ってます。 1000万件クラスのテーブルになると、今以上の対策がいるかも知れないです。

ご興味のある方は下記からダウンロードしてくださいね。 

売上猫くん on MySQL 5.0βのサイト
http://www.tpc.jp/product/neko/50/index.htm

ダウンロードページ


尚、バージョンを 5.0 としているのは、FileMaker Pro 5/6 で作成した旧製品「売上猫くん 4.5」の機能のほとんどを継承しているからです。データベース(バックエンド)に MySQL を使用した猫くん製品は、これが最初となります。


2010-11-16

『売上猫くん on MySQL』開発日記 - 番外20 - ウィンドウ内容の再表示ステップ実行時のクエリ

SQLデータベースを使用時、あるユーザが行った更新は他のユーザの画面に即時反映されることは無い。他のユーザが更新を行ったか否かを確認するには、[ウィンドウ内容の再表示]ステップを使用するが、このステップには、「キャッシュ結合結果を書き込む」「キャッシュされたSQLデータ」という2つのオプションがある。 オプションによって、SQLデータベースに送られるクエリにはどういう違いがあるのか?

1.キャッシュ結合結果を書き込む/キャッシュされたSQLデータの両オプション選択時
SELECT ID,`〒`,`住所` FROM vw_zips WHERE ID IN(121013,121014,~) 等のクエリを発行、画面は更新される

2.キャッシュ結合結果を書き込のみ
クエリ実行されず、画面未更新

3.キャッシュされたSQLデータのみ
上記1と同様

4.オプション無
クエリ実行されず、画面未更新

FileMakerの[レコード]-[ウィンドウ内容の再表示]を実行すると、1と同じ結果となり、画面は更新される。


注:
  • Selectクエリの対象となるフィールドは、レイアウトテーブルの全フィールドと、そのレイアウト上の関連フィールドに限られる。 よって、関連フィールドの値を最新に更新したい場合は、そのフィールドをレイアウト上に明示的に配置しなければなならい点に要注意。
  • 他ユーザが追加したレコードについては、[ウィンドウ内容の再表示]ステップでは表示できない。追加されたレコードをSelcectするような検索を実行すること。
参考URL
http://www.filemaker.co.jp/help/html/scripts_ref2.37.9.html


2010-11-05

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

前回は、FM社製のドキュメント(日本語版英語版)内の一文を引用し、それを小生なり解釈すると「他のフロントエンド開発ツールに比べるとFileMakerのESSはかなり限定されていること、また一見してFileMaker単体のシステムと同じ結果を返すように見えても異なることがある。ユーザはESSの限界と特性を意識して使用すべし」となる旨のことを書いた。これに加えて、同ドキュメントにもう一つ気になる文章がある。
    ESS は、FileMaker Pro ソリューションを、FileMaker Pro のみをベースにしたソリューションの限界を超えてスケールアップできる手段として設計されたものではありません。(ESS is not designed as a means to allow a FileMaker Pro solution to scale beyond the limits of a purely FileMaker Pro based solution.)
この一文は何のことを言っているのか? この日記番外シリーズにも書いたように、FileMakerソリューションであっても、当然ながら、SQLデータベースの多くの機能---ビュー、ストアドプロシジャ、トリガ、ログ照会、バイナリ/トランザクションログからのリカバリ等----FileMaker純正データベースエンジンでは不可能だった機能の恩恵に浴せる。この場合、主語を“ESS”ではなく“FileMaker”にすれば、「FileMaker Pro のみをベースにしたソリューションの限界を超えてスケールアップできる」のである。

とするとこの文は、「FileMaker純正データベースエンジンが実用的には(“論理的”にではない)取り扱うことができない大きなテーブルをSQLデータベースのそれに置き換えても、やはり実用的には取り扱うことはできない」と言っているのだと思われる。 タイトルにある「 FMで大きなSQLテーブルは扱えるのか?」という命題への直接否定のようだ。

直接否定だとすると、FileMakerの理論上、仕様上の限界はデータベース単位で8TBであるが、実用上の“限界”はどのくらいなのだろう? 古い記事だが、「(個人的な意見だがテーブルベースで)100万を超える位なら大丈夫だよ」とある。 小社でも数十フィールドあるFileMakerテーブルに100万~200万弱のレコードを収納して数年間、運用している。 200万レコード程度をFileMakerの“限界”と仮定すると、その“限界”を超えて、と主張するには、レコード件数的には400万レコード以上をテスト時の最大数とすれば十分だろうか。ちなみに、『売上猫くん on MySQL』のテスト環境で、いまのところ一番大きなテーブルのレコード数は約180万件である。

ただレコード件数をいくら増やそうとも、ユーザが強いストレスを感じたり、安定性が無いシステムでは“実用的”とは言えない。 “実用的”と言うには、その1とその2で書いたような問題をできる限り顕在化させてはいけない。問題6の激遅ソートは回避策がないので、ユーザによるカスタムソートの実用性は非常に低い。 その他の問題については、回避策を施したり、 運用で調整する(例えば、.fp7ファイルをサーバ上に置かず、ローカルに置く)ことにより、緩和できるものと判断している。 だが結局、「FileMaker Pro のみをベースにしたソリューションの限界を超えてスケールアップでき」たかどうかというのは、公正なエンドユーザの判断に拠る、としかいいようがない。

(土屋)

注:
「~限界を超えて~」については、上記の英文をキーにググってみたが(その3の参考URLを参照)、2007年当時は否定的な見方が優勢だった。 2008年以降は関連する記事を見つけることができなかった。


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

2010-11-04

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

FileMaker のVer9リリース時位にFM社が作成した不思議なドキュメントがある。『Introduction to External SQL Sources』(邦題:外部SQLソース入門)というタイトルが付いており、日本語版英語版がある。 読んでみると、

    ESS 機能セットは、FileMaker Pro をSQL データソースの「フロントエンド」として利用できるようにすることを意図したものではありません。 (The ESS feature set is not intended to allow FileMaker Pro to act as a “front end” to SQL data sources.)

と、衝撃の1文がある。ハァ? SQLデータベースに接続してFileMakerを使用しようとする者ならだれてもFileMakerまたはFileMakerで作成したアプリ(.fp7)がSQLデータベースのフロントエンドとなる、と思っている筈だ。 試しに「filemaker フロントエンド」をキーにググってみると、そう確信して記事を書いている人がたくさんいることがわかる。ところが、FM社の公式見解は、FileMaker(のESS)は、
「フロントエンド」として利用できるようにすることを意図したものではない、のである。 では、FM社がESSとか呼んでる機能なんなの?、ということである。 小生はフロントエンドとは、「ユーザが操作する部分=ユーザインタフェイス」位に理解していたのだが、念のためググってみると、こんな感じである。

Wikipedia
フロントエンドは、ユーザーと直接やりとりするソフトウェアシステムの部分を指し、バックエンドはフロントエンドへの出力を生成する部分を指す

IT用語辞典バイナリ
フロントエンドは、一般的に、表示やデータ入力を行うための仕組みを指し、ユーザーインターフェースUI)と同義と同義で用いられることも多い。
IT用語時点
ユーザからの操作の受付や画面表示などを担当するGUI(グラフィカルユーザインタフェース)プログラムなどがこれに当たる。

元々の小生の理解と大差は無い。であれば“ESS”はバックエンド(データベース)を操作するためのユーザインタフェイス=フロントエンドと言ってもいい筈だ。ただのユーザインタフェイスなんだから。 にもかかわらず、「FileMaker はフロントエンドじゃないんだ!」と宣言するFM社の意図はなんなのだろう?

ユーザはこの文章についてどう思っているのかと思いググってみたが、それらしき記事は日本のサイトには見当たらなかった。 海外のサイトには、上記のドキュメントに関する記事・投稿がいくつかある。

参考URL
SQL as backend? (2007年7月、V9当時?)
SQL performance: Alpha Five vs. Filemaker 9 benchmarks (2007年9月)
07.12.07FileMaker 9 SQL Link… ish(2007年9月)
beginners odbc question

特に3つめのURLは、Servoyという開発ツールと比較しながら、FileMakerの問題点を以下のように列挙している。

  1. SQLデータ変更時、その変更が他のユーザの画面に反映されない
  2. レコードレベルのロック機能がない
  3. テーブルやフィールドへの変更が自動的に反映されない
  4. ユーザが作成するSQLクエリを発行できない(SQLクエリは全てESSにお任せ)
  5. 未サポートのデータ型がある
  6. 検索が遅い
  7. ソートはさらに遅い(バックエンドでソートを行わず、一旦FMにデータをロードした後にそのデータをソートするため)
  8. データベース/テーブルをALTERできない

なんとなく解ってきた気がする。 開発者はFileMakerをフロントエンド開発ツールとして使用し始めると、他ツールと比較していろいろと不便な点があることに気がつく。 特に1.データ型、2.ユーザ独自のSQLクエリが実行不能、よって、3.検索、特にソートが非常に遅くなるというのは大問題で、簡単な回避策は無い。 ところが、多くのフロントエンド開発ツールは、何の問題もなくこれらのことを実行できる。 つまり、FM社が「フロントエンドにはなりませんよ」というのは、「他のフロントエンドツールにできることでも、FileMakerではできないことがあります。 その代り、FileMakerっぽくSQLのデータをある程度なら扱えますよ。 でも、FileMaker純正データベースとは違った結果が返ることも大いにありうるので、そこら辺、凄く注意して使ってね!」というのがかなりホントのところではないかと思われる。

(土屋)






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

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』開発日記の記事一覧

2010-09-22

『売上猫くん on MySQL』開発日記 - 番外12 - トリガの制約

CREATE OR REPLACE View はストアドプロシージャでは実行可能ですが、トリガから呼び出すと以下のようなエラーが返ります。

ERROR 1422: Explicit or implicit commit is not allowed in stored function or trigger.

ネット検索してみたところ、以下の命令を含むストアドプロシージャをトリガ呼び出しすると、暗黙のコミットが起こり、肝心のストアドプロシージャが実行されないらしいです。

ALTER FUNCTION, ALTER PROCEDURE, ALTER TABLE, BEGIN, CREATE DATABASE, CREATE FUNCTION, CREATE INDEX, CREATE PROCEDURE, CREATE TABLE, DROP DATABASE, DROP FUNCTION, DROP INDEX, DROP PROCEDURE, DROP TABLE, LOAD DATA INFILE LOCK TABLES, RENAME TABLE, SET AUTOCOMMIT=1, START TRANSACTION, TRUNCATE TABLE, UNLOCK TABLES

参ったな...。

参考:暗黙のコミットを引き起こすステートメント



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

2010-09-10

『売上猫くん on MySQL』開発日記 - 番外11 - 外部キー ってさぁ… orz

MySQL Workbench 5.2 の外部キー作成クエリがオカシイっぽいことは 番外10 書いた通り。 今回もなかなか思ったように動いてくれない。


この Referenced Column で選択したいのは estimatedId なのだが、候補として表示されるのはなぜか[customerNo]のみ。 どうにもならないので、構わず“Apply”を実行すると、次のようなSQL文が表示される。

ALTER TABLE `neko`.`estimatedtls`
ADD CONSTRAINT `fk_purchaseId_est`
FOREIGN KEY (`purchaseId` )
REFERENCES `neko`.`estimates` (`customerNo` )
ON DELETE CASCADE
ON UPDATE NO ACTION
, ADD INDEX `fk_purchaseId_est` (`purchaseId` ASC) ;

ここで cutomerNo を強引に estimateId に書きかえて、“Apply SQL” を実行してみると

ERROR 1005: Can't create table 'neko.#sql-be8_19b' (errno: 150)

なんてエラーが出る。 そこでしばし調べてみると、

, ADD INDEX `fk_purchaseId_est` (`purchaseId` ASC) ;

が機能していないようで、仕方なく、purchaseId の索引を別途作成。 その後、上記SQL文から「,ADD ~」移行を削除して、実行するとどうにか外部キーが作成できた。
Workbench の生成するSQL文はそのままではエラーが出ることがしばしばあるようだ。

土屋

追記(10/09/10)
companies というテーブルからあるフィールド(cmnKey)を削除しようとすると、

  ERROR 1025 (HY000): Error on rename of...(errno: 150)

と表示される。 削除しようとしているフィールドが他から参照されているから削除できない、とのことだが、この cmnKey フィールドはどこからも参照されていない。 companydepts というテーブルからは外部キー制約を companies に対して設定しているが、この制約の REFERECES フィールドは cmnKeyではない。 要するにどう考えてもオカシイ。 どうもMySQLのバグらしいことがわかったので、cmnKey とは全く関係ないこの外部キー制約を一旦削除。 その後、cmnKey を削除することができた。 どうもMySQLはオカシナことが多発し過ぎる。


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

2010-08-23

『売上猫くん on MySQL』開発日記 - 番外10 - 開発再開

4月末に入ったヘビーで超急ぎの仕事が入った。 終わってみると、風薫る5月も紫陽花の6月もとっくに過ぎて夏真っ盛りになっていた。 今年も紫陽花は見れないまま。 ということで、大中断していた『売上猫くん on MySQL』の開発をまたぁーりと再開。 一旦、開発の慣性がなくなると、再加速し走らせるのは異常に大変なのですよ。


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

2010-04-13

『売上猫くん on MySQL』開発日記 - 番外9 - 外部キー制約設定でのたうちまわる

システム開発、運用に様々なトラブルは付き物で、ストレスの元。 特に「マニュアル通りにやってるはずなのにできない、直らない」というのは凹む。 それでも最近は「トラブルも経験のうち」、「(自社の)ナレッジベースに入れてノウハウにしよう」、「ブログに書いて世の中に貢献しよう(笑)」とか、達観しはじめた。が、あまり長い時間、しょんもないことで時間を浪費すると、凄まじく凹む。

さて、今回は外部キー制約の設定について。ここでも凹んだというか、疲弊した。FileMakerのリレーションのところでそれらしきことは簡単にできる。 ただ、このシステムは折角MySQLをデータベースとして使うのだから、PHPやフレームワーク等の多言語、他環境での開発も視野に入れている(というより、実は、当初はFileMaker よりもCakePHPでのWeb開発が先行していたが、大人の事情で優先順位を変更した)。 であれば、MySQL側でできることはできるかぎりMySQLに任せた方が、開発効率が(システムのパフォーマンスも)上がる。 閑話休題。


データが入力されているテーブルに外部キーを設定しようと以下を実行。

alter table neko.estimatedtls
add foreign key estimatedtls(estimateId) references estimates(ID)
ON DELETE cascade ON UPDATE NO ACTION
,add index estimateId (estimateId);


いろいろシンタクスを弄ってみたが駄目。 そこでデータが無い同様のテーブルを作ってやってみると旨くいく。どうやら既存のデータに問題があるらしいことがわかったので、テーブルを一旦空にして外部キー制約を設定、データを取り込み直そうとするとエラーが出る。 MySQL有名某サイトにそれらしい記事を発見。 苦節2日、どうにか原因は分かった。親テーブルの参照キー(ID)にない外部キー(estimateID)が子テーブルにあることが原因らしい。 そこで、そのはぐれ者の子レコードを削除しようと以下を実行。

delete from estimatedtls where
(select ID from estimatedtls where estimateId not in
(select ID from estimates))

と、「Error Code : 1093 You can't specify target table 'estimatedtls' for update in FROM clause」と出る。 どうもこういうサブクエリはUpdataやDeleteでは使用できないらしい。しかたなく、サブクエリの結果をテンポラリテーブルに一旦納めて、それをwhere句に使用することにした。

create temporary table temp(id int);
insert into temp select ID from estimatedtls
where estimateId not in (select ID from estimates);
delete from estimatedtls where Id in (select id from temp);

結果、成功。 親テーブルに属さないはぐれ者の子レコードを削除できた。 そこで、上記の Alter table以下を再実行すると、めでたく外部キー制約を設定できた。 これにより、FileMakerのリレーションでの設定に関わりなく、見積を削除すると見積明細も自動的に削除されるようになった。
以上、めでたし。

追記(10/08/23)
MySQL Workbench 5.2.16 Beta のGUIを利用して外部キー制約を設定しようとすると、[Column]でフィールドのチェックボックスをチェックできないことがある。 どうもWorkbenchのバグのようだ。 この場合はしょうがないので、外部キーのSQL文を書いて実行すること。 尚、Workbenchの最新版では解消している可能性も。


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

2010-03-31

『売上猫くん on MySQL』開発日記 - 番外8 - insert...select構文とODBC

以下、ODBC 5.1.5だと×、5.1.6だとOK

INSERT INTO estimates(customerNo) SELECT customerNo FROM estimates WHERE ID=5

これ、痛い。 この構文使えずに困っている人、結構いるんじゃなかろうか? 回避方法も見つからないし。


参考サイト:Bug #42905


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

2010-03-29

『売上猫くん on MySQL』開発日記 - 番外7 - FileMaker内にMySQLの一般ログを表示する

MySQL の一般ログをFileMakerで表示する方法。

1.MySQLのMySQLデータベースをDSN登録する。
2.my.ini に log-output=TABLE,FILE を指定。
3.以下をMySQLコマンドラインで実行。
mysql> SET @old_log_state = @@global.slow_query_log;
mysql> SET GLOBAL general_log = 'OFF';
mysql> ALTER TABLE mysql.general_log ENGINE = MyISAM;
mysql>ALTER TABLE `mysql`.`general_log` ADD COLUMN `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY (`id`);
mysql> SET GLOBAL general_log = @old_log_state;
4.MySQL を再起動。
5.FileMaker のリレーションシップで、上記1で作成したDSNのmysql.general_logテーブル選択。このとき、上記で作成した id を指定。(以下、略)

するとこんな感じでログを表示できる。


FMの自動発行SQLクエリで「?」が頭に点灯したら、ログをチェックするのがよろしいかと。

尚、FM標準コマンドでは general_log テーブルのログは削除できないので、truncate(テーブル内の全行削除) を使用する。以下はFMの「SQLを実行」ステップを使用して、truncate を実行。

参考サイト:一般クエリとスロー クエリのログ出力先の選択

2010/9/24 追記
ログが出力されない場合、mysqlコマンドで
SET GLOBAL general_log = 'ON';
を実行してみる。

2010/10/25 追記
id の属性に AUTO_INCREMENT を追加。

2010/10/15 追記
slow_query についても、上記の要領でFileMaker上に表示可能だが、ログ対象となるクエリ時間の閾値がデフォルトでは10(秒)になっている。この為、ログ書き込みされないと思いこんでしまう。このデフォ値は、my.ini で long_query_time = 1 とするか、set global long_query_time = 1 を実行することにより変更できる。



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