Virtualmin SQL problem

[DownPW]

Hello @phenomlab

I have a problem with Virtualmin and mySQL.

We have a server bug due to lack of storage space.

We cleaned up our files/backup and overnight the free space was drastically reduced.

By doing research it seems that mysql takes a huge space in a short time.

It seems that the following tables cause problems

• undo_001
• undo_002
• and mysql.ibd file

I noticed this error on the log too:

I also noticed that mysql also eats all CPU resources.

---

What can we do to improve this situation?
What advice would you give?

I see this thread on virtualmin forum :
https://forum.virtualmin.com/t/mysql-files-occupied-whole-disk-which-file-to-delete-to-recover/116374/4

Do you think this is a solution?

Thanks in advance

[DownPW]

@phenomlab

I have delete the webmin modules like this :

https://sudonix.com/assets/uploads/files/1668193237472-fea622b7-2f4c-4bab-89d4-5293bb85e352-image.png

But it wasn’t enough because the mySQL service was still present.
So I have uninstall completely mysql like this:

• First make sure that MySQL service is stopped :

sudo systemctl stop mysql

• Remove MySQL related all packages completely :

sudo apt-get purge mysql-server mysql-client mysql-common mysql-server-core-* mysql-client-core-*

• Remove MySQL configuration and data :

sudo rm -rf /etc/mysql /var/lib/mysql /var/log/mysql

• (Optional) Remove unnecessary packages.

sudo apt autoremove

• (Optional) Remove apt cache.

sudo apt autoclean

No issues noted so far

Finally problem solved simply but I still don’t understand why this service was installed.

I thought Webmin or Virtualmin was using it when it’s not at all after confirming this by @phenomlab

Thanks dude. Always

[phenomlab]

@DownPW the presence of undo indicates a failed roll back from an attempted commit. I’m keen to understand what you are using mySQL for since Virtualmin alone should never cause this.

Ate any other sites or services reliant on mySQL?

In addition, the absence of any backup or maintenance plan can also cause the database to become huge in size because the transaction logs are not being committed.

[DownPW]

@phenomlab

I don’t use mySQL for any other service at all.

Just mongodb for nodebb and postgreSQL for my Wiki. Nothing more.

I have planified a scheduled backup of Webmin configuration and virtualmin but I have deactive it.

[phenomlab]

@DownPW ok. This line in the article you found is pretty much conclusive

SET GLOBAL innodb_undo_log_truncate=ON;

Having to execute this would indicate that the databases aren’t being backup up correctly or flushed after commit.

[DownPW]

@phenomlab

can you provide all command step by step ?
I don’t know with which user to connect to Mysql for webmin ?

Will this prevent us from having this kind of problem in the future?

Will undo files be deleted? for recovers space

[DownPW]

@phenomlab

obviously, the mysql service cannot start:

See this in the log

journalctl -fu mysql
-- Logs begin at Wed 2022-08-31 13:22:26 CEST. --
Oct 30 14:19:07 vps.XXXX systemd[1]: Failed to start MySQL Community Server.
Oct 30 14:19:08 vps.XXXX  systemd[1]: mysql.service: Scheduled restart job, restart counter is at 664.
Oct 30 14:19:08 vps.XXXX  systemd[1]: Stopped MySQL Community Server.
Oct 30 14:19:08 vps.XXXX  systemd[1]: Starting MySQL Community Server...
Oct 30 14:21:39 vps.XXXX  systemd[1]: mysql.service: Main process exited, code=exited, status=1/FAILURE
Oct 30 14:21:39 vps.XXXX  systemd[1]: mysql.service: Failed with result 'exit-code'.
Oct 30 14:21:39 vps.XXXX  systemd[1]: Failed to start MySQL Community Server.
Oct 30 14:21:39 vps.XXXX  systemd[1]: mysql.service: Scheduled restart job, restart counter is at 665.
Oct 30 14:21:39 vps.XXXX  systemd[1]: Stopped MySQL Community Server.
Oct 30 14:21:39 vps.XXXX  systemd[1]: Starting MySQL Community Server...

I believe the system is trying to restart the service to no avail.

I’m starting to miss this virtualmin

[phenomlab]

@DownPW let me have a look at this.

[DownPW]

@phenomlab

Ok. Good

[phenomlab]

@DownPW Just PM’d you - I need some details.

[phenomlab]

@DownPW To my mind, you need to increase the thread_stack value but I don’t see the my.cnf file for and I think Virtualmin does this another way.

When did this start happening ? From what I see, you have data waiting to be committed to mySQL, which is usually indicating that there is no backup which in turn would truncate the logs which are now out of control.

[phenomlab]

@phenomlab This is now fixed. For reference, your system is running at its maximum capacity with even the virtual memory 100% allocated. I needed to reboot the server to release the lock (which I’ve completed with no issues) and have also modified

etc/mysql/mysql.conf.d/mysqld.cnf

And increased the thread_stack size from 128k to 256k. The mysql service has now started successfully. You should run a backup of all databases ASAP so that remaining transactions are committed and the transaction logs are flushed.

[DownPW]

@phenomlab

I come back to you regarding the MySQL problem of virtualmin.

the service run smoothly, the backups of virtualmin are made without error, but I always have files with large sizes that always get bigger.

The /var/log/mysql/error.log is empty.

MySQL.idb is as 15 Go ???!! Very Big
Same for undo_001 (4,12 Go) and undo_002 (14,32 Go)

Virtualmin backup log:

I still don’t understand.

Your help is welcome

[phenomlab]

@DownPW You should consider using the below inside the my.cnf file, then restart the mySQL service

SET GLOBAL innodb_undo_log_truncate=ON;

[DownPW]

@phenomlab

Are you sure for my.cnf file because it is located on /etc/alternatives/my.cnf

And here is the file:

like this ?:

if i see into webmin the mySQL servers, it’s already activated:

[DownPW]

@phenomlab

If I add this line on my.cnf file, the mySQL service don’t start - failed

This is problematic because mySQL takes 36 GB of disk space so it alone takes up half of the server’s disk space.

I don’t think this is a normal situation.

[phenomlab]

@DownPW it’s certainly not normal as I’ve never seen this on any virtualmin build and I’ve created hundreds of them. Are you able to manually delete the undo files ?

[DownPW]

@phenomlab

I have delete these 2 files manually with webmin. Stop and start the service

I will monitor this and get back to you if it happens again.

[DownPW]

–> For mysql.ibd file, is his size normal? (15,6 Go)

[phenomlab]

@DownPW not normal, no, but you mustn’t delete it or it will cause you issues.

[phenomlab]

@DownPW the thing that concerns me here is that I’ve never seen an issue like this occur with no cause or action taken by someone else.

Do you know if anyone else who has access to the server has made any changes ?