SPUG: Question on Class::DBI, MySQL, mod_perl locking

[Kevin Fink]

Ah, nothing like sending out a detailed description of a problem to
help you figure it out for yourself...

I changed my web server config to run a single process, and that
eliminated the locking issue, so it definitely seems like it has to do
with objects not getting released and DESTROYed properly. However, the
value of the field doesn't change in the database, despite the
database claiming that it is. Here's a section of strace output
showing the update:

14097 write(1, "G\0\0\0\3UPDATE domain\nSET    domain_category_id =
\'2\'\nWHERE  domain_id=\'5200\'\n", 75) = 75
14097 setsockopt(1, SOL_SOCKET, SO_RCVTIMEO,
"\2003\341\1\0\0\0\0\0\0\0\0\0\0\0\0", 16) = 0
14097 read(1, "0\0\0\1", 4)             = 4
14097 read(1, "\0\1\0\0\0\0\0(Rows matched: 1  Changed: 1  Warnings:
0", 48) = 48

So MySQL says it matched 1 row and changed 1 row. But if I query that
in the DB, it hasn't changed. Maybe a transaction not being committed?

So I added an explicit dbi_commit() call, and lo and behold, the data
is being changed!

On Tue, Dec 8, 2009 at 8:04 PM, Kevin Fink <kevin-spug at fink.com> wrote:
> I have a very simple dynamic page that is not working, and I'm not
> sure what I'm doing wrong. The page loads a record out of a database
> and displays a variety of options for one of the fields. When the user
> submits one of those, the page saves that back into the database, then
> loads the next applicable record. About as simple as you can get.
> Everything works fine, except when I try to submit the data the first
> time, nothing changes in the database, and the second time I try to
> submit it locks until the request times out with the following error:
>
> COD::DB::Domain COD::DB::Domain=HASH(0x552b689d40) destroyed without
> saving changes to domain_category_id at
> /usr/lib64/perl5/vendor_perl/5.8.5/x86_64-linux-thread-multi/ModPerl/RegistryCooker.pm
> line 202
> [Tue Dec 08 19:38:58 2009] [error] Can't update 5200: DBD::mysql::st
> execute failed: Lock wait timeout exceeded; try restarting transaction
> [for Statement "UPDATE domain\nSET    domain_category_id = ?\nWHERE
> domain_id=?\n"] at
> /usr/lib/perl5/site_perl/5.8.5/DBIx/ContextualFetch.pm line 52, <FH>
> line 27.\n at /var/www/cod/domains.cgi line 30\n
>
> The relevant section of code is:
>
> {
> ...
>       my $record = COD::DB::Domain->search(domain => $domain)->first;
>        $record->domain_category_id($id);
>        $record->update;
> }
>
> COD::DB::Domain isa Class::DBI. domain_category_id is a FK to another
> table, represented by COD::DB::DomainCategory (but I don't think
> that's relevant here - but could very easily be wrong).
>
> >From what little I understand of Class::DBI I thought the DB
> transaction would be committed when $record goes out of scope, but I
> don't think that's happening, so when I grab the next domain I get the
> same one again (since it's still available for changes), and then it
> starts to chase its tail. If I restart the web server between each
> submission I don't get the lock, but nothing gets changed in the
> database.
>
> So how do I get the record to be updated so the next web call sees the change?
>
> I can make the updates via the MySQL client without any problems, so
> I'm assuming it's a mod_perl/Class::DBI issue.
>
> Apache/2.0.52
> This is perl, v5.8.5 built for x86_64-linux-thread-multi
> Class::DBI version 3.0.17
> MySQL 4.1.22
>
> Any thoughts?
>
> Kevin
> _____________________________________________________________
> Seattle Perl Users Group Mailing List
>     POST TO: spug-list at pm.org
> SUBSCRIPTION: http://mail.pm.org/mailman/listinfo/spug-list
>    MEETINGS: 3rd Tuesdays
>    WEB PAGE: http://seattleperl.org/
>
>