『売上猫くん 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.）

と、衝撃の１文がある。ハァ？　SQLデータベースに接続してFileMakerを使用しようとする者ならだれてもFileMakerまたはFileMakerで作成したアプリ（.fp７）が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』開発日記の記事一覧

『売上猫くん 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』開発日記の記事一覧

『売上猫くん 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』開発日記の記事一覧

『売上猫くん 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』開発日記の記事一覧

『売上猫くん 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』開発日記の記事一覧

『売上猫くん 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』開発日記の記事一覧

『売上猫くん 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』開発日記の記事一覧

『売上猫くん 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有名某サイトにそれらしい記事を発見。　苦節２日、どうにか原因は分かった。親テーブルの参照キー（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』開発日記の記事一覧

『売上猫くん 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』開発日記の記事一覧

『売上猫くん 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』開発日記の記事一覧

『売上猫くん on MySQL』開発日記 - 2 - 開発環境

『売上猫くん on MySQL』の開発環境は以下の通り。

アプリケーションファイル（NekoApp.fp7）は、FileMaker Server Advanced 10（以下、FMServerAdv)上に置き、開発PC1、2、3及び開発Mac 10.6 の FileMaker Pro Advanced 10(以下、FMPA)からそのファイルにアクセスして開発を行う。FMServerAdv を使用する理由には、複数人が開発を安定して行えること、オンラインバックアップがスケジュールできる、という2点が挙げられる。　
サーバ側にODBCをインストールしておけば、クライアント（開発PC）にはODBCは不要である（例外は後述）。

アプリケーション/ODBCの配布・アップグレードの容易さを考えれば、この構成は運用においても一考の価値はあると思う。　
なお、図の「開発PC3 Win2008/TS」は Terminal Service を載せたWindows Server 2008機で、各クライアントがリモートデスクトップ接続によりFMPAを起動し、FMServerAdv にアクセスできるようにしている。（もともと、FileMaker は Terminal Service との相性が良く、Ver5位より常用しているが、近年のTerminal Service はローカルプリンタへの印刷、ローカルディスクの利用も格段に簡単にできるようになり、加えて接続遮断時にも接続を自動で復旧してくれたりする）。

前述のように FMSA (FileMaker Server無印も同様)にODBCをインストールすればクライアントにODBCを入れる必要はないが、例外がある。それは「SQLを実行」スクリプトステップを使用する場合である。　FileMaker は通常、開発者自らSQLを記述する必要はないが、FileMaker が用意していないコマンドを実行する場合は「SQLを実行」を使用する。また、FileMaker の外部DBに対する処理の中には非常に遅いものがあり（激遅処理）、その典型的なものに多数のレコードに及ぶ一括処理がある。CSV等のファイル取込、全置換、ループ等がそれである。　その回避策として　「SQLを実行」スクリプトステップにSQL文を埋め込み実行すると直接外部データベースに素のSQLが送られるので、FileMakerのレコード取込やループ処理を使うのに比べれて、桁違いにパフォーマンスが向上する。尚、FileMaker の外部DB使用時の激遅処理については、機会を改めて書きたい。

さて、開発PC1、ここには予備用のMySQLを入れ、リストアやリカバリをテストを行うので、データベースを全部削除したりとか無茶をやる。無茶をやると「あー、なるほど」と新たな発見もあったりする。また、ローカルにアプリケーションファイル（NekoApp.fp7）を置いても、FMSAサーバの環境と同様に諸機能が動作するかのチェックも行う。


以上

『売上猫くん on MySQL』開発日記 - 番外6 - リモートのログを mysqlbinlog できない

市販のソフトウェアでログ（MySQLではバイナリログ）からのリカバリ機能を実装しているものはあまり無いような気がする。　以前、某有名財務ソフトのSEに、「ログからのリカバリはできるんですか？」と聞いたことがある。　答えは「そういうのが必要なときは、うちのSEがやりますよ」とのこと。　確かに、下手にユーザがリカバリを行うと大変なことになるので、こういう機能は組み込まないのが正解なのかもしれない。　また、小中規模のデータベースシステムなら、フルバックアップを日に何回か取って、障害発生時には最新のフルバックアップでリストアまでし、最終フルバックアップ以降の更新は、ユーザがせっせと入れる、というが実態なのかとも思う。　また、“ユーザがせってと”できないようなクリティカルなシステムは、自社のシステム管理者や外部のSI業者がしっかりリカバリする体制ができているのだろう。　

さて、『売上猫くん on MySQL』には、一応、バックアップ、フルバックアップからのリストア、バイナリログからのリカバリ機能を実装する予定で、バイナリログからのリカバリをテストしている。　MySQLが載っているサーバ機を使用し、そのサーバ上のログによりリカバリ（mysqlbinlog）を実行するのは、マニュアル通りに動く。　
ところが、ローカルPCからリモートサーバ上のログをリモートサーバに対してリカバリすべく下記を実行すると失敗する。

> mysqlbinlog --disable-log-bin --read-from-remote-server //remote-server/***/log-bin mysql --user=root --password=passord --host=remote-server --database=neko"

例によってググりながら、疑わしきところを弄りながら、異なる2台のサーバに対して試すこと数時間。　どうしても「Misconfigured master - server id was not set」　とか、「error reading packet from server: binary log is not open」みたいなエラーが出力される（この2つのエラーはレプリケーション絡みで出力されるようだが…）。

諦めかけたところ、いきなりプロンプト画面に正常な出力が行われる。データを見ると、確かに更新分が反映されている。そこで実行文を見直してみると、--read-from-remote-server を入れ忘れて実行していた。結果、成功。
--read-from-remote-server はマニュアルによると、「バイナリログをローカルファイルから読み取らずにMySQLサーバから読み取ります」とあるので、このオプションは必要と思ったのだが…

結局、半日以上を費やして、--read-from-remote-server の正しい使用方法は分からずじまいだが、目的は達したので良しとしよう。


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

『売上猫くん on MySQL』開発日記 - 番外5 - ポータルで検索できない

MySQL等の外部DBを使用し、複数のリレーションを設定したポータル内では、検索を実行できません。　たとえば、売上と売上明細のテーブルを［売上No］と［区分］という二つのフィールドでリレートし、ポータルに売上明細のフィールドを置き、これらで検索しても失敗します。
FMのバグでしょう。


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

『売上猫くん on MySQL』開発日記 - 番外4 - mysqldump で文字化け？

mysqldumpで作成したテキスト(ｓｑｌ)ファイルを WordPad で開いてみる。　文字化けだらけ。
MySQLクライアントを使用しても、phpMyAdmin を使用しても結果は変わらず。ただ、phpMyAdminで実行結果を画面に出力するとちゃんと表示される奇怪。
「mysqldump 文字化け」でググってみるといろいろと記事があり、それを見ながら --default-character-set やら、my.ini やらでいろいろ試してみるが解決には至らず。　
半日ほど頑張ってみたが埒があかないので、念のため、隣の人に聞いてみた。すると「WordPadを使ってませんか？」　。　「はぁ？」　、固まる小生。　WordPadって、NotePadの上位エディタじゃないの？　NotePadで　確かに、NotePadは UTF-8とUnicodeの保存オプションがあるのに、 WordPadにはUnicodeのみ。　読み込みできるキャラクタセットが違うらしい。 WordPad のUnicodeはUTF-16LE対応.
Notepadはこれに加えてUTF-8対応。　あー、とんんだ恥さらし記事だけど、小生の同類がいるかもしれないので、恥を忍んで上げておこう。


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

『売上猫くん on MySQL』開発日記 - 番外3 - 0を書き込みできない

売上明細テーブル（ vw_salesdtls）のInt型のフィールド（balanceFlg）に値を書き込もうとする。他の値は書き込みできるのだが、“0”の書き込みが実行できない。　ログで何が起こっているのか確認する。

Commitに失敗している場合はバイナリログには記録されないので、mysqlbinlog は使えない。　そこで、一般ログというものを、

mysql> set global general_log="ON";

で作成してみる。　以下、1を立てて成功したログと、0で失敗したログ（一部抜粋）。

【1立てで成功】
9 Query SELECT ID,`売上番号`,balanceFlg, ～略～ FROM vw_salesdtls WHERE ID=8340 FOR UPDATE
9 Query UPDATE vw_salesdtls SET balanceFlg=1 WHERE ID=8340

【0立てで失敗】
8 Query SELECT ID,`売上番号`,balanceFlg, ～略～ FROM vw_salesdtls WHERE ID=8341

失敗したほうでは、なぜか FOR UPDATE 以下が発行されず、当然これでは更新できない。　当初はInnoDB側に何か問題があるのかと疑ったが、FileMakerが適切なクエリを発行していないことがわかった。　ちなみに、類似した他のテーブルでは、1、0の値に関係なく、更新(Update)は成功する。

いろいろとやってみた。FMのファイルを修復したり、レイアウトモードにして数字の書式を変更してみたり、フィールド定義を眺めてみたり。　そして、これが FileMaker のバグだと判明。　なんと、FileMaker は　空欄(null)→0　への変更は変更と認識しない、null=0 と認識するのである。　更新と認識しないので、　Update が発行されない。　恐ろしい。

結論
ということで、null と 0 を区別して扱う必要がある場合は、非常に注意が必要だ。 0 を参照キーするのは極力避けるべきた。　MySQL側で初期値を0に設定しておく、というのは一案かもしれない。
ちなみに、上述のように「null=0 と認識」するので、0→空欄lに変更を行っても、コミットすると 0 のままである。



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

『売上猫くん on MySQL』開発日記 - 番外2 - MySQLミラーサイト

MySQL サイトが今朝から落ちてる。　レファレンスマニュアルが見れない、つまり開発できない。Googleのキャッシュは遅すぎる。　MySQLの全機能を網羅し、信頼の置けるドキュメントはここにしかない（と思う）。「MySQLの開発者はこういうときどうしているのだろう？」等と思いつつ、、、ミラーサイトをみつけた。これタイの大学のサイト。Googleキャッシュよりはずっと早い。　この大学のトップページにはなぜか秋篠宮殿下が…



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

『売上猫くん on MySQL』開発日記 - 番外1 - 全データベース削除→リストア

あるサーバにあるデータベースダンプを別のサーバに移動。その後、MySQLのすべてのデータベースを削除し、リストアのテストを行おう思い、まず、

> drop database information_schema
（だったと思う）

を実行してみた。　すると、「information_schemaは削除できない」旨のメッセージが表示される。
その後、mysqlを再起動しようとするとエラーが出て起動しない。　データフォルダをみると、ｍｙｓｑｌを含むすべてのデータベースは削除されている。エラーログをチェック（←これ大事）すると、 mysql データベースは起動に必須らしい。そこで、元のサーバから無理やりこのmysql フォルダをコピーし（サーバ停止無、こんなことやっていいいのか？）、丸ごと、起動しなくなった別サーバ内にコピー。　その後は再起動できた（ここまで半日潰れる）。

そこで、

> show databases

を実行すると、やはり表示されるのは、mysql データベースのみ。　そこで、コマンドプロンプトからリストアを実行すると、以下のようなエラーが出る。

InnoDB: Error: table test/parent already exists in InnoDB internal
InnoDB: data dictionary. Have you deleted the .frm file
InnoDB: and not used DROP TABLE? Have you used DROP DATABASE
InnoDB: for InnoDB tables in MySQL version <= 3.23.43? InnoDB: See the Restrictions section of the InnoDB manual. InnoDB: You can drop the orphaned table inside InnoDB by InnoDB: creating an InnoDB table with the same name in another
InnoDB: database and moving the .frm file to the current database.
InnoDB: Then MySQL thinks the table exists, and DROP TABLE will
InnoDB: succeed.

InnoDB内部の記録と不整合があるらしいので、一旦、同名の空のデータベースを作成し、それを削除したあとにもう一度リストアをやってみるとやっと成功。　めでたし、めでたし

参考サイト：トラブルシューティング InnoDB データ ディクショナリ操作



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

『売上猫くん on MySQL』開発日記 ― 1 始めに

昨年1月にFileMakerの新バージョンがリリースされ、ややモチベーションがアップ。　大人の事情によりMySQL 版の「FlexSql売管」は取り止め、暇をみつけて「売上猫くん 4.5」の MySQL版を作ろう、と思い立ったのが去年3月。　今後、当Blogではこの開発工程を綴ります。

概要
「売上猫くん4.5」の後継バージョン『売上猫くん on MySQL』、を、FileMaker Pro 10/MySQL 5.1 を使用し開発する。　完成後、『売上猫くん on MySQL』を流用し、データベースにFileMakerを使用した『売上猫くん5.0』、MS SQL-Server を使用した『売上猫くんon MSSQL』を開発する。可能であれば、最終的に3つのシステムが同一のFileMakerファイル上で動作するようにし、アップグレードを含む開発効率のアップを図る。

システム要件

• 基本機能は「売上猫くん 4.5」に準じる
• 見積/売上登録時の取引先/商品の入力方法改善
• マルチウインドウ対応
• データベースバックアップ/リカバリ機能(MySQL/MSSQLのみ)
• ユーザ管理/ログイン機能
• ユーザインターフェイスの改善

開発環境
データベース：MySQL Community Server 5.1.30
開発ツール
　FileMaker Pro Advanced 10 (Windows/Macintosh)
　MySQL Administrator (→開発終了！)
　phpMyAdmin

　MySQL Workbench

ドキュメント作成

　A5:SQL Mk-2
サーバ（MySQL用）
　CPU: Dual-Core AMD Opteron 1212 2GHz/8GB RAM
　OS： Windows Server 2008 Enterprise
ODBC: MySQL ODBC 5.1 ドライバ

2010/12/16 開発ツール加筆、記事一部削除

『FlexMy売管』を作るぞ！ (3) --- FileMaker から MySQLに接続できない！？

---

【FileMaker/MySQL関連の追加情報】
『売上猫くん on MySQL』開発日記の記事一覧
FileMaker/MySQL関連の記事が増えてきたので、目次にしました。(2012/06/25追記）

---

AccessとFileMakerの実行速度比較テスト記事とその動画　(2011/11/17追記）

---

FileMaker Pro 10/11、MySQL 5.1、ODBC 5.1.6～によるフリーウェア『売上猫くん on MySQL 5.0β』をリリースしました。ダウンロードは→こちら。　(2011/01/27追記）

---

前回、MySQL でデータベースとテーブルを一つ作成したので、FileMaker から接続できるか、試してみることにする。

1.　事前準備
FileMaker は MySQL には ODBC 経由で接続するので、まず、MySQLのODBCドライバ 3.51をインストール、さらにシステムDSNとして登録した。　接続テストを実行すると、「Success; connection was made!」と表示されるので、ここまでは問題ない。

2. FileMaker Pro 9 で接続できない！？いざFileMaker Pro 9を起動してテスト用ファイルを作成。［ファイル］メニュー→［管理］→［外部データソース...］を選択し、上記で作成したDSNを指定。

さらに、［ファイル］メニュー→［管理］→［データベース...］→［リレーションシップ］タグを選択し、ウィンドウ左下にある“+”ボタンをクリックしてMySQLのテーブルを選択、“OK”すると、

「必要なテーブルが見つからないため、この操作は実行できません。」

とメッセージが表示されてしまう。　そんなこと言っても、FileMaker はテーブルとして認識してるやん？
この後、いろいろやってみたが、どうも FileMaker Pro 9 は、MySQLのテーブル、コラムに日本語が使用されていると、そのテーブルは使用できないらしい。　SQL-Server は日本語使っても全然問題ないのに…
試しに、日本語を英語に変更してみると、ちゃんと使用できる。

ただ、今回はSQL-Server ベースの『FlexSql売管』をできる限り手間をかけずMySQL に移植する（テーブルさえ全部作ってDSNを入れ替えれば、『FlexMy売管』があっという間にできるだろう）ということを前提としていたので、いまさらコラム名（フィールド名）を変更する気力は、、、、無い、　全然。
ということで、FileMaker Pro 10　のリリースをまって、再度試してみる（かも）。

【メモ】

• 実は、MySQLコマンドでもデータベース、テーブル、コラムに日本語を使用していると、文字化けする。My.iniをいじったり、コマンドプロンプトの環境をUTF8にしたりしてみたが、問題は解消せず。ただ、MySQL Administrator だけは、データベース構造に日本語を使用しても問題なく認識してくれる。
• ODBC5.1を使用してDSNを作成しても駄目。
• FileMaker Pro 10 プレリリース版を使用してもやっぱり駄目。

新情報
FileMaker社は公式にサポートを言っていないが、MySQL ODBC 5.1　だと、日本語OK。　昨年の3月位から某案件で使用し始め、今のところ大きなトラブルは無し。　この某案件については、時間があれば書きたいな、っと→こちら
（2010/2/5追記）
（2010/2/19追記修正）
（2010/3/25ODBC5.1の部分を打ち消す）

『FlexMy売管』を作るぞ！ (2) --- MySQL データベースを作ってみる

(FileMaker Pro 10/11、MySQL 5.1、ODBC 5.1.6 の環境であれば、→ここ(開発日記) とか ここ(日記番外) の方が参考になるかも。　2010/10/21追記 )

1.　取り合えず、リモートのMySQL Administrator で接続していみる
大分前にインストールしたMySQLの管理ツール MySQL Administrator と、今回インストールした MySQL 5.1 は異なるサーバ上にあるが、MySQL Administrator を起動して MySQL 5.1 に管理者権限でアクセスしようとすると、以下のエラーが出る。

　　　　Could not connect to the specified instance.
　　　　MySQL Error 1045.

MySQL 側に移動して、grant all priviledges on *.* to untara@"%" identified by "kantara" を実行し、ここで指定したユーザ名とパスワードとにより、リモートの MySQL Admin からアクセスできるようになる。　

2.　Migrationツールはスキップ

MS SQL-Serverで作成した『FlexSql売管』のデータベースがあるので、本当ならこれを MySQL Migration Toolkit という奴で MySQLのデータベースに変換（マイグレーション）できるといいのだが、以前失敗してるので、今回はやめておく。

3. SQL-ServerでCreate文を生成し、MySQLでそのCreate文ファイル実行

まずは、create database flexmy うんたらとやって、データベースを作成。　

次に各テーブルは『FlexSql売管』の　SQL-Server　のデータベースから create table 文を生成して、MySQL側で実行しようと画策。　SQL-Server Management Studio Express を起動し、取引先テーブルを指定してcreate 文を作ると、こんな感じになる。

USE [neko] 　//『FlexSql売管』のデータベース名
GO
/****** オブジェクト: Table [dbo].[取引先] スクリプト日付: 01/07/2009 13:35:41 ******/
SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[取引先](
　 [〒] [varchar](8) COLLATE Japanese_CI_AS NULL,
　 [Fax] [varchar](50) COLLATE Japanese_CI_AS NULL,
　 [Tel] [varchar](50) COLLATE Japanese_CI_AS NULL,
　～～～～～～～～～～　中略　～～～～～～～～～～
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF

この文章をtorihikisaki.sqlとして保存し、プロンプトの画面で、

　　$ mysql flexmy < torihikisaki.sql

を実行するも、SQL-Serverの吐いたSQL文をそのまま実行できるはずも無く、紆余曲折の末、以下のように変更して上記コマンドを実行すると旨く行った。

CREATE TABLE 取引先(
　 〒 varchar(8) NULL,
　 Fax varchar(50) NULL,
　 Tel varchar(50) NULL,
　 ～～～～～～～～～～　中略　～～～～～～～～～～
PRIMARY KEY (id) );

以上でSQL-Serverのテーブル等は比較的簡単にMySQLに移植できそうなことが解かった。ところがこの後に思わぬ悲劇が待っていた。

以上