Upgrade 2.8.5.5 -> 2.8.6.1 - SQL ERROR

[sriccio]

Hello LibreBooking community,

I'm trying to troubleshoot an issue after upgrading from 2.8.5.5 to 2.8.6.1 (PHP 7.4 -> 8.1).

After the upgrade, the app displays an "Unknown error".

The only log entry I was able to get is by activating the sql.log and it shows:

[2024-04-23T07:39:35.540047+02:00] sql.ERROR: [User=  ()] MySql Query: SELECT `a`.*,                    (SELECT GROUP_CONCAT(`ag`.`group_id`) FROM `announcement_groups` `ag` WHERE `ag`.`announcementid` = `a`.`announcementid`) as `group_ids`,              (SELECT GROUP_CONCAT(`ar`.`resource_id`) FROM `announcement_resources` `ar` WHERE `ar`.`announcementid` = `a`.`announcementid`) as `resource_ids`                        FROM `announcements` `a`                WHERE ((`start_date` <= '2024-04-23 05:39:35' AND `end_date` >= '2024-04-23 05:39:35') OR (`end_date` IS NULL)) AND ('5' = -1 OR '5' = `display_page`)          ORDER BY `priority`, `start_date`, `end_date`; [] []

I tried the query in MySQL client and it reports no error. There is no row returnet though.

mysql> SELECT `a`.*,                    (SELECT GROUP_CONCAT(`ag`.`group_id`) FROM `announcement_groups` `ag` WHERE `ag`.`announcementid` = `a`.`announcementid`) as `group_ids`,              (SELECT GROUP_CONCAT(`ar`.`resource_id`) FROM `announcement_resources` `ar` WHERE `ar`.`announcementid` = `a`.`announcementid`) as `resource_ids`                        FROM `announcements` `a`                WHERE ((`start_date` <= '2024-04-23 05:39:35' AND `end_date` >= '2024-04-23 05:39:35') OR (`end_date` IS NULL))
AND ('5' = -1 OR '5' = `display_page`)          ORDER BY `priority`, `start_date`, `end_date`;
Empty set (0.00 sec)

Can it be that the query returning an empty set is triggering the error ? Anyone knows what could be wrong ?

Thanks a lot for your advice!

[sriccio]

Hello,

Some news about this one. After a lot of trial and error, I found out that the fix was to manually add

mysqli_report(MYSQLI_REPORT_OFF);

In the app config.php after the upgrade.

[sriccio]

Unfortunately, after fixing the issue manually, when saving the config from the app, the line goes away.

I see this commit but it looks like it's not available in 2.8.6.1 which is latest release.

https://github.com/LibreBooking/app/commit/c11d11ce1c522fdd38e6c94269dbe77228b16680

[PVTGoesen]

Hello, is this the only error in the sql.log?

I try to use PHP 8.1 and MySQL 8.0 I get this error too, but the login page loads. When I login then more errors show up, like:

sql.ERROR: [User=  ()] MySql Query: SELECT * FROM `user_email_preferences` WHERE `user_id` = '24'; [] []

I've checked the sql-server log and the commands are been executet:

Connect booked@xxx.com on booked using TCP/IP
Init DB booked
Query   SET NAMES utf8
Query   SET SESSION sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'
Query   SELECT `name`, `value` FROM `user_preferences` WHERE `user_id` = '24'
Quit

So even if the SQL-server sends a valid query an error appears in the sql.log. Can you confirm this behaviour?

[sriccio]

Hello, I fixed the issue by adding manually

mysqli_report(MYSQLI_REPORT_OFF); at the beginning of config.php

I also needed to patch this file with this change:

https://github.com/LibreBooking/app/commit/c11d11ce1c522fdd38e6c94269dbe77228b16680

Otherwise as soon as you trigger a config change it will remove the previously added line in the config.php

Maybe this can help you too

[PVTGoesen]

OK I did both, but nothing changed. Which version of MySQL do you use?

[sriccio]

Server version: 8.0.33-cll-lve MySQL Community Server - GPL

[PVTGoesen]

OK, thats the same I use. Thanks for your help!