MySQL :: MySQL 5.6 リファレンスマニュアル :: 20.7 ストアドプログラムのバイナリロギング

MySQL 5.6 リファレンスマニュアル / ストアドプログラムおよびビュー / ストアドプログラムのバイナリロギング

20.7 ストアドプログラムのバイナリロギング

バイナリログには、データベースの内容を変更する SQL ステートメントに関する情報が含まれます。この情報は、変更について記述した「イベント」の形式で格納されます。バイナリログには 2 つの重要な目的があります。

レプリケーションの場合、バイナリログは、スレーブサーバーに送信されるステートメントのレコードとして、マスターレプリケーションサーバー上で使用されます。マスターサーバーは、そのバイナリログに格納されているイベントをそのスレーブに送信し、スレーブはこれらのイベントを実行して、マスター上で実行されたものと同じデータ変更を実行します。セクション17.2「レプリケーションの実装」を参照してください。
ある特定のデータリカバリ操作には、バイナリログの使用が必要です。バックアップファイルがリストアされたあと、バックアップの作成後に記録されたバイナリログ内のイベントが、再度実行されます。これらのイベントは、データベースをバックアップのポイントから最新の状態に持って行きます。セクション7.3.2「リカバリへのバックアップの使用」を参照してください。

ただし、ロギングがステートメントレベルで行われる場合、ストアドプログラム (ストアドプロシージャーおよびストアドファンクション、トリガー、イベント) に関して該当する、特定のバイナリロギングの問題があります。

場合によっては、ステートメントが、マスターとスレーブで別々の行セットに影響する可能性があります。
スレーブ上で実行された複製ステートメントは、完全な権限を持つスレーブ SQL スレッドで処理されます。プロシージャーが、マスターサーバーとスレーブサーバーで別々の実行パスに従うことが可能なので、ユーザーは、スレーブ上でのみ実行し、完全な権限を持つスレーブスレッドで処理される危険なステートメントを含んだルーチンを作成できます。
データを変更するストアドプログラムが非決定的である場合、再現可能ではありません。これにより、マスターとスレーブでデータが異なる結果になったり、リストアしたデータが元のデータと一致しなくなったりする場合があります。

このセクションでは、MySQL 5.6 のストアドプログラムのバイナリロギングの処理について説明します。ここでは、実装がストアドプログラムの使用に対して設定している現在の条件と、問題を避けるために実行可能な対処について記しています。また、これらの条件の理由に関する追加情報も示します。

一般に、ここで述べる問題は、SQL ステートメントレベルでバイナリロギングが行われるときに生じます。行ベースのバイナリロギングを使用する場合、ログには、SQL ステートメントを実行した結果として個々の行に行われた変更が含まれます。ルーチンまたはトリガーが実行されると、行の変更が記録されますが、変更を行なったステートメントは記録されません。ストアドプロシージャーの場合、これは CALL ステートメントが記録されないことを意味します。ストアドファンクションの場合、関数内で行われた行の変更が記録され、関数呼び出しは記録されません。トリガーの場合、トリガーによって行われた行の変更が記録されます。スレーブ側では、行の変更だけが表示され、ストアドプログラムの呼び出しは表示されません。行ベースのロギングに関する一般情報については、セクション17.1.2「レプリケーション形式」を参照してください。

特に明記しないかぎり、ここでの説明では、--log-bin オプションを指定してサーバーを起動することによって、バイナリロギングを有効にしていると想定しています。(セクション5.2.4「バイナリログ」を参照してください。)バイナリログが有効でない場合、レプリケーションは可能でなく、バイナリログをデータリカバリに利用することもできません。

MySQL 5.6 でストアドファンクションを使用するための現在の条件は、次のように要約できます。これらの条件は、ストアドプロシージャーまたはイベントスケジューラのイベントには適用されず、バイナリロギングが有効でないかぎり適用されません。

ストアドファンクションを生成または変更するには、ユーザーは、通常必要になる CREATE ROUTINE 権限または ALTER ROUTINE 権限以外に、SUPER 権限が必要です。(関数定義の DEFINER 値によっては、バイナリロギングが有効かどうかにかかわらず SUPER が必要になる場合があります。セクション13.1.15「CREATE PROCEDURE および CREATE FUNCTION 構文」を参照してください。)
ストアドファンクションを作成するとき、その関数が決定的であるということ、またはデータを変更しないということを宣言する必要があります。そのようにしないと、データリカバリまたレプリケーションにとって安全でなくなる可能性があります。

デフォルトでは、CREATE FUNCTION ステートメントを受け入れるには、DETERMINISTIC、NO SQL、または READS SQL DATA の少なくとも 1 つを明示的に指定する必要があります。そうでない場合はエラーが発生します。
```
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)
```
次の関数は決定的なため (また、データを変更しません)、安全です。
```
CREATE FUNCTION f1(i INT)
RETURNS INT
DETERMINISTIC
READS SQL DATA
BEGIN
  RETURN i;
END;
```
次の関数は UUID() を使用しますが、これは決定的でないため、関数も決定的でなく、安全ではありません。
```
CREATE FUNCTION f2()
RETURNS CHAR(36) CHARACTER SET utf8
BEGIN
  RETURN UUID();
END;
```
次の関数はデータを変更するので、安全ではない可能性があります。
```
CREATE FUNCTION f3(p_id INT)
RETURNS INT
BEGIN
  UPDATE t SET modtime = NOW() WHERE id = p_id;
  RETURN ROW_COUNT();
END;
```
関数の性質の評価は、作成者の「誠実さ」に基づいています。MySQL は、DETERMINISTIC と宣言された関数に非決定的な結果を生成するステートメントが含まれていないかどうかをチェックしません。
DETERMINISTIC を指定しないで、決定的であるストアドファンクションを作成することは可能ですが、ステートメントベースのバイナリロギングを使用してこの関数を実行できません。このような関数を実行するには、行ベースまたは混合バイナリロギングを使用する必要があります。または、関数定義で DETERMINISTIC と明示的に指定すると、ステートメントベースのバイナリロギングを含むあらゆる種類のロギングを使用できます。
関数作成に関する前述の条件 (SUPER 権限を持つ必要があることと、関数が決定的であるか、データを変更しないと宣言する必要があること) を緩和するには、log_bin_trust_function_creators グローバルシステム変数を 1 に設定します。デフォルトでこの変数には 0 の値が設定されていますが、次のように変更できます。
```
mysql> SET GLOBAL log_bin_trust_function_creators = 1;
```
サーバーの起動時に --log-bin-trust-function-creators=1 オプションを使用することによって、この変数を設定することもできます。

バイナリロギングが有効でない場合、log_bin_trust_function_creators は適用されません。前述のように、関数定義の DEFINER 値が必要としないかぎり、関数の作成に SUPER は必要ありません。
レプリケーションで安全ではない可能性のある (そのため、これらを使用するストアドファンクションも安全でなくなります) 組み込み関数の詳細は、セクション17.4.1「レプリケーションの機能と問題」を参照してください。

トリガーは、ストアドファンクションと似ているので、関数に関する前述の説明がトリガーにも当てはまりますが、CREATE TRIGGER にはオプションの DETERMINISTIC 特性がないため、トリガーは常に決定的であると想定されるという点が異なります。ただし、この想定は一部の場合で無効になることがあります。たとえば、UUID() 関数は非決定的です (また、複製しません)。トリガーでのこのような関数の使用には注意する必要があります。

トリガーはテーブルを更新できるので、必要な権限がない場合には、CREATE TRIGGER で、ストアドファンクションの場合と同様のエラーメッセージが表示されます。スレーブ側では、スレーブは DEFINER トリガー属性を使用して、トリガーの作成者であると思われるユーザーを特定します。

このセクションの残りの部分では、ロギングの実装とその意味に関する追加詳細について説明します。ストアドルーチンの使用に関する現在のロギング関連の条件の理論的根拠についての背景に関心がある場合には、こちらをお読みください。この説明はステートメントベースのロギングにのみ該当し、行ベースのロギングには該当しませんが、CREATE および DROP ステートメントは、ロギングモードとは無関係にステートメントとして記録されるという最初の項目は除きます。

サーバーは、CREATE EVENT、CREATE PROCEDURE、CREATE FUNCTION、ALTER EVENT、ALTER PROCEDURE、ALTER FUNCTION、DROP EVENT、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;
  RETURN 0;
END;

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

SELECT f1(a) FROM t1;
```
SELECT ステートメントが実行されると、関数 f1() は 3 回呼び出されます。このうち 2 回の呼び出しで行を挿入し、MySQL は各行に対し SELECT ステートメントを記録します。つまり、MySQL は次のステートメントをバイナリログに書き込みます。
```
SELECT f1(1);
SELECT f1(2);
```
サーバーは、エラーを発生させるストアドプロシージャーをストアドファンクションが呼び出すときに、そのストアドファンクションの呼び出しに対する SELECT ステートメントも記録します。この場合、サーバーは、予想されるエラーコードとともに、SELECT ステートメントをログに書き込みます。スレーブ上で、同じエラーが起きた場合、これは予想される結果でありレプリケーションは継続します。それ以外の場合は、レプリケーションは停止します。
関数によって実行されるステートメントではなく、ストアドファンクションの呼び出しのロギングは、レプリケーションでは、次の 2 つの要因から生じるセキュリティー上の意味があります。
- 関数が、マスターサーバーとスレーブサーバーで別々の実行パスに従うことが可能です。
- スレーブ上で実行されたステートメントは、完全な権限を持つスレーブ SQL スレッドで処理されます。
つまり、ユーザーは関数を作成するために CREATE ROUTINE 権限を持つ必要がありますが、完全な権限を持つスレッドで処理されるスレーブ上でのみ実行する危険なステートメントを含んだ関数を作成できます。たとえば、マスターサーバーとスレーブサーバーのサーバー ID 値がそれぞれ 1 と 2 の場合、マスターサーバー上のユーザーは、安全ではない関数 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 グローバルシステム変数を 1 に設定します。サーバーの起動時に --log-bin-trust-function-creators=1 オプションを使用することによって、この変数を設定することもできます。バイナリロギングが有効でない場合、log_bin_trust_function_creators は適用されません。前述のように、関数定義の DEFINER 値が必要としないかぎり、関数の作成に SUPER は必要ありません。
更新を実行する関数が非決定的である場合、再現可能ではありません。これは次の 2 つの望ましくない影響を及ぼす可能性があります。
- スレーブがマスターと一致しなくなります。
- リストアされたデータが元のデータと異なります。
これらの問題に対処するために、MySQL では、関数を決定的であるか、データを変更しないと宣言しないかぎり、マスターサーバーでは関数の作成と変更は拒否されるという要件を強制しています。ここでは次の 2 つの関数特性セットが適用されます。
- DETERMINISTIC 特性と NOT DETERMINISTIC 特性は、関数が一定の入力に対して常に同じ結果を生成するかどうかを示します。どちらの特性も指定されていない場合は、デフォルトは NOT DETERMINISTIC です。関数が決定的であることを宣言するには、明示的に DETERMINISTIC を指定する必要があります。
- CONTAINS SQL、NO SQL、READS SQL DATA、および MODIFIES SQL DATA 特性は、関数がデータを読み取るか書き込むかに関する情報を示します。NO SQL または READS SQL DATA は、関数がデータを変更しないことを示しますが、特性が指定されていない場合にデフォルトは CONTAINS SQL になるので、これらのどちらかを明示的に指定する必要があります。
デフォルトでは、CREATE FUNCTION ステートメントを受け入れるには、DETERMINISTIC、NO SQL、または READS SQL DATA の少なくとも 1 つを明示的に指定する必要があります。そうでない場合はエラーが発生します。
```
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 に設定した場合、関数が決定的であるか、データを変更しないという要件は破棄されます。
ストアドプロシージャーの呼び出しは CALL レベルでなく、ステートメントレベルで記録されます。つまり、サーバーは、CALL ステートメントを記録せず、実際に実行するプロシージャー内のステートメントを記録します。その結果、マスターで行われた同じ変更が、スレーブサーバーで確認されます。これにより、別々のマシン上で異なる実行パスを持つプロシージャーから生じる問題が防止されます。

一般に、ストアドプロシージャー内で実行されるステートメントは、スタンドアロンでステートメントを実行した場合に適用されるものと同じルールを使用して、バイナリログに書き込まれます。プロシージャー内でのステートメントの実行は、非プロシージャーのコンテキストとまったく同じにはならないので、プロシージャーステートメントのロギング時には、十分に注意してください。
- 記録されるステートメントには、ローカルプロシージャー変数への参照が含まれる場合があります。これらの変数は、ストアドプロシージャーのコンテキスト外に存在しないので、このような変数を参照するステートメントは、文字どおりには記録できません。代わりに、ローカル変数のそれぞれの参照は、ロギングのために次の構造構文に置き換えられます。
```
NAME_CONST(var_name, var_value)
```
  var_name はローカル変数名であり、var_value は、ステートメントの記録時に変数に含まれていた値を示す定数です。NAME_CONST() には var_value の値と var_name の「名前」が含まれます。したがって、この関数を直接呼び出した場合、次のような結果が得られます。
```
mysql> SELECT NAME_CONST('myname', 14);
+--------+
| myname |
+--------+
|     14 |
+--------+
```
  NAME_CONST() は、マスター上でストアドプロシージャー内で実行された元のステートメントと同じ効果で、記録されたスタンドアロンのステートメントを、スレーブ上でも実行できるようにします。
  
  NAME_CONST() を使用した結果、ソースカラム式がローカル変数を参照するときに、CREATE TABLE ... SELECT ステートメントの問題が生じる場合があります。これらの参照を NAME_CONST() 式に変換した結果、マスターサーバーとスレーブサーバーでカラム名が異なったり、正当なカラム識別子としては長すぎる名前になったりすることがあります。回避策では、ローカル変数を参照するカラムのエイリアスを指定します。myvar の値が 1 の場合は次のステートメントを検討してください。
```
CREATE TABLE t1 SELECT myvar;
```
  これは次のように書き換えられます。
```
CREATE TABLE t1 SELECT NAME_CONST(myvar, 1);
```
  マスターテーブルとスレーブテーブルに同じカラム名があることを確認するには、次のようなステートメントを作成します。
```
CREATE TABLE t1 SELECT myvar AS myvar;
```
  書き換えられたステートメントは次のようになります。
```
CREATE TABLE t1 SELECT NAME_CONST(myvar, 1) AS myvar;
```
- 記録されるステートメントには、ユーザー定義変数への参照が含まれる場合があります。これを処理するために、MySQL は、SET ステートメントをバイナリログに書き込んで、マスター上にあるものと同じ値の変数がスレーブ上にもあることを確認します。たとえば、ステートメントが変数 @my_var を参照する場合、そのステートメントはバイナリログ内で次のステートメントに優先されます。ここで、value マスター上の @my_var の値です。
```
SET @my_var = value;
```
- プロシージャーの呼び出しは、コミットまたはロールバックしたトランザクション内で行えます。プロシージャー実行のトランザクションの側面が正しく複製されるように、トランザクションのコンテキストが説明されます。つまり、サーバーは、実際にデータを実行し修正するプロシージャー内のステートメントを記録し、BEGIN、COMMIT、および ROLLBACK ステートメントも必要に応じて記録します。たとえば、プロシージャーがトランザクションテーブルだけを更新し、ロールバックされるトランザクション内で実行される場合、これらの更新は記録されません。プロシージャーがコミットされたトランザクション内で行われた場合、BEGIN および COMMIT ステートメントが更新とともに記録されます。ロールバックしたトランザクション内で実行するプロシージャーの場合、そのステートメントは、ステートメントがスタンドアロンで実行された場合に適用されるものと同じルールを使用して記録されます。
  - トランザクションテーブルに対する更新は記録されません。
  - 非トランザクションテーブルに対する更新は、ロールバックで取り消されないので、記録されます。
  - トランザクションテーブルと非トランザクションテーブルの混在に対する更新は、スレーブがマスターと同じ変更およびロールバックを行うように、BEGIN と ROLLBACK で囲まれて記録されます。
ストアドプロシージャーの呼び出しは、ストアドファンクション内から呼び出される場合、ステートメントレベルのバイナリログに書き込まれません。この場合、記録される唯一の対象は、関数を呼び出すステートメント (記録されたステートメント内で行われた場合) または DO ステートメント (記録されないステートメント内で行われた場合) です。このため、それ以外の場合にプロシージャー自体が安全であっても、プロシージャーを呼び出すストアドファンクションを使用するときには注意を払う必要があります。

PREV HOME UP NEXT