Data gone.

[Ketil]

So i dont know if zabbix some how decided to do a purge or what happned, i had data for the last year or so,, but now i cant go further back than 9 days and some hours,
Zabbix was complaining about only having 20% disk space left, so i extended the lvm with around 40gb.
Would anyone be able to point me towards something that might help me troubleshoot this

all the best.
Ketil

[niveastn]

Hey there!

Im sorry to hear that! Something happened to your database. Have a look at your db logs (Please tell us if its postgres, mysql... and what version of zabbix you are using, so we can help you more )

[Ketil]

Hi niveastn Thanks for the quick response, thats appriciated.

I am using mysql and running zabbix_server (Zabbix) 5.0.17

[niveastn]

Hey there!

So a few things you can use to check how is you db:

1. check your logs. by default they are in /var/log/mysql. log, but if its not here, its in /var/log. Can be a mysql folder inside it. Look for errors, slow queries, any signal you database is not doing well
2. SHOW ENGINE INNODB STATUS; use this query inside you database to check for deadlocks. If a deadlock happens InnoDB will rollback the transaction..
3. SHOW GLOBAL VARIABLES LIKE ‘max_connections’; and SHOW GLOBAL STATUS LIKE ‘max_used_connections’; Check the amount of conn inside your db, and compare with the max limit.

Let us know!

[Ketil]

/var/log/mysql - in my errorlogs in im swimming in these
2021-11-04 0:09:15 138 [Warning] Aborted connection 138 to db: 'zabbix' user: 'zabbix' host: 'localhost' (Got timeout reading communication packets)
2021-11-04 1:00:25 109 [Warning] Aborted connection 109 to db: 'zabbix' user: 'zabbix' host: 'localhost' (Got timeout reading communication packets)
2021-11-04 2:27:25 162 [Warning] Aborted connection 162 to db: 'zabbix' user: 'zabbix' host: 'localhost' (Got timeout reading communication packets)
2021-11-04 4:33:15 90 [Warning] Aborted connection 90 to db: 'zabbix' user: 'zabbix' host: 'localhost' (Got timeout reading communication packets)
2021-11-04 5:15:15 122 [Warning] Aborted connection 122 to db: 'zabbix' user: 'zabbix' host: 'localhost' (Got timeout reading communication packets)
2021-11-04 5:40:15 184 [Warning] Aborted connection 184 to db: 'zabbix' user: 'zabbix' host: 'localhost' (Got timeout reading communication packets)
2021-11-04 6:30:15 156 [Warning] Aborted connection 156 to db: 'zabbix' user: 'zabbix' host: 'localhost' (Got timeout reading communication packets)
2021-11-04 7:00:16 134 [Warning] Aborted connection 134 to db: 'zabbix' user: 'zabbix' host: 'localhost' (Got timeout reading communication packets)
2021-11-04 7:30:15 2444 [Warning] Aborted connection 2444 to db: 'zabbix' user: 'zabbix' host: 'localhost' (Got timeout reading communication packets)
2021-11-04 8:00:15 152 [Warning] Aborted connection 152 to db: 'zabbix' user: 'zabbix' host: 'localhost' (Got timeout reading communication packets)
2021-11-04 8:54:15 2530 [Warning] Aborted connection 2530 to db: 'zabbix' user: 'zabbix' host: 'localhost' (Got timeout reading communication packets)
2021-11-04 9:00:16 2537 [Warning] Aborted connection 2537 to db: 'zabbix' user: 'zabbix' host: 'localhost' (Got timeout reading communication packets)
2021-11-04 10:00:15 2598 [Warning] Aborted connection 2598 to db: 'zabbix' user: 'zabbix' host: 'localhost' (Got timeout reading communication packets)
2021-11-04 11:51:15 2712 [Warning] Aborted connection 2712 to db: 'zabbix' user: 'zabbix' host: 'localhost' (Got timeout reading communication packets)
2021-11-04 12:00:16 2721 [Warning] Aborted connection 2721 to db: 'zabbix' user: 'zabbix' host: 'localhost' (Got timeout reading communication packets)
2021-11-04 12:04:15 153 [Warning] Aborted connection 153 to db: 'zabbix' user: 'zabbix' host: 'localhost' (Got timeout reading communication packets)
2021-11-04 13:00:15 2782 [Warning] Aborted connection 2782 to db: 'zabbix' user: 'zabbix' host: 'localhost' (Got timeout reading communication packets)
2021-11-04 13:07:15 2790 [Warning] Aborted connection 2790 to db: 'zabbix' user: 'zabbix' host: 'localhost' (Got timeout reading communication packets)
2021-11-04 14:00:20 2844 [Warning] Aborted connection 2844 to db: 'zabbix' user: 'zabbix' host: 'localhost' (Got timeout reading communication packets)
2021-11-04 14:00:22 2846 [Warning] Aborted connection 2846 to db: 'zabbix' user: 'zabbix' host: 'localhost' (Got timeout reading communication packets)
2021-11-04 15:48:18 2659 [Warning] Aborted connection 2659 to db: 'zabbix' user: 'zabbix' host: 'localhost' (Got timeout reading communication packets)
2021-11-04 17:30:22 3248 [Warning] Aborted connection 3248 to db: 'zabbix' user: 'zabbix' host: 'localhost' (Got timeout reading communication packets)





Could find any notifications about locked rows in the output .. (besides 0 locked rows)


+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 404 |
+-----------------+-------+
1 row in set (0.002 sec)

+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| Max_used_connections | 201 |
+----------------------+-------+




Is there a function that purge data, leave the "high level info" With further investigation, it seems like i can see "information" when i look at the graphs where the range is .. lets say a month, but when i zoom in to a given day, its gone.
I just checked when this "purge" seem to happen, after what seems to be 10days and 10 hours, the detail level goes way down.

this is the last 6 months




After zooming into the the marked area, i get a range of around 5 days which still have some level of detal.

After zooming into the area highlighted, all data seem to vanish. (timeframe is about 2 days)

[niveastn]

Maybe this link can help you with the errors you found in your log: https://dba.stackexchange.com/questi...cation-packets

[Ketil]

I will read up on that, do you think that is what have caused the issue with data missing ?
[config has been updated, and i will have a look regarding the errors in the log]

[niveastn]

Hey

Your log has too many aborted connections, so this got my attention.
I sent you the link because I thought this could be a solution:

One of the silent killers of MySQL Connections is the MySQL Packet.

RECOMMENDATION

Try raising the max_allowed_packet to a much larger number, since the default is 1M. I would suggest about 10 times the largest TEXT or BLOB field you have in your current dataset.

To set the max_allowed_packet to 256M, you can add it to /etc/my.cnf or my.ini

[mysqld] max_allowed_packet=256M
to cover future restarts of mysqld. To install the value now on the server, please run this:

SET GLOBAL max_allowed_packet = 1024 * 1024 * 256;

Did you change this setting? how are you logs now?
And did you use this command "SHOW ENGINE INNODB STATUS; "? what did you get?