Using MySQL Stacked Diagnostics to Locate Faulty INSERT Statements

Background: a client asked how to locate the exact INSERT statements that fail when migrating a large amount of data from Oracle to MySQL, because manually checking each statement is impractical.

MySQL provides a built‑in error‑diagnostics area that can be leveraged to automate this task.

First, create the target table n3 and an error‑log table error_log :

-- tables definition.
create table n3 (id int not null, id2 int generated always as ((mod(id,10))));
Query OK, 0 rows affected (0.04 sec)

create table error_log (sqltext text, error_no int unsigned, error_message text);
Query OK, 0 rows affected (0.04 sec)

Sample INSERT statements (only eight are shown for demonstration):

set @a1 = "INSERT INTO n3 (id) VALUES(100)";
set @a2 = "INSERT INTO n3 (id) VALUES('test')";
set @a3 = "INSERT INTO n3 (id) VALUES('test123')";
set @a4 = "INSERT INTO n3 (id) VALUES('123test')";
set @a5 = "INSERT INTO n3 (id) VALUES(200)";
set @a6 = "INSERT INTO n3 (id) VALUES(500)";
set @a7 = "INSERT INTO n3 (id) VALUES(null)";
set @a8 = "INSERT INTO n3 (id) VALUES(10000000000000)";

MySQL error codes fall into three categories: SQLWARNING (SQLSTATE starting with ‘01’), NOT FOUND (SQLSTATE starting with ‘02’), and SQLEXCEPTION (all other codes).

The following stored procedure sp_insert_simple loops through the statements, executes each, and uses GET STACKED DIAGNOSTICS to capture any errors, inserting them into error_log :

drop procedure if exists sp_insert_simple;
delimiter ||
create procedure sp_insert_simple()
l1:begin
  DECLARE i,j TINYINT DEFAULT 1;
  DECLARE v_errcount,v_errno INT DEFAULT 0;
  DECLARE v_msg TEXT;
  declare v_sql json;
  declare v_sql_keys varchar(100);
  declare v_sql_length int unsigned;

  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION,SQLWARNING,NOT FOUND
  l2:BEGIN
    get stacked diagnostics v_errcount = number;
    set j = 1;
    WHILE j <= v_errcount DO
      GET stacked DIAGNOSTICS CONDITION j v_errno = MYSQL_ERRNO, v_msg = MESSAGE_TEXT;
      INSERT INTO error_log(sqltext,error_no,error_message) VALUES (@sqltext, v_errno, v_msg);
      SET j = j + 1;
    END WHILE;
  END;

  set v_sql = '{
    "a1": "INSERT INTO n3 (id) VALUES(100)",
    "a2": "INSERT INTO n3 (id) VALUES(''test'')",
    "a3": "INSERT INTO n3 (id) VALUES(''test123'')",
    "a4": "INSERT INTO n3 (id) VALUES(''123test'')",
    "a5": "INSERT INTO n3 (id) VALUES(200)",
    "a6": "INSERT INTO n3 (id) VALUES(500)",
    "a7": "INSERT INTO n3 (id) VALUES(null)",
    "a8": "INSERT INTO n3 (id) VALUES(10000000000000)"
  }';
  set i = 1;
  set v_sql_length = json_length(v_sql);
  while i <= v_sql_length do
    set v_sql_keys = concat('$.a',i);
    set @sqltext = replace(json_extract(v_sql,v_sql_keys),'"','');
    prepare s1 from @sqltext;
    execute s1;
    set i = i + 1;
  end while;
  drop prepare s1;
end||
delimiter ;

Calling the procedure inserts the valid rows (ids 100, 200, 500) into n3 and records the failing statements in error_log :

select * from n3;
+-----+------+
| id  | id2 |
+-----+------+
| 100 | 0    |
| 200 | 0    |
| 500 | 0    |
+-----+------+

select * from error_log;
+--------------------------------------------+----------+-------------------------------------------------------------+
| sqltext                                    | error_no | error_message                                               |
+--------------------------------------------+----------+-------------------------------------------------------------+
| INSERT INTO n3 (id) VALUES('test')         | 1366     | Incorrect integer value: 'test' for column 'id' at row 1   |
| INSERT INTO n3 (id) VALUES('test123')      | 1366     | Incorrect integer value: 'test123' for column 'id' at row 1|
| INSERT INTO n3 (id) VALUES('123test')      | 1265     | Data truncated for column 'id' at row 1                     |
| INSERT INTO n3 (id) VALUES(null)           | 1048     | Column 'id' cannot be null                                  |
| INSERT INTO n3 (id) VALUES(10000000000000) | 1264     | Out of range value for column 'id' at row 1                |
+--------------------------------------------+----------+-------------------------------------------------------------+

The same logic can be implemented in external languages such as Python or PHP, but using MySQL’s own diagnostics makes the solution concise and self‑contained.