Drop a non existing procedure in MySQL

Question

I'm using MySQL 5.5.31 on fedora and I have trouble dropping a non existing procedure. With the root user:

• Create a new database: test
• Create a new user: test

• Give all permissions to user test for database test

  GRANT ALL PRIVILEGES ON test.* TO 'test'@'%' WITH GRANT OPTION;
  

• With user test

  CREATE PROCEDURE test.foo() SELECT NOW();
  DROP PROCEDURE IF EXISTS test.foo;
  
  Query OK, 0 rows affected (0.00 sec)
  
  DROP PROCEDURE IF EXISTS test.foo;
  
  ERROR 1370 (42000): alter routine command denied to user 'test'@'localhost' for routine 'test.foo'
  

Why is the "IF EXISTS" not working?

If I do the same with the root user everything works fine (with warning, but that is okay):

• With user test

  CREATE PROCEDURE test.foo() SELECT NOW();
  
  DROP PROCEDURE IF EXISTS test.foo;
  Query OK, 0 rows affected (0.00 sec)
  
  DROP PROCEDURE IF EXISTS test.foo;
  Query OK, 0 rows affected, 1 warning (0.00 sec)
  

Answer 1

Firstly, GRANT ALL PRIVILEGES ON test.* TO 'test'@'%' WITH GRANT OPTION; would not grant privileges to the user when connecting from localhost

You need to grant privileges for localhost separately

From the error it looks like you are disconnecting after the first DROP and connecting again from localhost.

Well, if you are executing these 4 statements one after the another, you should get the same result as you get with root

GRANT ALL PRIVILEGES ON test.* TO 'test'@'%' WITH GRANT OPTION;
CREATE PROCEDURE test.foo() SELECT NOW();
DROP PROCEDURE IF EXISTS test.foo; 
DROP PROCEDURE IF EXISTS test.foo; 

Let me know, if I have made my answer clear

Answer 2

Try to use SET sql_notes = 0; beforeDROP PROCEDURE IF EXISTS command.