14.19.3 InnoDB データディクショナリの操作のトラブルシューティング

テーブル定義に関する情報は、.frm ファイルと InnoDB データディクショナリの両方に格納されます。.frm ファイルをあちこちに移動したり、データディクショナリの操作の最中にサーバーがクラッシュしたりすると、これらの情報のソースに整合性がなくなることがあります。

データディクショナリの破損や一貫性の問題によって InnoDB を起動できない場合は、手動のリカバリに関する情報について、セクション14.19.2「InnoDB のリカバリの強制的な実行」を参照してください。

CREATE TABLE での問題

同期がとれていないデータディクショナリの 1 つの現象として、CREATE TABLE ステートメントが失敗することが挙げられます。これが発生した場合は、サーバーのエラーログを調べてください。そのログに、InnoDB 内部データディクショナリの内部にテーブルがすでに存在することが示されている場合は、InnoDB テーブルスペースファイルの内部に、対応する .frm ファイルのない孤立したテーブルがあります。そのエラーメッセージは次のようになります。

InnoDB: Error: table test/parent already exists in InnoDB internal
InnoDB: data dictionary. Have you deleted the .frm file
InnoDB: and not used DROP TABLE? Have you used DROP DATABASE
InnoDB: for InnoDB tables in MySQL version <= 3.23.43?
InnoDB: See the Restrictions section of the InnoDB manual.
InnoDB: You can drop the orphaned table inside InnoDB by
InnoDB: creating an InnoDB table with the same name in another
InnoDB: database and moving the .frm file to the current database.
InnoDB: Then MySQL thinks the table exists, and DROP TABLE will
InnoDB: succeed.

この孤立したテーブルは、エラーメッセージに示されている手順に従うことによって削除できます。それでも DROP TABLE を正常に使用できない場合、その問題の原因は mysql クライアントでの名前補完である可能性があります。この問題を回避するには、--skip-auto-rehash オプションを使用して mysql クライアントを起動し、DROP TABLE を再試行します。(名前補完がオンになっていると、mysql はテーブル名のリストを構築しようとしますが、今説明したような問題が存在した場合は失敗します。)

テーブルを開くときの問題

同期がとれていないデータディクショナリの別の現象として、MySQL から .InnoDB ファイルを開くことができないというエラーが出力されます。

ERROR 1016: Can't open file: 'child2.InnoDB'. (errno: 1)

エラーログに、次のようなメッセージが見つかります。

InnoDB: Cannot find table test/child2 from the internal data dictionary
InnoDB: of InnoDB though the .frm file for the table exists. Maybe you
InnoDB: have deleted and recreated InnoDB data files but have forgotten
InnoDB: to delete the corresponding .frm files of InnoDB tables?

これは、InnoDB の内部に対応するテーブルのない孤立した .frm ファイルが存在することを示します。この孤立した .frm ファイルは、手動で削除することによって削除できます。

孤立した中間テーブル

ALTER TABLE 操作の最中に MySQL がクラッシュした場合は、孤立した中間テーブルが残ったままになることがあります。中間テーブル名は 「#sql-」 で始まります。データディレクトリ内には #sql-*.ibd ファイルが見つかるほか、付随する #sql-*.frm ファイルも存在する可能性があります。中間テーブルはまた、テーブルモニターの出力にも表示され、InnoDB INFORMATION_SCHEMA テーブルでも参照されます。

孤立した中間テーブルを削除するには、#sql-*.ibd ファイルで定義されているテーブルスキーマに一致するテーブル形式ファイル (.frm ファイル) が必要です (カラムとインデックスが同じである必要があります)。クラッシュが ALTER TABLE 操作中のいつ発生したかに応じて、孤立した #sql-*.ibd ファイルには ALTER の前または ALTER のあとのスキーマ定義が存在する可能性があり、付随する #sql-*.frm ファイル (存在する場合) 内のデータも一致する場合と一致しない場合があります。

孤立した中間テーブルを削除するには、次の手順を実行します。

1. #sql-*.ibd ファイルに ALTER の前または ALTER のあとのどちらのスキーマ定義が存在するかを判定します。中間テーブルのカラムとインデックスは、テーブルモニターを使用して、または InnoDB INFORMATION_SCHEMA テーブルをクエリーすることによって表示できます。INNODB_SYS_TABLES は、中間テーブルの TABLE_ID を提供します。これを使用すると、INNODB_SYS_COLUMNS および INNODB_SYS_INDEXES からカラムとインデックスの情報を取得できます。

2. #sql-*.ibd ファイルに ALTER の前または ALTER のあとのどちらのスキーマ定義が存在するかを判定したら、一致する #sql-*.frm ファイルを別のデータベースディレクトリ内に作成します。たとえば、中間テーブルに ALTER のあとのスキーマ定義が存在する場合は、変更されたスキーマ定義に一致する .frm ファイルを作成します。

   mysql> CREATE TABLE tmp LIKE employees.salaries; ALTER TABLE tmp DROP COLUMN to_date;
   Query OK, 0 rows affected (0.02 sec)
         
   Query OK, 0 rows affected (0.06 sec)
   Records: 0  Duplicates: 0  Warnings: 0

3. .frm ファイルを孤立したテーブルが存在するデータベースディレクトリにコピーし、その名前を #sql-*.ibd ファイルの名前に一致するように変更します。

   shell> cp tmp.frm employees/#sql-ib87.frm

4. テーブルの名前にプリフィクス #mysql50# を付け、テーブル名を逆引用符で囲んだ DROP TABLE ステートメントを発行することによって、中間テーブルを削除します。例:

   mysql> DROP TABLE `#mysql50##sql-ib87`;
   Query OK, 0 rows affected (0.01 sec)

   #mysql50# のプリフィクスは、MySQL 5.1 で導入された file name safe encoding を無視するよう MySQL に指示します。テーブル名を逆引用符で囲むことは、「#」などの特殊文字を含むテーブル名に対して SQL ステートメントを実行するために必要です。

5. 残りの #sql-*.frm ファイルが存在する場合は、それを削除します。MySQL が「不明なテーブル」エラーをレポートしますが、これは無視できます。

   mysql> DROP TABLE `#mysql50##sql-36ab_2`;
   ERROR 1051 (42S02): Unknown table 'employees.#mysql50##sql-36ab_2'

テーブルスペースが見つからない問題

innodb_file_per_table が有効になっていると、.frm または .ibd ファイル (あるいはその両方) が見つからない場合に、次のメッセージが生成されることがあります。

InnoDB: in InnoDB data dictionary has tablespace id N,
InnoDB: but tablespace with that id or name does not exist. Have
InnoDB: you deleted or moved .ibd files?
InnoDB: This may also be a table created with CREATE TEMPORARY TABLE
InnoDB: whose .ibd and .frm files MySQL automatically removed, but the
InnoDB: table still exists in the InnoDB internal data dictionary.

これが発生した場合は、問題を解決するために次の手順を試してください。

1. 一致する .frm ファイルをどこかほかのデータベースディレクトリ内に作成し、それを孤立したテーブルが存在するデータベースディレクトリにコピーします。

2. 元のテーブルに対して DROP TABLE を発行します。それにより、テーブルが正常に削除され、InnoDB によって、.ibd ファイルが見つからなかったことを示す警告がエラーログに出力されるはずです。