第 20 章 ストアドプログラムおよびビュー

目次

20.1 ストアドプログラムの定義
20.2 ストアドルーチン (プロシージャーと関数) の使用
20.2.1 ストアドルーチンの構文
20.2.2 ストアドルーチンと MySQL 権限
20.2.3 ストアドルーチンのメタデータ
20.2.4 ストアドプロシージャー、関数、トリガー、および LAST_INSERT_ID()
20.3 トリガーの使用
20.3.1 トリガーの構文と例
20.3.2 トリガーのメタデータ
20.4 イベントスケジューラの使用
20.4.1 イベントスケジューラの概要
20.4.2 イベントスケジューラの構成
20.4.3 イベント構文
20.4.4 イベントメタデータ
20.4.5 イベントスケジューラのステータス
20.4.6 イベントスケジューラと MySQL 権限
20.5 ビューの使用
20.5.1 ビューの構文
20.5.2 ビュー処理アルゴリズム
20.5.3 更新可能および挿入可能なビュー
20.5.4 ビューのメタデータ
20.6 ストアドプログラムおよびビューのアクセスコントロール
20.7 ストアドプログラムのバイナリロギング

この章では、あとから実行するためにサーバーに格納された SQL コードの点で定義されたデータベースオブジェクトである、ストアドプログラムとビューについて説明します。

ストアドプログラムには次のオブジェクトが含まれます。

ビューは、参照されたときに結果セットを生成するストアドクエリーです。ビューは仮想テーブルとして機能します。

この章では、ストアドプログラムおよびビューを使用する方法について説明します。次のセクションで、これらのオブジェクトに関連したステートメントの SQL 構文に関する追加情報を示します。

MySQL 5.6.6 以降では、ストアドプログラムによって参照されるオブジェクトへのメタデータ変更が検出され、そのプログラムが次に実行されときに、影響を受けるステートメントの自動再解析が行われるようになります。詳細については、セクション8.9.4「プリペアドステートメントおよびストアドプログラムのキャッシュ」を参照してください。

20.1 ストアドプログラムの定義

各ストアドプログラムには、SQL ステートメントから構成される本体が含まれます。このステートメントは、セミコロン (;) 文字で区切られた複数のステートメントから構成される複合ステートメントの場合があります。たとえば、次のストアドプロシージャーには、SET ステートメントと REPEAT ループ (ループ自体に別の SET ステートメントが含まれます) を含む BEGIN ... END ブロックから構成される本体があります。

CREATE PROCEDURE dorepeat(p1 INT)
BEGIN SET @x = 0; REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
END;

mysql クライアントプログラムを使用してセミコロン文字を含むストアドプログラムを定義すると、問題が発生します。デフォルトでは、mysql 自体はセミコロンをステートメント区切り文字と認識します。したがって、mysql がストアドプログラム定義全体をサーバーに渡すように、区切り文字を一時的に再定義する必要があります。

mysql の区切り文字を再定義するには、delimiter コマンドを使用します。次の例は、上記の dorepeat() プロシージャーについてこれを行う方法を示しています。区切り文字は // に変更され、定義全体を単一のステートメントとしてサーバーに渡して、プロシージャーの呼び出し前に ; にリストアできます。これにより、プロシージャー本体で使用される ; 区切り文字を、mysql 自体が解釈するのではなく、サーバーに渡すようにすることができます。

mysql> delimiter //mysql> CREATE PROCEDURE dorepeat(p1 INT) -> BEGIN -> SET @x = 0; -> REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT; -> END -> //Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;mysql> CALL dorepeat(1000);Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @x;+------+
| @x |
+------+
| 1001 |
+------+
1 row in set (0.00 sec)

区切り文字を // 以外の文字列に再定義でき、区切り文字は単一の文字から構成することも、複数の文字から構成することもできます。バックスラッシュ (\) 文字は、MySQL のエスケープ文字なので使用しないでください。

次に、パラメータを受け取り、SQL 関数を使用して操作を実行したあと、結果を返す関数例を示します。この場合は、関数定義に内部の ; ステートメント区切り文字が含まれていないため、delimiter を使用する必要はありません。

mysql> CREATE FUNCTION hello (s CHAR(20))mysql> RETURNS CHAR(50) DETERMINISTIC -> RETURN CONCAT('Hello, ',s,'!');Query OK, 0 rows affected (0.00 sec)
mysql> SELECT hello('world');+----------------+
| hello('world') |
+----------------+
| Hello, world! |
+----------------+
1 row in set (0.00 sec)

20.2 ストアドルーチン (プロシージャーと関数) の使用

ストアドルーチン (プロシージャーおよび関数) は MySQL 5.6 でサポートされています。ストアドルーチンとは、サーバーに格納できる一連の SQL ステートメントです。これが行われていると、クライアントは個々のステートメントを繰り返し発行し続ける必要はなく、代わりにストアドルーチンを参照できます。

ストアドルーチンには、mysql データベース内の proc テーブルが必要です。このテーブルは、MySQL 5.6 インストール手順中に作成されます。以前のバージョンから MySQL 5.6 にアップグレードしている場合、必ず付与テーブルを更新して、proc テーブルの存在を確認してください。セクション4.4.7「mysql_upgrade — MySQL テーブルのチェックとアップグレード」を参照してください。

ストアドルーチンは特に、次のような特定の状況で役立ちます。

  • クライアントアプリケーションが異なる言語で作成されているか、異なるプラットフォームで動作しているが、同じデータベース操作を実行する必要がある場合。

  • セキュリティーが最重要である場合。たとえば、銀行では、すべての一般的な操作に対してストアドプロシージャーおよびストアドファンクションを使用します。これにより一貫したセキュアな環境が得られ、ルーチンによってそれぞれの操作が正しく記録されるようになります。このようなセットアップでは、アプリケーションおよびユーザーはデータベーステーブルに直接アクセスできませんが、特定のストアドルーチンだけを実行できます。

ストアドルーチンは、サーバーとクライアント間で送信する必要のある情報が少なくなるので、パフォーマンスを改善できます。そのトレードオフでは、これによりサーバー側で行われる作業が増え、クライアント (アプリケーション) 側で行われる作業が少なくなるので、データベースサーバーでのロードが増大します。1 台または少数のデータベースサーバーだけで多数のクライアントマシン (Web サーバーなど) にサービスを提供している場合にはこれを検討してください。

ストアドルーチンを使用すれば、データベースサーバーで関数のライブラリを保持することもできます。これは、内部的に (たとえばクラスを使用して) このような設計を可能にする、現代のアプリケーション言語で共有されている機能です。これらのクライアントアプリケーションの言語機能を使用すると、データベース使用のスコープ外でもプログラマにとって利点があります。

MySQL はストアドルーチンについて SQL:2003 構文に従っており、これは IBM の DB2 でも使用されています。ここで説明するすべての構文はサポートされており、すべての制限と拡張が適宜ドキュメント化されています。

追加のリソース

20.2.1 ストアドルーチンの構文

ストアドルーチンはプロシージャーまたは関数のどちらかです。ストアドルーチンは、CREATE PROCEDURE および CREATE FUNCTION ステートメントで作成されます (セクション13.1.15「CREATE PROCEDURE および CREATE FUNCTION 構文」を参照してください)。プロシージャーは CALL ステートメントを使用して呼び出され (セクション13.2.1「CALL 構文」を参照してください)、出力変数の使用でのみ値を戻すことができます。関数は、ほかの関数とまったく同様に (つまり、関数の名前を呼び出すことによって) ステートメント内部から呼び出すことができ、スカラー値を戻すことができます。ストアドルーチンの本体では、複合ステートメントを使用できます (セクション13.6「MySQL 複合ステートメント構文」を参照してください)。

ストアドルーチンは、DROP PROCEDURE および DROP FUNCTION ステートメントで削除でき (セクション13.1.26「DROP PROCEDURE および DROP FUNCTION 構文」を参照してください)、ALTER PROCEDURE および ALTER FUNCTION ステートメントで変更できます (セクション13.1.5「ALTER PROCEDURE 構文」を参照してください)。

ストアドプロシージャーまたはストアドファンクションは、特定のデータベースに関連付けられています。これにはいくつかの問題があります。

  • ルーチンが呼び出されると、暗黙の USE db_name が実行されます (その後、ルーチンが終了すると元に戻ります)。ストアドルーチン内での USE ステートメントは許可されていません。

  • データベース名でルーチン名を修飾できます。これは現在のデータベースに存在しないルーチンを参照する場合に使用できます。たとえば、testデータベースに関連するストアドプロシージャー p またはストアドファンクション f を呼び出すには、CALL test.p() または test.f() と指定します。

  • データベースを削除すると、そのデータベースに関連付けられたすべてのストアドルーチンも削除されます。

ストアドファンクションは再帰関数にはできません。

ストアドプロシージャーでの再帰は許可されていますが、デフォルトでは無効になっています。再帰を有効にするには、max_sp_recursion_depth サーバーシステム変数を正の値に設定します。ストアドプロシージャーの再帰により、スレッドスタック領域の要求が増加します。max_sp_recursion_depth の値を増やした場合、サーバー起動時に thread_stack の値を増やすことによってスレッドスタックサイズを増やすことが必要な場合もあります。詳細は、セクション5.1.4「サーバーシステム変数」を参照してください。

MySQL では、通常の SELECT ステートメントをストアドプロシージャー内で (つまり、カーソルまたはローカル変数を使用せずに) 使用できるようにする非常に役立つ拡張をサポートしています。このようなクエリーの結果セットは単にクライアントに直接送信されます。複数の SELECT ステートメントは複数の結果セットを生成するので、クライアントは複数の結果セットをサポートしている MySQL クライアントライブラリを使用する必要があります。これは、クライアントが、4.1 以降の MySQL のバージョンからクライアントライブラリを使用する必要があることを意味します。クライアントは、接続するときに、CLIENT_MULTI_RESULTS オプションも指定する必要があります。C プログラムの場合、これは、mysql_real_connect()C API 関数で実行できます。セクション23.8.7.53「mysql_real_connect()」およびセクション23.8.17「複数ステートメント実行の C API サポート」を参照してください。

20.2.2 ストアドルーチンと MySQL 権限

MySQL 許可システムはストアドルーチンを次のように考慮します。

  • ストアドルーチンを生成するには、CREATE ROUTINE 権限が必要です。

  • ストアドルーチンを変更または削除するには、ALTER ROUTINE 権限が必要です。この権限は、必要に応じて、ルーチンの作成者に自動的に与えられ、ルーチンが削除されると作成者から削除されます。

  • ストアドルーチンを実行するには、EXECUTE 権限が必要です。ただし、この権限は、必要に応じて、ルーチンの作成者に自動的に与えられます (ルーチンが削除されると作成者から削除されます)。また、ルーチンのデフォルトの SQL SECURITY 特性は DEFINER であり、これにより、ルーチンが関連付けられているデータベースにアクセス可能なユーザーがルーチンを実行できるようになります。

  • automatic_sp_privileges システム変数が 0 である場合、EXECUTE および ALTER ROUTINE 権限は作成者に対して自動的には付与および削除されません。

  • ルーチンの作成者は、ルーチンの CREATE ステートメントを実行するために使用されるアカウントです。これは、ルーチン定義で DEFINER として名前が指定されているアカウントと同じでないことがあります。

サーバーは、ストアドルーチンを作成、変更、または削除するステートメントに応じて、mysql.proc テーブルを操作します。このテーブルの手動操作に対するサーバーでの認識はサポートされていません。

20.2.3 ストアドルーチンのメタデータ

ストアドルーチンに関するメタデータは次のように取得できます。

20.2.4 ストアドプロシージャー、関数、トリガー、および LAST_INSERT_ID()

ストアドルーチン (プロシージャーまたは関数) またはトリガーの本体内では、LAST_INSERT_ID() の値は、このような種類のオブジェクトの本体外で実行されたステートメントと同様に変更されます (セクション12.14「情報関数」を参照してください)。あとに続くステートメントで参照される LAST_INSERT_ID() の値でのストアドルーチンまたはトリガーの効果は、ルーチンの種類によって異なります。

  • ストアドプロシージャーで LAST_INSERT_ID() の値を変更するステートメントが実行される場合は、プロシージャー呼び出しが続くステートメントで変更された値が参照されます。

  • 値を変更するストアドファンクションやトリガーでは、値は関数やトリガーが終了したときにリストアされるので、後続のステートメントは変更された値を表示しません。

20.3 トリガーの使用

トリガーとは、テーブルに関連付けられ、そのテーブルに対して特定のイベントが発生するとアクティブ化される名前付きデータベースオブジェクトのことです。トリガーを使用する場合には、テーブルに挿入する値のチェックを実行したり、更新にかかわる値の計算を実行したりする場合があります。

トリガーは、関連付けられたテーブルでステートメントが行の挿入、更新、または削除を行なったときにアクティブ化するように定義されます。これらの行操作がトリガーイベントになります。たとえば、行は、INSERT または LOAD DATA ステートメントで挿入でき、挿入トリガーは挿入された行ごとにアクティブ化します。トリガーは、トリガーイベントの前または後のどちらかでアクティブ化するように設定できます。たとえば、テーブルに挿入される各行の前、または更新される各行のあとでトリガーをアクティブ化させることができます。

重要

MySQL のトリガーは、SQL ステートメントがテーブルに対して行なった変更の場合にのみアクティブ化します。ビューでの変更や、SQL ステートメントを MySQL Server に転送しない API がテーブルに対して行なった変更ではアクティブ化しません。これは次のことを意味します。

  • INFORMATION_SCHEMA または performance_schema テーブルは実際にはビューなので、トリガーは、これらのテーブルでの変更ではアクティブ化されません。

  • トリガーは、NDB API を使用して行われた更新によってアクティブ化されません。

次のセクションでは、トリガーを作成および削除するための構文について説明し、使用方法の例をいくつか挙げ、トリガーメタデータを取得する方法を示します。

追加のリソース

20.3.1 トリガーの構文と例

トリガーを作成したり、トリガーを削除したりするには、セクション13.1.19「CREATE TRIGGER 構文」およびセクション13.1.30「DROP TRIGGER 構文」で説明しているように、CREATE TRIGGER または DROP TRIGGER ステートメントを使用します。

次に、INSERT 操作に対してアクティブ化するトリガーをテーブルに関連付ける簡単な例を示します。このトリガーは加算器として機能し、テーブルのいずれかのカラムに挿入された値を合計します。

mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));Query OK, 0 rows affected (0.03 sec)
mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account -> FOR EACH ROW SET @sum = @sum + NEW.amount;Query OK, 0 rows affected (0.06 sec)

CREATE TRIGGER ステートメントは、account テーブルに関連付けられている ins_sum という名前のトリガーを作成します。トリガーアクションタイム、トリガーイベント、およびトリガーがアクティブ化したときに行う動作を指定する句も含みます。

  • キーワード BEFORE は、トリガーアクションタイムを示します。この場合、トリガーは、テーブルに挿入された各行の前にアクティブ化します。ここで許可されている別のキーワードは AFTER です。

  • キーワード INSERT は、トリガーイベント、つまりトリガーをアクティブ化する操作の種類を示します。例では、INSERT 操作がトリガーのアクティブ化を引き起こします。DELETE および UPDATE 操作に対するトリガーも作成できます。

  • FOR EACH ROW に続くステートメントは、トリガー本体を定義します。これは、トリガーがアクティブ化するたびに実行するステートメントであり、トリガーイベントによって影響される行ごとに一度行われます。この例では、トリガー本体は、amount カラムに挿入された値をユーザー変数に累積する単純な SET です。このステートメントは、新しい行に挿入される amount カラムの値を意味する NEW.amount としてカラムを参照します。

トリガーを使用するには、加算器変数をゼロにセットし、INSERT ステートメントを実行して、その後変数がどの値になっているかを確認します。

mysql> SET @sum = 0;mysql> INSERT INTO account VALUES(137,14.98),(141,1937.50),(97,-100.00);mysql> SELECT @sum AS 'Total amount inserted';+-----------------------+
| Total amount inserted |
+-----------------------+
| 1852.48 |
+-----------------------+

この場合、INSERT ステートメントの実行後の @sum の値は 14.98 + 1937.50 - 100 または 1852.48 です。

トリガーを破棄するには、DROP TRIGGER ステートメントを使用します。トリガーがデフォルトスキーマにない場合、スキーマ名を指定する必要があります。

mysql> DROP TRIGGER test.ins_sum;

テーブルを削除すると、そのテーブルのトリガーもすべて削除されます。

トリガー名はスキーマの名前空間内に存在します。つまり、すべてのトリガーがスキーマ内で一意の名前を持つ必要があります。異なるスキーマ内のトリガーは同じ名前を持つことができます。

トリガー名はスキーマに対して一意であるという要件以外に、作成できるトリガーの種類に対して別の制限があります。特に、所定のテーブルに、同じトリガーイベントとアクションタイムを持つ複数のトリガーを含めることはできません。たとえば、1 つのテーブルに対して 2 つの BEFORE UPDATE トリガーを定義することはできません。これに対処するために、FOR EACH ROW のあとで BEGIN ... END 複合ステートメント構造構文を使用することにより、複数のステートメントを実行するトリガーを定義できます。(例はこのセクションであとから示します。)

トリガー本体内で、OLD および NEW キーワードを使用すると、トリガーの影響を受ける行のカラムにアクセスできます。OLD および NEW はトリガーに対する MySQL の拡張です。これらは大文字と小文字を区別しません。

INSERT トリガー内では、NEW.col_name だけを使用できます。古い行はありません。DELETE トリガーでは、OLD.col_name だけを使用できます。新しい行はありません。UPDATE トリガーでは、OLD.col_name を使用して、更新される前の行のカラムを参照でき、NEW.col_name を使用して、更新されたあとの行のカラムを参照できます。

OLD で指名されたカラムは読み取り専用です。(それに対する SELECT 権限がある場合) 参照はできますが、変更はできません。NEW で指名されたカラムは、それに対する SELECT 権限がある場合に参照できます。BEFORE トリガーでは、それに対する UPDATE 権限がある場合、SET NEW.col_name = value でその値を変更することもできます。これは、トリガーを使用して、新しい行に挿入する値または行の更新に使用される値を変更できることを意味します。(このような SET ステートメントは、行の変更はすでに行われているため、AFTER トリガーでは効果がありません。)

BEFORE トリガーでは、AUTO_INCREMENT カラムの NEW 値は 0 であり、新しい行が実際に挿入されるときに自動的に生成されるシーケンス番号ではありません。

BEGIN ... END 構造構文を使用することにより、複数のステートメントを実行するトリガーを定義できます。BEGIN ブロック内では、条件文やループなど、ストアドルーチン内で許可されたほかの構文を使用することもできます。ただし、ストアドルーチンの場合と同様に、mysql プログラムを使用して、複数のステートメントを実行するトリガーを定義する場合、トリガー定義内で ; ステートメント区切り文字を使用できるように、mysql ステートメント区切り文字を再定義する必要があります。次の例はこれらの要点を示しています。ここでは、各行の更新に使用する新しい値をチェックし、0 から 100 の範囲に収まるように値を変更する UPDATE トリガーを定義しています。行の更新に使用される前に値をチェックする必要があるので、これは BEFORE トリガーにする必要があります。

mysql> delimiter //mysql> CREATE TRIGGER upd_check BEFORE UPDATE ON account -> FOR EACH ROW -> BEGIN -> IF NEW.amount < 0 THEN -> SET NEW.amount = 0; -> ELSEIF NEW.amount > 100 THEN -> SET NEW.amount = 100; -> END IF; -> END;//mysql> delimiter ;

ストアドプロシージャーを個別に定義してから、単純な CALL ステートメントを使用してトリガーから呼び出したほうが簡単になる場合があります。これは、複数のトリガー内から同じコードを実行する場合にも便利です。

アクティブ化したときにトリガーが実行するステートメントに表示できる対象には制限があります。

  • トリガーは、CALL ステートメントを使用して、データをクライアントに戻すストアドプロシージャーや、ダイナミック SQL を使用するストアドプロシージャーの呼び出しはできません。(ストアドプロシージャーは、OUT または INOUT パラメータを通じてトリガーにデータを返すことが許可されています。)

  • トリガーは、START TRANSACTIONCOMMITROLLBACK など、トランザクションを明示的または暗黙的に開始したり終了したりするステートメントを使用できません。

セクションD.1「ストアドプログラムの制約」も参照してください。

MySQL は次のようにトリガー実行中にエラーを処理します。

  • BEFORE トリガーが失敗した場合、対応する行に対する操作は実行されません。

  • BEFORE トリガーは、行を挿入または変更しようとする試行 によってアクティブ化され、その試行がその後成功するかどうかには関係ありません。

  • AFTER トリガーは、すべての BEFORE トリガーと行操作の実行が成功した場合にのみ実行されます。

  • BEFORE または AFTER トリガーのどちらかの実行中にエラーが発生すると、トリガーの呼び出しを起こしたステートメント全体が失敗します。

  • トランザクションテーブルの場合、ステートメントの失敗により、ステートメントが実行したすべての変更がロールバックされます。トリガーの失敗はステートメントの失敗を招くので、トリガーの失敗はロールバックも引き起こします。非トランザクションテーブルの場合、このようなロールバックは行えないので、ステートメントが失敗しても、エラーの時点以前に実行されたすべて変更は有効なままです。

次の例に示す testref という名前のトリガーなど、トリガーには、名前によるテーブルへの直接の参照を含めることができます。

CREATE TABLE test1(a1 INT);
CREATE TABLE test2(a2 INT);
CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
CREATE TABLE test4( a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b4 INT DEFAULT 0
);
delimiter |
CREATE TRIGGER testref BEFORE INSERT ON test1 FOR EACH ROW BEGIN INSERT INTO test2 SET a2 = NEW.a1; DELETE FROM test3 WHERE a3 = NEW.a1; UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1; END;
|
delimiter ;
INSERT INTO test3 (a3) VALUES (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL);
INSERT INTO test4 (a4) VALUES (0), (0), (0), (0), (0), (0), (0), (0), (0), (0);

次に示すように、テーブル test1 に次の値を挿入するとします。

mysql> INSERT INTO test1 VALUES  -> (1), (3), (1), (7), (1), (8), (4), (4);Query OK, 8 rows affected (0.01 sec)
Records: 8 Duplicates: 0 Warnings: 0

この結果、4 つのテーブルに次のデータが含まれます。

mysql> SELECT * FROM test1;+------+
| a1 |
+------+
| 1 |
| 3 |
| 1 |
| 7 |
| 1 |
| 8 |
| 4 |
| 4 |
+------+
8 rows in set (0.00 sec)
mysql> SELECT * FROM test2;+------+
| a2 |
+------+
| 1 |
| 3 |
| 1 |
| 7 |
| 1 |
| 8 |
| 4 |
| 4 |
+------+
8 rows in set (0.00 sec)
mysql> SELECT * FROM test3;+----+
| a3 |
+----+
| 2 |
| 5 |
| 6 |
| 9 |
| 10 |
+----+
5 rows in set (0.00 sec)
mysql> SELECT * FROM test4;+----+------+
| a4 | b4 |
+----+------+
| 1 | 3 |
| 2 | 0 |
| 3 | 1 |
| 4 | 2 |
| 5 | 0 |
| 6 | 0 |
| 7 | 1 |
| 8 | 1 |
| 9 | 0 |
| 10 | 0 |
+----+------+
10 rows in set (0.00 sec)

20.3.2 トリガーのメタデータ

トリガーに関するメタデータは次のように取得できます。

20.4 イベントスケジューラの使用

MySQL イベントスケジューラは、イベント、つまりスケジュールに従って実行するタスクのスケジュール設定および実行を管理します。次の説明では、イベントスケジューラを取り上げ、次のセクションに分かれています。

ストアドルーチンには、mysql データベース内の event テーブルが必要です。このテーブルは、MySQL 5.6 インストール手順中に作成されます。以前のバージョンから MySQL 5.6 にアップグレードしている場合は、必ず付与テーブルを更新して、event テーブルがあるかどうか確認してください。セクション4.4.7「mysql_upgrade — MySQL テーブルのチェックとアップグレード」を参照してください。

追加のリソース

20.4.1 イベントスケジューラの概要

MySQL イベントはスケジュールに従って実行するタスクです。したがって、これらをスケジュール設定済みイベントと呼ぶことがあります。イベントの作成時には、特定の日時に開始して終了し、1 つ以上の定期的な間隔で実行される 1 つ以上の SQL ステートメントを含んだ、名前付きデータベースオブジェクトを作成します。概念的には、このことは Unix の crontab (cron ジョブとも呼ばれます) や、Windows のタスクスケジューラの考え方に似ています。

この種のスケジュール設定済みのタスクは、時間トリガーと呼ばれる場合もあり、これらが時間の経過によってトリガーされるオブジェクトであることを示しています。これは基本的には正しいのですが、セクション20.3「トリガーの使用」で説明している種類のトリガーと混同しないように、イベントの用語を使用します。さらに厳密にいえば、イベントは時間トリガーと混同しないようにする必要があります。トリガーは、指定したテーブルで行われるイベントの特定の種類に応じて実行されるステートメントを持つデータベースオブジェクトですが、(スケジュール設定済み) イベントは、指定された時間間隔の経過に応じて実行されるステートメントを持つオブジェクトです。

SQL 標準にはイベントのスケジュール設定への対応はありませんが、ほかのデータベースシステムには先例があり、これらの実装と MySQL Server で見られる実装との間には一定の類似性が認められます。

MySQL イベントには次の主要機能およびプロパティーがあります。

  • MySQL 5.6 では、イベントはその名前と、イベントに割り当てられているスキーマによって一意に識別されます。

  • イベントは、スケジュールに従って特定のアクションを実行します。このアクションは、SQL ステートメントから構成され、必要に応じて BEGIN ... END ブロック内の複合ステートメントにできます (セクション13.6「MySQL 複合ステートメント構文」を参照してください)。イベントのタイミングは一度だけまたは繰り返しのどちらかです。一度だけのイベントは一度しか実行しません。繰り返しのイベントは、一定の間隔でアクションを繰り返し、イベントを繰り返すためのスケジュールに、特定の開始日時と終了日時の両方または一方を割り当てるか、どちらも割り当てないことができます。(デフォルトで、繰り返しイベントのスケジュールは作成されるとすぐに開始し、無効または削除されるまで継続します。)

    繰り返しイベントがスケジュール間隔内に終了しない場合は、イベントの複数のインスタンスが同時に実行される可能性があります。これが好ましくない場合は、同時インスタンスを回避するためのメカニズムを設けてください。たとえば、GET_LOCK() 関数や、行またはテーブルのロックを使用できます。

  • ユーザーは、これらの目的用の SQL ステートメントを使用してスケジュール設定済みイベントを作成、変更、および削除できます。構文が無効なイベント作成および変更ステートメントは失敗し、対応するエラーメッセージが表示されます。ユーザーは、実際には自身が保有していない権限を必要とするステートメントを、イベントのアクションに含めることがあります。イベントの作成または変更ステートメントは成功しますが、イベントのアクションは失敗します。詳細は、セクション20.4.6「イベントスケジューラと MySQL 権限」を参照してください。

  • イベントのプロパティーの多くは、SQL ステートメントを使用して設定または変更できます。これらのプロパティーには、イベントの名前、タイミング、持続性 (つまり、そのスケジュールの有効期限が切れたあとも保持されるかどうか)、ステータス (有効または無効)、実行するアクション、および割り当て先のスキーマが含まれます。セクション13.1.2「ALTER EVENT 構文」を参照してください。

    イベントのデフォルトの定義者は、イベントが変更されていない場合は、イベントを作成したユーザーであり、変更されている場合は、定義者はそのイベントに影響する ALTER EVENT ステートメントを最後に発行したユーザーです。イベントが定義されているデータベースに対する EVENT 権限を保有するすべてのユーザーは、そのイベントを変更できます。セクション20.4.6「イベントスケジューラと MySQL 権限」を参照してください。

  • イベントのアクションステートメントには、ストアドルーチン内で許可されているほとんどの SQL ステートメントを含めることができます。制限については、セクションD.1「ストアドプログラムの制約」を参照してください。

20.4.2 イベントスケジューラの構成

イベントは、特別なイベントスケジューラスレッドによって実行されます。イベントスケジューラと呼ぶ場合、実際にはこのスレッドを指しています。実行中、イベントスケジューラスレッドとその現在の状態は、次の説明で示すように、PROCESS 権限を保有するユーザーが SHOW PROCESSLIST の出力で確認できます。

event_scheduler グローバルシステム変数によって、イベントスケジューラがサーバー上で有効であり実行しているかどうかが決まります。これは次の 3 つの値のいずれかを取り、それぞれ次に説明するようにイベントスケジュール設定に影響します。

  • OFF: イベントスケジューラは停止しています。イベントスケジューラスレッドは実行されておらず、SHOW PROCESSLIST の出力に表示されておらず、スケジュール設定済みイベントが実行されていません。OFFevent_scheduler のデフォルト値です。

    イベントスケジューラが停止している場合 (event_schedulerOFF です)、event_scheduler の値を ON に設定することで開始できます。(次の項目を参照してください。)

  • ON: イベントスケジューラが開始され、イベントスケジューラスレッドがすべてのスケジュール設定済みイベントを実行しています。

    イベントスケジューラが ON の場合、イベントスケジューラスレッドは、デーモンプロセスとして SHOW PROCESSLIST の出力に一覧表示され、その状態は次に示すように表示されます。

    mysql> SHOW PROCESSLIST\G*************************** 1. row *************************** Id: 1 User: root Host: localhost db: NULL
    Command: Query Time: 0 State: NULL Info: show processlist
    *************************** 2. row *************************** Id: 2 User: event_scheduler Host: localhost db: NULL
    Command: Daemon Time: 3 State: Waiting for next activation Info: NULL
    2 rows in set (0.00 sec)

    イベントスケジュール設定は、event_scheduler の値を OFF に設定することで停止できます。

  • DISABLED: この値はイベントスケジューラを動作しないようにします。イベントスケジューラが DISABLED の場合、イベントスケジューラスレッドは実行していません (また、SHOW PROCESSLIST の出力にも表示されません)。また、イベントスケジューラの状態は実行時に変更できません。

イベントスケジューラのステータスが DISABLED に設定されていない場合、(SET を使用して) event_schedulerONOFF を切り替えることができます。この変数を設定するときに、OFF0 を、ON1 を使用することも可能です。したがって、mysql クライアントで次の 4 つのどのステートメントを使用しても、イベントスケジューラをオンにできます。

SET GLOBAL event_scheduler = ON;
SET @@global.event_scheduler = ON;
SET GLOBAL event_scheduler = 1;
SET @@global.event_scheduler = 1;

同様に、次の 4 つのどのステートメントを使用してもイベントスケジューラをオフにできます。

SET GLOBAL event_scheduler = OFF;
SET @@global.event_scheduler = OFF;
SET GLOBAL event_scheduler = 0;
SET @@global.event_scheduler = 0;

ONOFF には対応する数値がありますが、SELECT または SHOW VARIABLES によって event_scheduler に対して表示される値は、常に OFFON、または DISABLED のいずれかになります。DISABLED に対応する数値はありません。このため、この変数を設定するときに、ONOFF は通常 10 よりも優先されます。

グローバル変数として指定しないで event_scheduler を設定しようとすると、エラーが発生します。

mysql< SET @@event_scheduler = OFF;ERROR 1229 (HY000): Variable 'event_scheduler' is a GLOBAL
variable and should be set with SET GLOBAL
重要

イベントスケジューラを DISABLED に設定できるのは、サーバーの起動時だけです。event_schedulerON または OFF の場合、実行時にこれを DISABLED には設定できません。また、イベントスケジューラが起動時に DISABLED に設定されている場合、実行時に event_scheduler の値に変更できません。

イベントスケジューラを無効にするには、次の 2 つの方法のいずれかを使用します。

  • サーバーの起動時のコマンド行オプションとして

    --event-scheduler=DISABLED
  • サーバー構成ファイル (Windows システムでの my.cnf または my.ini) で、サーバーが読み取る箇所 (たとえば、[mysqld] セクション内) に次の行を含めます。

    event_scheduler=DISABLED

イベントスケジューラを有効にするには、必要に応じて、--event-scheduler=DISABLED コマンド行オプションを使用しないでサーバーを再起動するか、サーバー構成ファイルの event-scheduler=DISABLED を含む行を削除するかコメントアウトしたあとでサーバーを再起動します。または、サーバーの起動時に DISABLED 値の代わりに ON (または 1) か OFF (または 0) を使用できます。

注記

event_schedulerDISABLED に設定されている場合、イベント操作ステートメントを発行できます。このような場合には警告もエラーも生成されません (ステートメント自体が有効であるとします)。ただし、この変数を ON (または 1) に設定するまで、スケジュール設定済みイベントは実行できません。これが行われると、イベントスケジューラスレッドは、スケジュール設定条件が満たされているすべてのイベントを実行します。

--skip-grant-tables オプションを使用して MySQL Server を起動すると、event_schedulerDISABLED に設定され、コマンド行や my.cnf または my.ini ファイルで設定されたほかのすべての値をオーバーライドします (Bug #26807)。

イベントの作成、変更、または削除に使用される SQL ステートメントについては、セクション20.4.3「イベント構文」を参照してください。

MySQL 5.6 は、INFORMATION_SCHEMA データベースの EVENTS テーブルを提供します。このテーブルは、サーバー上で定義されているスケジュール設定済みイベントに関する情報を取得するためにクエリーできます。詳細は、セクション20.4.4「イベントメタデータ」およびセクション21.7「INFORMATION_SCHEMA EVENTS テーブル」を参照してください。

イベントスケジュール設定と MySQL 権限システムに関する情報については、セクション20.4.6「イベントスケジューラと MySQL 権限」を参照してください。

20.4.3 イベント構文

MySQL 5.6 には、スケジュール設定済みイベントを処理するための複数の SQL ステートメントが用意されています。

20.4.4 イベントメタデータ

イベントに関するメタデータは次のように取得できます。

イベントスケジューラの時間表現

MySQL の各セッションには、セッションタイムゾーン (STZ) があります。これは、セッションの開始時にサーバーの time_zone グローバル値から初期化される time_zone セッション値ですが、セッション中に変更される可能性があります。

CREATE EVENT または ALTER EVENT ステートメントが実行するときに使用されているセッションタイムゾーンが、イベント定義で指定されている時間の解釈に使用されます。これがイベントタイムゾーン (ETZ) になります。つまり、イベントのスケジュール設定に使用され、イベントが実行するときにそのイベント内で有効になるタイムゾーンになります。

mysql.event テーブル内のイベント情報の表現については、execute_atstarts、および ends 時間は UTC に変換され、イベントタイムゾーンとともに格納されます。これにより、サーバータイムゾーンまたはサマータイムの影響に対し生じた変更とは無関係に、定義されたとおりにイベントの実行を処理できます。last_executed 時間も UTC で格納されます。

mysql.event から情報を選択すると、前述の時間は、UTC 値として取得されます。これらの時間は、INFORMATION_SCHEMA.EVENTS テーブルから、または SHOW EVENTS から選択して取得することもできますが、ETZ 値としてレポートされます。これらのソースから利用できるほかの時間は、イベントの作成時や最後の変更時を示します。これらは STZ 値として表示されます。次の表は、イベント時間の表現をまとめています。

mysql.eventINFORMATION_SCHEMA.EVENTSSHOW EVENTS
Execute atUTCETZETZ
StartsUTCETZETZ
EndsUTCETZETZ
Last executedUTCETZ該当なし
CreatedSTZSTZ該当なし
Last alteredSTZSTZ該当なし

20.4.5 イベントスケジューラのステータス

イベントスケジューラは、エラーまたは警告で終了したイベント実行に関する情報を、MySQL Server のエラーログに書き込みます。例については セクション20.4.6「イベントスケジューラと MySQL 権限」を参照してください。

デバッグおよびトラブルシューティングのためにイベントスケジューラの状態に関する状態を取得するには、mysqladmin debug を実行します (セクション4.5.2「mysqladmin — MySQL サーバーの管理を行うクライアント」を参照してください)。このコマンドの実行後に、ここに示すようなイベントスケジューラに関連した出力がサーバーのエラーログに含まれます。

Events status:
LLA = Last Locked At LUA = Last Unlocked At
WOC = Waiting On Condition DL = Data Locked
Event scheduler status:
State : INITIALIZED
Thread id : 0
LLA : init_scheduler:313
LUA : init_scheduler:318
WOC : NO
Workers : 0
Executed : 0
Data locked: NO
Event queue status:
Element count : 1
Data locked : NO
Attempting lock : NO
LLA : init_queue:148
LUA : init_queue:168
WOC : NO
Next activation : 0000-00-00 00:00:00

イベントスケジューラが実行するイベントの一部として生じるステートメント内で、診断メッセージ (エラーだけでなく警告も) がエラーログと、Windows ではアプリケーションイベントログに書き込まれます。頻繁に実行するイベントの場合、これにより、多数のメッセージが記録される結果になることがあります。たとえば、SELECT ... INTO var_list ステートメントの場合、クエリーが行を返さなければ、エラーコード 1329 で警告が発生し (No data)、変数値は変更されないままになります。クエリーが複数の行を返す場合は、エラー 1172 が発生します (結果が 2 行以上です)。どちらの条件についても、条件ハンドラを宣言すると、警告を記録させないようにできます。セクション13.6.7.2「DECLARE ... HANDLER 構文」を参照してください。複数の行を取得できるステートメントの場合、LIMIT 1 を使用して結果セットを単一の行に制限するという別の方法があります。

20.4.6 イベントスケジューラと MySQL 権限

スケジュール設定済みイベントの実行を有効または無効にするには、event_scheduler グローバルシステム変数の値を設定する必要があります。これには SUPER 権限が必要です。

EVENT 権限は、イベントの作成、変更、および削除を制御します。この権限は、GRANT を使用して与えることができます。たとえば、次の GRANT ステートメントは、myschema という名前のスキーマに対する EVENT 権限を、ユーザー jon@ghidora に与えます。

GRANT EVENT ON myschema.* TO jon@ghidora;

(このユーザーアカウントがすでに存在していることと、その他の点では変更されないままであると想定しています。)

この同じユーザーにすべてのスキーマに対する EVENT 権限を認めるには、次のステートメントを使用します。

GRANT EVENT ON *.* TO jon@ghidora;

EVENT 権限にはグローバルまたはスキーマレベルのスコープがあります。このため、単一のテーブルに対してこれを与えようとすると、次のようなエラーが生じます。

mysql> GRANT EVENT ON myschema.mytable TO jon@ghidora;ERROR 1144 (42000): Illegal GRANT/REVOKE command; please
consult the manual to see which privileges can be used

イベントはその定義者の権限で実行され、定義者が必須の権限を保有していないアクションは実行できません。たとえば、jon@ghidoramyschema に対する EVENT 権限を保有しているとします。また、このユーザーは myschema に対する SELECT 権限は保有しているが、このスキーマに対するほかの権限は保有していないとします。jon@ghidora は、次のような新しいイベントを作成できます。

CREATE EVENT e_store_ts ON SCHEDULE EVERY 10 SECOND DO INSERT INTO myschema.mytable VALUES (UNIX_TIMESTAMP());

このユーザーは 1 分ほど待機したあと、テーブルに複数の新しい行が表示されることを予想して SELECT * FROM mytable; クエリーを実行します。実際は、テーブルは空です。ユーザーは該当するテーブルに対する INSERT 権限がないので、イベントの効果はありませんでした。

MySQL エラーログ (hostname.err) を調べると、イベントは実行していますが、RetCode=0 で示されているように、イベントが実行しようとしているアクションは失敗していることがわかります。

060209 22:39:44 [Note] EVEX EXECUTING event newdb.e [EXPR:10]
060209 22:39:44 [Note] EVEX EXECUTED event newdb.e [EXPR:10]. RetCode=0
060209 22:39:54 [Note] EVEX EXECUTING event newdb.e [EXPR:10]
060209 22:39:54 [Note] EVEX EXECUTED event newdb.e [EXPR:10]. RetCode=0
060209 22:40:04 [Note] EVEX EXECUTING event newdb.e [EXPR:10]
060209 22:40:04 [Note] EVEX EXECUTED event newdb.e [EXPR:10]. RetCode=0

このユーザーは、エラーログにアクセスできない可能性が非常に高いので、直接それを実行することによって、イベントのアクションステートメントが有効であるかどうか検証できます。

mysql> INSERT INTO myschema.mytable VALUES (UNIX_TIMESTAMP());ERROR 1142 (42000): INSERT command denied to user
'jon'@'ghidora' for table 'mytable'

INFORMATION_SCHEMA.EVENTS テーブルを調べることによって、e_store_ts が存在し有効になっているが、その LAST_EXECUTED カラムが NULL になっていることがわかります。

mysql> SELECT * FROM INFORMATION_SCHEMA.EVENTS > WHERE EVENT_NAME='e_store_ts' > AND EVENT_SCHEMA='myschema'\G*************************** 1. row *************************** EVENT_CATALOG: NULL EVENT_SCHEMA: myschema EVENT_NAME: e_store_ts DEFINER: jon@ghidora EVENT_BODY: SQL
EVENT_DEFINITION: INSERT INTO myschema.mytable VALUES (UNIX_TIMESTAMP()) EVENT_TYPE: RECURRING EXECUTE_AT: NULL INTERVAL_VALUE: 5 INTERVAL_FIELD: SECOND SQL_MODE: NULL STARTS: 0000-00-00 00:00:00 ENDS: 0000-00-00 00:00:00 STATUS: ENABLED ON_COMPLETION: NOT PRESERVE CREATED: 2006-02-09 22:36:06 LAST_ALTERED: 2006-02-09 22:36:06 LAST_EXECUTED: NULL EVENT_COMMENT:
1 row in set (0.00 sec)

EVENT 権限を取り消すには、REVOKE ステートメントを使用します。この例では、スキーマ myschema に対する EVENT 権限が jon@ghidora ユーザーアカウントから削除されます。

REVOKE EVENT ON myschema.* FROM jon@ghidora;
重要

ユーザーから EVENT 権限を取り消しても、そのユーザーが作成したイベントが削除されたり無効にされたりすることはありません。

作成したユーザーの名前を変更したり削除したりしても、イベントが移行または削除されることはありません。

ユーザー jon@ghidora に、myschema スキーマに対する EVENT および INSERT 権限が与えられているとします。続いてこのユーザーが次のイベントを作成します。

CREATE EVENT e_insert ON SCHEDULE EVERY 7 SECOND DO INSERT INTO myschema.mytable;

このイベントの作成後、rootjon@ghidoraEVENT 権限を取り消します。ただし、e_insert は実行し続け、7 秒ごとに新しい行が mytable に挿入されます。root が次のどちらかのステートメントを発行した場合も、同じことが当てはまります。

  • DROP USER jon@ghidora;

  • RENAME USER jon@ghidora TO someotherguy@ghidora;

DROP USER または RENAME USER ステートメントの発行前後で、mysql.event テーブル (このセクションで後述します) か、INFORMATION_SCHEMA.EVENTS テーブル (セクション21.7「INFORMATION_SCHEMA EVENTS テーブル」を参照してください) を調べると、これが当てはまることを確認できます。

イベント定義は、mysql.event テーブルに格納されています。別のユーザーアカウントが作成したイベントを削除するには、MySQL root ユーザー (または必要な権限を保有する別のユーザー) がこのテーブルから行を削除できます。たとえば、前述のイベント e_insert を削除するには、root は次のステートメントを使用できます。

DELETE FROM mysql.event WHERE db = 'myschema' AND definer = 'jon@ghidora' AND name = 'e_insert';

mysql.event テーブルから行を削除するときには、イベント名、データベーススキーマ名、ユーザーアカウントを一致させることが重要です。これは、同じユーザーが、別々のスキーマに同じ名前の異なるイベントを作成できるためです。

ユーザーの EVENT 権限は、mysql.user および mysql.db テーブルの Event_priv カラムに格納されています。どちらの場合でも、このカラムには、「Y」または「N」のどちらかの値が含まれています。「N」がデフォルトです。指定されたユーザーがグローバルな EVENT 権限を保有している場合 (つまり、GRANT EVENT ON *.* を使用して権限が与えられた場合) にのみ、そのユーザーの mysql.user.Event_priv「Y」に設定されます。スキーマレベルの EVENT 権限の場合、GRANT は、mysql.db に行を作成し、その行の Db カラムをスキーマの名前に、User カラムをユーザーの名前に、Event_priv カラムを「Y」に設定します。GRANT EVENT および REVOKE EVENT ステートメントがこれらのテーブルでの必要な操作を実行するので、これらのテーブルを直接操作する必要はありません。

5 つのステータス変数が、イベント関連操作のカウントを提供します (ただし、イベントが実行するステートメントのカウントは提供しませんセクションD.1「ストアドプログラムの制約」を参照してください)。これらを次に示します。

  • Com_create_event: サーバーが最後に再起動してから実行された CREATE EVENT ステートメントの数。

  • Com_alter_event: サーバーが最後に再起動してから実行された ALTER EVENT ステートメントの数。

  • Com_drop_event: サーバーが最後に再起動してから実行された DROP EVENT ステートメントの数。

  • Com_show_create_event: サーバーが最後に再起動してから実行された SHOW CREATE EVENT ステートメントの数。

  • Com_show_events: サーバーが最後に再起動してから実行された SHOW EVENTS ステートメントの数。

ステートメント SHOW STATUS LIKE '%event%'; を実行すると、これらのすべての現在値を一度に表示できます。

20.5 ビューの使用

ビュー (更新可能なビューを含む) は MySQL Server 5.6 で使用できます。ビューは、呼び出されたときに結果セットを生成するストアドクエリーです。ビューは仮想テーブルとして機能します。

ビューをサポートしていなかった古いリリースから MySQL 5.6 にアップグレードした場合、ビューを使用するには、ビュー関連の権限を含めるように付与テーブルをアップグレードする必要があります。セクション4.4.7「mysql_upgrade — MySQL テーブルのチェックとアップグレード」を参照してください。

次の説明では、ビューを作成し削除するための構文について記述し、それらの使用法の例をいくつか示します。

追加のリソース

20.5.1 ビューの構文

CREATE VIEW ステートメントは新しいビューを作成します (セクション13.1.20「CREATE VIEW 構文」を参照してください)。ビューの定義を変更したり、ビューを削除したりするには、ALTER VIEW (セクション13.1.9「ALTER VIEW 構文」を参照してください) または DROP VIEW (セクション13.1.31「DROP VIEW 構文」を参照してください) を使用します。

ビューは、多くの種類の SELECT ステートメントから作成できできます。ベーステーブルまたはほかのビューを参照できます。結合、UNION、およびサブクエリーを使用できます。SELECT がテーブルをまったく参照しなくてもかまいません。次の例では、別のテーブルからの 2 つのカラムに加え、それらのカラムから計算される式を選択するビューを定義しています。

mysql> CREATE TABLE t (qty INT, price INT);mysql> INSERT INTO t VALUES(3, 50), (5, 60);mysql> CREATE VIEW v AS SELECT qty, price, qty*price AS value FROM t;mysql> SELECT * FROM v;+------+-------+-------+
| qty | price | value |
+------+-------+-------+
| 3 | 50 | 150 |
| 5 | 60 | 300 |
+------+-------+-------+
mysql> SELECT * FROM v WHERE qty = 5;+------+-------+-------+
| qty | price | value |
+------+-------+-------+
| 5 | 60 | 300 |
+------+-------+-------+

20.5.2 ビュー処理アルゴリズム

CREATE VIEW または ALTER VIEW のオプションの ALGORITHM 句は、標準 SQL に対する MySQL 拡張です。これは、MySQL によるビューの処理方法に影響を与えます。ALGORITHM は、MERGETEMPTABLE、または UNDEFINED の 3 つの値を受け取ります。ALGORITHM 句が存在しない場合、デフォルトのアルゴリズムは UNDEFINED です。

MERGE の場合、ビューを参照するステートメントのテキストとビュー定義がマージされ、ビュー定義の部分が対応するステートメントの部分と置き換えられます。

TEMPTABLE の場合、ビューの結果が一時テーブル内に取得され、その後、ステートメントを実行するために使用されます。

UNDEFINED の場合、MySQL は使用するアルゴリズムを選択します。できるかぎり TEMPTABLE より MERGE が優先されます。これは通常、MERGE のほうが効率性が高く、一時テーブルを使用するとビューを更新できなくなるためです。

明示的に TEMPTABLE を選択する理由は、一時テーブルが作成されたあとで、ステートメントの処理を終了するために使用される前に、ベースとなるテーブルでロックを解放できるからです。その結果、MERGE アルゴリズムよりもすみやかにロックが解除され、ビューを使用するほかのクライアントが長時間ブロックされることがなくなります。

次の 3 つの理由によって、ビューアルゴリズムをUNDEFINED にできます。

  • CREATE VIEW ステートメントの中に ALGORITHM 句が存在しない。

  • CREATE VIEW ステートメントに明示的な ALGORITHM = UNDEFINED 句が含まれている。

  • 一時テーブルだけでしか処理できないビューに対して、ALGORITHM = MERGE が指定されている。この場合、MySQL は警告を発し、アルゴリズムを UNDEFINED に設定します。

前述のように、MERGE は、ビュー定義の対応する部分を、ビューを参照するステートメントにマージして処理されます。次の例で、MERGE アルゴリズムの動作について簡単に説明します。例では、次の定義を含むビュー v_merge が存在していると想定します。

CREATE ALGORITHM = MERGE VIEW v_merge (vc1, vc2) AS
SELECT c1, c2 FROM t WHERE c3 > 100;

例 1: 次のステートメントを発行するとします。

SELECT * FROM v_merge;

MySQL は次のようにステートメントを処理します。

  • v_merget になる

  • *vc1, vc2 となり、c1, c2 と一致する

  • ビュー WHERE 句が追加される

結果が実行されるステートメントは次のようになります。

SELECT c1, c2 FROM t WHERE c3 > 100;

例 2: 次のステートメントを発行するとします。

SELECT * FROM v_merge WHERE vc1 < 100;

このステートメントは、前述のステートメントと同様に処理されますが、vc1 < 100c1 < 100 になり、AND 連結詞を使用してビュー WHERE 句がステートメント WHERE 句に追加される点が異なります (また、句の一部が確実に正しい優先順位で実行されるように、かっこが追加されます)。結果が実行されるステートメントは次のようになります。

SELECT c1, c2 FROM t WHERE (c3 > 100) AND (c1 < 100);

事実上、実行されるステートメントには、次の形式の WHERE 句が含まれます。

WHERE (select WHERE) AND (view WHERE)

MERGE アルゴリズムを使用できない場合、一時テーブルを代わりに使用する必要があります。ビューに次のいずれかの構造構文が含まれる場合、MERGE は使用できません。

  • 集計関数 (SUM()MIN()MAX()COUNT() など)

  • DISTINCT

  • GROUP BY

  • HAVING

  • LIMIT

  • UNION または UNION ALL

  • 選択リスト内のサブクエリー

  • リテラル値だけの参照 (この場合、ベースとなるテーブルがありません)

20.5.3 更新可能および挿入可能なビュー

いくつかのビューは更新可能です。つまり、これらのビューを UPDATEDELETEINSERT などのステートメントで使用して、ベースとなるテーブルの内容を更新できます。ビューが更新可能であるためには、そのビュー内の行とベースとなるテーブル内の行の間に 1 対 1 の関係が存在する必要があります。また、ビューを更新不可能にするその他の特定の構造構文も存在します。より具体的には、次のいずれかを含む場合、ビューは更新可能ではありません。

  • 集計関数 (SUM()MIN()MAX()COUNT() など)

  • DISTINCT

  • GROUP BY

  • HAVING

  • UNION または UNION ALL

  • 選択リスト内のサブクエリー

  • 特定の結合 (このセクションで後述する結合に関する追加説明を参照してください)

  • FROM 句内の更新不可能なビュー

  • FROM 句内のテーブルを参照する WHERE 句内のサブクエリー

  • リテラル値だけの参照 (この場合、更新するベースとなるテーブルがありません)

  • ALGORITHM = TEMPTABLE の使用 (一時テーブルを使用すると常にビューは更新不可能になります)

  • ベーステーブルのいずれかのカラムに対する複数の参照。

挿入可能性 (INSERT ステートメントで更新可能であること) については、更新可能なビューがビューカラムに対する次の追加要件も満たしている場合に挿入可能になります。

  • 重複したビューカラム名が存在しないようにする必要があります。

  • ビューには、デフォルト値を持たない、ベーステーブル内のすべてのカラムが含まれている必要があります。

  • ビューカラムは、派生カラムではなく、単純なカラム参照である必要があります。派生カラムは、単純なカラム参照ではなく、式から派生したカラムです。派生したカラムの例は次のとおりです。

    3.14159
    col1 + 3
    UPPER(col2)
    col3 / col4
    (subquery)

単純なカラム参照と派生カラムが混在しているビューは挿入できませんが、派生カラム以外のカラムだけを更新する場合は、更新可能になります。次のビューを考えてみてください。

CREATE VIEW v AS SELECT col1, 1 AS col2 FROM t;

このビューは、col2 が式から派生しているので挿入できません。ただし、更新で col2 を更新しようとしていない場合は、更新可能になります。次の更新は許可されます。

UPDATE v SET col1 = 0;

次の更新は、派生カラムを更新しようとしているので、許可されません。

UPDATE v SET col2 = 0;

MERGE アルゴリズムで処理できるとすれば、複数テーブルビューが更新できる可能性があります。これを実現するには、ビューで (外部結合または UNION ではなく) 内部結合を使用する必要があります。また、ビュー定義内の単一のテーブルだけを更新できるので、SET 句は、ビュー内のいずれかのテーブルのカラムだけを指名する必要があります。UNION ALL を使用するビューは、実装が一時テーブルを使用して処理するので、理論的に更新可能でも許可されません。

更新可能な複数テーブルビューでは、INSERT は、単一のテーブルに挿入する場合に機能します。DELETE はサポートされません。

INSERT DELAYED は、ビューではサポートされません。

テーブルに AUTO_INCREMENT カラムが含まれている場合、AUTO_INCREMENT カラムが含まれていないテーブル上の挿入可能なビューに挿入すると、LAST_INSERT_ID() の値を変更しません。これは、ビューの一部ではないカラムにデフォルト値を挿入した副作用が現れないようにするためです。

更新可能なビューに対して WITH CHECK OPTION 句を指定すると、select_statement 内の WHERE 句が true である行を除く行への挿入または更新を回避できます。

更新可能なビューに対する WITH CHECK OPTION 句では、そのビューが別のビューとの関連で定義されている場合、LOCAL および CASCADED キーワードによってチェックテストのスコープが決定されます。LOCAL キーワードは、CHECK OPTION を、定義されているビューのみに制限します。CASCADED を指定すると、ベースとなるビューに対するチェックも評価されます。どちらのキーワードも指定されていない場合、デフォルトは CASCADED になります。次のテーブルと一連のビューの定義を考えてみてください。

mysql> CREATE TABLE t1 (a INT);mysql> CREATE VIEW v1 AS SELECT * FROM t1 WHERE a < 2 -> WITH CHECK OPTION;mysql> CREATE VIEW v2 AS SELECT * FROM v1 WHERE a > 0 -> WITH LOCAL CHECK OPTION;mysql> CREATE VIEW v3 AS SELECT * FROM v1 WHERE a > 0 -> WITH CASCADED CHECK OPTION;

ここで、v2 および v3 ビューは、v1 という別のビューの観点で定義されています。v2 には LOCAL チェックオプションがあるので、挿入は、v2 チェックに対してのみテストされます。v3 には CASCADED チェックオプションがあるので、挿入はそれ自身のチェックに対してだけでなく、ベースとなるビューのチェックに対してもテストされます。次のステートメントでこれらの違いを示しています。

mysql> INSERT INTO v2 VALUES (2);Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO v3 VALUES (2);ERROR 1369 (HY000): CHECK OPTION failed 'test.v3'

MySQL は、CREATE VIEW 時に、ビューの更新可能性フラグというフラグを設定します。UPDATE および DELETE (および同様の操作) がビューで有効な場合、フラグは YES (true) に設定されます。それ以外の場合、フラグは NO (false) に設定されます。INFORMATION_SCHEMA.VIEWS テーブルの IS_UPDATABLE カラムは、このフラグのステータスを表示します。これは、ビューが更新可能であるかどうかをサーバーが常に把握していることを意味します。ビューが更新可能ではない場合、UPDATEDELETEINSERT などのステートメントは無効であり、拒否されます。(このセクションの別の箇所で説明しているように、ビューが更新可能である場合でも、ビューへの挿入はできない場合もあります。)

ビューを更新できるかどうかは、updatable_views_with_limit システム変数の値に影響されます。セクション5.1.4「サーバーシステム変数」を参照してください。

20.5.4 ビューのメタデータ

ビューに関するメタデータは次のように取得できます。

20.6 ストアドプログラムおよびビューのアクセスコントロール

ストアドプログラムとビューは使用する前に定義され、参照されるときに、その権限を決定するセキュリティーのコンテキスト内で実行します。これらの権限は、その DEFINER 属性と、存在する場合はその SQL SECURITY 特性で制御されます。

すべてのストアドプログラム (プロシージャー、関数、トリガー、およびイベント) とビューには、MySQL アカウントを指名する DEFINER 属性を含めることができます。DEFINER 属性をストアドプログラムまたはビュー定義から省略した場合、デフォルトのアカウントは、オブジェクトを作成するユーザーになります。

さらに、ストアドルーチン (プロシージャーおよび関数) とビューには、値が DEFINER または INVOKER である SQL SECURITY 特性があり、オブジェクトが定義側のコンテキストで実行するか、呼び出し元のコンテキストで実行するかを指定できます。SQL SECURITY 特性を省略した場合、デフォルトは定義側のコンテキストになります。

トリガーとイベントには、SQL SECURITY 特性がなく、常に定義側のコンテキストで実行します。サーバーが必要に応じて自動的にこれらのオブジェクトを呼び出すので、呼び出し元ユーザーは存在しません。

定義側と呼び出し元のセキュリティーのコンテキストは次のように異なります。

  • 定義側のセキュリティーコンテキストで実行するストアドプログラムまたはビューは、DEFINER 属性で指名されたアカウントの権限で実行します。これらの権限は、呼び出し元ユーザーの権限とは完全に異なる場合があります。呼び出し元は、オブジェクトを参照するために適切な権限 (たとえば、ストアドプロシージャーを呼び出すための EXECUTE や、ビューから選択するための SELECT) が必要ですが、オブジェクトが実行すると、呼び出し元の権限は無視され、DEFINER アカウント権限だけが重要になります。このアカウントの権限が低い場合、オブジェクトが実行できる操作は、それに応じて制限されます。DEFINER アカウントに高い権限が与えられている場合 (root アカウントなど)、呼び出し元のユーザーにかかわらず、オブジェクトは強力な操作を実行できます。

  • 呼び出し元のセキュリティーコンテキストで実行するストアドルーチンまたはビューは、呼び出し元が権限を持つ操作だけを実行できます。DEFINER 属性は指定できますが、呼び出し元のコンテキストで実行するオブジェクトに対して効果はありません。

次のストアドプロシージャーを検討してください。

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

p1 に対する EXECUTE 権限を持つどのユーザーでも、CALL ステートメントを使用してこれを呼び出すことができます。ただし、p1 が実行するときには、DEFINER のセキュリティーコンテキストで実行するので、DEFINER 属性で指名されたアカウントである 'admin'@'localhost' の権限で実行します。このアカウントは、p1EXECUTE 権限のほかに、テーブル t1UPDATE 権限が必要です。それ以外の場合、プロシージャーは失敗します。

続いて次のストアドプロシージャーを検討してください。これは p1 と同じですが、その SQL SECURITY 特性が INVOKER である点が異なります。

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

p2p1 と異なり、INVOKER のセキュリティーコンテキストで実行します。DEFINER 属性は無関係であり、p2 は呼び出し元ユーザーの権限で実行します。呼び出し元に p2 に対する EXECUTE 権限、またはテーブル t1 に対する UPDATE 権限が不足している場合、p2 は失敗します。

MySQL は、次のルールを使用して、ユーザーがオブジェクトの DEFINER 属性で指定できるアカウントを制御します。

  • SUPER 権限がある場合にかぎり、自身のアカウント以外の DEFINER 値を指定できます。

  • SUPER 権限がない場合、唯一の正当なユーザー値は、文字どおり指定するか、CURRENT_USER を使用して指定した自身のアカウントです。定義者をほかのアカウントに設定することはできません。

ストアドプログラムおよびビューの作成と使用に関して考えられるリスクを最小限に抑えるため、次のガイドラインに従ってください。

  • 可能な場合は、ストアドルーチンまたはビューに対して、オブジェクト定義の SQL SECURITY INVOKER を使用して、オブジェクトが実行する操作に適したアクセス許可を持つユーザーだけが使用できるようにします。

  • SUPER 権限を持つアカウントの使用中に、定義側のコンテキストのストアドプログラムまたはビューを作成する場合は、オブジェクトが実行する操作に必要な権限だけを所有しているアカウントを指名する明示的な DEFINER 属性を指定します。高い権限を持つ DEFINER アカウントは、絶対に必要な場合にのみ指定してください。

  • 管理者は、SUPER 権限をユーザーに与えなければ、高い権限を持つ DEFINER アカウントをユーザーが指定できないようにできます。

  • 定義側のコンテキストのオブジェクトを作成するときには、呼び出し元ユーザーに権限のないデータに定義側がアクセスできる場合があります。権限のないユーザーに特定の権限を与えなければ、これらのオブジェクトへの参照を防止できる場合があります。

    • ストアドプロシージャーまたはストアドファンクションに対する EXECUTE 権限を持たないユーザーは、これを参照できません。

    • ビューに対する適切な権限 (ビューから選択するための SELECT、ビューに挿入するための INSERT など) を持っていないユーザーは、ビューを参照できません。

    ただし、トリガーに対するこのような制御は存在しません。ユーザーが直接トリガーを参照することはないからです。トリガーは常に、DEFINER コンテキストで実行し、特別な権限を持たないユーザーによる通常のテーブルアクセスを含め、トリガーが関連付けられているテーブルへのアクセスがあるとアクティブ化されます。DEFINER アカウントに高い権限が与えられている場合、トリガーは、慎重を要する操作または危険な操作を実行できます。トリガーの作成に必要な SUPER および TRIGGER 権限が、作成したユーザーのアカウントから削除された場合にも、このことは引き続き当てはまります。管理者は、この権限の組み合わせをユーザーに認める場合、特に注意する必要があります。

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 ステートメントを受け入れるには、DETERMINISTICNO 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 EVENTCREATE PROCEDURECREATE FUNCTIONALTER EVENTALTER PROCEDUREALTER FUNCTIONDROP EVENTDROP 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 SQLNO SQLREADS SQL DATA、および MODIFIES SQL DATA 特性は、関数がデータを読み取るか書き込むかに関する情報を示します。NO SQL または READS SQL DATA は、関数がデータを変更しないことを示しますが、特性が指定されていない場合にデフォルトは CONTAINS SQL になるので、これらのどちらかを明示的に指定する必要があります。

    デフォルトでは、CREATE FUNCTION ステートメントを受け入れるには、DETERMINISTICNO 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;
    • プロシージャーの呼び出しは、コミットまたはロールバックしたトランザクション内で行えます。プロシージャー実行のトランザクションの側面が正しく複製されるように、トランザクションのコンテキストが説明されます。つまり、サーバーは、実際にデータを実行し修正するプロシージャー内のステートメントを記録し、BEGINCOMMIT、および ROLLBACK ステートメントも必要に応じて記録します。たとえば、プロシージャーがトランザクションテーブルだけを更新し、ロールバックされるトランザクション内で実行される場合、これらの更新は記録されません。プロシージャーがコミットされたトランザクション内で行われた場合、BEGIN および COMMIT ステートメントが更新とともに記録されます。ロールバックしたトランザクション内で実行するプロシージャーの場合、そのステートメントは、ステートメントがスタンドアロンで実行された場合に適用されるものと同じルールを使用して記録されます。

      • トランザクションテーブルに対する更新は記録されません。

      • 非トランザクションテーブルに対する更新は、ロールバックで取り消されないので、記録されます。

      • トランザクションテーブルと非トランザクションテーブルの混在に対する更新は、スレーブがマスターと同じ変更およびロールバックを行うように、BEGINROLLBACK で囲まれて記録されます。

  • ストアドプロシージャーの呼び出しは、ストアドファンクション内から呼び出される場合、ステートメントレベルのバイナリログに書き込まれません。この場合、記録される唯一の対象は、関数を呼び出すステートメント (記録されたステートメント内で行われた場合) または DO ステートメント (記録されないステートメント内で行われた場合) です。このため、それ以外の場合にプロシージャー自体が安全であっても、プロシージャーを呼び出すストアドファンクションを使用するときには注意を払う必要があります。

関連キーワード:  ステートメント,トリガー,イベント,テーブル,セクション,CREATE,サーバー,ユーザー,EVENT,event