I'm using MySQL 5.5 and I use SHOW ERRORS for detect the error from my stored procedure. But I need to get the message from SHOW ERRORS results to be inserted to my log table. How can I do it,.?
The SHOW ERRORS result is like this:
Level Code Message
========================================================
Error 1146 Table 'mysql.my_table' doesn't exist
If MySQL would have TRY-CATCH clause, then you could catch an error and write information about it.
I'd suggest you to do it in application level - catch the error and write information into log file/table.
I don't think there is a way to do this in 5.5, however if you've updated to 5.6+ you can do the following:
I have used GET DIAGNOSTICS to gain access to the error information and then use these as input for inserts to error logs.
Create structures for example:
CREATE TABLE table_that_exists
(
column_that_exists INT(11) NOT NULL
, PRIMARY KEY (column_that_exists)
);
CREATE TABLE tbl_error_log
(
id INT(11) NOT NULL AUTO_INCREMENT
, err_no INT(4)
, err_msg VARCHAR(50)
, source_proc VARCHAR(50)
, PRIMARY KEY (id)
);
Run query to produce an error & show output of SHOW_ERRORS:
SELECT anything FROM table_that_exists;
SHOW ERRORS;
Example of how to access data for use in other procedures/error management:
GET DIAGNOSTICS CONDITION 1
@P1 = MYSQL_ERRNO, @P2 = MESSAGE_TEXT;
SELECT @P1, @P2;
INSERT INTO tbl_error_log (err_no, err_msg, source_proc)
VALUES (@P1, @P2, 'sp_faulty_procedure');
SELECT * FROM tbl_error_log;
