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

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

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

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

場合によっては、ステートメントがソースとレプリカの異なる行セットに影響することがあります。
レプリカで実行されるレプリケートされたステートメントは、完全な権限を持つレプリカ SQL スレッドによって処理されます。プロシージャは、ソースサーバーとレプリカサーバーで異なる実行パスに従うことができるため、ユーザーは、完全な権限を持つスレッドによって処理されるレプリカでのみ実行される危険なステートメントを含むルーチンを記述できます。
データを変更するストアドプログラムが非決定的である場合、再現可能ではありません。これにより、ソースとレプリカでデータが異なる場合や、リストアされたデータが元のデータと異なる場合があります。

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

一般に、ここで説明する問題は、バイナリロギングが SQL ステートメントレベル (ステートメントベースのバイナリロギング) で発生した場合に発生します。行ベースのバイナリロギングを使用する場合、ログには、SQL ステートメントを実行した結果として個々の行に行われた変更が含まれます。ルーチンまたはトリガーが実行されると、行の変更が記録されますが、変更を行なったステートメントは記録されません。ストアドプロシージャーの場合、これは CALL ステートメントが記録されないことを意味します。ストアドファンクションの場合、関数内で行われた行の変更が記録され、関数呼び出しは記録されません。トリガーの場合、トリガーによって行われた行の変更が記録されます。レプリカ側では、行の変更のみが表示され、ストアドプログラムの起動は表示されません。

混合形式のバイナリロギング (binlog_format=MIXED) では、ステートメントベースのバイナリロギングが使用されますが、行ベースのバイナリロギングのみが正しい結果になることが保証されている場合を除きます。混合形式では、ストアドファンクション、ストアドプロシージャー、トリガー、イベント、またはプリペアドステートメントにステートメントベースのバイナリロギングに安全でないものが含まれている場合、ステートメント全体が安全でないとマークされ、行形式で記録されます。プロシージャ、関数、トリガーおよびイベントの作成および削除に使用されるステートメントは、常に安全であり、ステートメントの形式で記録されます。行ベース、混合およびステートメントベースのロギング、および安全なステートメントと安全でないステートメントの決定方法の詳細は、セクション17.2.1「レプリケーション形式」を参照してください。

特に明記されていないかぎり、ここでの備考は、バイナリロギングがサーバーで有効になっていることを前提としています (セクション5.4.4「バイナリログ」を参照)。) バイナリログが有効でない場合、レプリケーションは可能でなく、バイナリログをデータリカバリに利用することもできません。

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

ストアドファンクションを作成または変更するには、通常必要な CREATE ROUTINE または ALTER ROUTINE 権限に加えて、SET_USER_ID 権限 (または非推奨の SUPER 権限) が必要です。 (関数定義の DEFINER 値によっては、バイナリロギングが有効になっているかどうかに関係なく、SET_USER_ID または SUPER が必要になる場合があります。セクション13.1.17「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 と宣言された関数が、非決定的な結果を生成するステートメントを持たないかどうかはチェックされません。
ストアドファンクションを実行しようとするときに、binlog_format=STATEMENT が設定されている場合は、関数定義に DETERMINISTIC キーワードを指定する必要があります。そうでない場合は、このチェックをオーバーライドするように log_bin_trust_function_creators=1 が指定されていないかぎり、エラーが生成され、関数は実行されません (次を参照)。再帰的関数コールの場合、DETERMINISTIC キーワードは最も外側のコールでのみ必要です。行ベースまたは混合バイナリロギングが使用されている場合、関数が DETERMINISTIC キーワードなしで定義されていても、ステートメントは受け入れられ、レプリケートされます。
MySQL では、関数が作成時に実際に決定的かどうかはチェックされないため、DETERMINISTIC キーワードを使用してストアドファンクションを起動すると、ステートメントベースのロギングに対して安全でないアクションが実行されたり、安全でないステートメントを含むファンクションまたはプロシージャが起動される可能性があります。これが binlog_format=STATEMENT の設定時に発生した場合は、警告メッセージが発行されます。行ベースまたは混合バイナリロギングが使用されている場合、警告は発行されず、ステートメントは行ベースの形式でレプリケートされます。
関数作成に関する前述の条件 (SUPER 権限を持つ必要があることと、関数が決定的であるか、データを変更しないと宣言する必要があること) を緩和するには、log_bin_trust_function_creators グローバルシステム変数を 1 に設定します。デフォルトでこの変数には 0 の値が設定されていますが、次のように変更できます。
```
mysql> SET GLOBAL log_bin_trust_function_creators = 1;
```
この変数は、サーバー起動時に設定することもできます。
バイナリロギングが有効でない場合、log_bin_trust_function_creators は適用されません。前述のように、関数定義の DEFINER 値が必要としないかぎり、関数の作成に SUPER は必要ありません。
レプリケーションで安全ではない可能性のある (そのため、これらを使用するストアドファンクションも安全でなくなります) 組み込み関数の詳細は、セクション17.5.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 は適用されません。前述のように、関数定義の 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 ステートメント (記録されないステートメント内で行われた場合) です。このため、それ以外の場合にプロシージャー自体が安全であっても、プロシージャーを呼び出すストアドファンクションを使用するときには注意を払う必要があります。