Select specific columns from SHOW ERRORS query

mysql, stored-procedures

Solution

I was looking mysql for a mysql equivalent to the T-SQL @@ERROR and came across your question.

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;

Problem

There are 3 columns (Level, Code, Message) in the output when `SHOW ERRORS` is executed. Is there any way to select one specific column (lets say, Message) instead of all three. The main purpose is to get the error message (3rd column) in a variable for further processing. Edited: The result of the query `SHOW ERRORS` after a erroneous select query `SELECT anything` is like that: ``` +-------+------+-------------------------------------------+ | Level | Code | Message | +-------+------+-------------------------------------------+ | Error | 1054 | Unknown column 'anything' in 'field list' | +-------+------+-------------------------------------------+ ```

Original source