Troubleshooting of MySQL 5 (1)

1. Sample Problem: Log Of Failures Problem example: Fault Record
When INSERT fails, I want to record it in the log file to demonstrate 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 constraints.
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 displayed here)
I started to create a primary key table 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 Failures
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
Create procedure p22 (parameter1 INT)
BEGIN
Declare exit handler for 1216
Insert into error_log VALUES
(CONCAT ('time: ', current_date,
'. Foreign Key Reference Failure
Value = ', parameter1 ));
Insert into t3 VALUES (parameter1 );
END ;//
The 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 )//
Calling this stored procedure will fail, 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 statement syntax for Exception Handling
DECLARE
{EXIT | CONTINUE}
HANDLER
{Error-number | {SQLSTATE error-string} | condition}
SQL statement
The above is the usage of error handling, that is, a piece of code automatically triggered when a program fails. 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 ()
BEGIN
DECLARE CONTINUE HANDLER
For 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 ;//
This is an example of CONTINUE processing in 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 CONTINUE Exception Handling
Create table t4 (s1 int, primary key (s1 ));//
Create procedure p23 ()
BEGIN
DECLARE CONTINUE HANDLER
For 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 ;//
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 HANDLER
Create table t4 (s1 int, primary key (s1 ));//
Create procedure p23 ()
BEGIN
DECLARE CONTINUE HANDLER
For 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 example
Create table t4 (s1 int, primary key (s1 ));//
Create procedure p23 ()
BEGIN
DECLARE CONTINUE HANDLER
For 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 HANDLER
Create table t4 (s1 int, primary key (s1 ));//
Create procedure p23 ()
BEGIN
DECLARE CONTINUE HANDLER
For 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 the value of @ x is changed to 2.