Lizmap-web-client: Maps do not display after login in PostgreSQL / Lizmap 3.4.0

[geomoes]

I did an update and installed lizmap 3.4.0 in a new folder keeping the same structure of my old 3.3.3-version (following the instructions on https://docs.lizmap.com/current/en/install/upgrade.html#upgrading-from-lizmap-3-x-versions.
Authetification is done trough PostgreSQL-database.

Without loging in, I'm able to display all public projects and open them without any issues (the Monpellier demo maps just open fine, as well as my own public maps).
When I log in with my credentials (from the 3.3.3. version) accessing the same database, I can log in and all the projects are there and I can access the admin panel. When I try to open a map (even the Montepellier demo maps) it fails. I just see the map is loading with Lizmap-Logo. In the case I choose the Montpellier demo I can see the header of the Montpellier project in the background.

The Lizmap Web Client application error log says:
2020-12-24 16:32:27 xxx.xxx.236.209 error 2020-12-24 16:32:27 [403] unzulässige Anfrage(ERROR: current transaction is aborted, commands ignored until end of transaction block(SELECT "ug"."login", "ug"."id_aclgrp" FROM "jacl2_user_group" AS "ug" WHERE "ug"."login" = 'admin')) /var/www/lizmap-web-client-3.4.0/lib/jelix/plugins/db/pgsql/pgsql.dbconnection.php 180

I checked the lizmap PostgreSQL database and all the tables are there and the user exists. The query executed in DBeaver returns the correct login and id_aclgrp.

This is a updated version of the issue from last august #1789

Lizmap 3.4.0
QGIS Lizmap-Plugin 3.3.3
QGIS Desktop 3.10.9-A Coruña
QGIS FCGI server 3.10.12-A Coruña
PostgreSQL 12.5
PostGIS 3.1
Ubuntu 18.04.3:
OS (MacOS):
Browser (Firefox, Chrome):

[DavidGhedini]

Having this issue as well.

[kaloyan13]

This is the exact error from LM 3.4.0 log file, when trying to load a map, as admin. Using PG 13.1
2021-01-03 11:55:22 10.0.28.1 error 2021-01-03 11:55:22 [403] invalid query (ERROR: current transaction is aborted, commands ignored until end of transaction block(SELECT "ug"."login", "ug"."id_aclgrp" FROM "jacl2_ user_group" AS "ug" WHERE "ug"."login" = 'admin')) /var/www/lizmap-web-client-3.4.0/lib/jelix/plugins/db/pgsql/pgsql.dbconnection.php 180

And this is the PostgreSQL log
2021-01-03 11:10:42.275 UTC [1416] lizmap@lizmap ERROR: relation "lizmap_search" does not exist at character 15 2021-01-03 11:10:42.275 UTC [1416] lizmap@lizmap STATEMENT: SELECT * FROM lizmap_search LIMIT 0; 2021-01-03 11:10:42.276 UTC [1416] lizmap@lizmap ERROR: current transaction is aborted, commands ignored until end of transaction block 2021-01-03 11:10:42.276 UTC [1416] lizmap@lizmap STATEMENT: SELECT "ug"."login", "ug"."id_aclgrp" FROM "jacl2_user_group" AS "ug" WHERE "ug"."login" = 'admin'

[Gustry]

This is the exact error from LM 3.4.0 log file, when trying to load a map, as admin. Using PG 13.1

I have LWC 3.4 installed as well, users are managed in Postgresql. I have connected as admin and I could load my project. I couldn't replicate. Is there any special steps ?
I will try again later on another instance to double check.

[kaloyan13]

The issue comes from lizmap/modules/lizmap/classes/lizmapFtsSearch.listener.php:45
@$cnx->query('SELECT * FROM lizmap_search LIMIT 0;');
In my lizmap database the lizmap_search table doesn't exist, and puts the PG connection in an error state.
Next query (SELECT "ug"."login" ...) fails because of that, and user can't login.

[mdouchin]

Thanks a lot for your help @kaloyan13 We will propose a fix for this

[geomoes]

Thanks @kaloyan13. I just tried to fix it with the creation of a dummy table lizmap_search:

create table lizmap_search
(
id serial not NULL,
something varchar null,
PRIMARY KEY (id)
); 

Now I can access the projects when logged in.

[mdouchin]

The workaround would be better by creating a table with the same structure as the one expected by Lizmap Web Client

CREATE TABLE IF NOT EXISTS public.lizmap_search (
    item_layer text,
    item_label text,
    item_filter text,
    item_project text,
    geom geometry(point, 3857)
);

https://docs.lizmap.com/next/en/publish/configuration/spatial_search.html#postgresql-search

[kaloyan13]

Thank you. That was missing from my install script.

https://docs.lizmap.com/next/en/publish/configuration/spatial_search.html#postgresql-search

[geomoes]

Ok, merci @mdouchin.

[Gustry]

@geomoes I reopen, because it seems a bug, even if you got a workaround for now.

[mdouchin]

Working on it right now

[mdouchin]

@kaloyan13 @geomoes I cannot reproduce the issue, but I tried to add more tests in the function. Could you please temporarily rename you fake lizmap_search table into lizmap_search_hide and replace the function checkLizmapFts in lizmap/modules/lizmap/classes/lizmapFtsSearch.listener.php with the following code and try if it solves your previous issue with transaction ?

    protected function checkLizmapFts()
    {
        $ok = false;
        $profile = 'search';

        try {
            // try to get the specific search profile to do not rebuild it
            jProfiles::get('jdb', $profile, true);
        } catch (Exception $e) {
            // else use default
            $profile = null;
        }

        // Lizmap search available only for PostgreSQL
        $cnx = jDb::getConnection($profile);
        $dbms = $cnx->dbms;
        if ($dbms != 'pgsql') {
            return false;
        }

        // Use transaction to avoid; ERROR: current transaction is aborted
        // https://github.com/3liz/lizmap-web-client/issues/2008
        $cnx->beginTransaction();

        // Try to get data from lizmap_fts table / view / materialized view
        try {
            // try to get the specific search profile to do not rebuild it
            $cnx->query('SELECT * FROM lizmap_search LIMIT 0;');
            $cnx->commit();
            $ok = true;
        } catch (Exception $e) {
            $cnx->rollback();
            $ok = false;
        }

        return $ok;
    }

[mdouchin]

I created a PR for this issue. Please test #2034

[geomoes]

Bonjour,

I tried the proposed changes in lizmapFtsSearch.listener.php and and even though the lizmap_search_hide table is renamed I'm able to log in to Lizmap 3.4.0 without any issues. So for me this seems to fix the problem.

Thanks a lot.

[nboisteault]

@geomoes Bonjour, Does your logs are empty too?

[geomoes]

Yes I checked the logs in Lizmap and there are no entries. So everything seems to work properly.

[mdouchin]

Thanks all for your help. We just merged the PR. This fix will be available in next point release