MySQL Query Error after upgrading to 2.9.0

Icinga Web 2
1

Good morning,
i’d upgraded ICINGA Web to 2.9.0
If i relogin with the switch “Remember me” i’d got the following error

#0 [internal function]: PDO->exec(String)
#1 /usr/share/icinga-php/ipl/vendor/ipl/sql/src/Connection.php(82): call_user_func_array(Array, Array)
#2 /usr/share/icinga-php/ipl/vendor/ipl/sql/src/Connection.php(164): ipl\Sql\Connection->__call(String, Array)
#3 /usr/share/icinga-php/ipl/vendor/ipl/sql/src/Connection.php(399): ipl\Sql\Connection->connect()
#4 /usr/share/icinga-php/ipl/vendor/ipl/sql/src/Connection.php(438): ipl\Sql\Connection->prepexec(String)
#5 /usr/share/php/Icinga/Web/RememberMe.php(231): ipl\Sql\Connection->insert(String, Array)
#6 /usr/share/icingaweb2/application/forms/Authentication/LoginForm.php(142): Icinga\Web\RememberMe->persist()
#7 /usr/share/php/Icinga/Web/Form.php(1178): Icinga\Forms\Authentication\LoginForm->onSuccess()
#8 /usr/share/icingaweb2/application/controllers/AuthenticationController.php(83): Icinga\Web\Form->handleRequest()
#9 /usr/share/icingaweb2/library/vendor/Zend/Controller/Action.php(507): Icinga\Controllers\AuthenticationController->loginAction()
#10 /usr/share/php/Icinga/Web/Controller/Dispatcher.php(76): Zend_Controller_Action->dispatch(String)
#11 /usr/share/icingaweb2/library/vendor/Zend/Controller/Front.php(937): Icinga\Web\Controller\Dispatcher->dispatch(Object(Icinga\Web\Request), Object(Icinga\Web\Response))
#12 /usr/share/php/Icinga/Application/Web.php(304): Zend_Controller_Front->dispatch(Object(Icinga\Web\Request), Object(Icinga\Web\Response))
#13 /usr/share/php/Icinga/Application/webrouter.php(107): Icinga\Application\Web->dispatch()
#14 /usr/share/icingaweb2/public/index.php(4): require_once(String)
#15 {main}

If i press F5 the site works.

I’d added the added the database update https://github.com/Icinga/icingaweb2/blob/master/etc/schema/mysql-upgrades/2.9.0.sql

The same error appears if i click on the new link “manage-user-devices”
Our logins are checked against an LDAP.

Our system:

  • Debian 9
  • PHP 7.3
  • ICINGA lastest Release
  • ICINGA WEB 2.9.0

Regards
Sascha

Edit…
I’d fixed the issue by adding the charset “uft8” in the resources.ini

2

Hi,

we have a similar issue on Github.

May I ask which database vendor and version this is with?

3

This is our version

mysql Ver 15.1 Distrib 10.1.48-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

4

I have this problem too and occurs only if i activate the “stay logged in” slider.
If I reload the page I can login and Icinga Web works perfectly
This is the databse used: mysql Ver 8.0.25-0ubuntu0.20.04.1 for Linux on x86_64 ((Ubuntu))

SQLSTATE[42000]: Syntax error or access violation: 1115 Unknown character set: ''

#0 [internal function]: PDO->exec()
#1 /usr/share/icinga-php/ipl/vendor/ipl/sql/src/Connection.php(82): call_user_func_array()
#2 /usr/share/icinga-php/ipl/vendor/ipl/sql/src/Connection.php(164): ipl\Sql\Connection->__call()
#3 /usr/share/icinga-php/ipl/vendor/ipl/sql/src/Connection.php(399): ipl\Sql\Connection->connect()
#4 /usr/share/icinga-php/ipl/vendor/ipl/sql/src/Connection.php(438): ipl\Sql\Connection->prepexec()
#5 /usr/share/php/Icinga/Web/RememberMe.php(231): ipl\Sql\Connection->insert()
#6 /usr/share/icingaweb2/application/forms/Authentication/LoginForm.php(142): Icinga\Web\RememberMe->persist()
#7 /usr/share/php/Icinga/Web/Form.php(1178): Icinga\Forms\Authentication\LoginForm->onSuccess()
#8 /usr/share/icingaweb2/application/controllers/AuthenticationController.php(83): Icinga\Web\Form->handleRequest()
#9 /usr/share/icingaweb2/library/vendor/Zend/Controller/Action.php(507): Icinga\Controllers\AuthenticationController->loginAction()
#10 /usr/share/php/Icinga/Web/Controller/Dispatcher.php(76): Zend_Controller_Action->dispatch()
#11 /usr/share/icingaweb2/library/vendor/Zend/Controller/Front.php(937): Icinga\Web\Controller\Dispatcher->dispatch()
#12 /usr/share/php/Icinga/Application/Web.php(304): Zend_Controller_Front->dispatch()
#13 /usr/share/php/Icinga/Application/webrouter.php(107): Icinga\Application\Web->dispatch()
#14 /usr/share/icingaweb2/public/index.php(4): require_once(String)
#15 {main}
5

Did you import this SQL statement: https://github.com/Icinga/icingaweb2/blob/master/etc/schema/mysql-upgrades/2.9.0.sql
?
And you have to set in the file “/etc/icingaweb2/resources.ini”

charset = “utf8”

for the “icingaweb_db” connection.

6

I had also the sema problem.
I have changed the charset as suggested and restarted the services (I performed a reboot, instead)
But NOW, is I select “Stay logged in” in the login page…
After logged on I have another error:

SQLSTATE[42000]: Syntax error or access violation: 1142 INSERT command denied to user ‘icingaweb’@‘localhost’ for table ‘icingaweb_rememberme’

#0 /usr/share/icinga-php/ipl/vendor/ipl/sql/src/Connection.php(401): PDO->prepare(String)
#1 /usr/share/icinga-php/ipl/vendor/ipl/sql/src/Connection.php(438): ipl\Sql\Connection->prepexec(String)
#2 /usr/share/php/Icinga/Web/RememberMe.php(231): ipl\Sql\Connection->insert(String, Array)
#3 /usr/share/icingaweb2/application/forms/Authentication/LoginForm.php(142): Icinga\Web\RememberMe->persist()
#4 /usr/share/php/Icinga/Web/Form.php(1178): Icinga\Forms\Authentication\LoginForm->onSuccess()
#5 /usr/share/icingaweb2/application/controllers/AuthenticationController.php(83): Icinga\Web\Form->handleRequest()
#6 /usr/share/icingaweb2/library/vendor/Zend/Controller/Action.php(507): Icinga\Controllers\AuthenticationController->loginAction()
#7 /usr/share/php/Icinga/Web/Controller/Dispatcher.php(76): Zend_Controller_Action->dispatch(String)
#8 /usr/share/icingaweb2/library/vendor/Zend/Controller/Front.php(937): Icinga\Web\Controller\Dispatcher->dispatch(Object(Icinga\Web\Request), Object(Icinga\Web\Response))
#9 /usr/share/php/Icinga/Application/Web.php(304): Zend_Controller_Front->dispatch(Object(Icinga\Web\Request), Object(Icinga\Web\Response))
#10 /usr/share/php/Icinga/Application/webrouter.php(107): Icinga\Application\Web->dispatch()
#11 /usr/share/icingaweb2/public/index.php(4): require_once(String)
#12 {main}

If I do not select “Stay logged in” flag, I have no problem at login.
P.

7

I have applied the sql that create the table as per your suggestions…
But If I selecr the new table just created I have this warning - and the error at login is not gone!

image
image916×725 40.2 KB

8

This last error is a PhpMyAdmin issue\bug.
A possible solution is reported here: