Create Definer Error 1064
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site create stored procedure in mysql About Us Learn more about Stack Overflow the company Business Learn more about mysql stored procedure hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up MYSQL Error 1064 when importing stored procedures up vote 2 down vote favorite I'm importing a stored procedure which I just exported from my development server into my production one and I ran into the following error in phymyadmin. SQL query: Documentation $$ CREATE DEFINER = `devuser`@`localhost` FUNCTION `get_refundable_amount` ( enrol_id INT ) RETURNS double( 10, 2 ) READS SQL DATA BEGIN DECLARE refundable_amount double( 10, 2 ) DEFAULT 0; SELECT ( sum( P.amount ) - EI.amount ) INTO refundable_amount FROM site_payment_processed AS P INNER JOIN site_user_enroled AS E ON ( P.enrol_id = E.id AND P.payment_type = 'Refund' ) INNER JOIN site_user_enroled_invoice AS EI ON EI.enrol_id = E.id WHERE E.id = enrol_id GROUP BY E.id; RETURN ( refundable_amount ); END$$ MySQL said: Documentation #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '$ CREATE DEFINER=`devuser`@`localhost` FUNCTION `get_refundable_amount`' at line 1 mysql stored-procedures phpmyadmin mysql-error-1064 share|improve this question asked Sep 28 '10 at 11:31 siliconpi 2,893114692 add a comment| 2 Answers 2 active oldest votes up vote 2 down vote Surround the body of the stored procedure code with a new definition of the delimiter (e.g., from ; to $$). DELIMITER $$ CREATE DEFINER = `devuser`@`localhost` FUNCTION `get_refundable_amount` ( enrol_id INT ) RETURNS double( 10, 2 ) READS SQL DATA BEGIN DECLARE refundable_amount double( 10, 2 ) DEFAULT 0; SELECT ( sum( P.amount ) - EI.amount ) INTO refundable_amount FROM site_payment_processed AS P INNER JOIN site_user_enroled AS E ON ( P.enrol_id = E.id AND P.payment_type
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Error Code: 1064 in mysql up http://stackoverflow.com/questions/3812254/mysql-error-1064-when-importing-stored-procedures vote 4 down vote favorite I created a procedure that is supposed to do some operation but every time I call it, mysql comes out with an error, which I have not clue what it means. I have tried to understand it in vain, here 's the table structure which the stored procedure where made to do an operation on it: CREATE TABLE `recruitment`.`job_seeker` ( `user_id` http://stackoverflow.com/questions/7926082/error-code-1064-in-mysql INT Null , `fname` VARCHAR(45) Null , `lname` VARCHAR(45) Null , `mname` VARCHAR(45) Null , `gender` VARCHAR(10) Null , `dob` DATE Null , `marital_status` VARCHAR(45) Null , `address` VARCHAR(45) Null , `city` VARCHAR(45) Null , `nationality` VARCHAR(45) Null , `phone` VARCHAR(45) Null , `mobile` VARCHAR(45) Null , `degree_id` INT Null , `education` VARCHAR(100) Null , `experience` VARCHAR(250) Null , `other` VARCHAR(250) Null , `job_target` VARCHAR(250) Null , PRIMARY KEY (`user_id`) , INDEX `user_id` (`user_id` ASC) , INDEX `degree_id` (`degree_id` ASC) , CONSTRAINT `user_id` FOREIGN KEY (`user_id` ) REFERENCES `recruitment`.`user_authentication` (`user_id` ) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `degree_id` FOREIGN KEY (`degree_id` ) REFERENCES `recruitment`.`degree` (`degree_id` ) ON DELETE NO ACTION ON UPDATE NO ACTION ); Here's the stored procedure: DELIMITER $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `createSeekerProfile`(in userName varchar(45), in fn varchar(45),in mn varchar(45), in ln varchar(45), in gender varchar(6),in nationality varchar(45), in ad varchar(45),in city varchar(45),in phone varchar(15),in mob varchar(15), in maritalStatus varchar(45), in degId int, in educ varchar(100), in exper varchar(250), in other varchar(250), in dob date,in jtarg varchar(250)) begin declare returned_ID int; set @dyn_que = CONCAT('select user_id into @returned_ID from user_authentication where user_name = ? '); prepare s1 from @dyn_que ; set @us
here for a quick overview of the site Help Center Detailed answers to any questions you might have http://stackoverflow.com/questions/26508967/mysql-error-1064-stored-procedure-creation-error Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with http://serverfault.com/questions/418581/how-to-give-mysql-users-create-db-rights us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just stored procedure like you, helping each other. Join them; it only takes a minute: Sign up mysql error (#1064) stored procedure creation error up vote 0 down vote favorite I'm trying to create a stored procedure but i keep getting error: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your create definer error MySQL server version for the right syntax to use near 'NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT '' B' at line 3 My SQL is as follows: DELIMITER $$ CREATE FUNCTION `prodcat_descendants` (idcat INTEGER(11) ) RETURNS VARCHAR NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT '' BEGIN RETURN (select GROUP_CONCAT(DISTINCT id_category SEPARATOR ',') from cms_prodcategories where id_parent=idcat and active='1'); END$$ I've tried to include DELIMITER but no result. I guess the problem might be in the quotes. Can someone point me out the solution? Thanks! MySQL version 5.5 mysql stored-procedures phpmyadmin share|improve this question edited Oct 22 '14 at 15:22 asked Oct 22 '14 at 13:45 Luis 327 add a comment| 1 Answer 1 active oldest votes up vote 1 down vote accepted You need a varchar length on the return statement of the procedure something as RETURNS VARCHAR(1024) So the procedure becomes DELIMITER $$ CREATE FUNCTION `prodcat_descendants` (idcat INTEGER(11) ) RETURNS VARCHAR(1024) NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT '' BEGIN RETURN (sel
Start here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Server Fault Questions Tags Users Badges Unanswered Ask Question _ Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute: Sign up Here's how it works: Anybody can ask a question Anybody can answer The best answers are voted up and rise to the top How to give MySQL users CREATE DB rights? up vote 1 down vote favorite 1 I want to give MySQL users the ability to create new databases, and have ALL privileges on these, but not on the other database. I've tried to create a STORED PROCEDURE for this, but it's not working out. This is my procedure (NOTE: UPDATED CODE BELOW): DELIMITER // CREATE DEFINER = 'root'@'localhost' PROCEDURE createdb(IN newdb VARCHAR(255)) BEGIN CREATE DATABASE newdb; SET @newdbuser = USER(); GRANT ALL ON newdb.* TO '@newdbuser'; FLUSH PRIVILEGES; END // DELIMITER ; I call the procedure using: CALL createdb('newtestdb'); But what happens is that the database 'newdb' (variable name) is created. Trying to access that database as the mortal user who called the procedure doesn't work either: access denied. Question: What is wrong with my PROCEDURE? Or am I attacking the problem from the wrong angle? UPDATE Based on longneck's answer, I've revised the procedure to: DELIMITER // CREATE DEFINER = 'root'@'localhost' PROCEDURE createdb(IN newdb VARCHAR(255)) BEGIN PREPARE stmt1 FROM "CREATE DATABASE ?"; PREPARE stmt2 FROM "GRANT ALL ON newdb.* TO '?'"; EXECUTE stmt1 USING @newdb; SET @newdbuser = USER(); EXECUTE stmt2 USING @newdbuser; FLUSH PRIVILEGES; END // DELIMITER ; However, when calling thi