Is there an equivalent for SQL Server's @@error in MySQL

Question

I want to run an update query against a production database and as good little developer I am trying to make it as safe as possible. I am looking to do the following

BEGIN TRANSACTION
    UPDATE table_x SET col_y = 'some_value'
    .
    .
    .
IF (@@error <> 0)
BEGIN
    ROLLBACK
END
ELSE
BEGIN
    COMMIT
END

The above should work in SQL Server but I need this to work against a MySQL database.

EDIT: Sorry, there is more than 1 statement to execute. Yes I am aware of not needing to wrap a single query in a transaction.

Answer 1

I don't think this is necessary as there is the concept of implicit commit/rollback.

From MySQL docs:

By default, MySQL starts the session for each new connection with autocommit mode enabled, so MySQL does a commit after each SQL statement if that statement did not return an error. If a statement returns an error, the commit or rollback behavior depends on the error. See Section 13.6.13, “InnoDB Error Handling”.

Answer 2

BEGIN;
UPDATE foo SET bar = 3;
UPDATE bar SET thing = 5;
COMMIT;

If an error occurs, the entire transaction will be rolled back automatically. You really only need to execute ROLLBACK if something in your application indicates the need to rollback.

---

It is possible to handle errors explicitly within procedures or compound statements in MySQL, but I wouldn't recommend going down this route. See this how-to article and the docs for DECLARE HANDLER. You'll also have to find the specific error code you want to handle, or you can use the general SQLEXCEPTION condition. You'll also want to review compound statements and defining stored programs.

Anyway, based on the docs, you could do something like the query below, but it honestly wouldn't do anything differently than my previous answer above. It would also get you very strange looks from anyone who uses MySQL (including myself).

BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
  START TRANSACTION;
    UPDATE foo SET bar = 3;
    UPDATE bar SET thing = 5;
  COMMIT;
END;

---

Old answer:

If you're executing a single query, this doesn't make sense. Simply execute the query; if an error occurs, nothing will happen - your transaction will automatically be rolled back.

The reason is that, by default, all single queries are wrapped in a "hidden" transaction, called "autocommit" mode in MySQL. The typical alternative is to explicitly use transactions - once you execute "BEGIN" you have started a transaction. Once you either COMMIT or ROLLBACK you'll be back in autocommit mode.

So, the only reason to use a transaction in MySQL is if you want to rollback to a particular state when an error (or some other external event) occurs. In MySQL, a transaction is always aborted if an error occurs.

Lastly, it is possible to turn this behavior off entirely, and then you must use transactions explicitly at all times. I believe "BEGIN" is implied from when you last committed or rolled back, but you must either COMMIT or ROLLBACK any queries you run.

See The InnoDB Transaction Model in the MySQL manual for more info.

Answer 3

CREATE PROCEDURE prc_test()
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
  END;
  START TRANSACTION;
    INSERT
    INTO t_test VALUES ('test', 'test');
    INSERT
    INTO no_such_table
    VALUES ('no');
  COMMIT;
END;

CALL prc_test();

SELECT *
FROM t_test;

0 rows fetched.