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

Kevin Fink kevin-spug at fink.com
Tue Dec 8 20:47:00 PST 2009


Yep, that was it - I figured it out just before I saw your message. Thanks!

Kevin

On Tue, Dec 8, 2009 at 8:42 PM, Brian E. Lozier <brian at massassi.com> wrote:
> You could try explicitly committing the transaction.
>
> $record->db_commit(); # Class::DBI way of committing
>
> Code I write generally doesn't have auto commit enabled, maybe the
> author of that code did something similar.  You can look in the
> connection code and look for AutoCommit => 0.
>
> Brian
>
> 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/
>>
>
>


More information about the spug-list mailing list