MySQL improves the buffer for reading abnormal errors (MySQL5.6)

MySQL improves the buffer for reading abnormal errors (MySQL5.6)

What is an error buffer?

In MySQL, the Error Buffer only records the last error. If a new error is generated, the old one will be overwritten. If you want to know what errors are generated, you must follow "show warnings" or "show errors" after each statement that may have errors ". The simplest way is to redirect the output result to the custom log file, so that it can be conveniently viewed later. If you want to view these errors at any time, you must use the APIS provided by MySQL.

Since the emergence of the revolutionary MySQL 5.6 version, the problem has been preliminarily solved. Although it is far from what we think.

MySQL 5.6 provides the get diagnostic statement to get the content of the Error Buffer, and then output the content to variables in different range fields, so that we can flexibly perform subsequent operations.

Next, let's look at an example.

The target table structure is:

1. CREATE TABLE'T_ datetime '(
2. 'Id'Int(11)NOT NULL,
3. 'Log _ time'Timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
4. 'End _ time' datetimeNOT NULL,
5. PRIMARY KEY('Id ')
6. ) ENGINE = InnoDBDEFAULTCHARSET = utf8

The Stored Procedure Code is:

1. DELIMITER $
4. USE 'new _ t' $
7. DROP PROCEDUREIf exists 'SP _ do_update '$
10. CREATEDEFINER = 'root' @ 'localhost'PROCEDURE'SP _ do_update '(
11. INF_idINT,
12. INF_log_timeVARCHAR(255 ),
13. INF_end_timeVARCHAR(255)
14. )
15. BEGIN
16. DECLAREI _con1 TINYINTDEFAULT0;
17. DECLAREI _codeCHAR(5)DEFAULT '123';
18. DECLAREI _msg TEXT;
19. DECLAREI _rowsINT;
20. DECLAREI _con1 CONDITIONFOR1048;-- This error code indicates that the field cannot be NULL.
21. DECLARE CONTINUEHANDLERFORI _con1
22. BEGIN
23. SETI _con1 = 1;
24. Get diagnostics CONDITION 1
25. I _code = returned_sqlstate, I _msg = message_text;
26. END;
27. UPDATET_datetime
28. SETLog_time = IFNULL (f_log_time,NULL),
29. End_time = IFNULL (f_end_time,NULL)
30. WHEREId = f_id;
31. IF I _con1 = 0THEN
32. Get diagnostics I _rows = ROW_COUNT;
33. SET@ I _result = CONCAT ("Update succeeded, affected", I _rows,'.');
34. ELSE
35. SET@ I _result = CONCAT ('Update failed, error code is 1042, related message is', I _msg,'.');
36. ENDIF;
37. SELECT@ I _result;
38. END$
41. DELIMITER;

After the above stored procedure is completed, the error information can be saved to the SESSION variable @ I _result. This facilitates the subsequent output.

1. CALL sp_do_update (1, NOW (), DATE_ADD (NOW (), INTERVAL 1DAY));
2. Result.
3. UpdateSucceeded, affected 1.
6. CALL sp_do_update (1,NULL,NULL);
7. Result.
8. UpdateFailed, error codeIs1042, related messageIs Column 'Log _ time'Cannot beNull.