Does anyone knows how to fix this error? MariaDB is installed on a Synology. I used 42% of storage with a total of 1TB. I have tried to delete the database, but it just get stuck at processing request… message.
So your database is 400GB‽
Firstly, that error message is specific to the /tmp partition being full because that query is sorting its results. If /tmp is in a separate partition to the rest of the disk then that can happen, but from the rest of your description, it sounds as if /tmp is mounted directly on the root partition and the whole disk is full. That’s not good for computers so once you’ve sorted this, you probably want to look into some basic monitoring and alerting to let you know once things get tight next time. But it sounds like you still have access.
Your query there won’t ever finish unless space gets cleared.
Couple of ways spring to mind.
If you don’t care about any of the databases, you can reset Mariadb by deleting the sqldir via the command line but this will require you to recreate any new databases AND users. It’s very quick.
systemctl stop mariadb
rm -rf /var/lib/mysql
systemctl start mariadb
If you just want to remove a single database, then DROPping it is usually fairly fast. If it’s not, there might be some disk issues (check dmesg) or the disk is just incredibly slow. You don’t say how you’re deleting it - if you’re deleting every row manually, that’ll take ages, but this shouldn’t take that long.
From whatever client (you’re using phpmyadmin there, that’s fine)
DROP DATABASE databasename;
A little more background. The database is installed on a Synology which I used mainly for storage which is at 42% usage and the disks are healthy.
MariaDB is only used by HA. There is no other databases created other than the default ones when you install MariaDB. Deleting the HA database is no issue for me if I ever run into these errors. Quick and easy. However, I have tried several ways of deleting the database, but it just hangs and it doesn’t delete it.
I may have fix my issue for now by stopping/starting MariaDB. I am not able to delete the DB and start from scratch.
So my issue has not been fixed. I have been deleting the database to start new when I see my queries is taking long time to load. It is getting quite annoying. Need to fix this permantely. The /tmp is in a separate partition with only 249M available.
duceduc@GOD:~$ df -h
Filesystem Size Used Avail Use% Mounted on
/dev/root 2.4G 955M 1.4G 42% /
none 249M 0 249M 0% /dev
/tmp 251M 1.9M 249M 1% /tmp
/run 251M 3.7M 248M 2% /run
/dev/shm 251M 4.0K 251M 1% /dev/shm
/dev/vg1000/lv 1.8T 13G 1.8T 1% /volume1
Is it ok to move the default tmpdir to volume1? I can see a path to a tmp folder.
/volume1/@tmp