1

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?

-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> 
2
  • This works, but I still do not understand the error message I originally reported. ERROR 1243 (HY000): Unknown prepared statement handler (stmt) given to EXECUTE In your code you are testing for a known condition. If I wanted to continue on any exception, I do not know how to do it.
    – Vietyank
    Jul 5 '15 at 23:43
  • Here is a list with a lot of HANDLERs: gist.github.com/onare/c8d3042ba4d18408aeee
    – oNare
    Jul 6 '15 at 1:32
1

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$$
-1
        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. 
6
  • Thanks but I get a syntax error with that. ERROR 1064 (42000) at line 3: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '=1;
    – Vietyank
    Jul 5 '15 at 8:04
  • I tried DECLARE CONTINUE FOR NOT FOUND SET DONE =1 also DECLARE CONTINUE HANDLER FOR NOT FOUND SET DONE=1
    – Vietyank
    Jul 5 '15 at 8:06
  • use above link and statement for using handler in procedures
    – Ravi Kumar
    Jul 5 '15 at 8:08
  • The problem is that my uses a prepare/execute set of statements. If I declare an error handler, I get the ERROR 1243 I originally posted about.
    – Vietyank
    Jul 5 '15 at 8:15
  • @Vietyank you want to drop tables that are created before one week from current date and time.so if your condition not satisfies it returns null.so the main cause of above problem is your SQL statement that returns table list.
    – Ravi Kumar
    Jul 5 '15 at 12:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.