CGI::Session::MySQL lock problem

fireartist has asked for the wisdom of the Perl Monks concerning the following question:

Greetings,
I've searched for CGI::Session problems, found quite a few, but not this particular one.

I've been using CGI::Session with CGI::Application successfully, but now trying to use mysql rather than files to store the data is causing problems.

I'm getting the following error
could not flush: Couldn't acquire lock on id '395c4f3fa5efe60d26082727de8bf6c7'. Lock status: at Images.cgi line 0

The relevent bits of code are:

use strict;
use warnings;
use CGI::Session;
use DBI;

$datasource = 'dbi:mysql:database:hostname';
$user = 'username';
$pass = 'password';


$dbi = DBI->connect($datasource, $user, $pass,
                    { ShowErrorStatement => 1, 
                      RaiseError => 1,
                      PrintError => 1,
                      AutoCommit => 0}));


$session = new CGI::Session(
              "driver:MySQL", 
              $sid,
              {Handle => $dbh}
              ));
[download]

I've tested logging into the database via a console and gaining a lock in the same way as CGI::Session::MySQL is doing select get_lock('unique', 10) and it succeeds ( so does release_lock.)

Does anyone know what's going wrong?

update:
I found this here:

I'm posting this so others may benefit from the clearing up of the confusion...
I was playing with a package to create MySQL tables, and the 'sessions' table created was not precisely as expected by CGI::Session.

- though it doesn't give any suggestions as to how to fix it.
I've dropped the table and recreated it using the exact code given in the CGI::Session docs, to no avail.

Comment on CGI::Session::MySQL lock problem Select or Download Code

Replies are listed 'Best First'.
Re: CGI::Session::MySQL lock problem by erasei (Pilgrim) on Oct 16, 2003 at 14:07 UTC
I don't have a direct answer for you, but I do have a tip that might help. You may already know this, but I'd been using the Perl DBI about 8 months day-in and day-out before I discovered it. `DBI->trace()` will turn on debugging for the mysql connection. You need to pass it a number, `->trace(1)` is basic debugging.. the higher the number, the more debugging. Once you get up around 3 it gets really spammy and you should just log it to a file to read over after the script has run. Set your trace and see what turns up. It might show you what's happening.	[reply] [d/l] [select]
Re: Re: CGI::Session::MySQL lock problem by fireartist (Chaplain) on Oct 16, 2003 at 14:32 UTC
Thank you very much, that was so useful! I checked the trace log, found `!! ERROR: '2006' 'MySQL server has gone away'` [download] checked what was happening just before it, found... `<- disconnect= 1 at Images.pm line 215` [download] So, I add `$self->param('session')->close;` [download] before `$self->param('dbh')->disconnect();` [download] in my `teardown` sub, and it's fixed!	[reply] [d/l] [select]
Re: CGI::Session::MySQL lock problem by fireartist (Chaplain) on Oct 16, 2003 at 15:16 UTC
Another update, just in case someone searching for CGI::Session needs to know this. If you wish to use mysql to store the session data _and_ use a tablename other than 'sessions', then you must explicitly call `use CGI::Session; use CGI::Session::MySQL;` [download] before setting `$CGI::Session::MySQL::TABLE_NAME = 'alt_name';` [download] It's not enough to just `use CGI::Session;` as the docs state. (if you don't, then creating the new CGI::Session object will load the CGI::Session::MySQL module for you, which overwrites the value you set.)	[reply] [d/l] [select]