SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    perl MySQL connection error

    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.

    Code:
    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.

    Code:
     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

    Code:
    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

  2. #2
    SitePoint Member
    Join Date
    Aug 2011
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  3. #3
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •