MySQL のエラーハンドリングとロギング

はじめに

この実験では、MySQL のエラーハンドリングとログ記録技術を探索します。この実験では、サーバーで実行されたすべての SQL 文を記録するための一般クエリログの有効化について説明します。これは、デバッグや監査に役立ちます。general_log システム変数を ON に設定することで一般クエリログを有効にする方法と、general_log_file 変数を使用してログファイルの場所を確認または変更する方法を学びます。

最初のステップは、root ユーザーとして MySQL サーバーに接続し、次に一般クエリログをグローバルに有効にすることです。また、現在のログファイルの場所を表示する方法と、それを別のパスに変更する方法を学びます。この際、MySQL サーバープロセスが指定された場所に書き込み権限を持っていることを確認します。

Skills Graph

%%%%{init: {'theme':'neutral'}}%%%% flowchart RL mysql(("MySQL")) -.-> mysql/BasicKeywordsandStatementsGroup(["Basic Keywords and Statements"]) mysql(("MySQL")) -.-> mysql/SystemManagementToolsGroup(["System Management Tools"]) mysql(("MySQL")) -.-> mysql/AdvancedFeaturesGroup(["Advanced Features"]) mysql/BasicKeywordsandStatementsGroup -.-> mysql/create_database("Database Creation") mysql/BasicKeywordsandStatementsGroup -.-> mysql/create_table("Table Creation") mysql/BasicKeywordsandStatementsGroup -.-> mysql/select("Data Retrieval") mysql/BasicKeywordsandStatementsGroup -.-> mysql/insert("Data Insertion") mysql/BasicKeywordsandStatementsGroup -.-> mysql/update("Data Update") mysql/SystemManagementToolsGroup -.-> mysql/show_variables("Configuration Overview") mysql/AdvancedFeaturesGroup -.-> mysql/stored_procedures("Procedure Management") subgraph Lab Skills mysql/create_database -.-> lab-550905{{"MySQL のエラーハンドリングとロギング"}} mysql/create_table -.-> lab-550905{{"MySQL のエラーハンドリングとロギング"}} mysql/select -.-> lab-550905{{"MySQL のエラーハンドリングとロギング"}} mysql/insert -.-> lab-550905{{"MySQL のエラーハンドリングとロギング"}} mysql/update -.-> lab-550905{{"MySQL のエラーハンドリングとロギング"}} mysql/show_variables -.-> lab-550905{{"MySQL のエラーハンドリングとロギング"}} mysql/stored_procedures -.-> lab-550905{{"MySQL のエラーハンドリングとロギング"}} end

一般クエリログの有効化

このステップでは、MySQL で一般クエリログを有効にします。一般クエリログは、サーバーで実行されたすべての SQL 文を記録します。これは、デバッグ、監査、およびパフォーマンス分析に役立ちます。ただし、一般クエリログを有効にすると大量のデータが生成され、サーバーのパフォーマンスに影響を与える可能性があります。特にトラフィックの多い環境では注意が必要です。したがって、必要なときにのみ慎重に使用する必要があります。

まず、root ユーザーとして MySQL サーバーに接続しましょう。ターミナルを開き、次のコマンドを実行します。

mysql -u root -p

root パスワードの入力を求められます。パスワードを入力して Enter キーを押します。root パスワードを設定していない場合は、そのまま Enter キーを押します。

これで MySQL サーバーに接続したので、一般クエリログを有効にすることができます。これを行うには、general_log システム変数を ON に設定する必要があります。次の SQL 文を実行します。

SET GLOBAL general_log = 'ON';

このコマンドは、すべての接続に対して一般クエリログをグローバルに有効にします。

次に、クエリが書き込まれるログファイルを指定する必要があります。デフォルトのログファイルは通常、MySQL のデータディレクトリにあり、hostname.log という名前になっています。現在のログファイルの場所を確認するには、次の SQL 文を実行します。

SHOW VARIABLES LIKE 'general_log_file';

出力には、general_log_file 変数の現在の値、つまりログファイルのパスが表示されます。

ログファイルの場所を変更したい場合は、general_log_file システム変数を別のパスに設定することができます。たとえば、ログファイルを /tmp/mysql_general.log に設定するには、次の SQL 文を実行します。

SET GLOBAL general_log_file = '/tmp/mysql_general.log';

重要： MySQL サーバープロセスが指定されたログファイルの場所に書き込み権限を持っていることを確認してください。

これで一般クエリログが有効になりました。サーバーで実行されたすべての SQL 文はログファイルに書き込まれます。ログが正常に動作していることを確認するには、いくつかの簡単な SQL 文を実行します。たとえば、

SELECT NOW();
SHOW DATABASES;

次に、MySQL クライアントを終了します。

exit

最後に、ログファイルの内容を確認して、SQL 文が記録されているかどうかを確認します。cat コマンドを使用してログファイルを表示することができます。デフォルトのログファイルの場所を使用した場合、コマンドは次のようになります。

sudo cat /var/log/mysql/mysql.log

ログファイルの場所を /tmp/mysql_general.log に変更した場合、コマンドは次のようになります。

sudo cat /tmp/mysql_general.log

ログファイルには、実行した SQL 文とタイムスタンプやその他の情報が表示されるはずです。

一般クエリログの使用が終了したら、ディスク領域を大量に消費し、サーバーのパフォーマンスに影響を与える可能性があるため、無効にすることを忘れないでください。一般クエリログを無効にするには、次の SQL 文を実行します。

mysql -u root -p -e "SET GLOBAL general_log = 'OFF';"

このコマンドは、一般クエリログをグローバルに無効にします。

ストアドプロシージャにエラーハンドリングを追加する

このステップでは、MySQL のストアドプロシージャにエラーハンドリングを追加します。エラーハンドリングは、ストアドプロシージャの堅牢性と信頼性を確保するために重要です。無効な入力、データベース接続エラー、またはデータ整合性違反などの予期しない状況を適切に処理することができます。

まず、エラーハンドリングを持たない単純なストアドプロシージャを作成しましょう。このプロシージャは、テーブルに新しいレコードを挿入しようとします。挿入が失敗した場合（たとえば、重複キーのため）、プロシージャはエラーに関する具体的な情報を提供せずに単に終了します。

root ユーザーとして MySQL サーバーに接続します。

mysql -u root -p

次に、例のためにデータベースとテーブルを作成しましょう。

CREATE DATABASE IF NOT EXISTS testdb;
USE testdb;

CREATE TABLE IF NOT EXISTS products (
    id INT PRIMARY KEY,
    name VARCHAR(255)
);

次に、products テーブルに新しい製品を挿入する insert_product という名前のストアドプロシージャを作成します。

DELIMITER //
CREATE PROCEDURE insert_product(IN p_id INT, IN p_name VARCHAR(255))
BEGIN
    INSERT INTO products (id, name) VALUES (p_id, p_name);
END //
DELIMITER ;

このストアドプロシージャは 2 つの入力パラメータを受け取ります。p_id（製品 ID）と p_name（製品名）です。そして、指定された値で products テーブルに新しいレコードを挿入しようとします。

次に、ストアドプロシージャにエラーハンドリングを追加しましょう。特定のエラーが発生したときに実行されるエラーハンドラを定義するために、DECLARE ... HANDLER 構文を使用します。

insert_product ストアドプロシージャを次のように変更します。

DELIMITER //
CREATE PROCEDURE insert_product(IN p_id INT, IN p_name VARCHAR(255))
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        SELECT 'An error occurred during the insertion.' AS message;
    END;

    INSERT INTO products (id, name) VALUES (p_id, p_name);
END //
DELIMITER ;

この変更されたバージョンでは、DECLARE EXIT HANDLER FOR SQLEXCEPTION を使用してエラーハンドラを追加しています。このハンドラは、ストアドプロシージャの実行中に SQL 例外が発生した場合に実行されます。ハンドラの中では、エラーが発生したことを示すメッセージを選択しています。

エラーハンドリング付きのストアドプロシージャをテストしましょう。まず、製品を挿入します。

CALL insert_product(1, 'Product A');

これにより、products テーブルに新しいレコードがエラーなく挿入されるはずです。

次に、同じ製品を再度挿入してみましょう。

CALL insert_product(1, 'Product A');

今回は、id 列が主キーであり、重複値を含むことができないため、挿入は失敗します。しかし、単に終了する代わりに、エラーハンドラが実行され、「An error occurred during the insertion.」というメッセージが表示されます。

また、異なるタイプのエラーに対してより具体的なエラーハンドラを追加することもできます。たとえば、重複キーエラー（SQLSTATE '23000'）に対するハンドラを追加することができます。

DELIMITER //
CREATE PROCEDURE insert_product(IN p_id INT, IN p_name VARCHAR(255))
BEGIN
    DECLARE EXIT HANDLER FOR SQLSTATE '23000'
    BEGIN
        SELECT 'Duplicate key error.' AS message;
    END;

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        SELECT 'An error occurred during the insertion.' AS message;
    END;

    INSERT INTO products (id, name) VALUES (p_id, p_name);
END //
DELIMITER ;

これで、重複する製品を挿入しようとすると、一般的なエラーメッセージではなく、「Duplicate key error.」というメッセージが表示されます。

この例は、MySQL のストアドプロシージャに基本的なエラーハンドリングを追加する方法を示しています。エラーハンドラを使用することで、ストアドプロシージャをより堅牢にし、ユーザーにより有益なエラーメッセージを提供することができます。

SIGNAL を使用してカスタムエラーを発生させる

このステップでは、SIGNAL 文を使用して MySQL でカスタムエラーを発生させる方法を学びます。SIGNAL 文を使用すると、ストアドプロシージャ、関数、またはトリガー内でユーザー定義のエラー条件を生成できます。これは、ビジネスルールの適用、入力データの検証、およびユーザーにより有益なエラーメッセージを提供するのに役立ちます。

まず、root ユーザーとして MySQL サーバーに接続します。

mysql -u root -p

前のステップで作成した testdb データベースと products テーブルを引き続き使用します。まだ作成していない場合は、次の SQL 文を実行します。

CREATE DATABASE IF NOT EXISTS testdb;
USE testdb;

CREATE TABLE IF NOT EXISTS products (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    quantity INT
);

次に、製品の数量を更新するストアドプロシージャを作成しましょう。更新する数量が負でないことを確認するチェックを追加します。もし負の場合は、SIGNAL 文を使用してカスタムエラーを発生させます。

DELIMITER //
CREATE PROCEDURE update_quantity(IN p_id INT, IN p_quantity INT)
BEGIN
    IF p_quantity < 0 THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Quantity cannot be negative.';
    END IF;

    UPDATE products SET quantity = p_quantity WHERE id = p_id;
END //
DELIMITER ;

このストアドプロシージャでは、まず入力された数量 p_quantity が 0 未満かどうかを確認します。もし 0 未満の場合は、SIGNAL 文を実行します。

SIGNAL 文は SQLSTATE 値を引数として受け取ります。SQLSTATE は、特定のエラー条件を表す 5 文字の文字列です。この場合、一般的なユーザー定義エラーを表す SQLSTATE 値 '45000' を使用しています。カスタムエラーには '45000' から '45999' の範囲の任意の SQLSTATE 値を使用できます。

SET MESSAGE_TEXT 句を使用すると、ユーザーに返されるカスタムエラーメッセージを指定できます。この場合、エラーメッセージを 'Quantity cannot be negative.' に設定しています。

次に、ストアドプロシージャをテストしましょう。まず、products テーブルに製品を挿入します。

INSERT INTO products (id, name, quantity) VALUES (1, 'Product A', 10);

次に、数量を負の値に更新してみます。

CALL update_quantity(1, -5);

これにより、「Quantity cannot be negative.」というメッセージのカスタムエラーが発生します。次のようなエラーメッセージが表示されるはずです。

ERROR 1644 (45000): Quantity cannot be negative.

数量を正の値に更新しようとすると、更新は成功します。

CALL update_quantity(1, 15);
SELECT * FROM products WHERE id = 1;

これにより、製品 1 の数量が 15 に更新されます。

また、異なるエラー条件に対してカスタムの SQLSTATE 値とエラーメッセージを定義することもできます。これにより、ユーザーにより具体的で有益なエラーメッセージを提供できます。

この例は、MySQL で SIGNAL 文を使用してカスタムエラーを発生させる方法を示しています。SIGNAL 文を使用することで、ビジネスルールを適用し、入力データを検証し、ユーザーにより有益なエラーメッセージを提供できます。

エラーログエントリを確認する

このステップでは、MySQL のエラーログを確認して、データベース操作中に生成されたエラーメッセージを特定し理解します。エラーログは、問題のトラブルシューティング、診断、および MySQL サーバーの健全性を監視するための重要なリソースです。

MySQL のエラーログファイルの場所は、システム構成によって異なります。一般的な場所は /var/log/mysql/error.log です。log_error システム変数をクエリすることで、正確な場所を特定できます。

まず、root ユーザーとして MySQL サーバーに接続します。

mysql -u root -p

次に、エラーログファイルの場所を見つけるために、以下の SQL 文を実行します。

SHOW VARIABLES LIKE 'log_error';

出力には、log_error 変数の現在の値、つまりエラーログファイルへのパスが表示されます。

エラーログファイルの場所がわかったら、テキストエディタまたはコマンドラインツールを使用してその内容を表示できます。LabEx VM 環境では、nano エディタまたは cat コマンドの使用をおすすめします。

たとえば、エラーログファイルが /var/log/mysql/error.log にある場合、以下のコマンドを使用して表示できます。

sudo cat /var/log/mysql/error.log

または、nano を使用してテキストエディタでファイルを開くこともできます。

sudo nano /var/log/mysql/error.log

エラーログファイルには、エラー、警告、および情報メッセージを含むイベントの時系列記録が含まれています。各エントリには通常、タイムスタンプ、メッセージの深刻度レベル、およびイベントの説明が含まれています。

データベース操作で意図的にエラーを引き起こして、いくつかのエラーログエントリを生成しましょう。前のステップで使用した testdb データベースと products テーブルを使用します。

まず、products テーブルに重複する製品を挿入してみます。

INSERT INTO products (id, name, quantity) VALUES (1, 'Product A', 10);

id 列が主キーであるため、これにより重複キーエラーが生成されます。

次に、update_quantity ストアドプロシージャを使用して、製品の数量を負の値に更新してみます。

CALL update_quantity(1, -5);

これにより、前のステップで定義したカスタムエラーが発生します。

これらの操作によって生成されたエラーメッセージを確認するために、エラーログファイルを再度確認しましょう。以下のようなエントリが表示されるはずです。

[timestamp] [ERROR] [MY-013187] [InnoDB] Duplicate entry '1' for key 'products.PRIMARY'
[timestamp] [ERROR] [MY-013187] [Server] Quantity cannot be negative.

最初のエントリは、既存の ID で製品を挿入しようとしたときの重複キーエラーを示しています。2 番目のエントリは、update_quantity ストアドプロシージャによって生成されたカスタムエラーメッセージを示しています。

エラーログエントリを分析することで、エラーの原因に関する貴重な洞察を得ることができ、適切な対策を講じてエラーを解決することができます。たとえば、データ入力エラーを修正したり、ストアドプロシージャを変更したり、データベース構成を調整したりする必要があるかもしれません。

潜在的な問題が深刻な問題にエスカレートする前に特定して対処するために、定期的にエラーログファイルを確認することが重要です。また、エラーログファイルが大きくなりすぎないように、MySQL を設定してエラーログファイルを自動的にローテートすることもできます。

最後に、最初のステップで一般クエリログを有効にした場合は、大量のディスク領域を消費し、サーバーのパフォーマンスに影響を与える可能性があるため、無効にすることを忘れないでください。

mysql -u root -p -e "SET GLOBAL general_log = 'OFF';"

まとめ

この実験では、まず MySQL で一般クエリログを有効にしました。一般クエリログは、デバッグ、監査、およびパフォーマンス分析のために、実行されたすべての SQL 文を記録します。root ユーザーとして MySQL サーバーに接続し、general_logシステム変数をグローバルに有効にしました。

次に、SHOW VARIABLES LIKE 'general_log_file'を使用して一般クエリログファイルのデフォルトの場所を調べ、general_log_fileシステム変数を設定することでログファイルの場所を変更する方法を学びました。この際、MySQL サーバープロセスが指定されたログファイルの場所に書き込み権限を持っていることを確認する重要性を強調しました。