perl MySQL connection error

[IBazz]

Hi,

I run a setup running on perl and MySQL. I get the following error in one of my connections that has been working for about 2 years.

main_database_control_panel.pl: Issuing rollback() for database handle being DESTROY'd without explicit disconnect()., referer: http://cms.thechrissystem.com/cgi-bin/client_control_panel2/NewCustomerLogin.pl

Firstly, the knowledge of the error arose only when sessions stopped being passed correctly from the login page to the next script. Could that error be connected to the sessions not being passed?

Please help me work out how to fix the error so we can establish if this affects the passing of session data from script to script.

The scripting is as follows.

 require 'main_database_control_panel.pl';
  #my $connect = delete_insert_select_update_connection();
  my $connect = control_panel_connection();
  
queryDB($login{'username'}, $connect);


sub queryDB {
 
  my ($user, $connect) = @_;

  #my $connect = connect_to_db();
  

  my $sth = $connect->prepare ("
     SELECT
          c.title
		, c.user_name
        , c.stored_password
	    , c.user_language
	    , c.position
	    , c.first_name
		, c.last_name
		, c.position
		, pn.mobile_number
		, pn.email_address
		
     FROM contact c
   inner
     JOIN contact_phone_numbers AS cpn
       on cpn.user_name = c.user_name	 
  inner
     JOIN phone_numbers AS pn
       on pn.id = cpn.phone_id	 
    where c.user_name = ?
                            ") or die " prepare statement failed: $DBI::errstr\n"; 


  $sth->execute($user);
   
  
  my ( $stored_title
     , $stored_user_name
	 , $stored_password
	 , $user_language
	 , $user_position
	 , $first_name
	 , $last_name
	 , $job_title
	 , $mobile_number
	 , $email_address
	 ) = $sth->fetchrow_array;
  #$session->param('business_id',$business_id);
 $sth->finish;
  $connect->disconnect;
  return ( $stored_title
         , $stored_user_name
		 , $stored_password
		 , $user_language
		 , $user_position
		 , $first_name
		 , $last_name
		 , $job_title
		 , $mobile_number
		 , $email_address
		 );
}

and the connection scripts is here

sub control_panel_connection {

  my $db		= 'database_name';
  my $srv		= 'localhost';
  my $user		= '***************';
  my $pass		= '***************';
  my $port		= '3306';
  #$user		= shift;
  my $dbh = DBI->connect("DBI:mysql:$db:$srv", $user, $pass,
                        {'RaiseError' => 0, 'PrintError' => 0, 'AutoCommit' => 1 }) or die "Connection Failed: $db DB on $srv\n\t$DBI::errstr\n";

return $dbh;


}

bazz

[dsheroh]

Well, that's certainly unusual, in any case... For some reason, your database connection apparently has autocommit turned off (despite it being on by default and your control_panel_connection excerpt showing DBI being initialized with "AutoCommit => 1"). With autocommit off, you need to explicitly call $connect->commit before calling $connect->disconnect (or allowing $connect to go out of scope) or else any pending transactions will be automatically rolled back when the connection is closed.

Note, however, that:

a) Transactions are only relevant for queries which change the database and the code you showed only contains a single SELECT query. That won't create a transaction, so there isn't anything to commit or roll back in this case.

b) The warning states that the database handle is being DESTROYed without an explicit disconnect, but the code you showed does explicitly call $connect->disconnect.

That warning is coming from somewhere else in your program. Given your comment about the problem being discovered when you started having problems with passing sessions from page to page, I'd start by looking at the sections of code which deal with inserting session records into the database and updating existing session records. If those subs are exiting early (perhaps due to an error, perhaps by calling return), that would cause the database handle to go out of scope and be DESTROYed implicitly and, if that happens while the query to create the login session belongs to an uncommitted transaction, that would cause both the warning you asked about and the session problem you said you're having.

[IBazz]

Thanks for the response.

It turns out that the scripting is OK. The tmp dir on the server which contains the session files, was not being maintained correctly. a cron job was not doing as intended and so the tmp dir became too full and busted everything.

Now cleaned out, we are all tickety-boo again