MySQL - GET DIAGNOSTICS Statement

The diagnostic area holds information about the errors occurred and information about the statements generated them.

This area contains two kinds of information −

  • Statement information − such as the number of rows affected by a statement.

  • Condition information − such as error code for the error occurred while executing the statement and its Error message.

    While executing a particular statement, if multiple errors occur this stores information about all of them and if no error occurs the Condition information section of that particular statement will be empty.

GET DIAGNOSTICS Statement

Using the GET DIAGNOSTICS statement you can access this information. This statement is generally used with in a handler (in a stored program). Using this you can either retrieve either statement or condition information at a time.

Syntax

Following is the syntax of the GET DIAGNOSTICS Statement −

GET [CURRENT | STACKED] DIAGNOSTICS {
   statement_information_item
   [, statement_information_item] ...
   | CONDITION condition_number
   condition_information_item
   [, condition_information_item] ...
}

If you use the CURRENT keyword this statement retrieves the information from the current diagnostics area. If you use STACKED this statement retrieves the information from the stored diagnostics area. By default, information about the current diagnostic area is retrieved.

Example

Let us create a table with name MyPlayers in MySQL database using CREATE statement as shown below −

CREATE TABLE MyPlayers(
   ID INT,
   First_Name VARCHAR(255),
   Last_Name VARCHAR(255), 
   Date_Of_Birth date,
   Place_Of_Birth VARCHAR(255),
   Country VARCHAR(255),
   PRIMARY KEY (ID)
);

Now, we will insert some records in MyPlayers table using INSERT statements −

Insert into MyPlayers values
(1, 'Shikhar', 'Dhawan', DATE('1981-12-05'), 'Delhi', 'India'),
(2, 'Jonathan', 'Trott', DATE('1981-04-22'), 'CapeTown', 'SouthAfrica'),
(3, 'Kumara', 'Sangakkara', DATE('1977-10-27'), 'Matale', 'Srilanka'),
(4, 'Virat', 'Kohli', DATE('1988-11-05'), 'Delhi', 'India');

If verify the contents of the MyPlayers table, you can observe the created records as −

select * from MyPlayers;

Following is the output of the above query −

ID First_Name Last_Name Date_Of_Birth Place_Of_Birth Country
1 Shikhar Dhawan 1981-12-05 Delhi India
2 Jonathan Trott 1981-04-22 CapeTown SouthAfrica
3 Kumara Sangakkara 1977-10-27 Matale Srilanka
4 Virat Kohli 1988-11-05 Delhi India

Now, let us try to insert another row with repeated ID value −

Insert into MyPlayers values
(2, 'Rohit', 'Sharma', DATE('1987-04-30'), 'Nagpur', 'India');
ERROR 1062 (23000): Duplicate entry '2' for key 'myplayers.PRIMARY'

Following query retrieves the state and message of the above generated error −

GET DIAGNOSTICS CONDITION 1 @state = RETURNED_SQLSTATE, @msg= MESSAGE_TEXT;

You can display these variables (retrieved values) using the SELECT statement.

SELECT @state, @msg;

Output

Following is the output of the above query −

@state @msg
23000 Duplicate entry '2' for key 'myplayers.PRIMARY'

Example

Following is another example of this statement −

SELECT * FROM table_that_doesnot_exist;
ERROR 1146 (42S02): Table 'table_that_doesnot_exist' doesn't exist

GET DIAGNOSTICS CONDITION 1 @state = RETURNED_SQLSTATE, @msg= MESSAGE_TEXT;

You can display these variables (retrieved values) using the SELECT statement.

SELECT @state, @msg;

Output

The above query produces the following output −

@state @msg
42S02 Table 'sample.table_that_doesnot_exist' doesn't exist
Advertisements