I create my database and user
databases -> mySQL@ Databases -> add new user
CREATE DEFINER = `navid`@`%` PROCEDURE `d_answer` ( OUT `sp_out` INT( 11 ) , IN `sp_id` INT( 11 ) ) NO SQL BEGIN DELETE FROM `tblname` WHERE `a_id` = sp_id;
SET sp_out = ROW_COUNT( ) ;
MySQL said: Documentation
#1227 - Access denied; you need (at least one of) the SUPER privilege(s) for this operation
From the documentation (my emphasis):
The SUPER privilege enables an account to use CHANGE MASTER TO, KILL or mysqladmin kill to kill threads belonging to other accounts (you can always kill your own threads), PURGE BINARY LOGS, configuration changes using SET GLOBAL to modify global system variables, the mysqladmin debug command, enabling or disabling logging, performing updates even if the read_only system variable is enabled, starting and stopping replication on slave servers, specification of any account in the DEFINER attribute of stored programs and views, and enables you to connect (once) even if the connection limit controlled by the max_connections system variable is reached.
Since you are already
navid to the database, you do not need to set the
DEFINER attribute in your stored procedure; adding this line is causing the error to show up. If you remove this statement, your procedure will be created and you won't get the permissions error.
You only need to set
DEFINER if you are setting up the stored procedure for some other user, by default the stored procedure gets the same security context as the user that is creating it:
All stored programs (procedures, functions, and triggers) and views can have a DEFINER attribute that names a MySQL account. If the DEFINER attribute is omitted from a stored program or view definition, the default account is the user who creates the object.