CODEWITHSUNDEEP
mysqlerror-handlingtransactions
Chubaka
Chubaka

Reputation: 3155

MySQL transaction doesn't return enough error message to help debug

The following transaction runs no problem. However, once a transaction is failed, for example, column number doesn't match, or any other reason, it will only show an error message "'SQLWARNING: The request at Linux system time ${LINUX_SYSTEM_TIME} insert failed. Th data will be rollbacked'" but no error message to show the actual failure. May I know if there is any MySQL way to show an error message in a MySQL transaction? Thank you.

       delimiter //

        CREATE PROCEDURE 123
        BEGIN

            DECLARE EXIT HANDLER FOR SQLWARNING

                BEGIN

                ROLLBACK;

                SELECT 'SQLWARNING: The request at Linux system time ${LINUX_SYSTEM_TIME} insert failed. Th data will be rollbacked';

                END;


            DECLARE EXIT HANDLER FOR SQLEXCEPTION

                BEGIN

                ROLLBACK;

                SELECT 'The request at Linux system time ${LINUX_SYSTEM_TIME} insert failed. Th data will be rollbacked';

                END;

            START TRANSACTION;

            <do whatever we need in transaction>

            COMMIT;

            END //

            CALL 123;

            DROP PROCEDURE IF EXISTS

Upvotes: 0

Views: 517

Answers (2)

Chubaka
Chubaka

Reputation: 3155

Just to highlight the answer I kindly got from @@Solarflare. Resignal is an easy solution here (MySQL exception handler access exception being handled):

                DECLARE EXIT HANDLER FOR SQLWARNING

                BEGIN

                ROLLBACK;

                SELECT 'SQLWARNING: whatever error message';

                RESIGNAL;

                END;


            DECLARE EXIT HANDLER FOR SQLEXCEPTION

                BEGIN

                ROLLBACK;

                SELECT 'SQLWARNING: whatever error message;'

                RESIGNAL;

                END;

Upvotes: 2

mikep
mikep

Reputation: 3905

Turn on error logging. http://www.pontikis.net/blog/how-and-when-to-enable-mysql-logs has a good tutorial.

Upvotes: 0

Related Questions