Equivalent functions in MySQL that exist in SQL Server

In an EXIT HANDLER block, what are the MySQL equivalents of

• OBJECT_SCHEMA_NAME(@@PROCID) + '.' + OBJECT_NAME(@@PROCID)

• ERROR_MESSAGE()

Edit

What I want to do.
In SQL Server I'd have

CREATE PROC ...
AS
SET NOCOUNT, XACT_ABORT ON;

BEGIN TRY
 ...
END TRY
BEGIN CATCH
   ROLLBACK etc
   EXEC someGenericProc @@PROCID, @errmsg OUTPUT;
   RAISERROR ( @errmsg, 16, 1);
END CATCH
GO

CREATE PROC someGenericProc
   @LoggingProcID int
AS
..
SELECT
    @LoggingObject = OBJECT_SCHEMA_NAME(@LoggingProcID ) + '.' + OBJECT_NAME(@LoggingProcID),
    @ErrorObject = ERROR_PROCEDURE(),
    @ErrorMessage = ERROR_MESSAGE();

-- some processing to deal with nesting etc

INSERT SomeErrorTable (...)
VALUES (@LoggingObject, @ErrorObject, @ErrorMessage)
...
GO

Cheers

I believe SIGNAL (introduced in MySQL 5.5) is what you want, for automatic logging.

However, the current implementation cannot use the various conditions such as SCHEMA_NAME after the SIGNAL executes:


Other condition information items can be set, but currently have no effect, in the sense that they are not accessible from error returns. For example, you can set CLASS_ORIGIN in a SIGNAL statement, but cannot see it after SIGNAL executes

The only available information is the MYSQL_ERRNO and MESSAGE_TEXT, so if you want the schema_name, you'll have to include that in your MESSAGE_TEXT.

Update From the SIGNAL/RESIGNAL worklog


Since /include/mysqld_error.h says "#define ER_NO_DB_ERROR 1046"
MESSAGE_TEXT will be 'No database selected'.
Or, if language = German, 'Keine Datenbank ausgewhlt'.
Things get trickier if the default message has '%s' in it.
We won't support the above.

So you can set a signal to:
SIGNAL SQLSTATE '22012' SET MYSQL_ERRNO=1365, MESSAGE_TEXT='Division by 0' explicitly, but it won't automatically inherit the text and errornumber just from the sqlstate.

Caveat:
I was able to get some ERROR information using RESIGNAL. Consider this:

DROP PROCEDURE IF EXISTS p;
DELIMITER |
CREATE PROCEDURE p ()
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION  
  DROP TABLE xx;
END|
DELIMITER ;
CALL p();

Query OK, 0 rows affected (0.00 sec)

However with this:

DROP PROCEDURE IF EXISTS p;
DELIMITER |
CREATE PROCEDURE p ()
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    RESIGNAL;
  END;
  DROP TABLE xx;
END|
DELIMITER ;
CALL p();

ERROR 1051 (42S02): Unknown table 'xx'

Example (slightly modified) found from RESIGNAL documentation

Update2

Alas, it's not yet possible to create a generic exception handler. Here is a 6-year-old feature request for it: http://bugs.mysql.com/bug.php?id=11660

The (relatively) good news is, the fix is to create the ability to view the diagnostics stack. This is good because the pre-requisite for this feature is the SIGNAL/RESIGNAL mentioned above. The bad part about it is the worklog for the diagnostics stack is not publicly visible yet.

So for now you'd have to create an exception handler for each state your procedure would come across to log into a table, and only log the error number (hard-coded, as it's not available by the handler) and maybe the hard-coded message text.

StackExchange Database Administrators Q#5746, answer score: 4