lizmap-web-client PHP error if 2 identical pgsql profile set in profiles.ini.php with php8.1

What is the bug?

Original mailing list issue: https://lists.osgeo.org/pipermail/lizmap/2022-July/000517.html

Using php8.1, Lizmap qgis plugin fails to retrieve metadata and throw a 500 http error.

Looking at php error log, I have this PHP Fatal error:

PHP Fatal error: Uncaught Error: PostgreSQL connection has already been closed in /var/www/lizmap-web-client-3.5.4/lib/jelix/plugins/db/pgsql/pgsql.dbconnection.php:179 Stack trace: #0 /var/www/lizmap-web-client-3.5.4/lib/jelix/plugins/db/pgsql/pgsql.dbconnection.php(179): pg_close() #1 /var/www/lizmap-web-client-3.5.4/lib/jelix/db/jDbConnection.class.php(100): pgsqlDbConnection->_disconnect() #2 /var/www/lizmap-web-client-3.5.4/lib/jelix/plugins/db/pgsql/pgsql.dbconnection.php(63): jDbConnection->__destruct() #3 [internal function]: pgsqlDbConnection->__destruct() #4 {main} thrown in /var/www/lizmap-web-client-3.5.4/lib/jelix/plugins/db/pgsql/pgsql.dbconnection.php on line 179

If I use php7.4, plugin can successfully retrieve metadata.

Let me know if you need additional logs.

Steps to reproduce the issue

Use php8.1
Refresh server list in Lizmap qgis plugin

Versions

Lizmap Web Client : 3.5.4
Lizmap plugin : 3.8.3
QGIS Desktop : 3.22.8
QGIS Server : 3.22.8
QGIS Server plugin atlasprint : 3.3.1
QGIS Server plugin lizmap_server : 1.0.2
QGIS Server plugin wfsOutputExtension : 1.7.0

Check Lizmap plugin

[X] I have done the step just before in the Lizmap QGIS desktop plugin before opening this ticket.

QGIS server version, only if the section above doesn't mention the QGIS Server version

3.22.8

Operating system

Debian 10

Browsers

Chrome

Browsers version

Not relevant

Relevant log output

No response

Jul 12 '22 14:07 nicogodet

HI,

I don't reproduce the issue :-/ No problem with PHP 8.1 and Qgis 3.22 with Debian 10. No errors into lizmap/var/log/ ?

Jul 19 '22 10:07 laurentj

Hi, I can't reproduce neither.

Jul 20 '22 07:07 nboisteault

@nboisteault We discussed with Laurent on IRC yesterday trying to investigate. It could be a local issue and I will try a fresh install on fresh server soon.

Jul 20 '22 07:07 nicogodet

Ok I close, reopen if needed.

Jul 20 '22 07:07 nboisteault

I've done a fresh install on new server and I have the exact same error.

Install process

#!/bin/bash

### Config
export PHP_VERSION="8.1"
export LIZMAP_VERSION="3.5.5"
export PGSQL_VERSION="14"

export LIZMAP_DOMAIN=""

export LIZMAP_USER=""
export LIZMAP_PWD=""
export LIZMAP_DB=""
export EMAIL="[email protected]"

export POSTGRES_PWD=""
#LIZMAP_PASS=pass=$(perl -e 'print crypt($ARGV[0], "password")' $LIZMAP_PWD)

export NEEDRESTART_SUSPEND=True

PLATEFORM="$(lsb_release -si)"

### Mise à jour initiale complète du système
sudo apt-get update && sudo apt-get upgrade -y

### Installation des paquets additionnels [partie 1/2]
sudo apt-get --yes install \
  lsb-release \
  ca-certificates \
  apt-transport-https \
  software-properties-common \
  gnupg \
  wget

### Ajout des dépôts sources additionnels
cd /tmp
# PHP
case $PLATEFORM in
  Debian)
  wget -O- https://packages.sury.org/php/apt.gpg | sudo gpg --dearmor -o /usr/share/keyrings/php-apt.gpg
  echo "deb [signed-by=/usr/share/keyrings/php-apt.gpg] https://packages.sury.org/php/ $(lsb_release -sc) main" | sudo tee /etc/apt/sources.list.d/sury-php.list
  ;;

  Ubuntu)
  sudo add-apt-repository -y ppa:ondrej/php
  ;;

esac

# QGIS-LTR
wget -O- https://qgis.org/downloads/qgis-2021.gpg.key | sudo gpg --dearmor -o /usr/share/keyrings/qgis-archive.gpg
echo "deb [signed-by=/usr/share/keyrings/qgis-archive.gpg] https://qgis.org/ubuntu-ltr $(lsb_release -c -s) main" | sudo tee /etc/apt/sources.list.d/qgis-ltr.list

# PostgreSQL
wget -O- https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo gpg --dearmor -o /usr/share/keyrings/postgresql.gpg
echo "deb [signed-by=/usr/share/keyrings/postgresql.gpg] http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -c -s)-pgdg main" | sudo tee /etc/apt/sources.list.d/postgresql.list

### Installation des paquets additionnels [partie 2/2]
# Installation des paquets
sudo apt-get update && sudo apt-get upgrade -y
sudo apt-get --yes install \
  nginx \
  curl \
  openssl \
  php${PHP_VERSION}-cli \
  php${PHP_VERSION}-curl \
  php${PHP_VERSION}-fpm \
  php${PHP_VERSION}-gd \
  php${PHP_VERSION}-intl \
  php${PHP_VERSION}-ldap \
  php${PHP_VERSION}-mbstring \
  php${PHP_VERSION}-pgsql \
  php${PHP_VERSION}-sqlite3 \
  php${PHP_VERSION}-xml \
  php${PHP_VERSION}-zip \
  php${PHP_VERSION}-redis \
  postgresql-${PGSQL_VERSION} \
  postgresql-${PGSQL_VERSION}-postgis-3 \
  postgresql-${PGSQL_VERSION}-postgis-3-scripts \
  postgis \
  qgis-server \
  spawn-fcgi \
  xvfb \
  redis \
  ldap-utils \
  libsqlite3-mod-spatialite \
  certbot \
  python3 \
  python3-pip \
  python3-venv \
  python3-certbot-nginx \
  unzip \
  pure-ftpd \
  pure-ftpd-common \
  # acl pour les droits
case $PHP_VERSION in
  8.0|8.1) ;;
  *)
      sudo apt-get install php${PHP_VERSION}-json;;
esac

sudo apt-get clean

# Installation du module python qgis-plugin-manager
sudo pip3 install qgis-plugin-manager

### Arrêt de quelques services le temps de la configuration
sudo systemctl stop nginx.service
sudo systemctl stop php${PHP_VERSION}-fpm.service

### Création des dossiers nécessaires
sudo mkdir -p /etc/qgis-server
sudo mkdir -p /opt/lizmap/cache
sudo mkdir -p /opt/qgis-server/QGIS/plugins

### Configuration QGIS-server
# Fichier de service
sudo tee /etc/systemd/system/qgis-server.service > /dev/null <<EOF
[Unit]
Description=QGIS server
After=network.target

[Service]
EnvironmentFile=/etc/qgis-server/env

ExecStart=spawn-fcgi -s /run/qgisserver.socket -U www-data -G www-data -n /usr/lib/cgi-bin/qgis_mapserv.fcgi
ExecStop=rm /run/qgisserver.socket

[Install]
WantedBy=multi-user.target

EOF

# Fichier des variables d'environnement
sudo tee /etc/qgis-server/env > /dev/null <<EOF
LANG=fr_FR.UTF-8
QGIS_SERVER_OVERRIDE_SYSTEM_LOCALE=fr_FR.UTF-8
QGIS_SERVER_PARALLEL_RENDERING=1
QGIS_SERVER_MAX_THREADS=8
QGIS_SERVER_LOG_LEVEL=0
QGIS_SERVER_LOG_STDERR=1
QGIS_OPTIONS_PATH=/opt/qgis-server
QGIS_PLUGINPATH=/opt/qgis-server/QGIS/plugins
QGIS_SERVER_IGNORE_BAD_LAYERS=1
QGIS_SERVER_LIZMAP_REVEAL_SETTINGS=True
PGSERVICEFILE=/opt/lizmap/.pg_service.conf  # TODO : adapter

EOF

# Lancement du service
sudo systemctl enable --now qgis-server

### Configuration xvfb
sudo tee /etc/systemd/system/xvfb.service > /dev/null <<EOF
[Unit]
Description=X Virtual Frame Buffer Service
After=network.target

[Service]
ExecStart=/usr/bin/Xvfb :99 -screen 0 1024x768x24 -ac +extension GLX +render -noreset

[Install]
WantedBy=multi-user.target

EOF

# Lancement du service
sudo systemctl enable --now xvfb.service

### Configuration nginx
# Génération du certif Let's Encrypt + ajout du renouvellement auto
certbot certonly --standalone -n -d $LIZMAP_DOMAIN -m $EMAIL --agree-tos
cd /etc/ssl/certs
openssl dhparam -out dhparam.pem 4096
sudo tee /etc/nginx/ssl.conf > /dev/null <<EOF
ssl_dhparam /etc/ssl/certs/dhparam.pem;
ssl_session_timeout 1d;
ssl_session_cache shared:SSL:50m;
ssl_session_tickets off;
ssl_protocols TLSv1.3 TLSv1.2;
ssl_ciphers 'TLS-CHACHA20-POLY1305-SHA256:TLS-AES-256-GCM-SHA384:ECDHE-RSA-AES256-GCM-SHA512:DHE-RSA-AES256-GCM-SHA512:ECDHE-RSA-AES256-GCM-SHA384:DHE-RSA-AES256-GCM-SHA384';
ssl_ecdh_curve X448:secp521r1:secp384r1:prime256v1;
ssl_prefer_server_ciphers on;
ssl_stapling on;
ssl_stapling_verify on;

EOF

# Création fichier qgis-server.conf
sudo tee /etc/nginx/sites-available/qgis-server.conf > /dev/null <<EOF
server {
  listen 80;
  server_name localhost;

  location /qgisserver  {
      gzip           off;
      include        fastcgi_params;
      fastcgi_param  DISPLAY       ":99";
      fastcgi_pass   unix:/var/run/qgisserver.socket;
  }
}

EOF

# Création fichier lizmap.conf
sudo tee /etc/nginx/sites-available/lizmap.conf > /dev/null <<EOF
server {
  listen 80;
  listen [::]:80;
  server_name $LIZMAP_DOMAIN;
EOF
sudo tee -a /etc/nginx/sites-available/lizmap.conf > /dev/null <<'EOF'
  return 301 https://$server_name$request_uri;
}

server {
  listen 443 ssl http2;
  listen [::]:443 ssl http2;
EOF
sudo tee -a /etc/nginx/sites-available/lizmap.conf > /dev/null <<EOF
  server_name $LIZMAP_DOMAIN;

  root /var/www/html/lizmap;
  index index.php index.html index.htm;

  include ssl.conf;
  ssl_certificate     /etc/letsencrypt/live/$LIZMAP_DOMAIN/fullchain.pem;
  ssl_certificate_key /etc/letsencrypt/live/$LIZMAP_DOMAIN/privkey.pem;
EOF
sudo tee -a /etc/nginx/sites-available/lizmap.conf > /dev/null <<'EOF'
  access_log /var/log/nginx/lizmap-access.log combined;
  error_log /var/log/nginx/lizmap-error.log error;

  # compression setting
  gzip_vary on;
  gzip_proxied any;
  gzip_comp_level 5;
  gzip_min_length 100;
  gzip_http_version 1.1;
  gzip_types text/plain text/css application/json application/javascript text/xml application/xml application/xml+rss text/javascript text/json;

  location / {
      try_files $uri $uri/ =404;
  }

  location ~ [^/]\.php(/|$) {
     fastcgi_split_path_info ^(.+\.php)(/.*)$;
     set $path_info $fastcgi_path_info; # because of bug http://trac.nginx.org/nginx/ticket/321
     try_files $fastcgi_script_name =404;
     include fastcgi_params;

     fastcgi_index index.php;
     fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
     fastcgi_param PATH_INFO $path_info;
     fastcgi_param PATH_TRANSLATED $document_root$path_info;
EOF
sudo tee -a /etc/nginx/sites-available/lizmap.conf > /dev/null <<EOF
     fastcgi_pass  unix:/run/php/php${PHP_VERSION}-fpm.sock;
EOF
sudo tee -a /etc/nginx/sites-available/lizmap.conf > /dev/null <<'EOF'
     fastcgi_param SERVER_NAME $http_host;
  }
}

EOF

# Création des liens symboliques
sudo ln -s /etc/nginx/sites-available/qgis-server.conf /etc/nginx/sites-enabled/
sudo ln -s /etc/nginx/sites-available/lizmap.conf /etc/nginx/sites-enabled/

# Redémarrage du service 
sudo systemctl start nginx

### Configuration php
# Changement de la taille maximale de fichier uploadé
sudo sed -i 's/^post_max_size .*/post_max_size = 100M/g' /etc/php/${PHP_VERSION}/fpm/php.ini
sudo sed -i 's/^upload_max_filesize .*/upload_max_filesize = 100M/g' /etc/php/${PHP_VERSION}/fpm/php.ini

# Redémarrage du service
sudo systemctl start php${PHP_VERSION}-fpm

### Configuration PostgreSQL + PostGIS
# Création de l'utilisateur $LIZMAP_USER dans PostgreSQL
sudo -Eu postgres bash -c 'createuser $LIZMAP_USER --superuser'
sudo -Eu postgres bash -c "psql -c \"ALTER USER postgres WITH ENCRYPTED PASSWORD '${POSTGRES_PWD}'\""
sudo -Eu postgres bash -c "psql -c \"ALTER USER ${LIZMAP_USER} WITH ENCRYPTED PASSWORD '${LIZMAP_PWD}'\""
sudo -Eu postgres bash -c "psql -c \"create database ${LIZMAP_DB}\""
sudo -Eu postgres bash -c "psql -c \"grant all privileges on database ${LIZMAP_DB} to ${LIZMAP_USER}\""
sudo -Eu postgres bash -c "psql -d \"${LIZMAP_DB}\" -c \"CREATE EXTENSION postgis\""


# postgresql.conf
sudo sed -i "s/^#listen_addresses = 'localhost'*/listen_addresses = '*'/g" /etc/postgresql/${PGSQL_VERSION}/main/postgresql.conf

# pg_hba.conf
sudo sed -i "/^# IPv4 local connections:/a host ${LIZMAP_DB} ${LIZMAP_USER} 0.0.0.0/0 md5" /etc/postgresql/${PGSQL_VERSION}/main/pg_hba.conf
sudo sed -i "/^# IPv6 local connections:/a host ${LIZMAP_DB} ${LIZMAP_USER} ::/0 md5" /etc/postgresql/${PGSQL_VERSION}/main/pg_hba.conf

# redémarrage de postgresql
sudo systemctl restart postgresql@${PGSQL_VERSION}-main

# Ouverture du port dans le pare-feu
sudo ufw allow 5432

### Configuration Lizmap
# Téléchargement de l'archive
cd /var/www
wget https://github.com/3liz/lizmap-web-client/releases/download/$LIZMAP_VERSION/lizmap-web-client-$LIZMAP_VERSION.zip
unzip lizmap-web-client-$LIZMAP_VERSION.zip
ln -s /var/www/lizmap-web-client-$LIZMAP_VERSION/lizmap/www/ /var/www/html/lizmap
rm lizmap-web-client-$LIZMAP_VERSION.zip

# Téléchargement des plugins
cd /opt/qgis-server/QGIS/plugins
qgis-plugin-manager init
qgis-plugin-manager update
qgis-plugin-manager install 'Lizmap server'
qgis-plugin-manager install atlasprint
qgis-plugin-manager install wfsOutputExtension
chown -R www-data:www-data /opt/qgis-server/QGIS/plugins
systemctl restart qgis-server.service

# Configuration de Lizmap
cd /var/www/lizmap-web-client-$LIZMAP_VERSION/lizmap/var/config
cp profiles.ini.php.dist profiles.ini.php
cp lizmapConfig.ini.php.dist lizmapConfig.ini.php
cp localconfig.ini.php.dist localconfig.ini.php
sudo sed -i "s@^driver=sqlite3*@driver=pgsql@g" profiles.ini.php
sudo sed -i "s@^database=\"var:db/jauth.db\"*@database=$LIZMAP_DB@g" profiles.ini.php
sudo sed -i "/^driver=pgsql/a host=localhost" profiles.ini.php
sudo sed -i "/^host=localhost/a port=5432" profiles.ini.php
sudo sed -i "s@^database=\"var:db/logs.db\"*@database=$LIZMAP_DB@g" profiles.ini.php
sudo sed -i "/^database=$LIZMAP_DB/a user=$LIZMAP_USER" profiles.ini.php
sudo sed -i "/^user=$LIZMAP_USER/a password=$LIZMAP_PWD" profiles.ini.php
sudo sed -i "/^password=$LIZMAP_PWD/a search_path=lizmap,public" profiles.ini.php
sudo sed -i 's@^wmsServerURL.*@wmsServerURL="http://localhost/qgisserver"@g' lizmapConfig.ini.php
sudo sed -i 's@^cacheStorageType=file@cacheStorageType=redis@g' lizmapConfig.ini.php
sudo sed -i 's@^;rootRepositories=.*@rootRepositories="/opt/lizmap/projects/"@g' lizmapConfig.ini.php
cd /var/www/lizmap-web-client-$LIZMAP_VERSION
lizmap/install/set_rights.sh www-data www-data
php lizmap/install/installer.php

(Can't reopen by myself)

Jul 23 '22 19:07 nicogodet

And again, installing php7;4 solves the issue

Jul 23 '22 20:07 nicogodet

Can you provide the https://yourhost_and_path_to_lizmap/index.php/view/app/metadata ?

Do you have an error in qgis server log ? I can't help you to configure it with spawn-fcgi but the docs provide it:

https://docs.qgis.org/3.22/en/docs/server_manual/getting_started.html#spawn-fcgi
https://docs.qgis.org/3.22/en/docs/server_manual/config.html#logging

The PHP Fatal Error provided is more about an issue to reopen database connection than a request error

PHP Fatal error: Uncaught Error: PostgreSQL connection has already been closed in /var/www/lizmap-web-client-3.5.4/lib/jelix/plugins/db/pgsql/pgsql.dbconnection.php:179

You can probably fix this Error with pgsql.auto_reset_persistent

Jul 25 '22 09:07 rldhont

I reproduce the errors in a vagrant VM, with the installation script given above.

Jul 25 '22 11:07 laurentj

@rldhont

{"info":{"version":"3.5.5","date":"2022-07-21"},"dependencies":{"jelix":{"minversion":"1.1.6","maxversion":"1.6.*"}},"qgis_server":{"test":"OK","mime_type":"text\/xml; charset=utf-8","http_code":500,"response":"<ServerException>Project file error. For OWS services: please provide a SERVICE and a MAP parameter pointing to a valid QGIS project file<\/ServerException>\n"},"qgis_server_info":{"metadata":{"commit_id":"a8e9e6fae5","name":"Bia\u0142owie\u017ca","py_qgis_server":false,"py_qgis_server_version":"","version":"3.22.9","version_int":32209},"plugins":{"atlasprint":{"commitNumber":"unknown","commitSha1":"unknown","dateTime":"unknown","version":"3.3.1"},"lizmap_server":{"commitNumber":"1","commitSha1":"063960540d85f30099519dccb8913a1eba2f5c0a","dateTime":"2022-06-29T05:49:09Z","version":"1.0.2"},"wfsOutputExtension":{"commitNumber":"1","commitSha1":"d94b53f82946a1f289b05214a0e64e4d0471d38d","dateTime":"2022-03-24T10:48:18Z","version":"1.7.0"}},"services":["WMS","WFS","WCS","WMTS","ATLAS","EXPRESSION","LIZMAP"]}}

Jul 25 '22 11:07 nicogodet

The PHP error about the postgresql connection, is because in the profiles.ini.php, there are two profiles, jdb:jauth and jdb:lizlog, having the same parameters. Jelix has two connection objects into its pool, but PHP maintain only one connection because of the equality of parameters. So when the two connection objects are destroy, pg_close is called twice, and is called in reality twice on the same real pg connection. So it is closed at the first call of pg_close, but it failed at the second call of pg_close, as the connection is already closed by the first one.

A temporary solution is to declare only one profile : delete the jdb:lizlog section, and add into the jdb section : lizlog=jauth. I will see if I can make an improvement into Jelix to avoid this error. I don't know yet why it doesn't appear with PHP 7.4 and lower. It seems PHP 8.1 manage the postgresql connection differently than PHP 7.

This fix doesn't fix the error with the Lizmap plugin, so I think the errors are unrelated. There is an other problem.

Jul 25 '22 11:07 laurentj

@nicogodet do you still have the error in lizmap plugin ?

Jul 25 '22 12:07 rldhont

with PHP 8.1 when you have a response for https://yourhost_and_path_to_lizmap/index.php/view/app/metadata

Jul 25 '22 12:07 rldhont

With the temporary solution, I do not have an error in lizmap plugin any more, neither in php.log

Jul 25 '22 12:07 nicogodet

This issue is missing some feedbacks. 👻 Please have a look to the discussion, thanks. 🦎

Aug 26 '22 05:08 3liz-bot

Not stale :)

Aug 26 '22 07:08 nicogodet

It is fix into the upcoming 3.6.1

Jan 23 '23 12:01 laurentj

lizmap-web-client lizmap-web-client copied to clipboard

PHP error if 2 identical pgsql profile set in profiles.ini.php with php8.1

What is the bug?

Steps to reproduce the issue

Versions

Check Lizmap plugin

QGIS server version, only if the section above doesn't mention the QGIS Server version

Operating system

Browsers

Browsers version

Relevant log output

lizmap-web-client
lizmap-web-client copied to clipboard