Update 2.0.21 to 2.0.22 failed Sql Exception

MeinerEiner · September 2, 2023

Hello,

I try to upgrade froxlor to 2.0.22. The previous version was 2.0.21. The update is successful. Login page is display normal. After login I get an error:

Uncaught exception
500 SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') AND `d`.`email_only` = '0' AND `d`.`id` NOT IN ()' at line 3

#0 /var/www/froxlor/lib/Froxlor/Database/Database.php(124): Froxlor\Database\Database::showerror()
#1 /var/www/froxlor/lib/Froxlor/Database/Database.php(102): Froxlor\Database\Database::pexecute()
#2 /var/www/froxlor/lib/Froxlor/Api/Commands/SubDomains.php(1074): Froxlor\Database\Database::pexecute_first()
#3 /var/www/froxlor/lib/Froxlor/UI/Collection.php(132): Froxlor\Api\Commands\SubDomains->listingCount()
#4 /var/www/froxlor/lib/Froxlor/CurrentUser.php(156): Froxlor\UI\Collection->count()
#5 /var/www/froxlor/lib/navigation/00.froxlor.main.php(84): Froxlor\CurrentUser::canAddResource()
#6 /var/www/froxlor/lib/Froxlor/PhpHelper.php(235): include('...')
#7 /var/www/froxlor/lib/init.php(271): Froxlor\PhpHelper::loadConfigArrayDir()
#8 /var/www/froxlor/admin_index.php(27): require('...')
#9 {main}

System Debian Bookworm

Serversoftware
Apache/2.4.57 (Debian)
PHP-Version 8.2.9
MySQL server version 8.0.34
Webserver interface FPM-FCGI
Kernel 6.1.0-11-amd64 (x86_64)

Any suggestions

d00p · September 2, 2023

Hm it's related to the navigation menu and it checks for customer stuff but you're admin, can you try this patch:

diff --git a/lib/navigation/00.froxlor.main.php b/lib/navigation/00.froxlor.main.php
index f322fe85..9863ce54 100644
--- a/lib/navigation/00.froxlor.main.php
+++ b/lib/navigation/00.froxlor.main.php
@@ -38,7 +38,7 @@ return [
                                        'url' => 'customer_email.php?page=emails',
                                        'label' => lng('menue.email.emails'),
                                        'required_resources' => 'emails',
-                                       'add_shortlink' => CurrentUser::canAddResource('emails') ? 'customer_email.php?page=email_domain&action=add' : null,
+                                       'add_shortlink' => !CurrentUser::isAdmin() && CurrentUser::canAddResource('emails') ? 'customer_email.php?page=email_domain&action=add' : null,
                                ],
                                [
                                        'url' => Settings::Get('panel.webmail_url'),
@@ -60,7 +60,7 @@ return [
                                        'url' => 'customer_mysql.php?page=mysqls',
                                        'label' => lng('menue.mysql.databases'),
                                        'required_resources' => 'mysqls',
-                                       'add_shortlink' => CurrentUser::canAddResource('mysqls')? 'customer_mysql.php?page=mysqls&action=add' : null,
+                                       'add_shortlink' => !CurrentUser::isAdmin() && CurrentUser::canAddResource('mysqls')? 'customer_mysql.php?page=mysqls&action=add' : null,
                                ],
                                [
                                        'url' => Settings::Get('panel.phpmyadmin_url'),
@@ -81,7 +81,7 @@ return [
                                [
                                        'url' => 'customer_domains.php?page=domains',
                                        'label' => lng('menue.domains.settings'),
-                                       'add_shortlink' => CurrentUser::canAddResource('subdomains') ? 'customer_domains.php?page=domains&action=add' : null,
+                                       'add_shortlink' => !CurrentUser::isAdmin() && CurrentUser::canAddResource('subdomains') ? 'customer_domains.php?page=domains&action=add' : null,
                                ],
                                [
                                        'url' => 'customer_domains.php?page=sslcertificates',
@@ -98,7 +98,7 @@ return [
                                [
                                        'url' => 'customer_ftp.php?page=accounts',
                                        'label' => lng('menue.ftp.accounts'),
-                                       'add_shortlink' => CurrentUser::canAddResource('ftps') ? 'customer_ftp.php?page=accounts&action=add' : null,
+                                       'add_shortlink' => !CurrentUser::isAdmin() && CurrentUser::canAddResource('ftps') ? 'customer_ftp.php?page=accounts&action=add' : null,
                                ],
                                [
                                        'url' => Settings::Get('panel.webftp_url'),

d00p · September 2, 2023

I've tested this multiple times...do you login as admin or as customer?

nebulo · September 2, 2023

I am experiencing the same or a similar issue with a new installation.

Distributor ID:	Ubuntu
Description:	Ubuntu 22.04.3 LTS
Release:	22.04
Codename:	jammy

Tried it multiple-times with cli only and using the wizard. I am always getting:

Uncaught exception
500 SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') AND `d`.`email_only` = '0' AND `d`.`id` NOT IN ()' at line 3

#0 /var/www/html/froxlor/lib/Froxlor/Database/Database.php(124): Froxlor\Database\Database::showerror()
#1 /var/www/html/froxlor/lib/Froxlor/Database/Database.php(102): Froxlor\Database\Database::pexecute()
#2 /var/www/html/froxlor/lib/Froxlor/Api/Commands/SubDomains.php(1074): Froxlor\Database\Database::pexecute_first()
#3 /var/www/html/froxlor/lib/Froxlor/UI/Collection.php(132): Froxlor\Api\Commands\SubDomains->listingCount()
#4 /var/www/html/froxlor/lib/Froxlor/CurrentUser.php(156): Froxlor\UI\Collection->count()
#5 /var/www/html/froxlor/lib/navigation/00.froxlor.main.php(84): Froxlor\CurrentUser::canAddResource()
#6 /var/www/html/froxlor/lib/Froxlor/PhpHelper.php(235): include('...')
#7 /var/www/html/froxlor/lib/init.php(271): Froxlor\PhpHelper::loadConfigArrayDir()
#8 /var/www/html/froxlor/admin_index.php(27): require('...')
#9 {main}

d00p · September 2, 2023

@nebulo are you also using mysql-8.0 ?

nebulo · September 2, 2023

mysql --version
mysql  Ver 15.1 Distrib 10.6.12-MariaDB, for debian-linux-gnu (x86_64) using  EditLine wrapper

d00p · September 2, 2023

Could you please verify whether this fixes the issue?

diff --git a/lib/Froxlor/Api/Commands/SubDomains.php b/lib/Froxlor/Api/Commands/SubDomains.php
index ed8f49fc..c38fd9bf 100644
--- a/lib/Froxlor/Api/Commands/SubDomains.php
+++ b/lib/Froxlor/Api/Commands/SubDomains.php
@@ -1047,7 +1047,7 @@ class SubDomains extends ApiCommand implements ResourceEntity
                                $custom_list_result = $_custom_list_result['list'];
                        }
                        $customer_ids = [];
-                       $customer_stdsubs = [];
+                       $customer_stdsubs = [0=>0];
                        foreach ($custom_list_result as $customer) {
                                $customer_ids[] = $customer['customerid'];
                                $customer_stdsubs[$customer['customerid']] = $customer['standardsubdomain'];

nebulo · September 2, 2023

I've applied the change, but I am still getting the same Uncaught exception. Do I need to apply this before the installation?

d00p · September 2, 2023

no, weird, i'm not getting that. Is that a totally clean fresh install without any customers / domains / etc. ?

nebulo · September 2, 2023

This seems to fix it. I am able to login, now.

And you're right with your assumption:

Quote

totally clean fresh install without any customers / domains / etc.

nebulo · September 2, 2023

One addition that might be as well related and is not fixed with the above changes. If I execute `/var/www/html/froxlor/bin/froxlor-cli froxlor:config-services -i froxlor-settings.json` I am getting:

PHP Warning:  Trying to access array offset on value of type null in /var/www/html/froxlor/lib/Froxlor/Domain/IpAddr.php on line 78
PHP Warning:  Trying to access array offset on value of type null in /var/www/html/froxlor/lib/Froxlor/Domain/IpAddr.php on line 82
PHP Fatal error:  Uncaught TypeError: implode(): Argument #1 ($pieces) must be of type array, string given in /var/www/html/froxlor/lib/Froxlor/Domain/IpAddr.php:82
Stack trace:
#0 /var/www/html/froxlor/lib/Froxlor/Domain/IpAddr.php(82): implode()
#1 [internal function]: Froxlor\Domain\IpAddr::getIpPortCombinations()
#2 /var/www/html/froxlor/lib/Froxlor/UI/Form.php(200): call_user_func()
#3 /var/www/html/froxlor/lib/Froxlor/UI/Form.php(219): Froxlor\UI\Form::prefetchFormFieldData()
#4 /var/www/html/froxlor/lib/Froxlor/SImExporter.php(177): Froxlor\UI\Form::processForm()
#5 /var/www/html/froxlor/lib/Froxlor/Cli/ConfigServices.php(138): Froxlor\SImExporter::import()
#6 /var/www/html/froxlor/lib/Froxlor/Cli/ConfigServices.php(80): Froxlor\Cli\ConfigServices->importSettings()
#7 /var/www/html/froxlor/vendor/symfony/console/Command/Command.php(298): Froxlor\Cli\ConfigServices->execute()
#8 /var/www/html/froxlor/vendor/symfony/console/Application.php(1040): Symfony\Component\Console\Command\Command->run()
#9 /var/www/html/froxlor/vendor/symfony/console/Application.php(301): Symfony\Component\Console\Application->doRunCommand()
#10 /var/www/html/froxlor/vendor/symfony/console/Application.php(171): Symfony\Component\Console\Application->doRun()
#11 /var/www/html/froxlor/bin/froxlor-cli(66): Symfony\Component\Console\Application->run()
#12 {main}
  thrown in /var/www/html/froxlor/lib/Froxlor/Domain/IpAddr.php on line 82

d00p · September 2, 2023

Can you please provide your froxlor-settings.json? (If required, you can do so privately)

d00p · September 2, 2023

Ah from cli, there's no user-session there, try this:

diff --git a/lib/Froxlor/Domain/IpAddr.php b/lib/Froxlor/Domain/IpAddr.php
index 1c6f5cdf..c53eeac7 100644
--- a/lib/Froxlor/Domain/IpAddr.php
+++ b/lib/Froxlor/Domain/IpAddr.php
@@ -55,6 +55,7 @@ class IpAddr

        /**
         * @return array
+        * @throws \Exception
         */
        public static function getSslIpPortCombinations(): array
        {
@@ -75,7 +76,7 @@ class IpAddr
                $additional_conditions_params = [];
                $additional_conditions_array = [];

-               if ($userinfo['ip'] != '-1') {
+               if (!empty($userinfo) && $userinfo['ip'] != '-1') {
                        $admin_ip_stmt = Database::prepare("
                                SELECT `id`, `ip`, `port` FROM `" . TABLE_PANEL_IPSANDPORTS . "` WHERE `id` = IN (:ipid)
                        ");

nebulo · September 2, 2023

Thank you! This seems to fix it. At least I got:

Successfully imported settings from 'froxlor-settings.json'

MeinerEiner · September 3, 2023

So, it seems to be fixed. Is there a new release planned with that fix? And yes we have a multiple running systems with customers.

d00p · September 3, 2023

Yes, already committed that yesterday. There will most likely be a bug fix release this week

MeinerEiner · September 10, 2023

It is working with version 2.0.23! Thank's alot.

Sign In

Update 2.0.21 to 2.0.22 failed Sql Exception

Question

Link to comment

Share on other sites

16 answers to this question

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Create an account or sign in to comment

Create an account

Sign in