Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

mysql drop table if exists inside procedure

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?

like image 604
Alwin Kesler Avatar asked Oct 29 '12 14:10

Alwin Kesler


People also ask

Why DROP TABLE if exists?

The DROP TABLE statement deletes the specified table, and any data associated with it, from the database. The IF EXISTS clause allows the statement to succeed even if the specified tables does not exist.

Does DROP TABLE lock MySQL?

Yes, select . So, if you loop over a cursor in mysql (or php, for example with pdo::fetch ), and you run a ddl statement on the same table(s), you will get a deadlock.

What is the purpose of DROP TABLE command in MySQL how is it different from delete command?

DELETE is a Data Manipulation Language command, DML command and is used to remove tuples/records from a relation/table. Whereas DROP is a Data Definition Language, DDL command and is used to remove named elements of schema like relations/table, constraints or entire schema.


1 Answers

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.

like image 80
Devart Avatar answered Sep 23 '22 00:09

Devart