Error connecting to database

Akimace · 27 March 2019 15:53

Hi,
I have this issue:

Mariadb and Mysql services stopped
I tryed to check .env and add db logins
but it’s doesn’t work
After 10min~ my services stopping
Can you help me ?
Thank you

TheGreatDoc · 27 March 2019 16:11

How can you have both mariadb and mysql running?

So, your sql server stops after 10 min? Have you checked your sql logs?

Akimace · 28 March 2019 09:35

you mean, this log :
/var/log/mysql/error.log ?
I did, but I don’t know what’s wrong

error.log

2019-03-28 9:32:13 140639621774464 [Note] InnoDB: innodb_empty_free_list_algorithm has been changed to legacy because of small buffer pool size. In order to use backoff, increase buffer pool at least up$

2019-03-28 9:32:13 140639621774464 [Note] InnoDB: Using mutexes to ref count buffer pool pages
2019-03-28 9:32:13 140639621774464 [Note] InnoDB: The InnoDB memory heap is disabled
2019-03-28 9:32:13 140639621774464 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2019-03-28 9:32:13 140639621774464 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2019-03-28 9:32:13 140639621774464 [Note] InnoDB: Compressed tables use zlib 1.2.11
2019-03-28 9:32:13 140639621774464 [Note] InnoDB: Using Linux native AIO
2019-03-28 9:32:13 140639621774464 [Note] InnoDB: Using SSE crc32 instructions
2019-03-28 9:32:13 140639621774464 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2019-03-28 9:32:13 140639621774464 [Note] InnoDB: Completed initialization of buffer pool
2019-03-28 9:32:13 140639621774464 [Note] InnoDB: Highest supported file format is Barracuda.
2019-03-28 9:32:14 140639621774464 [Note] InnoDB: 128 rollback segment(s) are active.
2019-03-28 9:32:14 140639621774464 [Note] InnoDB: Waiting for purge to start
2019-03-28 9:32:14 140639621774464 [Note] InnoDB: Percona XtraDB (http://www.percona.com) 5.6.42-84.2 started; log sequence number 388779591
2019-03-28 9:32:14 140639621774464 [Note] Plugin ‘FEEDBACK’ is disabled.
2019-03-28 9:32:14 140638955763456 [Note] InnoDB: Dumping buffer pool(s) not yet started
2019-03-28 9:32:14 140639621774464 [Note] Server socket created on IP: ‘127.0.0.1’.
2019-03-28 9:32:14 140639621774464 [Note] /usr/sbin/mysqld: ready for connections.
Version: ‘10.1.38-MariaDB-0ubuntu0.18.04.1’ socket: ‘/var/run/mysqld/mysqld.sock’ port: 3306 Ubuntu 18.04
2019-03-28 9:32:21 140639620978432 [Note] /usr/sbin/mysqld: Normal shutdown
2019-03-28 9:32:21 140639620978432 [Note] Event Scheduler: Purging the queue. 0 events
2019-03-28 9:32:21 140639010281216 [Note] InnoDB: FTS optimize thread exiting.
2019-03-28 9:32:21 140639620978432 [Note] InnoDB: Starting shutdown…
2019-03-28 9:32:21 140639620978432 [Note] InnoDB: Waiting for page_cleaner to finish flushing of buffer pool
2019-03-28 9:32:23 140639620978432 [Note] InnoDB: Shutdown completed; log sequence number 388779601
2019-03-28 9:32:23 140639620978432 [Note] /usr/sbin/mysqld: Shutdown complete

2019-03-28 9:32:27 140702859021440 [Note] InnoDB: innodb_empty_free_list_algorithm has been changed to legacy because of small buffer pool size. In order to use backoff, increase buffer pool at least up$

2019-03-28 9:32:27 140702859021440 [Note] InnoDB: Using mutexes to ref count buffer pool pages
2019-03-28 9:32:27 140702859021440 [Note] InnoDB: The InnoDB memory heap is disabled
2019-03-28 9:32:27 140702859021440 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2019-03-28 9:32:27 140702859021440 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2019-03-28 9:32:27 140702859021440 [Note] InnoDB: Compressed tables use zlib 1.2.11
2019-03-28 9:32:27 140702859021440 [Note] InnoDB: Using Linux native AIO
2019-03-28 9:32:27 140702859021440 [Note] InnoDB: Using SSE crc32 instructions
2019-03-28 9:32:27 140702859021440 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2019-03-28 9:32:27 140702859021440 [Note] InnoDB: Completed initialization of buffer pool
2019-03-28 9:32:27 140702859021440 [Note] InnoDB: Highest supported file format is Barracuda.
2019-03-28 9:32:27 140702859021440 [Note] InnoDB: 128 rollback segment(s) are active.
2019-03-28 9:32:27 140702859021440 [Note] InnoDB: Waiting for purge to start
2019-03-28 9:32:27 140702859021440 [Note] InnoDB: Percona XtraDB (http://www.percona.com) 5.6.42-84.2 started; log sequence number 388779601
2019-03-28 9:32:27 140702859021440 [Note] Plugin ‘FEEDBACK’ is disabled.
2019-03-28 9:32:27 140702197479168 [Note] InnoDB: Dumping buffer pool(s) not yet started
2019-03-28 9:32:27 140702859021440 [Note] Server socket created on IP: ‘127.0.0.1’.
2019-03-28 9:32:27 140702859021440 [Note] /usr/sbin/mysqld: ready for connections.
Version: ‘10.1.38-MariaDB-0ubuntu0.18.04.1’ socket: ‘/var/run/mysqld/mysqld.sock’ port: 3306 Ubuntu 18.04

and today mysql/mariadb stay online, (atm)
but …another error on webui, sometime there is :
504 Gateway Time-out
nginx/1.14.0 (Ubuntu)

Kevin_Krumm · 28 March 2019 11:14

@Akimace you do understand MySQL and Mariadb are different databases instances? They are not the same and you shouldn’t have them both running.

Run ./validate.php and post it.

Akimace · 1 April 2019 09:53

validate.php

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

Component	Version
LibreNMS	1.49-105-g9037c0736
DB Schema	2019_02_10_220000_add_dates_to_fdb (132)
PHP	7.2.15-0ubuntu0.18.04.1
MySQL	10.1.38-MariaDB-0ubuntu0.18.04.1
RRDTool	1.7.0
SNMP	NET-SNMP 5.7.3

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

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

[OK] Database connection successful
[OK] Database schema correct
[FAIL] The poller (SRV-SNMP-01) has not completed within the last 5 minutes, check the cron job.
[WARN] Some devices have not been polled in the last 5 minutes. You may have performance issues.
[FIX]:
Check your poll log and see: http://docs.librenms.org/Support/Performance/
Devices:
10.1.30.3
10.1.30.4
10.1.30.6
10.1.30.7
10.1.30.13
10.1.30.8
10.1.30.9
10.1.30.2
[FAIL] Some devices have not completed their polling run in 5 minutes, this will create gaps in data.
[FIX]:
Check your poll log and see: http://docs.librenms.org/Support/Performance/
Devices:
10.1.30.2
[FAIL] Discovery has not completed in the last 24 hours.
[FIX]:
Check the cron job to make sure it is running and using discovery-wrapper.py
[WARN] Your install is over 24 hours out of date, last update: Mon, 25 Mar 2019 21:34:24 +0000
[FIX]:
Make sure your daily.sh cron is running and run ./daily.sh by hand to see if there are any errors.
[FAIL] You need to run this script as root or librenms

Akimace · 4 April 2019 11:05

I still this problème
I check my logs in logs/librenms.log
and I found this :

librenms.log

[2019-04-04 03:04:57] production.ERROR: SQLSTATE[22003]: Numeric value out of range: 1264 Out of range value for column ‘last_polled_timetaken’ at row 1 (SQL: UPDATE devices set uptime=8150529,last_ping=NOW(),last_ping_timetaken$
#1 /opt/librenms/poller.php(157): poll_device(Array, false)
#2 {main}
/opt/librenms/poller.php 47 2019-04-04 03:04:57 - 1 devices polled in 1182. secs
[2019-04-04 03:12:37] production.ERROR: Cannot use object of type stdClass as array {“exception”:"[object] (Symfony\Component\Debug\Exception\FatalThrowableError(code: 0): Cannot use object of type stdClass as array at /opt/librenms$
[stacktrace]
#0 {main}
"}
Error in ports module. The process “’/usr/bin/snmpbulkwalk’ ‘-v2c’ ‘-c’ ‘librenms’ ‘-OQUst’ ‘-m’ ‘IF-MIB’ ‘-M’ ‘/opt/librenms/mibs:/opt/librenms/mibs/comnet’ ‘udp:10.1.30.3:161’ ‘ifOutDiscards’” exceeded the timeout of 1200 seconds.
#0 /opt/librenms/vendor/symfony/process/Process.php(416): Symfony\Component\Process\Process->checkTimeout()
#1 /opt/librenms/vendor/symfony/process/Process.php(235): Symfony\Component\Process\Process->wait()
#2 /opt/librenms/includes/common.php(120): Symfony\Component\Process\Process->run()
#3 /opt/librenms/includes/snmp.inc.php(387): external_exec(Array)
#4 /opt/librenms/includes/snmp.inc.php(465): snmp_walk(Array, ‘ifOutDiscards’, ‘-OQUst’, ‘IF-MIB’, NULL)
#5 /opt/librenms/includes/polling/ports.inc.php(281): snmpwalk_cache_oid(Array, ‘ifOutDiscards’, Array, ‘IF-MIB’, NULL, ‘-OQUst’)
#6 /opt/librenms/includes/polling/functions.inc.php(305): include(’/opt/librenms/i…’)
#7 /opt/librenms/poller.php(157): poll_device(Array, false)
#8 {main}

Error in core module. SQLSTATE[HY000] [2002] Connection refused

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

Component	Version
LibreNMS	1.50-9-gc4fa3278b
DB Schema	Not Connected (0)
PHP	7.2.15-0ubuntu0.18.04.2
MySQL	?
RRDTool	1.7.0
SNMP	NET-SNMP 5.7.3

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

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

TheGreatDoc · 4 April 2019 11:59

Check you have correct credentials in both, config.php and .env files.

Try to connect to mysql using those credentials from console with:

mysql -u librenms -p librenms
That will ask you for the password.

Akimace · 4 April 2019 12:04

Ok for credentials in both
bu when I try to connect :
ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ (2 “No such file or directory”)

spencerbutler · 4 April 2019 13:27

It sounds like your config is not pointing to the proper database entry point. Try these commands to see how to configure your librenms install. (This assumes you have created the database and user already.) The one that returns the answer to 1+1 is the one you want to configure.

$config[‘db_host’] = ‘localhost’;

mysql -Nse ‘select 1+1’ -u librenms librenms -p -h localhost

$config[‘db_host’] = ‘127.0.0.1’;

mysql -Nse ‘select 1+1’ -u librenms librenms -p -h 127.0.0.1

$config[‘db_socket’] = ‘/var/run/mysqld/mysqld.sock’;

mysql -Nse ‘select 1+1’ -u librenms librenms -p -S /var/run/mysqld/mysqld.sock

Akimace · 4 April 2019 15:00

In my config file, there was :
$config[‘db_socket’] = ‘’;

I modified by :
$config[‘db_socket’] =’/var/run/mysqld/mysqld.sock’;
and restart mysql

spencerbutler · 4 April 2019 15:54

Did that resolve your issue?

Akimace · 5 April 2019 09:45

No, same problem today

spencerbutler · 5 April 2019 16:16

Did you run the commands I suggested? Did any of them work? Can you paste the output of the following (make sure to obfuscate your password).

$ grep db_ /opt/librenms/config.php

Pelo115 · 26 April 2019 08:36

Same error here…after a VM freeze and reset from Vbox gui, cannot connect to database:

ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ (2 “No such file or directory”)

The result for grep config.php:

$config[‘db_host’] = ‘127.0.0.1’;
$config[‘db_user’] = ‘librenms’;
$config[‘db_pass’] = ‘xxxxxxxx’;
$config[‘db_name’] = ‘librenms’;

Validate.php

Component	Version
LibreNMS	1.50.1
DB Schema	Not Connected (0)
PHP	7.2.15-0ubuntu0.18.04.2
MySQL	?
RRDTool	1.7.0
SNMP	NET-SNMP 5.7.3

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

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

librenms.log

[2019-04-26 08:05:45] production.ERROR: SQLSTATE[HY000] [2002] No such file or or directory (SQL: select user_id from users where (auth_type = mysql or auth_type is null or auth_type = ) and username = 2ws limit 1) {“exception”:"[object] (Illuminate\Database\QueryException(code: 2002 ): SQLSTATE[HY000] [2002] No such file or directory (SQL: select user_id from users where (auth_type = mysql or auth_type is null or auth_type = ) and username = 2ws limit 1) at /opt/librenms/vendor/laravel/framework/src/Illuminate/Database/Connection.php:664, Doctrine\DBAL\Driver\PDOException(code: 2002): SQLSTATE[HY000] [2002] No such file or directory at /opt/librenms/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php:31, PDOException(code: 2002): SQLSTATE[HY000] [2002] No such file or directory at /opt/librenms/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php:27)
[stacktrace]

any suggestions???

Chas · 26 April 2019 09:07

What does your /opt/librenms/.env file look like ? (https://docs.librenms.org/Support/FAQ/#where-do-i-update-my-database-credentials)

Pelo115 · 26 April 2019 09:09

Thanks for the answer!

this is my .env file content:

APP_KEY=base64:AUGa7/pO/qjrSc0DDLBwx4ZziBhemy77kMyrWtreFug=

DB_HOST=127.0.0.1
DB_DATABASE=librenms
DB_USERNAME=librenms
DB_PASSWORD=xxxxxxxxx

APP_URL=/
NODE_ID=5cb603c1b92b4
LIBRENMS_USER=librenms

Chas · 26 April 2019 09:15

Can you show outputs

ps -ef | grep mysql
netstat -ln | grep mysql

Pelo115 · 26 April 2019 09:18

ps -ef | grep mysql output:

librenms 3621 25601 0 09:16 pts/0 00:00:00 grep --color=auto mysql

netstat -ln | grep mysql output:

nothing

Chas · 26 April 2019 09:27

Its not running so Librenms cannot connect.

Check your mysql or mariadb service status

Pelo115 · 26 April 2019 09:45

yes, you´re right!

service mysql status output:

mariadb.service - MariaDB 10.1.38 database server
Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)
Active: failed (Result: exit-code) since Fri 2019-04-26 08:04:29 UTC; 1h 33min ago
Docs: man:mysqld(8)
https://mariadb.com/kb/en/library/systemd/
Process: 518 ExecStart=/usr/sbin/mysqld $MYSQLD_OPTS $_WSREP_NEW_CLUSTER $_WSREP_START_POSITION (code=exited, status=1/FAILURE)
Process: 462 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= || VAR=/usr/bin/galera_recovery; [ $? -eq 0 ] && systemctl set-environment _WSREP_START_POSITION=$VAR || exit 1 (code=ex
Process: 454 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
Process: 429 ExecStartPre=/usr/bin/install -m 755 -o mysql -g root -d /var/run/mysqld (code=exited, status=0/SUCCESS)
Main PID: 518 (code=exited, status=1/FAILURE)
Status: “MariaDB server is down”

when I try to start:

Failed to start mysql.service: The name org.freedesktop.PolicyKit1 was not provided by any .service files
See system logs and ‘systemctl status mysql.service’ for details.