Skip navigation links
MySQL 5.1 リファレンスマニュアル :: 17 ストアドプロシージャとファンクション :: 17.4 ストアドルーチンとトリガのバイナリログ
18 トリガ »
Section Navigation      [Toggle]

17.4. ストアドルーチンとトリガのバイナリログ

バイナリログには、データベースの中身を修正するSQLステートメントに関する情報が含まれていす。この情報は改良を説明する「イベント」の形で記憶されます。バイナリログは２つの重要な目的を持っています。

  • 複製の場合、マスターサーバはそのバイナリログに含まれているエベントを自分のスレーブに送ります。これによって、これらのイベントが実行されて、マスターが行ったと同じデータ変更が行われます。項5.5. 「レプリケーションの実装」 を参照してください。

  • ある種のデータリカバリには、バイナリログの使用が必要です。バックアップファイルが修復された後、バックアップ後に記録されたバイナリログ中のイベントは再実行されます。これらのイベントは、データベースをバックアップの点からデートまで持って行きます｡項4.9.2.2. 「バックアップ ファイルでリカバリ」 を参照してください。

このセクションで、MySQL 5.1がストアドルーチン(プロシージャとファンクション)およびトリガの為のバイナリログを取り扱う方法について説明します。ここで、その実装がストアドルーチンの使用上に置かれる現在の条件も説明し、これらの条件が必要とされる理由に関する追加情報を提供します。

一般に、ここで述べた問題はバイナリログがSQLステートメントレベルにおいて起こる時生じます。ユーザが行をベースとするバイナリログを使用する場合、ログには、SQLステートメントを実行した結果として個別の行に施した変更が含まれます。行をベースとするログに関する一般情報については、 項5.1.2. 「レプリケーション フォーマット」 を参照してください。

行をベースとするログを使用する時、ストアドルーチンとトリガの定義がステートメントとして複製されます。ルーチンやトリガを実行する時、行に施した変更は登録されますが、これらを実行するステートメントは登録されません。ストアドプロシージャに対して、これはCALLステートメントは登録されないことを意味します。保存されたファンクションに対して、ファンクション内で行に施した変更は登録されますが、ファンクション起動は登録されません。トリガの場合、トリガが行に対して行った変更は登録されます。スレーブ側では、行の変更のみ現れ、ルーチンまたはトリガ起動は現れません。

特に注記しない限り、ここに示した備考はユーザが既に--log-binオプションを使ってサーバを立ち上げることによって、バイナリログを有効化しているものと見なします｡（項4.11.4. 「バイナリ ログ」を参照してください。）バイナリログが有効化されていない場合、複製は不可能なばかりでなく、データリカバリ用に、バイナリログも利用できません。

MySQL5.1は以下の通り総括することができます：これらの条件はストアドプロシージャには適用されず、これらはバイナリログが有効化されない限り、適用されません。

  • 保存されたファンクションを生成もしくは変更するには、ユーザは通常要求されるCREATE ROUTINE権限もしくはALTER ROUTINE権限に加え、SUPER権限を保持していなければなりません。

  • 保存されたファンクションを生成する時、それが決定論的なものであるか、データを改良しないものであるかを宣言しなければなりません。これを怠ると、データリレカバリやデータの複製が安全にできなくなる場合があります。

  • ファンクション生成に対する(SUPER権限を持たなければならず、決定論的か、データを修正しないかの別を宣言しなければならない)前の規制を緩和するには、グローバル log_bin_trust_function_creatorsシステム変数を１に設定します。 デフォルトで、これは０に設定されていますが、ユーザはこのようにして変更することができます。 

    mysql> SET GLOBAL log_bin_trust_function_creators = 1;

    この変数を、サーバを立ち上げる時--log-bin-trust-function-creators=1オプションを使って設定することもできます。

    バイナリログが有効化されていない場合、log_bin_trust_function_creators は適用されず、ファンクション生成に対してSUPERは要求されません。

トリガは保存されたファンクションと同等であるため、ファンクションに関する前の備考は、以下の場合を除き、トリガには適用されません。CREATE TRIGGERには、オプションの DETERMINISTIC 特徴は含まれていないので、トリガは常に決定論的であると見なします。しかし、この仮定は場合によっては無効です。例えば、UUID()機能は非決定論的です（複製されません）。このような機能のトリガ中での使用に関して、注意すべきです。

CREATE TRIGGERトリガはテーブルを更新することができるので、ユーザがTRIGGER権限(MySQL 5.1.6より前の版ではSUPER)を持っておらず､　log_bin_trust_function_creators が０でる場合、保存されたファンクションに起こるこれらと同等なエラーメッセージが発生します。(スレーブ側では、スレーブはトリガDEFINER属性を使って、どのユーザがトリガ生成者であるか査定します。)

以下のディスカッションで、ログの実装とその意味に関する追加詳細を提供します。このディスカッションは最初のアイテムを除き、ステートメントをベースとするログだけを対象とし、行をベースとするログには適用されません。CREATEステートメントおよびDROPステートメントはログモードと関係なく、ステートメントとして登録されます。

  • サーバはCREATE PROCEDURE、CREATE FUNCTION、ALTER PROCEDURE, ALTER FUNCTION、DROP PROCEDUREおよびDROP FUNCTION ステートメントをバイナリログに書き込みます。

  • 保存されたファンクションの利用は、ファンクションがデータを変更し、これ以外登録しないステートメントの中に起こる場合、SELECTステートメントとして登録されます。これによって、未登録ステートメントの中で保存されたファンクションを使用したことにより、データの変更が複製されない問題が防止されます。例えば、SELECT ステートメントはバイナリログに書き込まれないが、SELECTは変更を施す保存されたファンクションを使用しなければならならない場合があります。これを扱うため、SELECT func_name())ステートメントは、あるファンクションが変更を実行する時、バイナリログに書き込まれます｡以下のステートメントがマスターの上で実行されると仮定すると： 

    CREATE FUNCTION f1(a INT) RETURNS INT
BEGIN
  IF (a < 3) THEN 
    INSERT INTO t2 VALUES (a);
  END IF;
END;

CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (1),(2),(3);

SELECT f1(a) FROM t1;

    SELECT ステートメントが実行される時、ファンクションf1() は三回起動されます。これらの起動の内、2回の起動で、行を挿入し、MySQLはそれらの各々に対してSELECTステートメントを登録します。即ち、MySQLは以下のステートメントをバイナリログに書き込みます。 

    SELECT f1(1);
SELECT f1(2);

    サーバは、ファンクションがエラーを引き起こすストアドプロシージャを取り出す時、保存されたファンクションの取り出しに対するSELECTステートメントも登録します。この場合、サーバは、SELECT ステートメントを期待エラーコードと一緒にログに書き込みます。スレーブに同じエラーが発生しても、それは期待された結果で、複製は継続されます。さもないと、複製はストップします。

    注:MySQL 5.1.7の前に、DO func_name()として登録されたこれらのSELECT func_name()ステートメントが見えます｡SELECTに対する変更は、DOを使用してのエラーコードチェッキングに十分な管理が得られないことが判明した結果行われています。

  • ファンクションによって実行されたステートメントを除く、保存されたファンクションの利用に対する登録には、複製に対するセキュリティー問題が関与します。当該問題は、以下からなる2つの要因によって起こります｡

    • ファンクションがマスタサーバとスレーブサーバ上にある異なった実行パスに従うことは可能です。

    • スレーブサーバ上で実行されたステートメントはフル権限を持つスレーブSQLスレッドによって処理されます。

    ユーザはファンクションを生成させるCREATE ROUTINE権限を持っていなければならないが､そのユーザは、フル権限を持つSQLスレッドによって処理されるスレーブ上でのみ実行される危険なステートメントを含むファンクションを書き込むことができます。例えば、マスタサーバとスレーブサーバがそれぞれ１と２のID値を持っている場合、マスタサーバ上のユーザは安全でない関数unsafe_func()を以下の通り生成して取り出すことができる場合があります。 

    mysql> delimiter //
mysql> CREATE FUNCTION unsafe_func () RETURNS INT
    -> BEGIN
    ->   IF @@server_id=2 THEN dangerous_statement; END IF;
    ->   RETURN 1;
    -> END;
    -> //
mysql> delimiter ;
mysql> INSERT INTO t VALUES(unsafe_func());

    CREATE FUNCTIONステートメントおよびINSERTステートメントはバイナリログに書き込まれるので、スレーブサーバはそれらを実行します。スレーブSQLスレッドはフル権限を持っているので、それは危険なステートメントを実行します。このようにして、ファンクションの取り出しはマスタとスレーブに異なった効果を与え、複製は安全でなくなります。

    バイナリログを有効化したサーバに対するこの危険からサーバを守るには、保存されたファンクションの生成者は、要求された通常のCREATE ROUTINE権限に加え、SUPER権限も持っていなければなりません。同様に、ALTER FUNCTIONを使用するため、ユーザはALTER ROUTINE権限に加え、SUPER権限を持っていなければなりません。SUPER権限がないと、エラーが起こります： 

    ERROR 1419 (HY000): You do not have the SUPER privilege and
binary logging is enabled (you *might* want to use the less safe
log_bin_trust_function_creators variable)

    ファンクションの生成者にSUPER権限を持つよう要求したくない場合(例えば、ユーザのシステム上にCREATE ROUTINE権限を持つすべてのユーザが経験豊かなアプリケーションデベロッパーである場合、グローバルlog_bin_trust_function_creatorsシステム変数を１にセットしてください。この場合、ユーザはこの値を--log-bin-trust-function-creators=1 オプションを使って、サーバを立ち上げる時セットすることができます。バイナリログが有効化されていない場合、 log_bin_trust_function_creatorsは適用されず、ファンクション生成に対してSUPERは要求されません。

  • 更新を実行するファンクションが非決定論的ある場合、それは反復可能ではありません。これは２つの望ましくない効果をもたらす場合があります。

    • それがスレーブをマスタと違ったものにします。

    • 修復されたデータが元のデータと異なります。

    これらの問題を処理するため、MySQLは以下の要件を実施しています：マスタサーバ上で、ユーザがファンクションに対して決定論的か、データを修正しないかの別を宣言しない限り、ファンクションの生成と変更は拒否されます。以下からなるファンクション特性の２つのセットが適用されます：

    • DETERMINISTIC特性とNOT DETERMINISTIC特性は、あるインプットに対して、いつも同じ結果を生成するか否かを示します｡いずれかの特性を附与しない場合、デフォルト設定はNOT DETERMINISTICとなります。ファンクションが決定論的であると宣言するには、DETERMINISTIC を明確に規定しなければなりません。

      NOW() 関数(またはその同義語)またはRAND() を使用しても、必ずしもファンクションが決定論的になるとは限りません｡NOW()の場合、バイナリログはタイムスタンプを含み、正しく複製されます。RAND()はファンクション内で１回呼び出した場合のみ、正しく複製されます。(ファンクション実行のタイムスタンプと乱数種を、マスタとスレーブ上で同等であるとする暗黙のインプットとみなすことができます。)

      SYSDATE() はバイナリログの中でのタイムスタンプによって影響されないので、ステートメントをベースとするログが使われる場合、それは、ストアドルーチンを非決定論的になるようにします。行をベースとするログが使用されるか、サーバを--sysdate-is-nowオプション使って立ち上げ、SYSDATE()がNOW()と別名とする場合、これは起こりません｡

    • CONTAINS SQL, NO SQL、READS SQL DATAおよびMODIFIES SQL DATA特徴はデータの読み取りか、書き込みかに関する情報を提供します。NO SQLまたはREADS SQL DATAは、特徴が附与されていない場合、ファンクションはデータを変更しないが、デフォルト設定はCONTAINS SQLとなっているので、ユーザはこれらの中からいずれか1つを選んで明確に規定しなければなりません。

    デフォルト設定で、CREATE PROCEDURE またはCREATE FUNCTION ステートメントが受け入れられるようにするには、DETERMINISTIC または、NO SQL か READS SQL DATAのどちらかを明示的に指定しなければなりません。指定しないと、エラーが発生します。 

    ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL,
or READS SQL DATA in its declaration and binary logging is enabled
(you *might* want to use the less safe log_bin_trust_function_creators
variable)

    log_bin_trust_function_creatorsを1にセットした場合、ファンクションを決定論的であるようにするか、データを修正しないにすべき要件は撤去されます。

    ファンクションの性質は、生成者の「誠意」に基づき評価されます。MySQLはファンクションが宣言したDETERMINISTICが非決定論的結果結果を生成しないことがないかチェックしません。

  • ストアドプロシージャの呼び出しはCALL レベルでなく、ステートメントレベルで登録されます。即ち、サーバはCALLステートメントを登録せず、実際に実行されたプロシージャ中にこれらのステートメントを登録します。結果として、マスタサーバに起こったと同じ変更がスレーブサーバに見られます。これは、異なった実行パス上に異なった機械を持つプロシージャから結果が得られる恐れがある問題を提供します。

    一般に、ストアドプロシージャ内の実行されたステートメントは、独立ファッションで実行すべきでステートメントに適用されたと同じ規則を使ってバイナリログに書き込まれます。プロシージャステートメントを登録する時、プロシージャ内でのステートメントの実行は、非プロシージャコンテキスト内での実行と全く同じではないので、注意が必要です。

    • 登録すべきステートメントにはローカルプロシージャ変数に対するリファレンスを含めなければならない場合があります｡これらの変数はストアドプロシージャコンテキストの外側に存在しないので、当該変数を引用したステートメントを文章で登録することができません。これにも係わらず､ローカル変数に対する各リファレンスはログを目的として、これに置き換えされます: 

      NAME_CONST(var_name, var_value)

      var_name ローカル変数の名称で、var_valueはステートメントを登録する時、その変数が持つ値を示す定数です。NAME_CONST()はvar_valueおよび「var_name」のnameの値を持っています｡このようにして、ユーザこのファンクションを直接起動する場合、このような結果が得られます。 

      mysql> SELECT NAME_CONST('myname', 14);
+--------+
| myname |
+--------+
|     14 |
+--------+

      NAME_CONST() )は、登録された独立ステートメントがストアドプロシージャの中でマスタ上で実行された元のステートメントと同じ効果を持つように、スレーブ上で実行されることを許容します。

    • 登録すべきステートメントにはユーザが規定した変数に対するリファレンスを含めなければならない場合がります。これを扱うため、MySQLはSET ステートメントをバイナリログに書き込んで、マスタ上にあると同じ値を持つスレーブ上にその変数が存在することを確認します。例えば、ステートメントが変数@my_varを引用する場合、そのステートメントは以下のステートメントによって、バイナリログの中で先行します。この場合、valueはマスタ上@my_varの値です。 

      SET @my_var = value;

    • プロシージャの呼び出しは、コミットされているもしくはロールバックトランザクション中で発生します。以前には、CALLステートメントは、それらがロールバックトランザクション中に発生した場合も、登録されました。MySQL 5.0.12より、プロシージャ実行のトランザクションが正しく複製されるように、トランザクションコンテキストが考慮されます。即ち、サーバは、これらのステートメントを、実際に実行し、データを修正するプロシージャ中に登録し更に、BEGIN, COMMITステートメントおよびROLLBACK ステートメントも必要に応じて登録します。例えば、プロシージャがトランザクションテーブルだけを更新し、ロールバックされる取引の中で実行される場合、これらの更新は登録されません。プロシージャがコミットされたトランザクション中で起こる場合、BEGINステートメントとCOMMITステートメントはその更新を使って登録されます。ロールバックトランザクション中で実行されるプロシージャに対して、そのステートメントは、取引が独立してで実行された場合に適用されたと同じ規則を使って登録されます。

      • トランザクションテーブルの更新は登録されません。

      • 非トランザクションテーブルの更新は､ロールバックがこれらをキャンセルしないので、登録されます。

      • トランザクションテーブルと非トランザクションテーブルを混ぜたものの更新は、スレーブがマスタ上で行われたと同じ変更とロールバック行うように、 BEGINとROLLBACKの間に登録されます。

  • ストアドプロシージャの呼び出しは、保存されたファンクション中から呼び出す場合、ステートメントレベルのバイナリログに書き込まれません。この場合、登録される唯一つのものは、(それが登録されたステートメントの中で起こる場合)機能を取り出すステートメントまたは(それが登録されていないステートメントの中で起こる場合)DOステートメントです。よって、プロシージャを呼び出す保存されたファンクションの利用には、プロシージャそれ自体が安全でない限り、注意してください。