Transcript SQL error diagnostics, Get Diagnostics and well designed

DBTech VET / DBTechNet
SQL Transactions
SQL error diagnostics, Get Diagnostics
and well designed Transactions
November 2014
Christos Skourlas
www.dbtechnet.org
Contents

Transaction perspectives of: user, client-server, and database

Transaction Logic: Problems related to. Simple examples

SQL error diagnostics in the MySQL/InnoDB environment

GET DIAGNOSTICS: Examples of using

Well designed transaction: Some best practices of

The DBTechNet virtual machine (Debian Linux)
Transactions, need for


Today, the society infrastructure, business, and the every day
life of citizens all depend on ICT and OLTP database
application software utilizing services for reliable data storage
and retrieval
Improper access to database services results into erroneous
and/or missing data:
- Missing orders, shipments, payments, ..
- Double-bookings, double-invoicing, ..
- Delays, erroneous information, ..
- etc.
Transaction, as defined in textbooks



A sequence of client operations set to comprise a
logical unit of work and execute in an 'all or nothing'
fashion
As far as the state at which it leaves the database,
a transaction appears to execute in isolation w.r.t.
transactions of other clients that execute concurrently
Upon recovering from a system crash, the transaction
leaves the database in a consistent state, i.e. either as it
was prior to the starting of its execution, or as it is
meant to become after the transaction executes to its
entirety
DB application architecture: conceptual level
Martti Laiho
Retry wrapper and some best practices
If a transaction fails due to a concurrency conflict, it should,
in many cases, have a retry wrapper in the application code with a
limit of some 10 retries.
If the transaction is dependent on the database content retrieved in
some previous SQL transaction of the same user transaction, and some
concurrent transactions have changed that content in the database and
the current transaction therefore fails to update the content, then this
transaction should not have a retry wrapper, but the control should be
returned to the user for possible restart of the whole user transaction.
A new database connection needs to be opened for the retry of the
SQL transaction in case the connection was lost due to some
network problems.
User perspective
Business rules
 Requirements specifications
 Use cases

User perspective
Business rules
 Requirements specifications
 Use cases

A business or user transaction relates to a data processing task
that need to be executed as a whole. It may involve one or more
use cases and multiple SQL transactions.
User perspective
Business rules
 Requirements specifications
 Use cases

A business or user transaction relates to a data processing task
meant to be executed as a whole. It may involve one or more
use cases and multiple SQL transactions.
It is the application logic that implements user transactions as
well as the programmatic retry actions/wrappers that resolve
concurrency failures
Client-Server perspective
Martti Laiho
Client-Server perspective
Client-initiated DB connection / SQL-session
 DB services used to pass SQL commands as
parameters to API functions/methods
 An SQL command may involve >1 SQL statements
 SQL commands execute in atomic fashion at the DB server
 Logical level client-server dialogue, utilizing the SQL language syntax
 DB server generated exceptions and diagnostics facilitate
user transaction support at the application level

DB application perspective
SQL Transaction
Logical unit of work (LUW)
 Unit of consistency
 Unit of recovery

Well designed transaction





Involves short, concise interaction instances with the
DB server
Does not contain user (intervention) session(s)
Inspects the diagnostic messages that are generated by
the data access services, and it incorporates the logic
for taking appropriate action whenever necessary
Handles exceptions
It is restarted each time it is rolled back due to some
concurrency, or connection failure problem. Furthermore,
it is restarted in a way that avoids livelocks
SQL Transaction
Martti Laiho
Implicit and Explicit start of Transactions
Some DBMS products for example, SQL Server,
MySQL/InnoDB, PostgreSQL and Pyrrho operate
by default in the AUTOCOMMIT mode.
 If the result of every single SQL command is
automatically committed, the ROLLBACK command
is not able to conduct a series of reverse operations.

Examples of Transactions: Autocommit on/off
SQL Transactions in My SQL/InnoDB
-- Autocommit mode
USE TestDB;
CREATE TABLE T (id INT NOT NULL PRIMARY KEY,
s VARCHAR(30));
INSERT INTO T (id, s) VALUES (1, 'first');
ROLLBACK;
SELECT * FROM T ;
-- Turning transactional mode on
SET AUTOCOMMIT=0;
INSERT INTO T (id, s) VALUES (2, 'second');
SELECT * FROM T ;
ROLLBACK;
SELECT * FROM T;
COMMIT;
Transaction Logic: Problems related to a
simple bank example
Transferring of 100 euros from one account to another
CREATE TABLE Accounts (
acctId INTEGER NOT NULL PRIMARY KEY,
balance DECIMAL(11,2) CHECK (balance >= 0.00));
BEGIN TRANSACTION;
UPDATE Accounts SET balance = balance - 100
WHERE acctId = 101;
UPDATE Accounts SET balance = balance + 100
WHERE acctId = 202;
COMMIT;
Developers need to be aware of the way DBMS products
behave, and of how the SQL diagnostics are inspected in the
data access interface the API used.
Example of a solution in mySQL
DELIMITER //
DROP PROCEDURE BankTransfer //
CREATE PROCEDURE BankTransfer (IN fromAcct INT,
IN toAcct INT,
IN amount INT,
OUT msg VARCHAR(100)
)
P1: BEGIN
DECLARE rows INT ;
DECLARE newbalance INT;
SELECT COUNT(*) INTO rows FROM Accounts WHERE acctID = fromAcct;
UPDATE Accounts SET balance = balance - amount WHERE acctID = fromAcct;
SELECT balance INTO newbalance FROM Accounts WHERE acctID = fromAcct;
IF rows = 0 THEN
ROLLBACK;
SET msg = CONCAT('rolled back because of missing account ', fromAcct);
ELSEIF newbalance < 0 THEN
ROLLBACK;
SET msg = CONCAT('rolled back because of negative balance of account ', fromAcct);
ELSE
SELECT COUNT(*) INTO rows FROM Accounts WHERE acctID = toAcct;
UPDATE Accounts SET balance = balance + amount WHERE acctID = toAcct;
IF rows = 0 THEN
ROLLBACK;
SET msg = CONCAT('rolled back because of missing account ', toAcct);
ELSE
COMMIT;
SET msg = 'committed';
END IF;
END IF;
END P1 //
DELIMITER ;
Testing
SET AUTOCOMMIT=0;
SET @out = ' ';
CALL BankTransfer (101, 202, 100, @out);
SELECT @OUT;
Select * from accounts;
COMMIT;
Testing
SET autocommit=0;
SET @out = ' ';
CALL BankTransfer (100, 201, 100, @out);
SELECT @OUT;
Select * from accounts;
commit;
Testing
SET AUTOCOMMIT=0;
SET @out = ' ';
CALL BankTransfer (101, 201, 1500, @out);
SELECT @OUT;
Select * from accounts;
COMMIT;
Diagnosing the SQL errors
• SQL standards have defined various diagnostic
indicators some of which are implemented in most
DBMS products, and some are not, while DBMS
products have implemented indicators or exception
handling methods of their own.
• Procedural extensions of SQL dialects are
different, especially in terms of error handling.
SQL standards and SQL errors
• ISO SQL-89 standard defined the integer typed
indicator SQLCODE
• ISO SQL-92 standard, the SQLCODE was
deprecated and the new indicator SQLSTATE was
introduced
• SQLCODE was left out from ISO SQL:1999
standard, but most DBMS products still support it
addition to the SQLSTATE indicator
Error handling in Oracle PL/SQL language
BEGIN
<processing>
EXCEPTION
WHEN <exception name> THEN
<exception handling>;
...
WHEN OTHERS THEN
err_code := sqlcode;
err_text := sqlerrm;
<exception handling>;
END;
Example in Oracle
-- Transaction logic depending on SQLCODE in PL/SQL!
SET SERVEROUTPUT ON;
-- a PL/SQL procedure script declaring local variables
-- DBMS_OUTPUT package
DECLARE
v_result NUMBER;
BEGIN
SELECT (1/0) INTO v_result FROM DUAL;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('** SQLCODE= ' || SQLCODE
|| ' - ' || SQLERRM);
END;
/
Diagnostics: SQLcode, SQLSTATE
Integer:
ISO SQL-89 SQLcode:
100 No data
0 successful execution
< 0 errors
String of 5 characters:
ISO SQL-92 SQLSTATE:
Successful execution
Warning
No data
...
Transaction rollback
class
0 0
0 1
0 2
subclass
0 0 0
n n n
0 0 0
0 0 0
0 0 1
Serialization failure
0 0 2
Integrity constraint violation
0 0 3
Statement completion unknown
0 0 4
Triggred action exception
etc - lots of standardized and implementation dependent codes
ISO SQL:1999 Get Diagnostics
Martti Laiho
…
4 0
List of diagnostic items, including SQLSTATE and
number of rows. Only few implementations this far
The ISO SQL diagnostic indicators SQLCODE
and SQLSTATE
The diagnostic indicators are available
in the SQL language’s procedural extension
for stored procedures:
<SQL statement>
IF (SQLSTATE <> '00000') THEN
<error handling>
END IF;
Get Diagnostics
To provide better diagnostic information to client application on
what has occurred on the server side, X/Open group has
extended the SQL language by GET DIAGNOSTICS statement
which can used to get more detailed information items and can
be repeated to browse through the diagnostics records reporting
multiple errors or warnings.
This has also been extended in ISO SQL standards
since SQL:1999, but only parts of the items have been
implemented in DBMS products, for example DB2,
Mimer, and MySQL 5.6, 5.7
The Diagnostics Area
The diagnostics area contains statement and condition
information items related to a specific SQL statement.
In the case of mySQL each SQL statement clears the
diagnostics area and then writes a warning, the produced
diagnostic information, to the diagnostics area (DA).
The SET statement and the GET DIAGNOSTICS do not write to
the DA except the cases that they produce some warning.
Even in this case the GET DIAGNOSTICS statement does
not clear the DA before to write.
We can see the errors using the SHOW WARNINGS statement.
Population of the content of the Diagnostics Area
Example of extracting statement information
using GET DIAGNOSTICS
-- insert statement
INSERT INTO accounts VALUES (101, 1000), (201, 2500);
-- inspect the diagnostic errors
GET DIAGNOSTICS @cno = NUMBER, @cn1 = ROW_COUNT;
SELECT @cno, @cn1;
Population of the content of the Diagnostics Area
Example of extracting condition information
using GET DIAGNOSTICS
-- Try to drop a non-existing table
Drop table new;
GET DIAGNOSTICS condition @cno
@p1 = MESSAGE_TEXT,
@p2 = RETURNED_SQLSTATE,
@p3 = MYSQL_ERRNO,
@p4 = SCHEMA_NAME,
@p5 = TABLE_NAME;
SELECT @p1, @p2, @p3, @p4, @p5;
Population of the content of the Diagnostics Area
Example of extracting condition information
using GET DIAGNOSTICS – wrong condition number
-- Try to drop a non-existing table
Drop table new;
GET DIAGNOSTICS condition 1
@p1 = MESSAGE_TEXT,
@p2 = RETURNED_SQLSTATE,
@p3 = MYSQL_ERRNO,
@p4 = SCHEMA_NAME,
@p5 = TABLE_NAME;
SELECT @p1, @p2, @p3, @p4, @p5;
Show warnings - Example
-- Initialize database and table accounts
DROP DATABASE IF EXISTS bank;
CREATE DATABASE bank;
USE bank;
CREATE TABLE accounts (acctID int, balance int);
mysql> DROP TABLE IF EXISTS bank.new;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> SHOW WARNINGS;
+-------+------+------------------------------------+
| Level | Code | Message
|
+-------+------+------------------------------------+
| Note | 1051 | Unknown table 'bank.new'
|
+-------+------+------------------------------------+
1 row in set (0.00 sec)
mysql> SET @y = 10;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW WARNINGS;
+-------+------+------------------------------------+
| Level | Code | Message
|
+-------+------+------------------------------------+
| Note | 1051 | Unknown table 'bank.new'
|
+-------+------+------------------------------------+
1 row in set (0.00 sec)
mysql> SET @y = @@qwerty;
ERROR 1193 (HY000): Unknown system variable ‘qwerty'
mysql> SHOW WARNINGS;
+-------+------+------------------------------------------+
| Level | Code | Message
|
+-------+------+------------------------------------------+
| Error | 1193 | Unknown system variable ‘qwerty' |
+-------+------+------------------------------------------+
1 row in set (0.00 sec)
Get Diagnostics
The GET DIAGNOSTICS statement writes a warning
to the diagnostics area:
mysql> GET DIAGNOSTICS CONDITION 2 @p = MESSAGE_TEXT;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> SHOW WARNINGS;
+-------+------+------------------------------------------+
| Level | Code | Message
|
+-------+------+------------------------------------------+
| Error | 1193 | Unknown system variable ‘qwerty‘ |
| Error | 1753 | Invalid condition number
|
+-------+------+-------------------------------------------+
2 rows in set (0.00 sec)
In this case we have two conditions in the diagnostics area.
Using GET DIagnostics in Mysql 5.6, 5.7
Presenting the idea of reading the diagnostic items




GET DIAGNOSTICS @number = NUMBER,
@rowcount = ROW_COUNT;
GET DIAGNOSTICS CONDITION @number
@sqlstate = RETURNED_SQLSTATE,
@sqlcode = MYSQL_ERRNO;
SELECT @number, @rowcount;
SELECT @sqlstate, @sqlcode, @rowcount;
Get Diagnostics in Mysql 5.6, 5.7
The GET DIAGNOSTICS statement could be used by the
developer to inspect the diagnostic errors issued by the server
after the execution of any SQL statements.
In the case of the MySQL product, it is permitted to use the
GET DIAGNOSTICS statement:
- In the exception handler context, as the SQL standard
-Specifies
-
- Outside the handler context, which is a MySQL extension
Using GET DIAGNOSTICS in Mysql 5.6, 5.7 Example
Use bank;
Show tables;
Drop table new; -- not existing table
GET DIAGNOSTICS @rowcount = ROW_COUNT;
GET DIAGNOSTICS CONDITION 1
@code = RETURNED_SQLSTATE, @msg = MESSAGE_TEXT;
SELECT @code, @msg;
Example
GET DIAGNOSTICS syntax
(http://dev.mysql.com/doc/refman/5.7/en/get-diagnostics.html)
GET [CURRENT | STACKED] DIAGNOSTICS
{
statement_information_item
[, statement_information_item] ...
| CONDITION condition_number
condition_information_item
[, condition_information_item] ...
}
statement_information_item:
target = statement_information_item_name
condition_information_item:
target = condition_information_item_name
statement_information_item_name:
NUMBER
| ROW_COUNT
condition_information_item_name:
CLASS_ORIGIN | SUBCLASS_ORIGIN | RETURNED_SQLSTATE | MESSAGE_TEXT |
MYSQL_ERRNO | CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME |
CATALOG_NAME | SCHEMA_NAME | TABLE_NAME | COLUMN_NAME | CURSOR_NAME
condition_number, target:
GET DIAGNOSTICS syntax
The GET DIAGNOSTICS syntax includes:
- Statement information items,
NUMBER (the number of conditions that occurred) or
ROW_COUNT (the affected-rows count)
-Condition information items
e.g. RETURNED_SQLSTATE, MESSAGE_TEXT,
MYSQL_ERRNO, SCHEMA_NAME, TABLE_NAME,
COLUMN_NAME
Attention! MySQL does not populate all condition items recognized by
the GET DIAGNOSTICS statement.
Condition items - Example
-- table new does not exist
Drop table new;
get diagnostics condition 1
@p1 = MESSAGE_TEXT,
@p2 = RETURNED_SQLSTATE,
@p3 = MYSQL_ERRNO,
@p4 = SCHEMA_NAME,
@p5 = TABLE_NAME;
SELECT @p1, @p2, @p3, @p4, @p5;
Example
Note
The same GET DIAGNOSTICS statement cannot obtain statement
information and condition information, both, in the same statement.
Therefore:
1) The following GET DIAGNOSTICS statement obtains
ONLY statement information items and put them into variables:
GET DIAGNOSTICS @p1 = NUMBER, @p2 = ROW_COUNT;
2) The following GET DIAGNOSTICS statement obtains
ONLY condition information items (, by specifying the condition
number) and put the condition items into variables:
GET DIAGNOSTICS CONDITION 1
@p3 = RETURNED_SQLSTATE, @p4 = MESSAGE_TEXT;
Using GET DIAGNOSTICS as a client - inspect
the diagnostic errors - Example
-- Initialize table accounts
DROP DATABASE IF EXISTS bank;
CREATE DATABASE bank;
USE bank;
CREATE TABLE accounts (acctID int, balance int);
INSERT INTO accounts VALUES (101, 1000), (201, 2500);
SELECT * FROM accounts;
Simple examples of using GD as a client
-- inspect the diagnostic errors
GET DIAGNOSTICS @cno = NUMBER;
GET DIAGNOSTICS CONDITION @cno
@errno = MYSQL_ERRNO;
SELECT @CNO;
Simple examples of using GD as a client
-- Wrong insert statement
INSERT INTO ACCOUNTS VALUES(303);
-- inspect the diagnostic errors
GET DIAGNOSTICS @cno = NUMBER;
GET DIAGNOSTICS CONDITION @cno
@errno = MYSQL_ERRNO;
SELECT @CNO; -- See the content of the NUMBER item
Simple examples of using GD as a client
Using GET DIAGNOSTICS in exception handler example
- BankTransfer using Condition Handlers.
- Using a GET DIAGNOSTICS statement and an exception
handler to inspect the errors’ outcome of an insert statement.
“The DECLARE ... HANDLER statement specifies a handler that
deals with one or more conditions. If one of these conditions
occurs, the specified statement executes. statement can be a simple
statement such as SET var_name = value, or a compound statement
written using BEGIN and END.”
“The handler_action value indicates what action the handler takes
after execution of the handler statement:
CONTINUE: Execution of the current program continues.
EXIT: Execution terminates for the BEGIN ... END compound
statement in which the handler is declared. This is true even if the
condition occurs in an inner block.
UNDO: Not supported.”
(http://dev.mysql.com/doc/refman/5.7/en/declare-handler.html)
Using GET DIAGNOSTICS in exception handler example
DROP TABLE IF EXISTS Accounts;
CREATE TABLE Accounts ( acctID INTEGER NOT NULL
PRIMARY KEY, balance INTEGER NOT NULL,
CONSTRAINT unloanable_account CHECK (balance >= 0) );
INSERT INTO Accounts (acctID, balance) VALUES (101, 1000);
INSERT INTO Accounts (acctID, balance) VALUES (202, 2000);
COMMIT;
DROP PROCEDURE IF EXISTS BankTransfer;
SET AUTOCOMMIT = 0;
DELIMITER !
CREATE PROCEDURE BankTransfer (IN fromAcct INT,
IN toAcct INT,
IN amount INT,
OUT msg VARCHAR(100))
LANGUAGE SQL MODIFIES SQL DATA
P1: BEGIN
DECLARE acct INT;
DECLARE balance_v INT;
DECLARE EXIT HANDLER FOR NOT FOUND
BEGIN ROLLBACK;
SET msg = CONCAT('missing account ', CAST(acct AS CHAR));
END;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN ROLLBACK;
SET msg = CONCAT('negative balance (?) in ', fromAcct);
END;
SET acct = fromAcct;
SELECT acctID INTO acct FROM Accounts WHERE acctID = fromAcct ;
UPDATE Accounts SET balance = balance - amount
WHERE acctID = fromAcct;
SET acct = toAcct;
SELECT acctID INTO acct FROM Accounts WHERE acctID = toAcct ;
UPDATE Accounts SET balance = balance + amount
WHERE acctID = toAcct;
SELECT balance INTO balance_v
FROM accounts
WHERE acctID = fromAcct;
IF balance_v < 0 THEN
ROLLBACK;
SET msg = CONCAT(' negative balance in ', fromAcct);
ELSE
COMMIT;
SET msg = 'committed';
END IF;
END P1 !
DELIMITER ;
Some explanation - examples
Testing
-- testing
CALL BankTransfer (101, 201, 100, @msg);
Select @msg;
CALL BankTransfer (100, 202, 100, @msg);
Select @msg;
CALL BankTransfer (101, 202, 100, @msg);
Select @msg;
CALL BankTransfer (101, 202, 1000, @msg);
Select @msg;
Bank transfer and Database Consistency
using GET DIAGNOSTICS
DROP TABLE IF EXISTS Accounts;
CREATE TABLE Accounts ( acctID INTEGER NOT NULL
PRIMARY KEY,
balance INTEGER NOT NULL,
CONSTRAINT unloanable_account CHECK (balance >= 0));
INSERT INTO Accounts (acctID, balance) VALUES (101, 1000);
INSERT INTO Accounts (acctID, balance) VALUES (202, 2000);
COMMIT;
Bank transfer and Database Consistency
using GET DIAGNOSTICS
-- create procedure BankTransfer
DROP PROCEDURE IF EXISTS BankTransfer;
SET AUTOCOMMIT = 0;
CREATE PROCEDURE BankTransfer (IN fromAcct INT,
IN toAcct INT,
IN amount INT,
OUT msg VARCHAR(100))
LANGUAGE SQL
P1: BEGIN
DECLARE rowcount INT;
DECLARE balance_v INT;
UPDATE Accounts SET balance = balance - amount
WHERE acctID = fromAcct;
GET DIAGNOSTICS rowcount = ROW_COUNT;
IF rowcount = 0 THEN
ROLLBACK;
SET msg =
CONCAT('missing account ', fromAcct);
ELSE
SELECT balance INTO balance_v
FROM accounts
WHERE acctID = fromAcct;
IF balance_v < 0 THEN
ROLLBACK;
SET msg = CONCAT(' negative balance in ', fromAcct);
ELSE
UPDATE Accounts SET balance = balance + amount
WHERE acctID = toAcct;
GET DIAGNOSTICS rowcount = ROW_COUNT;
IF rowcount = 0 THEN
ROLLBACK;
SET msg =
CONCAT('rolled back because of missing account ', toAcct);
ELSE
COMMIT;
SET msg = 'committed';
END IF;
END IF;
END IF;
END P1 !
Some explanation - examples
Bank transfer and Database Consistency
using GET DIAGNOSTICS - Testing
-- testing
CALL BankTransfer (101, 201, 100, @msg);
Select @msg;
CALL BankTransfer (100, 202, 100, @msg);
Select @msg;
CALL BankTransfer (101, 202, 100, @msg);
Select @msg;
CALL BankTransfer (101, 202, 1000, @msg);
Select @msg;
Select * from accounts;
Some Best Practices - Background
DBMS products differ from each other in terms of concurrency control
services and transaction managing behavior, so it is important for
reliability and performance that the application developer knows the
behavior of the DBMS to be used.
Reliability is the number one priority but the default transaction
isolation level used by DBMS products often favors performance
before reliability!
Some Best Practices – Isolation levels
SQL transactions, even in the same user transaction sequence, may
have different reliability and isolation requirements. Always define
transaction isolation level in the beginning of every transaction.
According to the SQL standard READ UNCOMMITTED isolation
level can only be used in READ ONLY transactions, but the products
don't force this.
If the developer cannot decide which isolation level provides reliable
enough isolation then the proper isolation level should be planned with
extra care, and the SERIALIZABLE isolation level with the ISO SQL
semantics should be used.
Some Best Practices – User Interface
A user transaction typically needs multiple dialogues with the database.
Some of these dialogues will only collect data from the database
supporting the user transaction, and as a final step of user transaction,
some "save" button will trigger an SQL transaction which will update
the database.
SQL transactions should not contain any dialogue with the end user, as
this would slow down the processing. Since SQL transactions may get
rolled back during the transaction, they should not affect anything else
but the database.
Some Best Practices - transactions
Every SQL transaction should have a well-defined task, starting and
ending in the same application component.
The SQL transaction should be as short as possible, to minimize the
concurrency competition, and blocking the concurrent transactions.
Avoid DDL commands in transactions. Implicit commits due to DDL
may result unintentional transactions.
Hands-on laboratory practice based on the
virtual database laboratory DebianDB
Questions