GET DIAGNOSTICS
I know of seven DBMSs that support GET DIAGNOSTICS: DB2, Oracle Rdb, MySQL, MariaDB, PostgreSQL, Teradata, Mimer.
I regret that its clauses don’t resemble SELECT’s clauses and that the result isn’t a table, but it’s always good to see standard compliance.
Now GET DIAGNOSTICS is getting better due to two new features, one in MySQL and the other in MariaDB.
STACKED
The problem: suppose a condition (an error or warning) happens. Execution goes to a condition handler (a series of statements that are preceded by DECLARE … CONDITION HANDLER FOR condition-value). Now, within those statements, how do we know what was the condition that caused the execution to go to the handler?
For example:
CREATE PROCEDURE p ()
BEGIN
DECLARE CONTINUE HANDLER FOR SQLWARNING
BEGIN
statement-1;
statement-2;
statement-3;
END;
SIGNAL SQLSTATE '01000';
END
Well, the answer looks easy: if you say GET DIAGNOSTICS before statement-1, you’ll get ‘01000’. But there are no guarantees after that, so you’d have to store the results of GET DIAGNOSTICS somewhere, in case you need to see them later.
The solution: GET STACKED DIAGNOSTICS, a new feature in MySQL 5.7. There are now two diagnostics areas: diagnostics area #1 = the “current” one, and diagnostics area #2 = the “stacked” one. The stacked diagnostics area always has the results as they were upon entry. Here’s an illustration:
delimiter //
CREATE PROCEDURE p ()
BEGIN
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN
SIGNAL SQLSTATE '01000';
GET CURRENT DIAGNOSTICS CONDITION 1 @current = RETURNED_SQLSTATE;
GET STACKED DIAGNOSTICS CONDITION 1 @stacked = RETURNED_SQLSTATE;
END;
SIGNAL SQLSTATE '03000';
END//
CALL p()//
SELECT @current, @stacked//
The result: @current = ‘01000’ but @stacked =’03000′. There’s been a known bug since February, but that’s okay in an unreleased version.
Multiple warnings
The problem: if there are two warnings, you want to see both of them with GET DIAGNOSTICS. This is especially desirable because SHOW WARNINGS has no trouble returning a set of warnings, and it would be great if we could eliminate SHOW WARNINGS usage (for reasons unrelated to this problem). However, we don’t know in advance that there will be two warnings, and we don’t have a direct way to check. If we put the check for warnings in a procedure, then that works with MariaDB but requires a privilege. If we ask for conditions that don’t exist, then a curious error/warning hybrid happens, which is workaroundable but confusing.
The solution: dynamic compound statements, a new feature in MariaDB 10.1. I mentioned it last week but didn’t think: hmm, if there’s no reason that the diagnostics area would be cleared by a simple comparison. So can I put GET DIAGNOSTICS statements inside an IF … THEN … ELSE so that I get zero, or one, or two results just by typing in appropriately on the mysql client?
I’ll start by creating a table and doing an INSERT, with default SQL mode, in MariaDB only, will cause two “Division by 0” warnings. Then I’ll see what the new feature does.
DELIMITER //
CREATE TABLE test (s1 INT)//
INSERT INTO test VALUES ('a'),('a')//
GET DIAGNOSTICS @condition_count = number//
IF @condition_count = 1 THEN
GET DIAGNOSTICS CONDITION 1 @message_1 = message_text;
SELECT @message_1;
ELSEIF @condition_count = 2 THEN
GET DIAGNOSTICS CONDITION 1 @message_1 = message_text;
GET DIAGNOSTICS CONDITION 2 @message_2 = message_text;
SELECT @message_1, @message_2;
END IF//
The result: both warnings are displayed. Whoopee.
By the way
The worklog task for GET DIAGNOSTICS was WL#2111 Stored Procedures: Implement GET DIAGNOSTICS. Since it got implemented long ago, it’s obsolete. But if you’re interested “what happens if GET DIAGNOSTICS itself causes an error?” and the reasons behind our decisions at that time, go over there and click the high level architecture box.
