Mysql Data disappears intermittently

Hi there,

A few likely causes:

Your app might be deleting or overwriting records unintentionally. Check your application code for any DELETE or TRUNCATE statements that might be running on a schedule or triggered unexpectedly.

Check the MariaDB general query log to see what queries are actually running against that table:

# Enable general log temporarily
mysql -u root -p -e "SET GLOBAL general_log = 'ON';"
tail -f /var/log/mysql/mysql.log

Also check if the Droplet is running out of disk space, which can cause MariaDB to behave unexpectedly:

df -h

Check MariaDB error logs for anything unusual:

tail -100 /var/log/mysql/error.log

Also worth checking if the Droplet is being rebooted and whether MariaDB is configured to start automatically:

systemctl status mariadb
journalctl -u mariadb --since "7 days ago" | grep -i "start\|stop\|error"

One important note: for production you should considr DigitalOcean Managed Databases which handles reliability, backups, and failover for you: https://docs.digitalocean.com/products/databases/mysql/

It is worth checking what storage engine the “log” table is actually using, since one specific engine produces exactly the symptom you are describing.

SHOW CREATE TABLE log;

If that shows ENGINE=MEMORY, that explains it completely. MEMORY tables keep their row data in RAM only. The table definition itself is stored on disk, so the empty table is still there after a restart, but every row in it is gone. MariaDB’s own documentation on the MEMORY storage engine is explicit that this is expected behavior, not a bug, and the engine is intended for caches or temporary work areas rather than anything you need to keep.

This lines up well with what you are seeing. Data inserted over several days disappearing intermittently, rather than all at once, points at something tied to periodic restarts rather than an application bug deleting rows or a malicious actor. Ubuntu 24.04 Droplets commonly apply unattended security upgrades that can restart MariaDB in the background, and each one of those would silently wipe a MEMORY table without anything showing up in the MariaDB error log, since nothing actually went wrong from the database’s point of view.

If that turns out to be the cause, converting the table to a persistent engine fixes it:

ALTER TABLE log ENGINE=InnoDB;

If the table is already InnoDB or Aria, this is not your answer and Bobby’s checklist is the right place to keep looking, disk space and an unexpected DELETE or TRUNCATE from application code being the next most likely causes.

Regards

Thanks for comprehensive answer. It’s INNODB already so I’ll go through everything else. I only use DO for testing, so this is an annoyance rather than a big deal ATM. Thanks again Hugh B