INSERT IGNOREは何のエラーを握りつぶす子なのかの話

INSERT IGNOREとは何か

公式での説明

INSERT IGNOREを使えば、INSERT実行中の無視可能なエラーは無視してくれる。重複エラーだけ握り潰してくれる認識だったがそうではない様子。他に何のエラーが無視可能なんだろうか https://dev.mysql.com/doc/refman/8.0/ja/insert.html

IGNORE 修飾子を使用すると、INSERT ステートメントの実行中に発生する無視可能なエラーは無視されます。たとえば、IGNORE を使用しない場合は、テーブル内の既存の UNIQUE インデックスまたは PRIMARY KEY 値を複製する行によって重複キーエラーが発生し、このステートメントは中止されます。 IGNORE を指定すると、その行が破棄され、エラーは発生しません。無視されたエラーでは、かわりに警告が生成されます。

https://dev.mysql.com/doc/refman/8.0/ja/sql-mode.html#ignore-effect-on-execution

MySQL のいくつかのステートメントでは、オプションの IGNORE キーワードがサポートされます。このキーワードを使用すると、サーバーは特定のタイプのエラーをダウングレードし、かわりに警告を生成します。複数行のステートメントの場合、IGNORE はステートメントを中断するのではなく、次の行にスキップします。 (無視できないエラーの場合、IGNORE キーワードに関係なくエラーが発生します。)

実験

環境

mysql> select version();
+--------------+
| version()    |
+--------------+
| 8.0.28-debug |
+--------------+
1 row in set (0.01 sec)

mysql> show create table test;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                           |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test  | CREATE TABLE `test` (
  `id` int NOT NULL,
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.09 sec)

mysql> select count(*) from test;
+----------+
| count(*) |
+----------+
|  1048578 |
+----------+
1 row in set (0.91 sec)

mysql> select * from test limit 5;
+----+-------+
| id | name  |
+----+-------+
|  1 | kubo  |
|  2 | kubo2 |
|  3 | kubo3 |
|  4 | kubo4 |
|  5 | kubo5 |
+----+-------+
5 rows in set (0.00 sec)

重複エラーを握りつぶす確認

普通に重複行を挿入するとエラー

mysql> insert  into test values(1,'kubo');
ERROR 1062 (23000): Duplicate entry '1' for key 'test.PRIMARY'

しかし、ignoreを付けるとステートメントはOKを返し、警告として表示される

mysql> insert ignore into test values(1,'kubo');
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> show warnings;
+---------+------+--------------------------------------------+
| Level   | Code | Message                                    |
+---------+------+--------------------------------------------+
| Warning | 1062 | Duplicate entry '1' for key 'test.PRIMARY' |
+---------+------+--------------------------------------------+
1 row in set (0.00 sec)

型が違うぞエラーは握りつぶすのか確認

違う型のものを挿入しようとするとエラーが表示される

mysql> insert into test values ('A','kubo1048580');
ERROR 1366 (HY000): Incorrect integer value: 'A' for column 'id' at row 1

しかし、ignoreを付けるとステートメントはOKを返し、警告として表示される

mysql> insert ignore into test values ('A','kubo1048580');
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> show warnings;
+---------+------+-------------------------------------------------------+
| Level   | Code | Message                                               |
+---------+------+-------------------------------------------------------+
| Warning | 1366 | Incorrect integer value: 'A' for column 'id' at row 1 |
+---------+------+-------------------------------------------------------+
1 row in set (0.00 sec)

ロックタイムアウトエラーをは握りつぶすのか確認

--待つのイヤなのでロック待ち時間を１秒にしとく
mysql> select @@global.innodb_lock_wait_timeout;
+-----------------------------------+
| @@global.innodb_lock_wait_timeout |
+-----------------------------------+
|                                 1 |
+-----------------------------------+
1 row in set (0.00 sec)

通常通りのinsert文では以下のようにロックタイムアウトエラー

mysql1> begin;
Query OK, 0 rows affected (0.00 sec)

mysql1> insert into test values (1048580,'kubo1048580');
Query OK, 1 row affected (0.01 sec)

--別セッションで同じinsert文
mysql2>insert into test values (1048580,'kubo1048580');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

ignoreをつけてもエラーが表示された。これは握りつぶさないエラーのようだ。

mysql1> begin;
Query OK, 0 rows affected (0.00 sec)

mysql1> insert into test values (1048580,'kubo1048580');
Query OK, 1 row affected (0.00 sec)

--別セッションでinsert ignore文
mysql2> insert ignore into test values (1048580,'kubo1048580');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

カラム数違うぞエラーは握りつぶすのか確認

通常ver

mysql> insert into test values ('kubo1048580');
ERROR 1136 (21S01): Column count doesn't match value count at row 1

ignoreをつけてもエラーが表示された。これも握りつぶさないエラーのようだ。

mysql> insert ignore into test values ('kubo1048580');
ERROR 1136 (21S01): Column count doesn't match value count at row 1

そんなテーブル存在しないぞエラーは握りつぶすのか確認

通常ver

mysql> insert into hogehoge values (1048580,'kubo1048580');
ERROR 1146 (42S02): Table 'kubo.hogehoge' doesn't exist

ignoreをつけてもエラーが表示された。これも握りつぶさないエラーのようだ。

mysql> insert ignore into hogehoge values (1048580,'kubo1048580');
ERROR 1146 (42S02): Table 'kubo.hogehoge' doesn't exist

ソース見てみる

このままでは日が暮れると思ったので、ソースを追ってみることにした。

ソース場所

結論多分ここ。

Ignore_error_handlerというクラス。これはInternal_error_handlerという内部エラーハンドラのクラスを継承している。 handle_conditionというメソッドをオーバーライドしている様子。

このクラスでは特定のエラーに対してSL_ERRORからSL_WARNINGにダウングレードしている（つまり警告として扱うようにしている）。無視されるエラーの種類を特定できそう。

/**
  This internal handler implements downgrade from SL_ERROR to SL_WARNING
  for statements which support IGNORE.
*/

class Ignore_error_handler : public Internal_error_handler {
 public:
  bool handle_condition(THD *thd, uint sql_errno, const char *sqlstate,
                        Sql_condition::enum_severity_level *level,
                        const char *msg) override;
};

/**
  This handler is used for the statements which support IGNORE keyword.
  If IGNORE is specified in the statement, this error handler converts
  the given errors codes to warnings.
  These errors occur for each record. With IGNORE, statements are not
  aborted and next row is processed.

*/
bool Ignore_error_handler::handle_condition(
    THD *thd, uint sql_errno, const char *,
    Sql_condition::enum_severity_level *level, const char *) {
  /*
    If a statement is executed with IGNORE keyword then this handler
    gets pushed for the statement. If there is trigger on the table
    which contains statements without IGNORE then this handler should
    not convert the errors within trigger to warnings.
  */
  if (!thd->lex->is_ignore()) return false;
  /*
    Error codes ER_DUP_ENTRY_WITH_KEY_NAME is used while calling my_error
    to get the proper error messages depending on the use case.
    The error code used is ER_DUP_ENTRY to call error functions.

    Same case exists for ER_NO_PARTITION_FOR_GIVEN_VALUE_SILENT which uses
    error code of ER_NO_PARTITION_FOR_GIVEN_VALUE to call error function.

    There error codes are added here to force consistency if these error
    codes are used in any other case in future.
  */
  switch (sql_errno) {
    case ER_SUBQUERY_NO_1_ROW:
    case ER_ROW_IS_REFERENCED_2:
    case ER_NO_REFERENCED_ROW_2:
    case ER_NO_REFERENCED_ROW:
    case ER_ROW_IS_REFERENCED:
    case ER_BAD_NULL_ERROR:
    case ER_DUP_ENTRY:
    case ER_DUP_ENTRY_WITH_KEY_NAME:
    case ER_DUP_KEY:
    case ER_VIEW_CHECK_FAILED:
    case ER_NO_PARTITION_FOR_GIVEN_VALUE:
    case ER_NO_PARTITION_FOR_GIVEN_VALUE_SILENT:
    case ER_ROW_DOES_NOT_MATCH_GIVEN_PARTITION_SET:
    case ER_CHECK_CONSTRAINT_VIOLATED:
      (*level) = Sql_condition::SL_WARNING;
      break;
    default:
      break;
  }
  return false;
}

実際ここにブレークポイントを貼って、エラーが握り潰されるパターンのinsert ignore into test values(1,'kubo');を実行するとちゃんと捕まる

(gdb) b Ignore_error_handler::handle_condition

(gdb) c
Continuing.
[Switching to Thread 0x7fca904f6700 (LWP 367)]

Thread 38 "connection" hit Breakpoint 1, Ignore_error_handler::handle_condition (this=
    0x55a8cd7997ce <pfs_log_error_v1(unsigned int, PSI_error_operation)+275>, thd=0x7fca904f11e0,
    sql_errno=21928, level=0x55a8d18edc70) at /mysql-8.0.28/sql/error_handler.cc:74
74     Sql_condition::enum_severity_level *level, const char *) {
(gdb) bt
#0  Ignore_error_handler::handle_condition (
    this=0x55a8cd7997ce <pfs_log_error_v1(unsigned int, PSI_error_operation)+275>,
    thd=0x7fca904f11e0, sql_errno=21928, level=0x55a8d18edc70)
    at /mysql-8.0.28/sql/error_handler.cc:74
#1  0x000055a8cb62ed03 in THD::handle_condition (this=0x7fca4c0012d0, sql_errno=1062,
    sqlstate=0x55a8cf344586 "23000", level=0x7fca904f1230,
    msg=0x7fca904f1380 "Duplicate entry '1' for key 'test.PRIMARY'")
    at /mysql-8.0.28/sql/sql_class.cc:893
#2  0x000055a8cb62f686 in THD::raise_condition (this=0x7fca4c0012d0, sql_errno=1062,
    sqlstate=0x55a8cf344586 "23000", level=Sql_condition::SL_ERROR,
    msg=0x7fca904f1380 "Duplicate entry '1' for key 'test.PRIMARY'", fatal_error=false)
    at /mysql-8.0.28/sql/sql_class.cc:996
#3  0x000055a8cb550f3f in my_message_sql (error=1062,
    str=0x7fca904f1380 "Duplicate entry '1' for key 'test.PRIMARY'", MyFlags=0)
    at /mysql-8.0.28/sql/mysqld.cc:3721
#4  0x000055a8cce6580d in my_printf_error (error=1062,
    format=0x7fcacbfb8d40 "Duplicate entry '%-.64s' for key '%-.385s'", MyFlags=0)
    at /mysql-8.0.28/mysys/my_error.cc:274
#5  0x000055a8cbaeb03b in print_keydup_error (table=0x7fca4c1513e0, key=0x7fca4c0b0340,
    msg=0x7fcacbfb8d40 "Duplicate entry '%-.64s' for key '%-.385s'", errflag=0, org_table_name=0x0)
    at /mysql-8.0.28/sql/handler.cc:4062
#6  0x000055a8cbaeb0fc in print_keydup_error (table=0x7fca4c1513e0, key=0x7fca4c0b0340, errflag=0,
    org_table_name=0x0) at /mysql-8.0.28/sql/handler.cc:4074
#7  0x000055a8cbafc299 in print_keydup_error (table=0x7fca4c1513e0, key=0x7fca4c0b0340, errflag=0)
--Type <RET> for more, q to quit, c to continue without paging--c
    at /mysql-8.0.28/sql/handler.h:7041
#8  0x000055a8cbaeb4c4 in handler::print_error (this=0x7fca4c1527e0, error=121, errflag=0) at /mysql-8.0.28/sql/handler.cc:4177
#9  0x000055a8cbe81eed in write_record (thd=0x7fca4c0012d0, table=0x7fca4c1513e0, info=0x7fca904f3490, update=0x7fca904f3510) at /mysql-8.0.28/sql/sql_insert.cc:2170
#10 0x000055a8cbe7d46f in Sql_cmd_insert_values::execute_inner (this=0x7fca4c11a788, thd=0x7fca4c0012d0) at /mysql-8.0.28/sql/sql_insert.cc:635
#11 0x000055a8cb7a299a in Sql_cmd_dml::execute (this=0x7fca4c11a788, thd=0x7fca4c0012d0) at /mysql-8.0.28/sql/sql_select.cc:581
#12 0x000055a8cb717611 in mysql_execute_command (thd=0x7fca4c0012d0, first_level=true) at /mysql-8.0.28/sql/sql_parse.cc:3553
#13 0x000055a8cb71cbba in dispatch_sql_command (thd=0x7fca4c0012d0, parser_state=0x7fca904f4bd0) at /mysql-8.0.28/sql/sql_parse.cc:5174
#14 0x000055a8cb71298c in dispatch_command (thd=0x7fca4c0012d0, com_data=0x7fca904f5bc0, command=COM_QUERY) at /mysql-8.0.28/sql/sql_parse.cc:1938
#15 0x000055a8cb710b2b in do_command (thd=0x7fca4c0012d0) at /mysql-8.0.28/sql/sql_parse.cc:1352
#16 0x000055a8cb916375 in handle_connection (arg=0x55a8d2289d90) at /mysql-8.0.28/sql/conn_handler/connection_handler_per_thread.cc:302
#17 0x000055a8cd78d137 in pfs_spawn_thread (arg=0x55a8d3a0d410) at /mysql-8.0.28/storage/perfschema/pfs.cc:2947
#18 0x00007fcad8141609 in start_thread (arg=<optimized out>) at pthread_create.c:477
#19 0x00007fcad788f133 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

IGNOREステートメントが実行されており、エラー番号も確かに重複エラーの番号1062になっていることが確認できる

(gdb) p thd->lex->is_ignore()
$1 = true
(gdb) p sql_errno
$2 = 1062

結局どのエラーが握り潰される？

ここに書いてあるもの。

switch (sql_errno) {
    case ER_SUBQUERY_NO_1_ROW:
    case ER_ROW_IS_REFERENCED_2:
    case ER_NO_REFERENCED_ROW_2:
    case ER_NO_REFERENCED_ROW:
    case ER_ROW_IS_REFERENCED:
    case ER_BAD_NULL_ERROR:
    case ER_DUP_ENTRY:
    case ER_DUP_ENTRY_WITH_KEY_NAME:
    case ER_DUP_KEY:
    case ER_VIEW_CHECK_FAILED:
    case ER_NO_PARTITION_FOR_GIVEN_VALUE:
    case ER_NO_PARTITION_FOR_GIVEN_VALUE_SILENT:
    case ER_ROW_DOES_NOT_MATCH_GIVEN_PARTITION_SET:
    case ER_CHECK_CONSTRAINT_VIOLATED:

ここにエラーリストがあるので、それぞれどんなエラー内容かは確認できる。 https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-reference.html

いくつかエラー内容を除いてみる。

ER_SUBQUERY_NO_1_ROW

Error number: 1242; Symbol: ER_SUBQUERY_NO_1_ROW; SQLSTATE: 21000 Message: Subquery returns more than 1 row

↓こんな感じのクエリを書いて、サブクエリの結果が複数レコードあった時に起こるエラー。

SELECT * FROM t1 
WHERE column1 = (SELECT column1 FROM t2);

https://dev.mysql.com/doc/refman/8.0/ja/subquery-errors.html

ER_ROW_IS_REFERENCED_2

Error number: 1451; Symbol: ER_ROW_IS_REFERENCED_2; SQLSTATE: 23000 Message: Cannot delete or update a parent row: a foreign key constraint fails (%s) InnoDB reports this error when you try to delete a parent row that has children, and a foreign key constraint fails. Delete the children first.

親子関係のあるテーブルに対して親テーブルの行を先に消そうとした時に起こる外部キー制約に関するエラー。上記にも書いている通り、子どもの行を先に消すんやでと言っている

ER_NO_REFERENCED_ROW_2

Error number: 1452; Symbol: ER_NO_REFERENCED_ROW_2; SQLSTATE: 23000 Message: Cannot add or update a child row: a foreign key constraint fails (%s) InnoDB reports this error when you try to add a row but there is no parent row, and a foreign key constraint fails. Add the parent row first.

親子関係のあるテーブルに対して親レコードのない子レコードを追加しようとした時に起こる外部キー制約に関するエラー。上記にも書いている通り、親の行を先に追加するんやでと言っている

ER_NO_REFERENCED_ROW

Error number: 1216; Symbol: ER_NO_REFERENCED_ROW; SQLSTATE: 23000 Message: Cannot add or update a child row: a foreign key constraint fails InnoDB reports this error when you try to add a row but there is no parent row, and a foreign key constraint fails. Add the parent row first.

ER_NO_REFERENCED_ROW_2とエラー条件は同じ。ただしこのエラーでは対象の親レコードの情報はメッセージに記載されない（テーブルの参照権限がない場合はこちらのエラーになる） https://dev.mysql.com/doc/refman/8.0/ja/create-table-foreign-keys.html

ER_ROW_IS_REFERENCED

Error number: 1217; Symbol: ER_ROW_IS_REFERENCED; SQLSTATE: 23000 Message: Cannot delete or update a parent row: a foreign key constraint fails InnoDB reports this error when you try to delete a parent row that has children, and a foreign key constraint fails. Delete the children first.

ER_ROW_IS_REFERENCED_2とエラー条件は同じ。ただしこのエラーでは対象の親レコードの情報はメッセージに記載されない（テーブルの参照権限がない場合はこちらのエラーになる）

ER_BAD_NULL_ERROR

Error number: 1048; Symbol: ER_BAD_NULL_ERROR; SQLSTATE: 23000 Message: Column '%s' cannot be null

NOT NULL 制約のあるカラムに対して NULL を挿入しようとした時に起こるエラー

ER_DUP_ENTRY

Error number: 1062; Symbol: ER_DUP_ENTRY; SQLSTATE: 23000 Message: Duplicate entry '%s' for key %d The message returned with this error uses the format string for ER_DUP_ENTRY_WITH_KEY_NAME.

ユニークキーで重複している行を挿入しようとした時に起こるエラー。 ER_DUP_ENTRY_WITH_KEY_NAMEやER_DUP_KEYとの違いはまだよく分からない。

ER_VIEW_CHECK_FAILED

Error number: 1369; Symbol: ER_VIEW_CHECK_FAILED; SQLSTATE: HY000 Message: CHECK OPTION failed '%s.%s'

チェック制約のあるカラムに対して指定された条件を満たさない行を挿入/更新しようとした時に起こるエラー

備考

ちゃんと探したらDocにIGNOREで無視するエラーリストの記載があった。。

とはいえ、ソースコードで見つけたものと若干乖離はある。ちなみに5.7のDocも全く同じ記述。

https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html

The IGNORE keyword applies to the following ignorable errors: ER_BAD_NULL_ERROR ER_DUP_ENTRY ER_DUP_ENTRY_WITH_KEY_NAME ER_DUP_KEY ER_NO_PARTITION_FOR_GIVEN_VALUE ER_NO_PARTITION_FOR_GIVEN_VALUE_SILENT ER_NO_REFERENCED_ROW_2 ER_ROW_DOES_NOT_MATCH_GIVEN_PARTITION_SET ER_ROW_IS_REFERENCED_2 ER_SUBQUERY_NO_1_ROW ER_VIEW_CHECK_FAILED

amamanamam

データベースと仲良くなりたいです