Mysql Data disappears intermittently

Question

I put a few records into a test database over the past few days, but, from time to time they seem to disappear. Obviously I can’t use any (cheap) instance like this for production work!

Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 291 Server version: 10.11.14-MariaDB-0ubuntu0.24.04.1 Ubuntu 24.04

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

MariaDB [(none)]> use datamap ; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A

Database changed MariaDB [datamap]> select * from log ; Empty set (0.000 sec)

Bobby · Answer

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/

alexdo · Answer

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

hbarnard · Answer

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

Become a contributor for community

DigitalOcean Documentation

Resources for startups and AI-native businesses

The developer cloud

Start building today

Report this

Mysql Data disappears intermittently

Related questions

Droplet: Docker => laravel (mysql) + astro app [unusable]

docker laravel app with astro frontend

Can I create a managed MySQL cluster running a recent version?

how to connect digital ocean app platform app with cpanel mysql database?

What’s the best way to handle downtime during software deployments?

Where to Find Slow Query Logs in Managed Database (Already Enabled)?

Search index command in pimcore 10.5.15 production gives error.

Query taking 1 minute to run

Fullstack Architecture Deployments Recomendations

Mysql CPU alerts and max_execution_time

I can't install MySQL on the server

How to reset database cluster

Report this

Mysql Data disappears intermittently

Related questions

Droplet: Docker => laravel (mysql) + astro app [unusable]

docker laravel app with astro frontend

Can I create a managed MySQL cluster running a recent version?

how to connect digital ocean app platform app with cpanel mysql database?

What’s the best way to handle downtime during software deployments?

Where to Find Slow Query Logs in Managed Database (Already Enabled)?

Search index command in pimcore 10.5.15 production gives error.

Query taking 1 minute to run

Fullstack Architecture Deployments Recomendations

Mysql CPU alerts and max_execution_time

I can't install MySQL on the server

How to reset database cluster