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?
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.
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.
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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With