Author: Yang Taotao

background

Today's article comes from a question asked by today's customers.

Problem: I'm migrating from Oracle to MySQL, and the data has been converted to a simple INSERT statement. Because there are many statements, I don't know how to locate the wrong statement every time I import it. If there are fewer INSERT statements, I can read them by hand, but there are many INSERT statements. How can I locate which statements are wrong and correct them? You can't always correct every time you encounter a mistake. Do you continue to correct it when you run it again? Is there a simpler way?

In fact, MySQL itself has a fault diagnosis area, if you can make good use of it, you will get twice the result with half the effort. Let me briefly describe how to use the error diagnosis area.

For example, the table structure I want to insert is n3, and the log table for error_log is as follows:

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

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

Assuming the inserted statement, for demonstration purposes, I have just written eight statements here.

mysql
-- statements body.
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 has a lot of error code, but it can be grouped into three categories as a whole:

• The sqlwarning SQLSTATE code starts at'01'
• not found SQLSTATE code starts at'02'
• The sqlexception SQLSTATE code starts all error codes that are not'00','01','02'.

For simplicity and convenience, we write these codes into stored procedures. The following is an example stored procedure.

mysql
-- stored routines body.
drop procedure if exists sp_insert_simple;
delimiter ||
create procedure sp_insert_simple()
l1:begin
  DECLARE i,j TINYINT DEFAULT 1;   -- loop counter.
  DECLARE v_errcount,v_errno INT DEFAULT 0; -- error count and error number.
  DECLARE v_msg TEXT; -- error details.
  declare v_sql json; -- store statements list.
  declare v_sql_keys varchar(100); -- array index.
  declare v_sql_length int unsigned; -- array length.

  -- Handler declare.
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION,SQLWARNING,NOT FOUND  -- exception in mysql routines.
  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;
      -- record error messages into table.
      INSERT INTO error_log(sqltext,error_no,error_message) VALUES (@sqltext, v_errno,v_msg);
      SET j = j + 1;
    END WHILE;
  end;
  -- sample statements array.
  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;
  -- invoke procedure.
  -- call sp_insert_simple;
end;
||
delimiter ;

Let's call this stored procedure to see the result.

mysql
[(none)]>use ytt
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A    
Database changed

[ytt]>call sp_insert_simple;
Query OK, 0 rows affected (0.05 sec)

Table N3.

mysql
[ytt]>select  * from n3;
+-----+------+
| id  | id2  |
+-----+------+
| 100 |    0 |
| 200 |    0 |
| 500 |    0 |
+-----+------+
3 rows in set (0.00 sec)

The error log records all the wrong statements.

mysql
[ytt]>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                 |
+--------------------------------------------+----------+-------------------------------------------------------------+
5 rows in set (0.00 sec)

In fact, if we use Python or PHP and other external languages, this problem will be simpler, the idea is similar.