MySQL - Diagnostics Area

---

---

Diagnostics Area

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.

You access either statement or condition information using the GET DIAGNOSTICS statement.

Example

Suppose we have created a table that contains the sales details along with the contact details of the customers as shown below −

CREATE TABLE SALES_DETAILS (
   ID INT,
   ProductName VARCHAR(255) NOT NULL,
   CustomerName VARCHAR(255) NOT NULL,
   DispatchDate date,
   DeliveryTime time,
   Price INT,
   Location VARCHAR(255),
   CustomerAge INT,
   CustomrtPhone BIGINT,
   DispatchAddress VARCHAR(15),
   Email VARCHAR(50)
);

Now, let’s insert a record into the above created table using the INSERT statement as −

insert into SALES_DETAILS values(
   'not_number',
   'Raja',
   ,
   DATE('2019-09-01'),
   TIME('11:00:00'),
   7000,
   'Hyderabad',
   25,
   '9000012345',
   'Hyderabad – Madhapur',
   'pujasharma@gmail.com');
ERROR 1064 (42000): You have an error in your SQL syntax; check 
the manual that corresponds to your MySQL server version for the 
right syntax to use near ',
   DATE('2019-09-01'),
   TIME('11:00:00'),
   7000,
' at line 4
GET DIAGNOSTICS CONDITION 1 @state = RETURNED_SQLSTATE, @msg= MESSAGE_TEXT;

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
42000	You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ', DATE('2019-09-01'), TIME('11:00:00'), 7000, ' at line 4

Example

Following is another example −

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