Database will not start

Help
1

The librenms I am running is the VM version.

This post describes the same issue I am having: Error connecting to database. The solution here did not work for me.

Validate.php
librenms@librenms:~$ ./validate.php

Component Version
LibreNMS 1.63-43-g305ccf2
DB Schema Not Connected (0)
PHP 7.2.14-1+ubuntu16.04.1+deb.sury.org+1
Python 3.5.2
MySQL ?
RRDTool 1.5.5
SNMP NET-SNMP 5.7.3

====================================

[OK] Composer Version: 1.10.6
[OK] Dependencies up-to-date.
Could not connect to database, check logs/librenms.log.
librenms@librenms:~$

librenms.log
/opt/librenms/poller.php 147 2020-05-08 09:56:31 - 1 devices polled in 1.575 secs
[2020-05-11 10:13:48] production.ERROR: SQLSTATE[HY000] [2002] Connection refused (SQL: select user_id from users where (auth_type = mysql or auth_type is null or auth_type = ) and username = librenms limit 1) {“exception”:"[object] (Illuminate\Database\QueryException(code: 2002): SQLSTATE[HY000] [2002] Connection refused (SQL: select user_id from users where (auth_type = mysql or auth_type is null or auth_type = ) and username = librenms limit 1) at /opt/librenms/vendor/laravel/framework/src/Illuminate/Database/Connection.php:664, Doctrine\DBAL\Driver\PDOException(code: 2002): SQLSTATE[HY000] [2002] Connection refused at /opt/librenms/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php:31, PDOException(code: 2002): SQLSTATE[HY000] [2002] Connection refused at /opt/librenms/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php:27)
[stacktrace]
#0 /opt/librenms/vendor/laravel/framework/src/Illuminate/Database/Connection.php(624): Illuminate\Database\Connection->runQueryCallback(‘select user_id...', Array, Object(Closure)) #1 /opt/librenms/vendor/laravel/framework/src/Illuminate/Database/Connection.php(333): Illuminate\\Database\\Connection->run('select user_id…’, Array, Object(Closure))
#2 /opt/librenms/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php(2130): Illuminate\Database\Connection->select(‘select `user_id…’, Array, true)
#3 /opt/librenms/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php(2118): Illuminate\Database\Query\Builder->runSelect()
#4 /opt/librenms/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php(2604): Illuminate\Database\Query\Builder->Illuminate\Database\Query\{closure}()
#5 /opt/librenms/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php(2119): Illuminate\Database\Query\Builder->onceWithColumns(Array, Object(Closure))
#6 /opt/librenms/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Builder.php(521): Illuminate\Database\Query\Builder->get(Array)

Manually starting mysql:
librenms@librenms:~$ service mysql start
==== AUTHENTICATING FOR org.freedesktop.systemd1.manage-units ===
Authentication is required to start ‘mysql.service’.
Authenticating as: , (librenms)
Password:
==== AUTHENTICATION COMPLETE ===
Job for mysql.service failed because the control process exited with error code. See “systemctl status mysql.service” and “journalctl -xe” for details.
librenms@librenms:~$

2

Hi,

Check why your sql server is not starting checking both commands that are suggested:

“systemctl status mysql.service” and “journalctl -xe”

after trying to start the server

3 
librenms@librenms:~$ systemctl status mysql.service
● mysql.service - LSB: Start and stop the mysql database server daemon
   Loaded: loaded (/etc/init.d/mysql; bad; vendor preset: enabled)
   Active: failed (Result: exit-code) since Wed 2020-05-20 08:04:30 CDT; 6h ago
     Docs: man:systemd-sysv-generator(8)
  Process: 52705 ExecStart=/etc/init.d/mysql start (code=exited, status=1/FAILURE)

May 20 08:03:59 librenms systemd[1]: Starting LSB: Start and stop the mysql database server daemon...
May 20 08:03:59 librenms mysql[52705]:  * Starting MariaDB database server mysqld
May 20 08:04:30 librenms mysql[52705]:    ...fail!
May 20 08:04:30 librenms systemd[1]: mysql.service: Control process exited, code=exited status=1
May 20 08:04:30 librenms systemd[1]: Failed to start LSB: Start and stop the mysql database server daemon.
May 20 08:04:30 librenms systemd[1]: mysql.service: Unit entered failed state.
May 20 08:04:30 librenms systemd[1]: mysql.service: Failed with result 'exit-code'.

librenms@librenms:~$ journalctl -xe
-- Unit apt-daily.service has begun starting up.
May 20 14:31:47 librenms systemd[1]: Started Daily apt download activities.
-- Subject: Unit apt-daily.service has finished start-up
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
--
-- Unit apt-daily.service has finished starting up.
--
-- The start-up result is done.
May 20 14:45:30 librenms systemd[1]: Starting LSB: Start and stop the mysql database server daemon...
-- Subject: Unit mysql.service has begun start-up
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
--
-- Unit mysql.service has begun starting up.
May 20 14:45:30 librenms mysql[31211]:  * Starting MariaDB database server mysqld
May 20 14:46:00 librenms mysql[31211]:    ...fail!
May 20 14:46:00 librenms systemd[1]: mysql.service: Control process exited, code=exited status=1
May 20 14:46:00 librenms systemd[1]: Failed to start LSB: Start and stop the mysql database server daemon.
-- Subject: Unit mysql.service has failed
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
--
-- Unit mysql.service has failed.
--
-- The result is failed.
May 20 14:46:00 librenms systemd[1]: mysql.service: Unit entered failed state.
May 20 14:46:00 librenms systemd[1]: mysql.service: Failed with result 'exit-code'.
lines 2119-2146/2146 (END)
4

check your mysql.log

5

I know the default for that is /var/log/mysql. There is an mysql log there but it is empty. Is there another location for its logs?

6

Umh… When did sql stopped working?

Check /var/log/syslog or /var/log/messages

7

It stopped working after Windows forced an update that closed the VM while it was running, it has been a month or so. Nothing appears to be logging to /var/log. There are some older logs in there but nothing in the current files.

8

Oh, are you using a prebuilt ova? If yes, you are logging to librenms (thats why you dont have logs in /var/log)…

Without knowing the sql error im not sure I can help you… Maybe disable syslog-ng then check if local logs are writted again?

9

Yes it is a ova from librenms. Its been running great for years. The logs in the /opt/librenms/logs all show to be old. The newest entry into the log file is the librenms.log and the file shows it was last touched May 15.

10

When I ran “mysqld --verbose” I got:
200522 7:45:51 [Note] InnoDB: Using mutexes to ref count buffer pool pages
200522 7:45:51 [Note] InnoDB: The InnoDB memory heap is disabled
200522 7:45:51 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
200522 7:45:51 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
200522 7:45:51 [Note] InnoDB: Compressed tables use zlib 1.2.8
200522 7:45:51 [Note] InnoDB: Using Linux native AIO
200522 7:45:51 [Note] InnoDB: Using CPU crc32 instructions
200522 7:45:51 [Note] InnoDB: Initializing buffer pool, size = 128.0M
200522 7:45:51 [Note] InnoDB: Completed initialization of buffer pool
200522 7:45:51 [ERROR] InnoDB: ./ibdata1 can’t be opened in read-write mode
200522 7:45:51 [ERROR] InnoDB: The system tablespace must be writable!
200522 7:45:51 [ERROR] Plugin ‘InnoDB’ init function returned error.
200522 7:45:51 [ERROR] Plugin ‘InnoDB’ registration as a STORAGE ENGINE failed.
200522 7:45:51 [ERROR] mysqld: File ‘/var/lib/mysql/aria_log_control’ not found (Errcode: 13 “Permission denied”)
200522 7:45:51 [ERROR] mysqld: Got error ‘Can’t open file’ when trying to use aria control file ‘/var/lib/mysql/aria_log_control’
200522 7:45:51 [ERROR] Plugin ‘Aria’ init function returned error.
200522 7:45:51 [ERROR] Plugin ‘Aria’ registration as a STORAGE ENGINE failed.
200522 7:45:51 [Note] Plugin ‘FEEDBACK’ is disabled.
200522 7:45:51 [ERROR] Can’t open the mysql.plugin table. Please run mysql_upgrade to create it.
200522 7:45:51 [ERROR] Unknown/unsupported storage engine: InnoDB
200522 7:45:51 [ERROR] Aborting

200522 7:45:51 [Note] mysqld: Shutdown complete

Not sure if that does anything to help.

11

Backup and delete /var/lib/mysql/ibdata1 and the ib_logfileX files then try to start mysql again

12

librenms_mysqld
librenms_mysqld865×576 10.2 KB

After backing up and deleting /var/lib/mysql/ibdata1 and the ib_logfileX files, I get what is shown in the screenshot.

No luck restoring the database. What config file tells mysql to create the mysql.sock file? Searching shows that “My.cnf” does it but that doesn’t look like its being used.

13

After rebooting and running validate.php, I am getting:

[OK] Composer Version: 1.10.7
[OK] Dependencies up-to-date.

In Connection.php line 664:

SQLSTATE[42S02]: Base table or view not found: 1932 Table ‘librenms.migrations’ doesn’t exist in engine (SQL: select migratio n from migrations)

In PDOConnection.php line 63:

SQLSTATE[42S02]: Base table or view not found: 1932 Table ‘librenms.migrations’ doesn’t exist in engine

In PDOConnection.php line 61:

SQLSTATE[42S02]: Base table or view not found: 1932 Table ‘librenms.migrations’ doesn’t exist in engine

14

To answer your previous question you put the socket statement in my.cnf and have the socket set to go somewhere mysql has access to:

socket=/var/lib/mysql/mysql.sock

With that in mind it looks like you might have a couple things going on, one there is an error that mysql cant create a test file in /var/lib/mysql/librenms this could either be a permission issue or corruption. First verify that mysql is set for the ower and group of /var/lib/mysql and /var/lib/mysql/librenms (it should really be for everything in /var/lib/mysql). If it is not then fix that with

chown -R mysql. /var/lib/mysql

To me it sounds like db corruption though, is mysql starting now? If so the error “Table ‘librenms.migrations’ doesn’t exist in engine” is probably from a corrupted table and the .frm/.ibd files have been left behind causing the warning. If it’s db corruption and mysql will not start you could try and recover, the steps differ depending on if mysql is able to start up or not though and its also risky. If it is not starting and you want to try recovery, since it looks like it is in a vm I would back up that vm first, whether its a snapshot or just copying the whole image, you will then need to set innodb_force_recovery=1 in my.cnf, remove the ib_* files again and try to start mysql, if it does not start you will have to increment 1 to 2, and so on (up to 6 but be aware the higher the number the higher the chance of data loss). Once mysql starts dump the db somewhere safe, if you get errors during dumping the database you will probably need to increment the innodb_force_recovery by 1
mysqldump librenms > /opt/librenms.sql

and optionally for extra caution back up the db directory to librenms.bak
cp -a /var/lib/mysql/librenms{,.bak}

Create a list of current tables to a text file for your reference (optional but could come in handy)
mysql --batch --skip-column-names librenms -e "show tables" >> /opt/librenms.tables

drop the database
mysql -e "drop database librenms"

then create the database in mysql
mysql -e "create database librenms

Then back in your shell import the db
mysql librenms < /opt/librenms.sql

If after that you still get errors about the “migrations” table you could try to delete the migrations.frm and ibd files from /var/lib/mysql/librenms. I’m not 100% certain they are critical to have as I do not have that table in my db.

If mysql is starting up you could try to drop that table that is giving the warning after backing up the database and see if that helps
mysqldump librenms > /opt/librenms.sql
mysql librenms -e "drop table migrations" #this might blow an error or it might not
stop mysql and move the migrations.frm/.ibd from /var/lib/mysql/librenms to somewhere else then restart mysql

There is a large risk of data loss whenever doing a repair on innodb so I would advise caution and only repair if it is the last option.