Mysql crash when the user exceeds the allowed quota

toli · February 2, 2021, 11:16pm

Operating system: Debian 10
MySQL version 10.3.27

Mysql crash when the user exceeds the allowed quota.

A BIG issue!

Feb 3 00:02:34 host7 systemd[1]: Started MariaDB 10.3.27 database server.
Feb 3 00:02:34 host7 /etc/mysql/debian-start[7553]: Upgrading MySQL tables if necessary.
Feb 3 00:02:34 host7 /etc/mysql/debian-start[7556]: /usr/bin/mysql_upgrade: the ‘–basedir’ option is always ignored
Feb 3 00:02:34 host7 /etc/mysql/debian-start[7556]: Looking for ‘mysql’ as: /usr/bin/mysql
Feb 3 00:02:34 host7 /etc/mysql/debian-start[7556]: Looking for ‘mysqlcheck’ as: /usr/bin/mysqlcheck
Feb 3 00:02:34 host7 /etc/mysql/debian-start[7556]: Version check failed. Got the following error when calling the ‘mysql’ command line client
Feb 3 00:02:34 host7 /etc/mysql/debian-start[7556]: ERROR 1045 (28000): Access denied for user ‘root’@‘localhost’ (using password: NO)
Feb 3 00:02:34 host7 /etc/mysql/debian-start[7556]: FATAL ERROR: Upgrade failed
Feb 3 00:02:34 host7 /etc/mysql/debian-start[7566]: Checking for insecure root accounts.
Feb 3 00:02:34 host7 debian-start[7551]: ERROR 1045 (28000): Access denied for user ‘root’@‘localhost’ (using password: NO)
Feb 3 00:02:40 host7 systemd[1]: mariadb.service: Main process exited, code=killed, status=6/ABRT
Feb 3 00:02:40 host7 systemd[1]: mariadb.service: Failed with result ‘signal’.
Feb 3 00:02:45 host7 systemd[1]: mariadb.service: Service RestartSec=5s expired, scheduling restart.
Feb 3 00:02:45 host7 systemd[1]: mariadb.service: Scheduled restart job, restart counter is at 23.
Feb 3 00:02:45 host7 systemd[1]: Stopped MariaDB 10.3.27 database server.
Feb 3 00:02:45 host7 systemd[1]: Starting MariaDB 10.3.27 database server…
Feb 3 00:02:46 host7 mysqld[7675]: 2021-02-03 0:02:46 0 [Note] /usr/sbin/mysqld (mysqld 10.3.27-MariaDB-0+deb10u1) starting as process 7675 …
Feb 3 00:02:46 host7 systemd[1]: Started MariaDB 10.3.27 database server.
Feb 3 00:02:46 host7 /etc/mysql/debian-start[7713]: Upgrading MySQL tables if necessary.
Feb 3 00:02:46 host7 /etc/mysql/debian-start[7716]: /usr/bin/mysql_upgrade: the ‘–basedir’ option is always ignored
Feb 3 00:02:46 host7 /etc/mysql/debian-start[7716]: Looking for ‘mysql’ as: /usr/bin/mysql
Feb 3 00:02:46 host7 /etc/mysql/debian-start[7716]: Looking for ‘mysqlcheck’ as: /usr/bin/mysqlcheck
Feb 3 00:02:46 host7 /etc/mysql/debian-start[7716]: Version check failed. Got the following error when calling the ‘mysql’ command line client
Feb 3 00:02:46 host7 /etc/mysql/debian-start[7716]: ERROR 1045 (28000): Access denied for user ‘root’@‘localhost’ (using password: NO)
Feb 3 00:02:46 host7 /etc/mysql/debian-start[7716]: FATAL ERROR: Upgrade failed
Feb 3 00:02:46 host7 /etc/mysql/debian-start[7726]: Checking for insecure root accounts.
Feb 3 00:02:46 host7 debian-start[7711]: ERROR 1045 (28000): Access denied for user ‘root’@‘localhost’ (using password: NO)

tpnsolutions · February 3, 2021, 12:21am

@toli,

Can’t say I’ve ever experienced that with any of our clients who go over their quotas on the regular.

How did you come to this conclusion?

Best Regards,
Peter Knowles | TPN Solutions

toli · February 3, 2021, 8:41am

@tpnsolutions

Yes, I know it’s strange.

I have set up a new virtualmin server. During testing, I installed Drupal 9 CMS. Everything was fine. I did a few more minor things. Suddenly the mysql crashed. I was wondering what could be causing this. I noticed that the server exceeded the allowed quote of 1GB. This is default. I raised it to 2GB. Suddenly mysql started working fine.

Who will tell me what happened?

I have many years of experience with virtualmin and this is the first time I see something like this. It is also my first server on debian 10.

tpnsolutions · February 3, 2021, 8:44am

@toli,

So when you say “quota” are you referring to RAM? This would change the conversation. Typically quota relates to disk space.

Best Regards,
Peter Knowles | TPN Solutions

toli · February 3, 2021, 8:49am

@tpnsolutions
I understand your point of view. It’s all about the disk space quota. Maybe the problem is only with debian 10.

tpnsolutions · February 3, 2021, 10:47am

@toli,

Have you reviewed the error log for the site?

Best Regards,
Peter Knowles | TPN Solutions

toli · February 3, 2021, 11:50am

It does not matter
Mysql is down and no application can connect to it.

You can recreate it:

Fresh debian 10
Create a virtual saver 2GB quota
Put files there over 1GB
Make a database with some tables
Change quota for server to 1GB
Restart mysql
Mysql crash
Change quota to 2GB again
restart mysql
Now is ok

tpnsolutions · February 3, 2021, 4:08pm

@toli,

Would you be open to a screen sharing session, I’d like to see this first hand as I’ve never seen a quota cause MySQL server to crash.

If interested, let me know.

Best Regards,
Peter Knowles | TPN Solutions

Gomez_Adams · February 3, 2021, 5:04pm

I’m not sure if I’m understanding your problem correctly, but when I read this:

According to this the problem is simple: when you install files MORE than 1 GB and then you limit your site to 1 GB then Virtualmin will automatically shut the site down for being over quota. Any webhost will do the same thing. You have no room for virtual memory or anything else when you do that so executing anything becomes impossible.

Why you would do that is simply beyond me to begin with.

Imagine a Windows Computer with a 1 GB hard drive. Try installing 1.3 GB of files on it (which you’ll never be able to do) and then still using it.

That is literally what you are doing and why it stops working.

If your original problem is because you’re going over your allowed limit then that is your issue. You’ll need to reduce the size of the site or increase the limit.

If you are being hosted by someone else though and you’re hitting MySQL limits even when you’re below your disc quota, then that’s something else entirely.

A lot of hosts limit MySQL queries based on the hosting program you buy. What happens is if you get too many queries in an hour, they shut down your MySQL for the rest of that hour until it resets and then it’ll start working again.

Again, I’m probably not understanding your actual problem correctly. Crashing your own Virtual Server by limiting its resources has me curious though.

tpnsolutions · February 3, 2021, 5:16pm

@toli @Gomez_Adams,

If “hard quotas” are enabled, and it is really shutting down MySQL which is still hard to believe since shutting down the daemon means every virtual server would be affected by a single virtual server over quota…

Then you could set “soft quotas” which essentially just complains you’re over limit, but doesn’t actually stop you from using more space.

*** I’d still be interested in seeing this issue in action for curiosity sake. ***

Best Regards,
Peter Knowles | TPN Solutions

Gomez_Adams · February 3, 2021, 5:27pm

Just did it on my server (limited on of my virtual servers below it’s usage). As predicted, it messes up that particular virtual server (it couldn’t even write the Webinizer report to disc on closing because there was no room.) but it didn’t effect any other virtual server on my server.

It still displayed properly on the internet though with the exception of any scripted material. (The likes and follow buttons were disabled as they use scripting.)

tpnsolutions · February 3, 2021, 5:51pm

@Gomez_Adams @toli,

Well that’s good to know, in the sense that it only affected the virtual server which was over quota. This, if you are using “hard quotas” is the intended result.

“Hard Quotas” is the default setting, so if you haven’t changed the “Default Settings” Server Template, then this will apply.

You can adjust the “Disk Quota Type”, visit:

System Settings > Server Templates > Default Settings

Navigate to the “template section” called “Administration User”, and you’ll see option “Disk Quota Type”.

Now, typically like most things, when you adjust this setting, it does NOT take effect on existing Virtual Servers, however I read somewhere (as I’ve never had a reason to try thus far) that if after adjusting the setting, you simply make even a “slight” change to the Virtual Server’s quota, the new settings should take effect for that Virtual Server.

Hope this helps!

Best Regards,
Peter Knowles | TPN Solutions

toli · February 3, 2021, 10:13pm

Thank You all for reply.

I made a video showing how mysql is crashing.
Watch to the end.

The syslog is shown in the lower right corner of the video.

https://infor.prywatnyinformatyk.pl/debian10-mysql-quota-crash-2.m4v

tpnsolutions · February 3, 2021, 11:53pm

@toli,

Interesting, I would be interested in inspecting this first hand, as I do also see a few other errors being logged related to MySQL.

Let me know if you’d like to setup a session.

Best Regards,
Peter Knowles | TPN Solutions

Gomez_Adams · February 4, 2021, 11:21am

Again, you are past the disc quota when you lower it. It is going to crash the site. Every. Single. Time.

Your site CAN NOT FUNCTION if you do not have the memory/disc space to run it.

You are literally shutting your site down by lowering the allowed usage to 1 Gigabyte when you have 1.07 Gigabytes on that particular virtual server.

toli · February 4, 2021, 12:23pm

@Gomez_Adams

Can’t You see that mysql crash? Problem is system wide. Not only for this one virtual server, but for all virtual servers.

toli · February 4, 2021, 12:24pm

@tpnsolutions
We can try.

Gomez_Adams · February 4, 2021, 1:22pm

Ah! OK. I missed that.

Looking at the readout, I’m seeing this:

I’m probably wrong here, but it looks like after you changed the virtual server limit to 1 GB, and the virtual server shuts down and restarts, you’re not able to start MySQL again because no password is entered on the bootup.

It tries to start MySQL, then denies access for the root user using no password. That seems odd to me.

toli · February 4, 2021, 2:01pm

@Gomez_Adams

Every time mysql restarts I have this:

Feb 4 14:48:50 host7 systemd[1]: Starting MariaDB 10.3.27 database server…
Feb 4 14:48:50 host7 mysqld[16132]: 2021-02-04 14:48:50 0 [Note] /usr/sbin/mysqld (mysqld 10.3.27-MariaDB-0+deb10u1) starting as process 16132 …
Feb 4 14:48:51 host7 systemd[1]: Started MariaDB 10.3.27 database server.
Feb 4 14:48:51 host7 /etc/mysql/debian-start[16170]: Upgrading MySQL tables if necessary.
Feb 4 14:48:51 host7 /etc/mysql/debian-start[16173]: /usr/bin/mysql_upgrade: the ‘–basedir’ option is always ignored
Feb 4 14:48:51 host7 /etc/mysql/debian-start[16173]: Looking for ‘mysql’ as: /usr/bin/mysql
Feb 4 14:48:51 host7 /etc/mysql/debian-start[16173]: Looking for ‘mysqlcheck’ as: /usr/bin/mysqlcheck
Feb 4 14:48:51 host7 /etc/mysql/debian-start[16173]: Version check failed. Got the following error when calling the ‘mysql’ command line client
Feb 4 14:48:51 host7 /etc/mysql/debian-start[16173]: ERROR 1045 (28000): Access denied for user ‘root’@‘localhost’ (using password: NO)
Feb 4 14:48:51 host7 /etc/mysql/debian-start[16173]: FATAL ERROR: Upgrade failed
Feb 4 14:48:51 host7 /etc/mysql/debian-start[16184]: Checking for insecure root accounts.
Feb 4 14:48:51 host7 debian-start[16167]: ERROR 1045 (28000): Access denied for user ‘root’@‘localhost’ (using password: NO)

And mysql works stably.

But when one of the virtual servers is over quota, mysql crashes and restarts.

When I raise the quota for virtual server mysql is stable again.

My post is a warning to you all that something like this may happen.

Besides, I wonder what causes this behavior.

On the screen I have marked the moment when mysql crashes and restarts

Gomez_Adams · February 4, 2021, 4:16pm

That is weird. That doesn’t happen on my system. (Running Ubuntu 20.04)