Re: MySQL Stored Procedure works differently with DBI

Solved. This seemes to have been an issue of "matched rows" vs. "changed 
rows". The use of stored procedures was not part of the scope of the 
problem. The different behaviors were the result of how different 
clients connect to the server.

This thread helped me understand the problem:
http://lists.mysql.com/perl/72.

It seems that calling ROW_COUNT() when connected via DBD::mysql returns 
"matched rows" and not "changed rows"

I was not able to resolve this by adding "mysql_found_rows=0" to the DSN 
passed to DBD::mysql. It seemed to not have any effect.

I was able to overcome this in my situation by changing this:
UPDATE categories c,temporary_cat_status t
SET c.categories_status=0
WHERE c.categories_id=t.cid
AND  t.enabledsubcats=0

to this:
UPDATE categories c,temporary_cat_status t
SET c.categories_status=0
WHERE c.categories_id=t.cid
AND  t.enabledsubcats=0
AND  c.categories_status=1

This ensured that the matched rows and changed rows were always the same.

Thanks.

On 02/15/2010 04:47 PM, Erik wrote:
> 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)
>