MySQL 5 Exception Handling

MySQL 5 Exception Handling 1. sample Problem: Log Of Failures example: When the INSERT operation fails, I want to record it in the log file. It is common to show the error handling example. I want to get an error record. When the INSERT operation fails, I want to record the error information in another file, such as the error time and cause. I am particularly interested in insertion because it violates the foreign key Association constraint. sample Problem: Log Of Failures (2) mysql> create table t2 (s1 INT, primary key (s1) engine = innodb; mysql> create table t3 (s1 INT, KEY (s1), foreign key (s1) REFERENCES t2 (s1) engine = innodb; // mysql> insert into t3 VALUES (5 );... ERROR 1216 (23000): Cannot add or update a child row: a foreign keyconstraint fails (the system ERROR message is displayed here, and a foreign key table. We use InnoDB, so the foreign key Association check is enabled. Then, when I insert a value from a non-primary key table to an external key table, the Operation will fail. Of course, the error code 1216 can be quickly found under such conditions. 3. Sample Problem: Log Of Failuresmysql> create table error_log (error_message CHAR (80); the next step is to CREATE a TABLE that stores errors when an insert error occurs. 4. sample Problem: Log Of Errors DECLARE handler_action handler for condition_value [, condition_value]... statement handler_action: CONTINUE | EXIT | UNDO condition_value: mysql_error_code | SQLSTATE [VALUE] sqlstate_value | condition_name | SQLWARNING | not found | sqlexception create procedure p22 (parameter1 INT) begin declare exit handler for 1216 insert into error_log VALUES (CONCAT ('time :', Current_date, '. Foreign Key Reference Failure For Value =', parameter1); insert into t3 VALUES (parameter1); END; above is our program. The first statement declare exit handler is used to handle exceptions. It means that if error 1215 occurs, this program inserts a row in the error record table. EXIT means to EXIT the compound statement after the action is successfully submitted. 5. Sample Problem: Log Of Errors CALL p22 (5) This stored procedure fails to be called, which is normal because the value 5 does not appear in the primary key table. However, no error message is returned because the error processing is included in the process. Nothing is added to table t3, but some information is recorded in table error_log, which tells us that the INSERT into table t3 action fails. Declare handler syntax declares the syntax of exception handling. DECLARE {EXIT | CONTINUE} handler for {error-number | {SQLSTATE error-string} | condition} SQL statement indicates the usage of error handling, that is, a piece of code automatically triggered when a program error occurs. MySQL allows two processors, one of which is EXIT processing, which we just used. The other is what we will demonstrate. The CONTINUE processing is similar to the EXIT processing. The difference is that after it is executed, the original main program continues to run, so this composite statement has no EXIT. 1. declare continue handler example CONTINUE processing example create table t4 (s1 int, primary key (s1); // create procedure p23 () begindeclare continue handlerfor sqlstate '000000' SET @ x2 = 1; SET @ x = 1; insert into t4 VALUES (1); SET @ x = 2; insert into t4 VALUES (1); SET @ x = 3; END; // This is an example of CONTINUE processing in the MySQL reference manual. This example is very good, so I copied it here. Through this example, we can see how the CONTINUE processing works. 2. declare continue handler declares that CONTINUE Exception Handling create table t4 (s1 int, primary key (s1); // create procedure p23 () begindeclare continue handlerfor sqlstate '000000' SET @ x2 = 1; <-- SET @ x = 1; insert into t4 VALUES (1); SET @ x = 2; insert into t4 VALUES (1); SET @ x = 3; END; // this time I will define a handler for the SQLSTATE value. Remember the MySQL error code 1216 we used earlier? As a matter of fact, the 23000SQLSTATE is more commonly used. When a foreign key constraint error or a primary key constraint error occurs, it is called. 3. declare continue handlercreate table t4 (s1 int, primary key (s1); // create procedure p23 () begindeclare continue handlerfor sqlstate '200' SET @ x2 = 1; SET @ x = 1; <-- insert into t4 VALUES (1); SET @ x = 2; insert into t4 VALUES (1); SET @ x = 3; END; // The first statement executed in this stored procedure is "SET @ x = 1 ". 4. declare continue handler exampleCREATE TABLE t4 (s1 int, primary key (s1); // create procedure p23 () begindeclare continue handlerfor sqlstate '200' SET @ x2 = 1; SET @ x = 1; insert into t4 VALUES (1); SET @ x = 2; insert into t4 VALUES (1); <-- SET @ x = 3; END; // value 1 after running is inserted into the primary key table. 5. declare continue handlercreate table t4 (s1 int, primary key (s1); // create procedure p23 () begindeclare continue handlerfor sqlstate '200' SET @ x2 = 1; SET @ x = 1; insert into t4 VALUES (1); SET @ x = 2; <-- insert into t4 VALUES (1); SET @ x = 3; END; // then change the value of @ x to 2. 6. declare continue handler example create table t4 (s1 int, primary key (s1); // create procedure p23 () begin declare continue handler for sqlstate '100' SET @ x2 = 1; SET @ x = 1; insert into t4 VALUES (1); SET @ x = 2; insert into t4 VALUES (1); <-- SET @ x = 3; END; // then, the program tries to insert a value into the primary key table again, but fails because the primary key has a unique limit. 7. declare continue handler example create table t4 (s1 int, primary key (s1); // create procedure p23 () begin declare continue handler for sqlstate '100' SET @ x2 = 1; <-- SET @ x = 1; insert into t4 VALUES (1); SET @ x = 2; insert into t4 VALUES (1); SET @ x = 3; END; // The error handling program is triggered due to insertion failure and error handling starts. The next statement to be executed is the statement for error handling. @ x2 is set to 2. 8. declare continue handler example create table t4 (s1 int, primary key (s1); // create procedure p23 () begin declare continue handler for sqlstate '100' SET @ x2 = 1; SET @ x = 1; insert into t4 VALUES (1); SET @ x = 2; insert into t4 VALUES (1); SET @ x = 3; <-- END; // It does not end here because this is a CONTINUE exception handling. Therefore, after executing the failed insert statement, continue to set @ x to 3. 9. declare continue handler example mysql> CALL p23 () // Query OK, 0 rows affected (0.00 sec) mysql> SELECT @ x, @ x2 // + ------ + | @ x | @ x2 | + ------ + | 3 | 1 | + ------ + 1 row in set (0.00 sec) after running the process, we observe the value of @ x. We are sure that it is 3. Observe the value of @ x2, which is 1. From this point, we can determine that the program runs correctly, which is based entirely on our ideas. You can take some time to adjust the error processor so that the check can be placed at the beginning of the statement segment, rather than at the place where errors may occur, although it seems like the program is messy and it just jumps. However, such code is safe and clear. 1. declare condition create procedure p24 () begin declare 'constraint Violation 'condition for sqlstate '123456'; declare exit handler for 'constraint violation' ROLLBACK; start transaction; insert into t2 VALUES (1); insert into t2 VALUES (1); COMMIT; END; // This is another example of error handling, which was modified on the basis of the preceding statement. In fact, you can give other names of SQLSTATE or error code, and you can use your own name in processing. The following shows how it is implemented: I define table t2 as an InnoDB table, so the insert operations on this table will be ROLLBACK (ROLLBACK) and ROLLBACK (ROLLBACK transaction) it also happens. Because inserting two identical values for the primary key will cause the SQLSTATE 23000 error. Here, SQLSTATE 23000 is a constraint error. 2. declare condition Statement condition create procedure p24 () begin declare 'constraint Violation 'condition for sqlstate '201312'; declare exit handler for 'constraint violation' ROLLBACK; start transaction; insert into t2 VALUES (1); insert into t2 VALUES (1); COMMIT; END; // This constraint error will cause ROLLBACK and SQLSTATE 23000 errors. 3. declare condition mysql> CALL p24 () // Query OK, 0 rows affected (0.28 sec) mysql> SELECT * FROM t2 // Empty set (0.00 sec) we call this stored procedure to see what the results are. From the above results, we can see that table t2 does not insert any records. All transactions are rolled back. This is exactly what we want. 4. declare condition mysql> create procedure p9 ()-> BEGIN-> declare exit handler for not found begin end;-> declare exit handler for sqlexception begin end; -> declare exit handler for sqlwarning begin end;-> END; // Query OK, 0 rows affected (0.00 sec) Here are three pre-declared conditions: not found (row not found), SQLEXCEPTION (error), and SQLWARNING (warning or comment ). Because they are pre-declared, they can be used without declaration conditions. However, if you DECLARE "declare sqlexception condition...", you will receive an error message.