in reply to Peculiar Behaviour with DBI and MySQL

Sorry, I screwed up the formatting.
This may be a little easier to read:

Hello Monks,
Long time fan, first time poster.

I have a Perl script that collects a webpage and picks out particular pieces of code in order to draw up an array of links. These links are then inserted into a MySQL database.

Next, a 'foreach' loop takes each URL from the array and analyses that page creating a hash where the hash key is the column name in a MySQL table and the hash value is the entry for that column.

Finally, another SQL statement, via DBI, updates the database with all of the hash values. I had this working fine before but now, after moving from my dev. server to a real one, find that the script stops updating the database after ten of these updates (11 updates in total including the initial one).

I appreciate that this could be a MySQL problem but the setup for the two servers appears to be identical so I suspect it may be the Perl.

Any help greatly appreciated.

Thanks Chris

  • Comment on Re: Peculiar Behaviour with DBI and MySQL

Replies are listed 'Best First'.
Re^2: Peculiar Behaviour with DBI and MySQL
by pbeckingham (Parson) on Jul 22, 2004 at 14:02 UTC

    If you could post your code, we would help. Could you provide the code that interacts with MySQL? Specifically the code preceding the INSERT.

[reply]
[d/l]

      Thanks both of you for the quick replies.

      The code for updating the database is shown below, jlongino: thanks for the pointer, I'm not much of an admin and it all looks to be set up the same. I'll keep on it though.

      %hash: $ColName => $Value
      The subroutine below is called with $page_url to tell it where to apply the update. The hash is global.

      sub update_database {
    my($page_url)=@_;
    my(@categorys);
    my($category);
    my($dbfield);

    @categorys = keys(%hash);
    foreach $category (@categorys)
    {
        # Key is prefixed to match database
        $dbfield="Prefix".$category;
        # Establish database connection with MySQL
        my $dsn = 'DBI:mysql:*database*:localhost';
        my $db_user_name = '*username*';
        my $db_password = '*password*';
        my $dbh = DBI->connect($dsn, $db_user_name, $db_password);
        my $sth = $dbh->prepare(qq{
            update table set $dbfield = '$hash{$category}' where pageu
+rl = '$page_url'
        });
        $sth->execute();
        $sth->finish();
    }
}

      [download]
[reply]
[d/l]

        It is almost certainly some limit on the number of connections that you have to the database - I think you will find that if you move the : 

               
 my $dsn = 'DBI:mysql:*database*:localhost';
 my $db_user_name = '*username*';
 my $db_password = '*password*';
 my $dbh = DBI->connect($dsn, $db_user_name, $db_password);

        [download]
        outside the foreach loop then all your troubles will go away.

        /J\

[reply]
[d/l]

        Seems to me that there is a $dbh->disconnect missing, so either add the disconnect inside the loop, or do what gellyfish says, and then add a disconnect outside the loop.

[reply]
[d/l]
[select]
        As mentioned above the maximum number of connections per mysql login could be a factor. I think this is the max_user_connections option in your my.cnf file (maybe my.ini under windows). Also max_connection applies to the maximum number of connections to mysqld over all login accounts. Check out this mysql.com page for info on system variables.
[reply]

In Section Seekers of Perl Wisdom