[AMaViS-user] amavisd-new-2.2.1 sql connect bug

This is a multi-part message in MIME format.
--------------020903000008000508050700
Content-Type: text/plain; charset=ISO-8859-15; format=flowed
Content-Transfer-Encoding: 7bit

Hello everybody!

For the last weeks I observed some errors in amavisd-new-2.2.1 with
mysql support:

a) "lookup_sql: 2013, Lost connection to MySQL server during query"
This error occurs every time an amavis proccess established a connection
to mysql and idled for more than 28800 seconds. After that time period
the connection is closed by mysql. amavisd-new still thinks the
connection is established and runs into an error.

b) "lookup_sql: 19, fetch() without execute()"
Because there is no errorhandling in case that $sth->execute(); (line
8659) fails the a_ref=$sth->fetchrow_arrayref (line 8660) is executed
and amavisd runs into an error.

After the eval {} the problem is logged (line 8682) and the mail will be
rejected with an 450 error.

Both errors set the sql_connected variable to zero and on the next run
the process will reconnect to the sql db.
The errors are not fatal but delay the message.


Both problems were mentioned previously on this ML but without a solution.

The attatched file contains a dirty hack to patch these errors.
Use it on your own risk!


Regards,
Simon







--------------020903000008000508050700
Content-Type: text/plain;
name="amavisd-sql.patch"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
filename="amavisd-sql.patch"

--- amavisd.old 2005-02-27 14:54:06.000000000 +0100
+++ amavisd 2005-02-27 15:04:50.000000000 +0100
@@ -8535,6 +8535,20 @@
$dbh;
}

+# dirty hack: sql connect function
+sub SQLHACK() {
+ my($sql_dbh) = connect_to_sql(@lookup_sql_dsn);
+ section_time('sql-connect');
+ defined($sql_dbh) or die "SQL server(s) not reachable";
+ $sql_dbh->{'RaiseError'} = 1;
+ #dirty hack to prevent: "lookup_sql: 2013, Lost connection to MySQL server during query"
+ $sql_dbh->{'mysql_auto_reconnect'} = 1;
+ $Amavis::sql_policy->store_dbh($sql_dbh, $sql_select_policy)
+ if defined $sql_select_policy;
+ $Amavis::sql_wblist->store_dbh($sql_dbh, $sql_select_white_black_list)
+ if defined $sql_select_white_black_list;
+}
+
# return a new Lookup::SQL object to contain DBI handle and prepared selects
sub new {
my($class) = @_; bless {}, $class;
@@ -8620,14 +8634,8 @@
}
}
if (!$sql_connected) {
- my($sql_dbh) = connect_to_sql(@lookup_sql_dsn);
- section_time('sql-connect');
- defined($sql_dbh) or die "SQL server(s) not reachable";
- $sql_dbh->{'RaiseError'} = 1;
- $Amavis::sql_policy->store_dbh($sql_dbh, $sql_select_policy)
- if defined $sql_select_policy;
- $Amavis::sql_wblist->store_dbh($sql_dbh, $sql_select_white_black_list)
- if defined $sql_select_white_black_list;
+# dirty hack: execute sql connect function (defined above)
+SQLHACK();
}
my($is_local); # $local_domains_sql is not looked up to avoid recursion!
$is_local = Amavis::Lookup::lookup(0,$addr,
@@ -8656,7 +8664,12 @@
my($a_ref,$found); my($match) = {};
eval {
snmp_count('OpsSqlSelect');
- $sth->execute(@pos_args); # do the query
+ # dirty hack to prevent: "lookup_sql: 19, fetch() without execute()"
+ if(!$sth->execute(@pos_args)) { # do the query
+ do_log(5,"sth->execute failed: SQLHACK() executed...");
+ SQLHACK(); # exec SQLHACK
+ $sth->execute(@pos_args);
+ }
while ( defined($a_ref=$sth->fetchrow_arrayref) ) { # fetch query results
my(@names) = @{$sth->{NAME_lc}};
$match = {}; @$match{@names} = @$a_ref;

--------------020903000008000508050700--


-------------------------------------------------------
SF email is sponsored by - The IT Product Guide
Read honest & candid reviews on hundreds of IT Products from real users.
Discover which products truly live up to the hype. Start reading now.
http://ads.osdn.com/?ad_id=6595&alloc_id=14396&op=click
_______________________________________________
AMaViS-user mailing list
AMaViS-user@lists.sourceforge.net
https://lists.sourceforge.net/lists/...fo/amavis-user
AMaViS-FAQ:http://www.amavis.org/amavis-faq.php3
AMaViS-HowTos:http://www.amavis.org/howto/