MySQL Bugs: #17539: stored procedures called from c api do not commites data

Bug #17539	stored procedures called from c api do not commites data
Submitted:	18 Feb 2006 2:47	Modified:	25 Aug 2006 13:02
Reporter:	Radek Kochlewski	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.18	OS:	Linux (linux Mandrake 2006)
Assigned to:		CPU Architecture:	Any

Description:
Stored procedures called from c api do not commites data to the tables.
Problem is not stable. Some small changes in the code are changing the effect.
Adding or removing one select can change the result.
Adding commit sometime gives positive result, sometime not.
The same procedure called from the command interface always working fine,
or more detail, I have not get wrong result any time.
Also effect is changing between implementaton of api, but also not stable.
I used api of Asterisk app_addon_sql_mysql.c  (http://svn.digium.com/view/asterisk-addons/)
and very simple implementation writen by us for some tests.
For some procedures both are not working, for others one is working, one not.
The same result I have with MySQL 5.0.15 and 5.0.18, with MyISAM and MEMORY tables.
autocommit is always set to 1.
Queries are always logged in query log (I have it on)
Used system: Linux Mandriva 2006.0, kernel 2.6.12-12mdk, pentium3 600 MHz.
There are two versions of very simple example, but I have also much more complex, where
commit; or select somevalue; gives positive result.

How to repeat:
This example is working with our mytest and is not with Asterisk:
-- -----------------------------------------------------------------------
DROP TABLE IF EXISTS bugtest1;
CREATE TABLE bugtest1 (
`tmpstring` CHAR(40),
`tmpvalue` INT,
KEY `tmpstring` (`tmpstring`)
-- ) ENGINE=MEMORY;
) ENGINE=MyISAM;

DROP PROCEDURE IF EXISTS test_proc3;

DELIMITER //

CREATE PROCEDURE test_proc3(
			IN c_count INT
			) DETERMINISTIC MODIFIES SQL DATA

BEGIN
	DECLARE c_counter INT DEFAULT 0;
	INSERT INTO bugtest1 ( tmpstring, tmpvalue) VALUES ('adsfadfadfasd1', c_counter);
	
	REPEAT
		SET c_counter = c_counter + 1;
	UNTIL c_counter > c_count END REPEAT;
	
	INSERT INTO bugtest1 ( tmpstring, tmpvalue) VALUES ('adsfadfadfasd', c_counter);

commit;
-- SELECT * FROM bugtest1 LIMIT 0,20;

END
//

DELIMITER ;
-- -----------------------------------------------------------------------

This version is not working with both:
-- -----------------------------------------------------------------------
DROP TABLE IF EXISTS bugtest1;
CREATE TABLE bugtest1 (
`tmpstring` CHAR(40),
`tmpvalue` INT,
KEY `tmpstring` (`tmpstring`)
-- ) ENGINE=MEMORY;
) ENGINE=MyISAM;

DROP PROCEDURE IF EXISTS test_proc3;

DELIMITER //

CREATE PROCEDURE test_proc3(
			IN c_count INT
			) DETERMINISTIC MODIFIES SQL DATA

BEGIN
	DECLARE c_counter INT DEFAULT 0;
	INSERT INTO bugtest1 ( tmpstring, tmpvalue) VALUES ('adsfadfadfasd1', c_counter);
	
	REPEAT
		SET c_counter = c_counter + 1;
	UNTIL c_counter > c_count END REPEAT;
	
	INSERT INTO bugtest1 ( tmpstring, tmpvalue) VALUES ('adsfadfadfasd', c_counter);

commit;
SELECT * FROM bugtest1 LIMIT 0,20;

END
//

DELIMITER ;

Thank you for a problem report. Please, upload any files you want (up to 200K) using the File tab.

Simple test for c api

Attachment: mytest.tar.bz2 (application/x-bzip, text), 2.29 KiB.

If you have a problem to repeat the bug, please let me know.
I have some other sample procedures.
Please let me know, how much you need result on MySQL 5.1.6, because I am not successfull (yet) with compilation RPM package. Somewhere I have got line:
test -z "//share/mysql"
and
test -z "//libexec"

I think your problem is just that you do not take into account that a procedure that returns SELECT results delivers multiple result sets, one for each SELECT and one for the procedure call status itself.

See http://dev.mysql.com/doc/refman/5.0/en/c-api-multiple-queries.html

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".