search

3liz / lizmap-web-client

Transfer a QGIS project on a server, Lizmap is providing the web interface to browse it
https://www.lizmap.com
Mozilla Public License 2.0
254 stars 143 forks source link

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

Closed nicogodet closed 1 year ago

nicogodet commented 2 years ago

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

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

Let me know if you need additional logs.

Steps to reproduce the issue

  1. Use php8.1
  2. Refresh server list in Lizmap qgis plugin

Versions

Check Lizmap plugin

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

laurentj commented 2 years ago

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/ ?

nboisteault commented 2 years ago

Hi, I can't reproduce neither.

nicogodet commented 2 years ago

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

nboisteault commented 2 years ago

Ok I close, reopen if needed.

nicogodet commented 2 years ago

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

Install process ```bash #!/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="test@test.fr" 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 < /dev/null < /dev/null < /dev/null < /dev/null < /dev/null < /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 < /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 < /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)

nicogodet commented 2 years ago

And again, installing php7;4 solves the issue

rldhont commented 2 years ago

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:

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

laurentj commented 2 years ago

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

nicogodet commented 2 years ago

@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"]}}
laurentj commented 2 years ago

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.

rldhont commented 2 years ago

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

rldhont commented 2 years ago

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

nicogodet commented 2 years ago

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

3liz-bot commented 2 years ago

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

nicogodet commented 2 years ago

Not stale :)

laurentj commented 1 year ago

It is fix into the upcoming 3.6.1