phpmqttsystemdmosquitto

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


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.


Solution

  • 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.