Icingaweb2 error: Base table or view not found (rememberme does not exist)

Written by Claudio Kuenzler - 1 comments

Published on March 15th 2022 - last updated on January 31st 2023 - Listed in Icinga Icingaweb MySQL Databases

---

After having enabled the "remember me" checkbox on a Icinga Web 2 login, I found myself with a big error message on the user interface.

Table 'icingaweb_rememberme' doesn't exist

Instead of showing the well-known Icingaweb2 dashboard, a full screen error message showed up:

The full error also showed the stack trace:

SQLSTATE[42S02]: Base table or view not found: 1146 Table 'icingaweb2.icingaweb_rememberme' doesn't exist
#0 /usr/share/icinga-php/ipl/vendor/ipl/sql/src/Connection.php(401): PDO->prepare()
#1 /usr/share/icinga-php/ipl/vendor/ipl/sql/src/Connection.php(489): ipl\Sql\Connection->prepexec()
#2 /usr/share/php/Icinga/Web/RememberMe.php(293): ipl\Sql\Connection->delete()
#3 /usr/share/php/Icinga/Web/RememberMe.php(84): Icinga\Web\RememberMe->remove()
#4 /usr/share/icingaweb2/application/controllers/AuthenticationController.php(63): Icinga\Web\RememberMe::forget()
#5 /usr/share/icingaweb2/library/vendor/Zend/Controller/Action.php(507): Icinga\Controllers\AuthenticationController->loginAction()
#6 /usr/share/php/Icinga/Web/Controller/Dispatcher.php(76): Zend_Controller_Action->dispatch()
#7 /usr/share/icingaweb2/library/vendor/Zend/Controller/Front.php(937): Icinga\Web\Controller\Dispatcher->dispatch()
#8 /usr/share/php/Icinga/Application/Web.php(304): Zend_Controller_Front->dispatch()
#9 /usr/share/php/Icinga/Application/webrouter.php(107): Icinga\Application\Web->dispatch()
#10 /usr/share/icingaweb2/public/index.php(4): require_once(String)
#11 {main}

A manual check in the icingaweb2 database revealed that this table (rememberme) is indeed missing:

root@icinga2:~# mysqlshow icingaweb2 icingaweb_rememberme
Database: icingaweb2  Wildcard: icingaweb_rememberme
+--------+
| Tables |
+--------+
+--------+

Manual database schema upgrades

Interesting is however, that the "remember me" function was added in Icingaweb 2.9 - yet the current package version 2.9.6 has been installed for a long time. Yet this "bug" seems only to occur, when the remember me checkbox was activated.

The Icingaweb2 upgrade notes show a relevant information concerning database changes:

Upgrading to Icinga Web 2.9.0
Icinga Web 2 now permits its users to stay logged in. This requires a new database table.
 Please apply the v2.9.0.sql upgrade script depending on your database vendor. In package installations this file can be found in /usr/share/doc/icingaweb2/schema/*-upgrades/ (Debian/Ubuntu: /usr/share/icingaweb2/etc/schema/*-upgrades/).

Upgrading to Icinga Web 2.9.1
Please apply the v2.9.1.sql upgrade script depending on your database vendor. In package installations this file can be found in /usr/share/doc/icingaweb2/schema/*-upgrades/ (Debian/Ubuntu: /usr/share/icingaweb2/etc/schema/*-upgrades/).

 These notes are helpful and point to SQL files, which can be found in /usr/share/icingaweb2/etc/schema/mysql-upgrades:

root@icinga2:/usr/share/icingaweb2/etc/schema/mysql-upgrades# ls -la
total 16
-rw-r--r-- 1 root root 1096 Mar  8 12:14 2.0.0beta3-2.0.0rc1.sql
-rw-r--r-- 1 root root  382 Mar  8 12:14 2.5.0.sql
-rw-r--r-- 1 root root  544 Mar  8 12:14 2.9.0.sql
-rw-r--r-- 1 root root   79 Mar  8 12:14 2.9.1.sql

Taking a closer look at them, shows the creation of the missing rememberme table in 2.9.0.sql and a varchar size increase in 2.9.1:

root@icinga2:/usr/share/icingaweb2/etc/schema/mysql-upgrades# cat 2.9.0.sql
CREATE TABLE `icingaweb_rememberme`(
  id                int(10) unsigned NOT NULL AUTO_INCREMENT,
  username          varchar(254) COLLATE utf8mb4_unicode_ci NOT NULL,
  passphrase        varchar(256) NOT NULL,
  random_iv         varchar(24) NOT NULL,
  http_user_agent   text NOT NULL,
  expires_at        timestamp NULL DEFAULT NULL,
  ctime             timestamp NULL DEFAULT NULL,
  mtime             timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;


root@icinga2:/usr/share/icingaweb2/etc/schema/mysql-upgrades# cat 2.9.1.sql
ALTER TABLE `icingaweb_rememberme`
    MODIFY random_iv varchar(32)  NOT NULL;

These SQL files can now be applied to the Icinga2 database:

root@icinga2:/usr/share/icingaweb2/etc/schema/mysql-upgrades# mysql icingaweb2 < 2.9.0.sql
root@icinga2:/usr/share/icingaweb2/etc/schema/mysql-upgrades# mysql icingaweb2 < 2.9.1.sql

And the table should now exist:

root@icinga2:~# mysqlshow icingaweb2 icingaweb_rememberme
Database: icingaweb2  Wildcard: icingaweb_rememberme
+----------------------+
|        Tables        |
+----------------------+
| icingaweb_rememberme |
+----------------------+

Application upgrades via package manager

When upgrading applications with package managers (such as apt or yum), we have gotten used to application upgrades being handled through the packages. Such application upgrades (e.g. altering database tables of the application) usually happens in so-called post-upgrade scripts.

But not always are such application-level upgrades handled in packages. Sometimes the package maintainer doesn't want to add complexity into the package, sometimes it simply doesn't make sense to handle this in a package.

Non-default (additional) settings, such as security settings, might also prevent the package upgrade process to read some necessary configuration files (e.g. database credentials), which then would lead to a broken package installation. 

Nevertheless, reading the README or upgrade notes of an application is still valid these days.

Add a comment

Show form to leave a comment

Comments (newest first)

Martin from wrote on Nov 18th, 2023:

Thank you very much! It solved my problem without going into all the details.

---