MySQL server has gone away and reconnect for postfix relay.php

[bilias]

Hi, I'm using EFA v4 with mailwatch 1.2.15 My DB is a galera cluster which is controlled by keepalived virtual service.

When a DB node is restarted I have error on the relay php scripts mailwatch_milter_relay.php and mailwatch_postfix_relay.php

Feb 13 12:38:12 efa1 php: PHP Warning: mysqli::query(): MySQL server has gone away in /var/www/html/mailscanner/functions.php on line 1061 Feb 13 12:38:12 efa1 php: Warning: mysqli::query(): MySQL server has gone away in /var/www/html/mailscanner/functions.php on line 1061 Feb 13 12:38:12 efa1 php: Warning: mysqli::query(): Error reading result set's header in /var/www/html/mailscanner/functions.php on line 1061 Feb 13 12:38:12 efa1 php: Fatal error: Uncaught mysqli_sql_exception: MySQL server has gone away in /var/www/html/mailscanner/functions.php:1061 Feb 13 12:38:12 efa1 php: Stack trace: Feb 13 12:38:12 efa1 php: #0 /var/www/html/mailscanner/functions.php(1061): mysqli->query('SELECT id from ...') Feb 13 12:38:12 efa1 php: #1 /usr/bin/mailwatch/tools/Postfix_relay/mailwatch_milter_relay.php(75): dbquery('SELECT id from ...') Feb 13 12:38:12 efa1 php: #2 /usr/bin/mailwatch/tools/Postfix_relay/mailwatch_milter_relay.php(97): doittail('tail -F -n0 /va...') Feb 13 12:38:12 efa1 php: #3 {main} Feb 13 12:38:12 efa1 php: thrown in /var/www/html/mailscanner/functions.php on line 1061 Feb 13 12:38:12 efa1 php: PHP Warning: mysqli::query(): Error reading result set's header in /var/www/html/mailscanner/functions.php on line 1061 Feb 13 12:38:12 efa1 php: tail: write error: Broken pipe Feb 13 12:38:12 efa1 php: tail: write error Feb 13 12:38:12 efa1 php: PHP Fatal error: Uncaught mysqli_sql_exception: MySQL server has gone away in /var/www/html/mailscanner/functions.php:1061#012Stack trace:#012#0 /var/www/html/mailscanner/functions.php(1061): mysqli->query('SELECT id from ...')#012#1 /usr/bin/mailwatch/tools/Postfix_relay/mailwatch_milter_relay.php(75): dbquery('SELECT id from ...')#012#2 /usr/bin/mailwatch/tools/Postfix_relay/mailwatch_milter_relay.php(97): doittail('tail -F -n0 /va...')#012#3 {main}#012 thrown in /var/www/html/mailscanner/functions.php on line 1061 Feb 13 12:38:12 efa1 systemd: milter_relay.service: main process exited, code=exited, status=255/n/a Feb 13 12:38:12 efa1 systemd: Unit milter_relay.service entered failed state.

service is restarted by systemd but I don't like this. what I've done is this on function dbquery()

--- functions.php       2020-02-13 15:22:58.657710695 +0200
+++ /var/www/html/mailscanner/functions.php     2020-02-13 15:09:39.358239586 +0200
@@ -1046,8 +1057,15 @@
     if (DEBUG && headers_sent() && preg_match('/\bselect\b/i', $sql)) {
         dbquerydebug($link, $sql);
     }
-    mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
+    //mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
+    mysqli_report(MYSQLI_REPORT_ERROR);
     $result = $link->query($sql);
+        if ($link->errno == 2006) {
+               $link = dbclose();
+               sleep(5);
+               $link = dbconn();
+               $result = $link->query($sql);
+        }

2006 error code is for "server gone away" The sleep is to wait load balancer to catch the missing server and redirect to other hosts. Can by a tunable in conf.php