php-mqtt stops receiving messages after some time the publisher is offline

Question

I set up a systemd service (in Ubuntu 22.04) in order to process messages from Mosquitto using the php-mqtt library. Here my whole code:

<?php header("Access-Control-Allow-Origin: *");
require 'database.php'; 
require('vendor/autoload.php');

use PhpMqtt\Client\MqttClient;
use PhpMqtt\Client\ConnectionSettings;

ini_set('display_errors', 1);
error_reporting(E_ALL);

$server   = '<server>';
$port     = 1883;
$clientId = '<id>';
$username = '<user>';
$password = '<password>';

$connectionSettings = (new ConnectionSettings)
  ->setUsername($username)
  ->setPassword($password)
  ->setKeepAliveInterval(60)
  ->setLastWillQualityOfService(1)
  ->setConnectTimeout(60)
  ->setMaxReconnectAttempts(PHP_INT_MAX)
  ->setReconnectAutomatically(true);

$mqtt = new MqttClient($server, $port, $clientId, MqttClient::MQTT_3_1);
$mqtt->connect($connectionSettings, false);

function append($db, $sample) {
  try {
    $stmt = $db->prepare("INSERT INTO sensors (sampled, temperature, humidity) VALUES (CURRENT_TIMESTAMP(), :temperature, :humidity);");
    $stmt->execute($sample);
  } catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
  }    
}

$mqtt->subscribe('<topic1>', function ($topic, $message) use (&$data, &$dbApp) {
  printf("Received message on topic [%s]: %s\n", $topic, $message);

  if ($message === 'Offline') {
    $sample = [
      'temperature' => NULL,
      'humidity' => NULL
    ];

    append($dbApp, $sample);
  }

}, 0);

$mqtt->subscribe('<topic2>', function ($topic, $message) use (&$data, &$dbApp) {
  printf("Received message on topic [%s]: %s\n", $topic, $message);
  $obj = json_decode($message);

  $sample = [
    'temperature' => floatval($obj->Temperature),
    'humidity' => floatval($obj->Humidity)
  ];
  
  append($dbApp, $sample);
}, 0);
   
$mqtt->loop(true);
$mqtt->close();
$dbApp = null;      

and here the systemd unit:

[Unit]
Description=MQTT receiver
 
[Service]
Type=simple
ExecStart=/usr/bin/php /usr/share/nginx/html/mqtt.php
Restart=always
RestartSec=10
 
[Install]
WantedBy=multi-user.target

Here the observed behavior:

1. start the broker
2. start the mqtt.service
3. power on the publisher (a IoT device) that sends on <topic1> the offline messages as LWT and on <topic2> the actual data
4. check all is working fine: OK
5. power off the publisher and wait for the disconnection (the LWT message is received): OK
6. after some time turn on again and confirm it's still working as expected: OK

the problem arises if the duration of "some time" in point 6 is several hours (like overnight). On the following morning I power on the sensor but the PHP page does not receive the messages anymore (i.e. no more "Received message on topic...").

Subscribing to the topics using mosquitto_sub leads to receive the messages - hence the broker is running.

The systemd service is active and no errors are shown, I see the last lines of the previous day. Restarting the service leads to receive again the messages.

This seems a good indicator I made a mistake in my code, but I cannot find where.

Answer 1

Based upon the good suggestion of user Namoshek, I solved changing the code as follow:

database.php

<?php

$SERVER_APP = "localhost";
$DATABASE_APP = "<database>";
$USERNAME_APP = "<user>";
$PASSWORD_APP = "<password>";

function db_connect() {
    global $SERVER_APP, $DATABASE_APP, $USERNAME_APP, $PASSWORD_APP;

    try {
        $dbApp = new PDO("mysql:host=$SERVER_APP;dbname=$DATABASE_APP", $USERNAME_APP, $PASSWORD_APP);
        $dbApp->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $dbApp->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
        return $dbApp;
    } catch (PDOException $e) {
        echo "Connection to APP database failed:" . PHP_EOL . $e->getMessage();
    }
}

mqtt.php

<?php header("Access-Control-Allow-Origin: *");
require 'database.php';
require('vendor/autoload.php');

use PhpMqtt\Client\MqttClient;
use PhpMqtt\Client\ConnectionSettings;

ini_set('display_errors', 1);
error_reporting(E_ALL);

$server   = '<address>';
$port     = 1883;
$clientId = '<id>';
$username = '<user>';
$password = '<password>';

$connectionSettings = (new ConnectionSettings)
  ->setUsername($username)
  ->setPassword($password)
  ->setKeepAliveInterval(60)
  ->setConnectTimeout(60)
  ->setMaxReconnectAttempts(PHP_INT_MAX)
  ->setReconnectAutomatically(true);

$mqtt = new MqttClient($server, $port, $clientId, MqttClient::MQTT_3_1_1);
$mqtt->connect($connectionSettings, false);

function append($sample) {
  $dbApp = db_connect();   
  try {
    $stmt = $dbApp->prepare("INSERT INTO sensors (sampled, temperature, humidity) VALUES (CURRENT_TIMESTAMP(), :temperature, :humidity);");
    $stmt->execute($sample);
  } catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
  }    
  $dbApp = null;      
}

$mqtt->subscribe('<topic>', function ($topic, $message) {
  printf("Received message on topic [%s]: %s\n", $topic, $message);
  $obj = json_decode($message);

  $sample = [
    'temperature' => floatval($obj->Temperature),
    'humidity' => floatval($obj->Humidity)
  ];
  
  append($sample);
});
   
$mqtt->loop(true);
$mqtt->close();

Now the connection to the database is created every time I need to append a record and closed immediately after.