I have a stored procedure which produces different results when called
from Perl DBI compared to when it is called from the other MySQL
clients, including the MySQL command line.
The procedure does not take any arguments and does not retrn any data.
It simply processes data in a few tables, iterating over a WHILE loop
until some condition is met. Basically it looks like this:
BEGIN
DECLARE rowcnt INT DEFAULT 1;
[Some queries to get things set up]
WHILE ( rowcnt > 0 ) DO
TRUNCATE TABLE scratch;
INSERT INTO scratch
SELECT .... ;
UPDATE datatable1,scratch
SET datatable1.x = 1
WHERE [conditons] ;
SET rowcnt=ROW_COUNT();
[call to procedure to log value of rowcnt]
END WHILE;
END
During the second iteration the UPDATE statment is where the behavior
changes depening on the database client. As far as I can tell the tables
contain identical data at this point when using DBI or another client. I
have compared the contents of the tables at various stages and it
appears the ROW_COUNT function is not returning the correct value.
I have compared the results of SHOW SESSION VARIABLES to ensure that the
connections are set up the same. But I don't see how a stored procedure
which is run completely on the server can function differently when it
is called by different clients.
I would greatly appreciate any suggestions you may have to assist me in
troubleshooting.
I have tested with:
Client side: perl(5.10.0-24ubuntu4), libdbi-perl( 1.609-1),
libdbd-mysql-perl (4.011-1ubuntu1)
Server: mysql(5.0.22-4~bpo.1)
and
Client side: perl(5.8.8-7etch6), libdbi-perl(1.53-1etch1),
libdbd-mysql-perl(3.0008-1)
Server: mysql(5.0.22-4~bpo.1)
and
Client side: perl(5.10.0-19lenny2), libdbi-perl(1.605-1),
libdbd-mysql-perl(4.007-1+lenny1)
Server: mysql(5.0.51a-24+lenny2+spu1)
