Can't connect to database after restart

[Vince95]

Hi,

Everytime I restart my server, zabbix dont starts.
Here is the output from the log files:

syslog:

Mar 11 14:00:45 zabbix shutdown[28362]: shutting down for system reboot
Mar 11 14:00:46 zabbix init: Switching to runlevel: 6
Mar 11 14:00:48 zabbix mysqld: 130311 14:00:48 [Note] /usr/sbin/mysqld: Normal shutdown

zabbix-server.log:

24316:20130311:140048 [Z3005] Query failed: [2006] MySQL server has gone away [select i.itemid,i.key_,h.host,h.port,i.delay,i.descriptio n,i.nextcheck,i.type,i.snmp_community,i.snmp_oid,h .useip,h.ip,i.history,i.lastvalue,i.prevvalue,i.ho stid,h.status,i.value_type,h.errors_from,i.snmp_po rt,i.delta,i.prevorgvalue,i.lastclock,i.units,i.mu ltiplier,i.snmpv3_securityname,i.snmpv3_securityle vel,i.snmpv3_authpassphrase,i.snmpv3_privpassphras e,i.formula,h.available,i.status,i.trapper_hosts,i .logtimefmt,i.valuemapid,i.delay_flex,h.dns,i.para ms,i.trends,h.useipmi,h.ipmi_port,h.ipmi_authtype, h.ipmi_privilege,h.ipmi_username,h.ipmi_password,i .ipmi_sensor,i.lastlogsize from hosts h, items i where i.nextcheck<=1363006853 and h.hostid=i.hostid and h.status=0 and i.status in (0,3) and ((h.disable_until<=1363006848 and h.errors_from=0 and i.type in (0,1,4,6)) or i.type in (3,5,8,10,11)) and (h.proxy_hostid=0 or i.type in (5,8)) and mod(i.itemid,5)=3 and i.key_ not in ('status','icmpping','icmppingsec','zabbix[log]') and h.hostid between 000000000000000 and 099999999999999 order by i.nextcheck]
24316:20130311:140048 Database is down. Retrying in 10 seconds
24317:20130311:140049 [Z3005] Query failed: [2006] MySQL server has gone away [select i.itemid,i.key_,h.host,h.port,i.delay,i.descriptio n,i.nextcheck,i.type,i.snmp_community,i.snmp_oid,h .useip,h.ip,i.history,i.lastvalue,i.prevvalue,i.ho stid,h.status,i.value_type,h.errors_from,i.snmp_po rt,i.delta,i.prevorgvalue,i.lastclock,i.units,i.mu ltiplier,i.snmpv3_securityname,i.snmpv3_securityle vel,i.snmpv3_authpassphrase,i.snmpv3_privpassphras e,i.formula,h.available,i.status,i.trapper_hosts,i .logtimefmt,i.valuemapid,i.delay_flex,h.dns,i.para ms,i.trends,h.useipmi,h.ipmi_port,h.ipmi_authtype, h.ipmi_privilege,h.ipmi_username,h.ipmi_password,i .ipmi_sensor,i.lastlogsize from hosts h, items i where i.nextcheck<=1363006854 and h.hostid=i.hostid and h.status=0 and i.status in (0,3) and ((h.disable_until<=1363006849 and h.errors_from=0 and i.type in (0,1,4,6)) or i.type in (3,5,8,10,11)) and (h.proxy_hostid=0 or i.type in (5,8)) and mod(i.itemid,5)=4 and i.key_ not in ('status','icmpping','icmppingsec','zabbix[log]') and h.hostid between 000000000000000 and 099999999999999 order by i.nextcheck]
24317:20130311:140049 Database is down. Retrying in 10 seconds
24369:20130311:140049 [Z3005] Query failed: [2006] MySQL server has gone away [select httptestid,name,applicationid,nextcheck,status,del ay,macros,agent from httptest where status=0 and nextcheck<=1363006849 and mod(httptestid,1)=0 and httptestid between 000000000000000 and 099999999999999]
24369:20130311:140049 Database is down. Retrying in 10 seconds
24373:20130311:140050 [Z3005] Query failed: [2006] MySQL server has gone away [select escalationid,actionid,triggerid,eventid,r_eventid, esc_step,status from escalations where status in (0,1) and nextcheck<=1363006850 and escalationid between 000000000000000 and 099999999999999]
24373:20130311:140050 Database is down. Retrying in 10 seconds
24313:20130311:140050 [Z3005] Query failed: [2006] MySQL server has gone away [select i.itemid,i.key_,h.host,h.port,i.delay,i.descriptio n,i.nextcheck,i.type,i.snmp_community,i.snmp_oid,h .useip,h.ip,i.history,i.lastvalue,i.prevvalue,i.ho stid,h.status,i.value_type,h.errors_from,i.snmp_po rt,i.delta,i.prevorgvalue,i.lastclock,i.units,i.mu ltiplier,i.snmpv3_securityname,i.snmpv3_securityle vel,i.snmpv3_authpassphrase,i.snmpv3_privpassphras e,i.formula,h.available,i.status,i.trapper_hosts,i .logtimefmt,i.valuemapid,i.delay_flex,h.dns,i.para ms,i.trends,h.useipmi,h.ipmi_port,h.ipmi_authtype, h.ipmi_privilege,h.ipmi_username,h.ipmi_password,i .ipmi_sensor,i.lastlogsize from hosts h, items i where i.nextcheck<=1363006855 and h.hostid=i.hostid and h.status=0 and i.status in (0,3) and ((h.disable_until<=1363006850 and h.errors_from=0 and i.type in (0,1,4,6)) or i.type in (3,5,8,10,11)) and (h.proxy_hostid=0 or i.type in (5,8)) and mod(i.itemid,5)=0 and i.key_ not in ('status','icmpping','icmppingsec','zabbix[log]') and h.hostid between 000000000000000 and 099999999999999 order by i.nextcheck]
24313:20130311:140050 Database is down. Retrying in 10 seconds
24314:20130311:140051 [Z3005] Query failed: [2006] MySQL server has gone away [select i.itemid,i.key_,h.host,h.port,i.delay,i.descriptio n,i.nextcheck,i.type,i.snmp_community,i.snmp_oid,h .useip,h.ip,i.history,i.lastvalue,i.prevvalue,i.ho stid,h.status,i.value_type,h.errors_from,i.snmp_po rt,i.delta,i.prevorgvalue,i.lastclock,i.units,i.mu ltiplier,i.snmpv3_securityname,i.snmpv3_securityle vel,i.snmpv3_authpassphrase,i.snmpv3_privpassphras e,i.formula,h.available,i.status,i.trapper_hosts,i .logtimefmt,i.valuemapid,i.delay_flex,h.dns,i.para ms,i.trends,h.useipmi,h.ipmi_port,h.ipmi_authtype, h.ipmi_privilege,h.ipmi_username,h.ipmi_password,i .ipmi_sensor,i.lastlogsize from hosts h, items i where i.nextcheck<=1363006856 and h.hostid=i.hostid and h.status=0 and i.status in (0,3) and ((h.disable_until<=1363006851 and h.errors_from=0 and i.type in (0,1,4,6)) or i.type in (3,5,8,10,11)) and (h.proxy_hostid=0 or i.type in (5,8)) and mod(i.itemid,5)=1 and i.key_ not in ('status','icmpping','icmppingsec','zabbix[log]') and h.hostid between 000000000000000 and 099999999999999 order by i.nextcheck]
24314:20130311:140051 Database is down. Retrying in 10 seconds
24360:20130311:140051 [Z3005] Query failed: [2006] MySQL server has gone away [select h.hostid,min(i.itemid) from hosts h,items i where mod(h.hostid,1)=0 and i.nextcheck<=1363006856 and i.status in (0) and i.type in (0,1,4,6,12) and h.status=0 and h.disable_until<=1363006851 and h.errors_from!=0 and h.hostid=i.hostid and (h.proxy_hostid=0 or i.type in (5)) and i.key_ not in ('status','icmpping','icmppingsec','zabbix[log]') and h.hostid between 000000000000000 and 099999999999999 group by h.hostid]
24360:20130311:140051 Database is down. Retrying in 10 seconds
24315:20130311:140052 [Z3005] Query failed: [2006] MySQL server has gone away [select i.itemid,i.key_,h.host,h.port,i.delay,i.descriptio n,i.nextcheck,i.type,i.snmp_community,i.snmp_oid,h .useip,h.ip,i.history,i.lastvalue,i.prevvalue,i.ho stid,h.status,i.value_type,h.errors_from,i.snmp_po rt,i.delta,i.prevorgvalue,i.lastclock,i.units,i.mu ltiplier,i.snmpv3_securityname,i.snmpv3_securityle vel,i.snmpv3_authpassphrase,i.snmpv3_privpassphras e,i.formula,h.available,i.status,i.trapper_hosts,i .logtimefmt,i.valuemapid,i.delay_flex,h.dns,i.para ms,i.trends,h.useipmi,h.ipmi_port,h.ipmi_authtype, h.ipmi_privilege,h.ipmi_username,h.ipmi_password,i .ipmi_sensor,i.lastlogsize from hosts h, items i where i.nextcheck<=1363006857 and h.hostid=i.hostid and h.status=0 and i.status in (0,3) and ((h.disable_until<=1363006852 and h.errors_from=0 and i.type in (0,1,4,6)) or i.type in (3,5,8,10,11)) and (h.proxy_hostid=0 or i.type in (5,8)) and mod(i.itemid,5)=2 and i.key_ not in ('status','icmpping','icmppingsec','zabbix[log]') and h.hostid between 000000000000000 and 099999999999999 order by i.nextcheck]
24315:20130311:140052 Database is down. Retrying in 10 seconds
24346:20130311:140057 [Z3005] Query failed: [2006] MySQL server has gone away [select distinct h.ip from hosts h,items i where mod(h.hostid,12)=11 and i.hostid=h.hostid and h.proxy_hostid=0 and h.status=0 and i.key_ in ('icmpping','icmppingsec') and i.type=3 and i.status in (0,3) and h.useip=1 and i.nextcheck<=1363006857 and h.hostid between 000000000000000 and 099999999999999]
24346:20130311:140057 Database is down. Retrying in 10 seconds
24368:20130311:140057 [Z3001] Connection to database 'zabbix' failed: [2002] Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)
24368:20130311:140057 Database is down. Reconnecting in 10 seconds
24323:20130311:140058 [Z3005] Query failed: [2006] MySQL server has gone away [select distinct h.ip from hosts h,items i where mod(h.hostid,12)=0 and i.hostid=h.hostid and h.proxy_hostid=0 and h.status=0 and i.key_ in ('icmpping','icmppingsec') and i.type=3 and i.status in (0,3) and h.useip=1 and i.nextcheck<=1363006858 and h.hostid between 000000000000000 and 099999999999999]
24323:20130311:140058 Database is down. Retrying in 10 seconds
24357:20130311:140058 [Z3005] Query failed: [2006] MySQL server has gone away [select a.alertid,a.mediatypeid,a.sendto,a.subject,a.messa ge,a.status,mt.mediatypeid,mt.type,mt.description, mt.smtp_server,mt.smtp_helo,mt.smtp_email,mt.exec_ path,mt.gsm_modem,mt.username,mt.passwd,a.retries from alerts a,media_type mt where a.status=0 and a.mediatypeid=mt.mediatypeid and a.alerttype=0 and mt.mediatypeid between 000000000000000 and 099999999999999 order by a.clock]
24357:20130311:140058 Database is down. Retrying in 10 seconds
24316:20130311:140058 [Z3001] Connection to database 'zabbix' failed: [2002] Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)
24316:20130311:140058 Database is down. Reconnecting in 10 seconds
24317:20130311:140059 [Z3001] Connection to database 'zabbix' failed: [2002] Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)
24317:20130311:140059 Database is down. Reconnecting in 10 seconds
24289:20130311:140059 One child process died. Exiting ...
24289:20130311:140101 [Z3001] Connection to database 'zabbix' failed: [2002] Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)
583:20130311:140111 Starting zabbix_server. ZABBIX 1.6.6 (revision 7836).
583:20130311:140111 **** Enabled features ****
583:20130311:140111 SNMP monitoring: YES
583:20130311:140111 WEB monitoring: YES
583:20130311:140111 Jabber notifications: YES
583:20130311:140111 ODBC: NO
583:20130311:140111 IPv6 support: YES
583:20130311:140111 **************************
583:20130311:140111 [Z3001] Connection to database 'zabbix' failed: [2002] Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)
7046:20130311:143933 Starting zabbix_server. ZABBIX 1.6.6 (revision 7836).
7046:20130311:143933 **** Enabled features ****
7046:20130311:143933 SNMP monitoring: YES
7046:20130311:143933 WEB monitoring: YES
7046:20130311:143933 Jabber notifications: YES
7046:20130311:143933 ODBC: NO
7046:20130311:143933 IPv6 support: YES
7046:20130311:143933 **************************

I think this is the problem:

The zabbix server starts before the mysql server.
So the zabbix server can't find the database.

I changed in Runlevel 6 the order, but its still the same problem.


Thanks,
Vince95

[BDiE8VNy]

Check the order SysV init scripts get started.
See: chkconfig(8) - Linux man page with focus on 'start priority'.

[Vince95]

Unfortunately, this command doesn't exist.
I am not allowed to install any packages. I'm sorry.

Is there another possibility to fix that?

Regards,
Vince95

[benbugohit]

Unfortunately, this command doesn't exist.
I am not allowed to install any packages. I'm sorry.

Is there another possibility to fix that?

Regards,
Vince95

did you verify free space on mysqld data partition ?
i had this problem with zabbix installed from debian packages.
when i did a "df -h", the partition containing /var/lib/mysql/ was full !

the problem was "ibdata1" file which contains all mysqld transactions,
as we store many data with item' history in zabbix.

[Vince95]

Hi,

Thanks for the tip.
The partition containing /var/lib/mysql is not full (40% used).

Run Level 2:

S01bootlogs
S01mk-slave-delay
S01rsyslog
S01sudo
S02apache2
S03atd
S03cron
S03mysql
S03ntp
S03ssh
S03zabbix-agent
S03zabbix-server
S04exim4
S05rc.local
S05rmnologin
S05stop-bootlogd

Regards,
Vince95

[benbugohit]

Hi,

Thanks for the tip.
The partition containing /var/lib/mysql is not full (40% used).

Run Level 2:

S01bootlogs
S01mk-slave-delay
S01rsyslog
S01sudo
S02apache2
S03atd
S03cron
S03mysql
S03ntp
S03ssh
S03zabbix-agent
S03zabbix-server
S04exim4
S05rc.local
S05rmnologin
S05stop-bootlogd

i suggest :
1/ Try to start mysql before apache.
2/ is mysqld listening on localhost port 3306 ? so modify zabbix server and zabbbix.conf.php to connect to mysql port instead of socket
3/ To "debug" mysql, stop all zabbix services, mysqld, and start it, then try to connect to mysqld instance using mysql command-line client (mysql -u<username> -p<password> zabbix -h localhost) and do some query

[Vince95]

Hi,

I tried your steps, but it still doesn't work

Step 1:

Run Level 2:

lrwxrwxrwx 1 root root  18  1. Sep 2011  S01bootlogs -> ../init.d/bootlogs
lrwxrwxrwx 1 root root  24  1. Sep 2011  S01mk-slave-delay -> ../init.d/mk-slave-delay
lrwxrwxrwx 1 root root  15 20. Mär 13:09 S01mysql -> ../init.d/mysql
lrwxrwxrwx 1 root root  17  1. Sep 2011  S01rsyslog -> ../init.d/rsyslog
lrwxrwxrwx 1 root root  14  1. Sep 2011  S01sudo -> ../init.d/sudo
lrwxrwxrwx 1 root root  17  1. Sep 2011  S02apache2 -> ../init.d/apache2
lrwxrwxrwx 1 root root  13  1. Sep 2011  S03atd -> ../init.d/atd
lrwxrwxrwx 1 root root  14  1. Sep 2011  S03cron -> ../init.d/cron
lrwxrwxrwx 1 root root  13  1. Sep 2011  S03ntp -> ../init.d/ntp
lrwxrwxrwx 1 root root  13  1. Sep 2011  S03ssh -> ../init.d/ssh
lrwxrwxrwx 1 root root  22 20. Mär 13:08 S03zabbix-agent -> ../init.d/zabbix-agent
lrwxrwxrwx 1 root root  23 20. Mär 13:08 S03zabbix-server -> ../init.d/zabbix-server
lrwxrwxrwx 1 root root  15  1. Sep 2011  S04exim4 -> ../init.d/exim4
lrwxrwxrwx 1 root root  18  1. Sep 2011  S05rc.local -> ../init.d/rc.local
lrwxrwxrwx 1 root root  19  1. Sep 2011  S05rmnologin -> ../init.d/rmnologin
lrwxrwxrwx 1 root root  23  1. Sep 2011  S05stop-bootlogd -> ../init.d/stop-bootlogd

Step 2: (Zabbix.conf.php)

$DB["TYPE"]      = "mysql";
$DB["SERVER"]    = "localhost";
$DB["PORT"]      = "3306";
$DB["DATABASE"]  = "zabbix";
$DB["USER"]      = "zabbix";
$DB["PASSWORD"]  = "mypassword";
$ZBX_SERVER      = "127.0.0.1";
$ZBX_SERVER_PORT = "10051";

Step 3: (Simple Query)

zabbix:~/zabbix/conf# mysql -uzabbix -pmypassword zabbix -h localhost
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 39
Server version: 5.1.49-3-log (Debian)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SHOW TABLES;
+-----------------------+
| Tables_in_zabbix      |
+-----------------------+
| acknowledges          |
| actions               |
| alerts                |
| applications          |
| auditlog              |
| conditions            |
| config                |
| dchecks               |
| dhosts                |
| drules                |
| dservices             |
| escalations           |
| events                |
| functions             |
| graphs                |
| graphs_items          |
| groups                |
| help_items            |
| history               |
| history_log           |
| history_str           |
etc..

Here is the log file:

602:20130320:130958 Starting zabbix_server. ZABBIX 1.6.6 (revision 7836).
   602:20130320:130958 **** Enabled features ****
   602:20130320:130958 SNMP monitoring:       YES
   602:20130320:130958 WEB monitoring:        YES
   602:20130320:130958 Jabber notifications:  YES
   602:20130320:130958 ODBC:                   NO
   602:20130320:130958 IPv6 support:          YES
   602:20130320:130958 **************************
   602:20130320:130958 [Z3001] Connection to database 'zabbix' failed: [2002] Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

Regards,
Vince

[benbugohit]

Hi,

I tried your steps, but it still doesn't work

602:20130320:130958 [Z3001] Connection to database 'zabbix' failed: [2002] Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)
[/CODE]

Regards,
Vince

so, check your "zabbix_server.conf", and look for "DBHost=localhost" and "DBPort=3306", be sure to uncomment it.

Here is an example in my test server :

root@zabbixtest:~# grep ^DB /etc/zabbix/zabbix_server.conf 
DBHost=localhost
DBName=zabbix
DBUser=zabbix
DBPassword=XXXXX
DBPort=3306
root@zabbixtest:~#

and restart zabbix-server...
It should have been the 1st step to be done