Mysql Error Number 1422
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 mysql call stored procedure from function Learn more about Stack Overflow the company Business Learn more about hiring developers mysql stored procedure return value or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack create procedure mysql Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Calling a procedure inside a function throws MySQL mysql triggers ERROR 1422 up vote 0 down vote favorite I am building a "bank" as an assignment for a database course I am taking. I have created a stored function which takes a few IN variables, such as account ID, customer ID and a PIN number, and does checks on this to see if the submitted data is valid. If the data is valid, the procedure updates the account balance to represent a monetary transaction. It then "returns" whether or not the submitted data was valid. Here is the code for the procedure: DELIMITER // CREATE PROCEDURE retrieveMoney ( IN holder INT, IN pin VARCHAR(4), IN account INT, IN amount FLOAT, OUT success INT ) BEGIN START TRANSACTION; SELECT COUNT(id) INTO success FROM account_holder WHERE id=holder AND pin=pin; IF success IS NOT NULL THEN IF (SELECT balance-amount FROM account WHERE id=account) >= 0 THEN UPDATE account SET balance = balance-amount WHERE id=account; CALL logTransaction(account,NULL,amount); COMMIT; ELSE ROLLBACK; END IF; ELSE ROLLBACK; END IF; END// DELIMITER ; I wanted to make it easier to view the output of the procedure, and as I'm not allowed to use transactions within a function I chose to write a wrapper function, as follows: DELIMITER // CREATE FUNCTION retrieveMoney ( holder INT, pin VARCHAR(4), account INT, amount FLOAT ) RETURNS INT BEGIN CALL retrieveMoney(holder,pin,account,amount,@success); RETURN @success; END// DELIMITER ; Unfortunately this does not work, and I still get this following error: ERROR 1422 (HY000): Explicit or implicit commit is not allowed in stored function or trigger. Is this because I call the procedure, which contains transactions, inside the function? mysql sql s
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 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up MySQL Error when trigger calls a procedure up vote 2 down vote favorite I have a table (ft_ttd) and http://stackoverflow.com/questions/26096621/calling-a-procedure-inside-a-function-throws-mysql-error-1422 want to sort it descending (num) and insert rating numbers into rating column. Something like that: I've created a procedure. CREATE PROCEDURE proc_ft_ttd_sort BEGIN CREATE TEMPORARY TABLE ft_ttd_sort (id int (2), num int (3), rating int (2) AUTO_INCREMENT PRIMARY KEY); INSERT INTO ft_ttd_sort (id, num) SELECT id, num FROM ft_ttd ORDER BY num DESC; TRUNCATE TABLE ft_ttd; INSERT INTO ft_ttd SELECT * FROM ft_ttd_sort; DROP TABLE ft_ttd_sort; END; When I call it - http://stackoverflow.com/questions/8051431/mysql-error-when-trigger-calls-a-procedure it works great. CALL proc_ft_ttd_sort; After that I've created trigger calling this procedure. CREATE TRIGGER au_ft_ttd_fer AFTER UPDATE ON ft_ttd FOR EACH ROW BEGIN CALL proc_ft_ttd_sort(); END; Now every time when I update ft_ttd table I've got a error. UPDATE ft_ttd SET num = 9 WHERE id = 3; ERROR 1422 (HY000): Explicit or implicit commit is not allowed in stored function ortrigger. Any ideas how to make it work? Maybe this process can be optimized? Thank you! mysql sql database triggers share|improve this question edited Nov 8 '11 at 20:02 pilcrow 32.2k55898 asked Nov 8 '11 at 13:51 Oleg Legun 1313 add a comment| 2 Answers 2 active oldest votes up vote 1 down vote accepted Triggers can't do it DDL aside, your trigger-based approach has a few difficulties. First, you want to modify the very table that's been updated, and that's not permitted in MySQL 5. Second, you really want a statement-level trigger rather than FOR EACH ROW — no need to re-rank the whole table for every affected row — but that's not supported in MySQL 5. Dynamically compute "rating" So ... is it enough to just compute rating dynamically using a MySQL ROW_NUMBER() workaround? -- ALTER TABLE ft_ttd DROP COLUMN rating; -- if you like SELECT id, num, @i := @i
2011 9:17 Reporter: Malvinder Dhawan Email Updates: Status: Not a Bug Impact on me: https://bugs.mysql.com/bug.php?id=59971 None Category:MySQL Server: Compiling Severity:S2 (Serious) Version:5.0.45 OS:Any Assigned to: View Add Comment Files Developer Edit Submission View Progress Log Contributions [6 Feb 2011 4:48] Malvinder http://dba.stackexchange.com/questions/99124/how-to-create-function-using-another-functions-mysql Dhawan Description: I am making a trigger which has no syntax error I am also using delimiter this is the error I get when I stored procedure compile the whole code error number 1422 Explicit or implicit commit cannot be done in a function or procedure or trigger How to repeat: please find a solution for it [6 Feb 2011 4:49] Malvinder Dhawan error 1422 explicit or implicit commitAttachment: trigger.sql (application/octet-stream, text), 740 bytes.
[6 Feb 2011 9:17] Valerii mysql error number Kravchuk You can not use TRUNCATE in trigger. Read http://dev.mysql.com/doc/refman/5.0/en/truncate-table.html: "As of MySQL 5.0.8, truncate operations cause an implicit commit." and http://dev.mysql.com/doc/refman/5.0/en/stored-program-restrictions.html: "For stored functions (but not stored procedures), the following additional statements or operations are not permitted: Statements that perform explicit or implicit commit or rollback." Same restriction applies to triggers, as you can see from the error message. [6 Feb 2011 9:22] Peter Laursen So there is a detail to document here, I think. "For stored functions (but not stored procedures), the following additional statements or operations are not permitted: ...." >> "For stored functions (but not stored procedures) and triggers, the following additional statements or operations are not permitted: ...." [6 Feb 2011 9:23] Peter Laursen and EVENTS btw?? [6 Feb 2011 9:45] Valerii Kravchuk That same page says at the beginning: "The restrictions for stored functions also apply to triggers." As for EVENTs, 5.1 manual has a special subsection, http://dev.mysql.com/doc/refman/5.1log in tour help Tour 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 Database Administrators Questions Tags Users Badges Unanswered Ask Question _ Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. 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 create function using another functions mysql up vote 0 down vote favorite I want to create a function in MySQL using another function but I am getting an error "Explicit or implicit commit is not allowed in stored function or trigger". This is my code: DELIMITER $$ CREATE FUNCTION `ufn_gettotalavg` (pUserID bigint,pclass bigint) RETURNS int(11) BEGIN DECLARE learnavg BIGINT; DECLARE Assesavg BIGINT; DECLARE Pratiecavg BIGINT; DROP TABLE IF EXISTS AvgTemp; CREATE TEMPORARY TABLE IF NOT EXISTS AvgTemp (SType varchar(20),Percentage bigint); DROP TABLE IF EXISTS assesTemp; CREATE TEMPORARY TABLE IF NOT EXISTS assesTemp AS (SELECT COALESCE(ROUND(((SELECT uf_GetTotalScoreByAssessment( AssessmentID,pUserID,1)) / (SELECT ufn_getMaxMarks( AssessmentID))) * 100),0) AS Percentage FROM tr_t_assessments where AssessmentType='LiveTest'); SET Assesavg= (SELECT COALESCE(round(AVG(Percentage)),0) FROM assesTemp); DROP TABLE IF EXISTS learnTemp; CREATE TEMPORARY TABLE IF NOT EXISTS learnTemp AS (SELECT IFNULL(ROUND((SELECT count(MaterialID) FROM tr_t_materialtracking where MaterialStatus=1 and UserID=pUserID and SubjectID = S.SubjectID and MaterialID in (SELECT MaterialID FROM tr_m_material where ClassID=pclass and SubjectID = S.SubjectID)) / (SELECT COUNT(TopicID) FROM tr_m_material where TopicID IN (SELECT TopicID FROM tr_m_subjecttopics where SubjectID = S.SubjectID))* 100) ,0) AS Percentage FROM tr_m_subjects S WHERE Status = 1 AND SubjectID IN (SELECT SubjectID FROM tr_m_subjects where ClassID=pclass)); SET learnavg= (SELECT round(AVG(Percentage)) FROM learnTemp); DROP TABLE IF EXISTS PratiecTemp; CREATE TEMPORARY TABLE IF NOT EXISTS PratiecTemp AS (SELECT COALESCE(ufn_getPracticePercentage(pUserID,pclass,S.SubjectID),0) as Percentage FROM tr_m_subjects S WHERE Status = 1 AND SubjectID IN (SELECT SubjectID FROM tr_m_subjects where ClassID=pclass)) ; SET Pratiecavg= (SELECT ROUND(AVG(Percentag