Exception handling of MySQL5

1. Sample Problem:log of failures Problem example: Fault logging

When an insert fails, I want to be able to record it in a log file. The example of the problem we used to show the error handling is common. I want to get the wrong record. When the insert fails, I want to write down the error information in another file, such as the time of the error, the cause of the error, and so on. The reason I'm particularly interested in inserting is that it will violate the Foreign KEY association constraint

2. Sample Problem:log of Failures (2)

mysql> CREATE TABLE T2

1 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 key

Constraint fails (the system error message is shown here)

I started to create a primary key table and a foreign key table. We are using InnoDB, so the foreign Key Association check is open. Then when I insert a value from a Non-key table to the Foreign key table, the action will fail. Of course this condition can quickly find error number 1216.

3. Sample Problem:log of failures

CREATE TABLE Error_log (error_message

CHAR (80))//

The next step is to create a table that stores errors when you make an error in the Insert action.

4. Sample Problem:log of Errors

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;//

The above is our program. The first statement here declare EXIT handler is used to handle exceptions. This means that if error 1215 occurs, the program will insert a row in the Error record table. Exit means to exit the compound statement when the action is successfully submitted.

5. Sample Problem:log of Errors

Call P22 (5)//

Calling this stored procedure will fail, which is normal, because the 5 value does not appear in the primary key table. However, no error message is returned because error handling is already included in the procedure. Nothing is added to the T3 table, but some information is recorded in the Error_log table, which tells us that the INSERT into table T3 action fails.

Syntax for DECLARE HANDLER syntax declaring exception handling

DECLARE

{EXIT | CONTINUE}

HANDLER for

{Error-number | {SQLSTATE error-string} | Condition}

SQL statement