How to get internal exception error message in storedproc

Help Procedural SQL
1

Hi,

I’m getting some exceptions while processing data in my stored procedure. I would like to log exact error/exception message thrown by memsql when exception occurs.

I tried using EXCEPTION_MESSAGE() but of no use, looks like it only captures user defined exception error messages not internal exceptions.

Thanks,
Santosh

2

I think that’s a bug. I’ll bring this up with the team and see if we can get it fixed.

It would also be good to be able to get the output of SHOW ERRORS in a stored procedure, say by showing the same information in a management view. I’ll make a note of that for future planning.

3

Thank you hanson for taking this up.

4

Hi Hanson,

Is this bug got fix? I’m trying to using Exception_Message() but still receive NULL values while printing Internal exception. If it got fix then please let me know ‘5.5.58 MemSQL source distribution’ had it or not.

Thanks
Arun

5

This is fixed in 7.0.1 – the current refresh of 7.0 beta 1. It is not fixed in 6.8.

Also, you should run select @@memsql_version to find your version. That version you are showing looks like a mock version for MySQL compatibility, which you get with select @@version.

Meanwhile, this sort of approach may offer a workaround:

DELIMITER //
CREATE OR REPLACE PROCEDURE exceptionMsgRepro() AS
DECLARE i INT;
BEGIN

    BEGIN
      CALL attempt();

EXCEPTION
   WHEN OTHERS THEN
      declare
         tmp text = exception_message();
      begin
         insert into error_log_table values (tmp);
      end;
end;
END //
DELIMITER ;

create table error_log_table(a text);

DELIMITER //
CREATE OR REPLACE PROCEDURE attempt() AS
DECLARE j INT NOT NULL = 0;
BEGIN
  j = NULL; -- force an exception by assigning NULL to a NOT NULL type
END //
DELIMITER ;

call exceptionMsgRepro();

select * from error_log_table;
1 Like
6

Thanks Hanson for the info.