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

Written by - 1 comments

Published on - last updated on January 31st 2023 - Listed in Icinga 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:

icingaweb2 table rememberme does not exist

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.


RSS feed

Blog Tags:

  AWS   Android   Ansible   Apache   Apple   Atlassian   BSD   Backup   Bash   Bluecoat   CMS   Chef   Cloud   Coding   Consul   Containers   CouchDB   DB   DNS   Database   Databases   Docker   ELK   Elasticsearch   Filebeat   FreeBSD   Galera   Git   GlusterFS   Grafana   Graphics   HAProxy   HTML   Hacks   Hardware   Icinga   Influx   Internet   Java   KVM   Kibana   Kodi   Kubernetes   LVM   LXC   Linux   Logstash   Mac   Macintosh   Mail   MariaDB   Minio   MongoDB   Monitoring   Multimedia   MySQL   NFS   Nagios   Network   Nginx   OSSEC   OTRS   Office   PGSQL   PHP   Perl   Personal   PostgreSQL   Postgres   PowerDNS   Proxmox   Proxy   Python   Rancher   Rant   Redis   Roundcube   SSL   Samba   Seafile   Security   Shell   SmartOS   Solaris   Surveillance   Systemd   TLS   Tomcat   Ubuntu   Unix   VMWare   VMware   Varnish   Virtualization   Windows   Wireless   Wordpress   Wyse   ZFS   Zoneminder