mysql drop table if exists inside procedure

Question

I'm trying to apply a nested set model example with procedures. I've found many of them with this technique and in the process I've found a problem. Every time I call the procedure I get unknown table XXX. When I create the procedure I got no problem at all. The quick example:

CREATE PROCEDURE `sp_getRoleTree` (IN root INT)
  READS SQL DATA
BEGIN
    DECLARE rows SMALLINT DEFAULT 0;
    DROP TABLE IF EXISTS ROLE_TREE;
    CREATE TABLE ROLE_TREE (
        nodeID INT PRIMARY KEY
    ) ENGINE=HEAP;

    INSERT INTO ROLE_TREE VALUES (root);

    SELECT * FROM ROLE_TREE;
    DROP TABLE ROLE_TREE;
END;

So my question is, am I doing something wrong here (it's example code), can I disable the warning on the if exists if the code is fine? Is there a special looping inside the procedures that's causing these kind of warnings?

Why do you need this table? If you want to output a data-set from the procedure - just run SELECT query. — Devart, Oct 29 '12 at 15:14
Not just a data set... a nested set, it's for hierarchical data parent-child-grandchild relationships. Basically you create a table hierarchy that can be fetched to evaluate relationship. The example above is a portion of the actual procedure — Alwin Kesler, Oct 29 '12 at 15:17

score 3 · Accepted Answer · answered Oct 29 '12 at 15:35

3

As a work around: try to truncate table instead of re-creating.

Do not use DROP TABLE/CREATE TABLE. Create this table once (or when you need it) and use TRUNCATE TABLE command.

answered Oct 29 '12 at 15:35

Devart

119,203
23
166
186

This seems a possible solution. Still, don't undestand why books like **Get It Done With MySQL 5&6** by _Peter Brawley and Arthur Fuller_ explain this kind of solutions without naming this kind or error. Is it a mysql bug? or configuration issue? – Alwin Kesler Oct 29 '12 at 15:52
Looks like configuration issue. – Devart Oct 29 '12 at 15:56

score 2 · Answer 2 · answered Feb 14 '13 at 17:58

MySQL generates a warning when using DROP TABLE IF EXISTS tbl; when the table does not exist. This can be confusing and perhaps counter intuitive, but it is the expected behavior.

From http://dev.mysql.com/doc/refman/5.5/en/drop-table.html

Use IF EXISTS to prevent an error from occurring for tables that do not exist. A NOTE is generated for each nonexistent table when using IF EXISTS. See Section 13.7.5.41, SHOW WARNINGS Syntax.

IF EXISTS prevents MySQL from throwing an error, which is nice, but it causes a warning if the table does not exist. There is not an option to suppress this warning.

Thanks, I've followed @Devart solution at the end (which seem logic to me as the procedure runs over a program) but your extra info always help — Alwin Kesler, Feb 14 '13 at 20:56

mysql drop table if exists inside procedure

2 Answers2