How to use a MySQL error handler in a stored procedure with prepare/execute

Question

I have a procedure defined like this:

drop procedure if exists drop_tables_like;
delimiter $$
create procedure drop_tables_like(myint int, pattern varchar(255), db      varchar(255) )
begin
DECLARE CONTINUE HANDLER FOR 1064
select now() - interval  myint day into @owa;
set group_concat_max_len = 65535;
select concat('drop table ', group_concat(table_name)) into @str_sql
from information_schema.tables
where
    table_schema=db and table_name like pattern and
    create_time <= @owa;

prepare stmt from @str_sql;
execute stmt;
drop prepare stmt;
end$$

The idea is to drop tables matching a certain pattern that have a create_time <= a specified date.

When executed, I get an error:

mysql> call drop_tables_like(7, 'monkey%', 'daweb2012_civicrm');
ERROR 1243 (HY000): Unknown prepared statement handler (stmt) given to     EXECUTE
mysql>

How can I declare a handler for an exception in a procedure that is using a prepared statement?

Answer 1

The problem is, when you try to EXECUTE your DYNAMIC query the query returns NULL when there's not found rows.

Test:

mysql> CALL drop_tables_like(
    -> '6', #myint INT, 
    -> 'onare', #pattern VARCHAR(255), 
    -> 'PRUEBA'); #db VARCHAR(255))
+--------+
| sqlSTR |
+--------+
| NULL   |
+--------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> 

So, try to first COUNT to verify if exists any row with your pattern:

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `drop_tables_like`(
myint INT, 
pattern VARCHAR(255), 
db VARCHAR(255))
BEGIN
DECLARE CONTINUE HANDLER FOR 1064
SELECT (now() - interval  myint day) into @owa;
SET group_concat_max_len = 65535;
# [DINAMYC QUERY]
SET @TotalRows=(select COUNT(*)
from information_schema.tables where table_schema=db and table_name like pattern and create_time <= @owa);
IF (@TotalRows>0) THEN
    SELECT concat('drop table ', group_concat(table_name)) into @str_sql
    FROM information_schema.tables
    WHERE
        table_schema=db and table_name like pattern and
        create_time <= @owa;
    PREPARE stmt FROM @str_sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
ELSE
    SELECT 'There\'s not rows to update.' as sDESC_ERROR;
END IF;
END$$

Result if there's not rows to update:

mysql> CALL drop_tables_like( '6',  'onare',  'PRUEBA');
+-----------------------------+
| sDESC_ERROR                 |
+-----------------------------+
| There's not rows to update. |
+-----------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> 

Answer 2

Here is a sample of updated procedure with 1064 and 1065 errors caught

refer to https://gist.github.com/onare/c8d3042ba4d18408aeee for a longer list of errors

drop procedure if exists drop_tables_like;
delimiter $$
create procedure drop_tables_like(myint int, pattern varchar(255), db      varchar(255) )
begin
DECLARE E INT DEFAULT 0;
DECLARE M TEXT DEFAULT NULL;

DECLARE CONTINUE HANDLER FOR 1064 SET E='1064', M="%s near '%s' at line %d";
DECLARE CONTINUE HANDLER FOR 1065 SET E='1065', M="Query was empty";

select now() - interval  myint day into @owa;
set group_concat_max_len = 65535;
select concat('drop table ', group_concat(table_name)) into @str_sql
from information_schema.tables
where
table_schema=db and table_name like pattern and
create_time <= @owa;

prepare stmt from @str_sql;
IF E> 0 THEN
        SELECT CONCAT("MySQL Error #", E,  ": ", M);;
        set E=0;
    else
        execute stmt;
        drop prepare stmt;
    end if;
end$$

Answer 3

        Use DECLARE CONTINUE HANDLER FOR SQLSTATE '1064'  use link

           https://dev.mysql.com/doc/refman/5.0/en/declare-handler.html

        @Vietyank Your procedure working fine.The message showing is not an error it is an warning.

        Example:
        I have created a Database Test1;
        mysql>Create Database Test1; 
        mysql>use Test1;
        mysql>CREATE TABLE t1(id int);
        mysql>SHOW TABLES;
        +-----------------+
        | Tables_in_test1 |
        +-----------------+
        | t1              |
        +-----------------+
        1 row in set (0.00 sec)
        mysql> drop procedure if exists drop_tables_like;
        Query OK, 0 rows affected (0.00 sec)

        mysql> delimiter $$
        mysql> create procedure drop_tables_like(myint int, pattern varchar(255), db
          varchar(255) )
            -> begin
            -> DECLARE CONTINUE HANDLER FOR 1064
            -> set group_concat_max_len = 65535;
            -> select concat('drop table ', group_concat(table_name)) into @str_sql
            -> from information_schema.tables
            -> where
            ->     table_schema=db and table_name like pattern;
            ->  prepare stmt from @str_sql;
            ->     execute stmt;
            ->     drop prepare stmt;
            ->
            -> end$$
        Query OK, 0 rows affected (0.00 sec)

        mysql> delimiter ;
        mysql>
        mysql> call drop_tables_like(7, 't%', 'test1');
        Query OK, 0 rows affected (0.06 sec)

        mysql> show tables;
        Empty set (0.00 sec)

        After successfully execution of stored procedure.I am again executed same procedure Now it shows warning because your database contains zero tables.

        mysql> drop procedure if exists drop_tables_like;
        Query OK, 0 rows affected (0.00 sec)
        mysql> delimiter $$
        mysql> create procedure drop_tables_like(myint int, pattern varchar(255), db
          varchar(255) )
            -> begin
            -> DECLARE CONTINUE HANDLER FOR 1064
            -> set group_concat_max_len = 65535;
            -> select concat('drop table ', group_concat(table_name)) into @str_sql
            -> from information_schema.tables
            -> where
            ->     table_schema=db and table_name like pattern;
            ->  prepare stmt from @str_sql;
            ->     execute stmt;
            ->     drop prepare stmt;
            ->
            -> end$$
        Query OK, 0 rows affected (0.00 sec)

        mysql> delimiter ;
        mysql>
        mysql> call drop_tables_like(7, 't%', 'test1');
        ERROR 1243 (HY000): Unknown prepared statement handler (stmt) given to EXECUTE.

    It shows above message because of your database doesn't contain tables so that SQL statement that you wrote returns NULL value.

mysql> select concat('drop table ', group_concat(table_name)) into @str_sql
    -> from information_schema.tables
    -> where
    ->     table_schema='Test1' and table_name like 't%';
Query OK, 1 row affected (0.00 sec)
mysql> SELECT @str_sql;
+----------+
| @str_sql |
+----------+
| NULL     |
+----------+
1 row in set (0.00 sec)

    If you change your error HANDLER TO DECLARE CONTINUE HANDLER FOR SQLEXCEPTION Then it shows only warning after procedure execution try it Once.