Re^3: Peculiar Behaviour with DBI and MySQL

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]

Comment on Re^3: Peculiar Behaviour with DBI and MySQL Download Code

Replies are listed 'Best First'.
Re^4: Peculiar Behaviour with DBI and MySQL by gellyfish (Monsignor) on Jul 22, 2004 at 15:24 UTC
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]
Re^4: Peculiar Behaviour with DBI and MySQL by pbeckingham (Parson) on Jul 22, 2004 at 15:52 UTC
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]
Re^4: Peculiar Behaviour with DBI and MySQL by superfrink (Curate) on Jul 23, 2004 at 01:49 UTC
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]