Cloudpanel 2.x & MariaDB errors: Could not increase number of max_open_files

MariaDB errors on Cloudpanel 2.x with Debian 11: ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: NO) & [Warning] Could not increase number of max_open_files to more than 32768 (request: 65535)

One of my clients had issues with MariaDB and I found an issue or bug with MariaDB and Debian 11 on Cloudpanel 2.x, at the time of discovery my client was running Cloudpanel Version 2.3.2 and I noticed this issue was on all Cloudpanel machines that use MariaDB.

When requesting the MariaDB status you can see those errors:

# systemctl status mariadb

[Warning] Could not increase number of max_open_files to more than 32768 (request: 65535)

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

FATAL ERROR: Upgrade failed

Checking for insecure root accounts.

Access denied for user 'root'@'localhost' (using password: NO)

How to fix the issue?

1. Update the server
2. Modify /etc/mysql/debian-start
3. Modify /usr/lib/systemd/system/mariadb.service
4. Reload the Deamon & MariaDB

Update the server

# apt update && apt -y upgrade && apt -y install curl wget sudo

Modify /etc/mysql/debian-start

# nano /etc/mysql/debian-start

Search for the MariaDB section and replace it with this:

MARIADB_PWD=$(clpctl db:show:master-credentials  | grep '| Pass' | awk  '{ print $4 }')

MARIADB="/usr/bin/mariadb --defaults-file=/etc/mysql/debian.cnf --host=127.0.0.1 --password=$MARIADB_PWD"
MYADMIN="/usr/bin/mariadb-admin --defaults-file=/etc/mysql/debian.cnf --host=127.0.0.1 --password=$MARIADB_PWD"
# Don't run full mariadb-upgrade on every server restart, use --version-check to do it only once
MYUPGRADE="/usr/bin/mariadb-upgrade --defaults-extra-file=/etc/mysql/debian.cnf --version-check --silent --host=127.0.0.1 --password=$MARIADB_PWD"
MYCHECK="/usr/bin/mariadb-check --defaults-file=/etc/mysql/debian.cnf --host=127.0.0.1 --password=$MARIADB_PWD"
MYCHECK_SUBJECT="WARNING: mariadb-check has found corrupt tables"
MYCHECK_PARAMS="--all-databases --fast --silent"
MYCHECK_RCPT="${MYCHECK_RCPT:-root}"

I commented out the old settings just for backup and added the new section, this will use the root credentials to update MariaDB on 127.0.0.1 instead of localhost.

Modify /usr/lib/systemd/system/mariadb.service

# nano /usr/lib/systemd/system/mariadb.service

Search for: “LimitNOFILE=32768” and “LimitMEMLOCK=524288” under the [service] section and comment it out and add: LimitNOFILE=infinity and “LimitMEMLOCK=infinity”.

This will get rid of the error: [Warning] Could not increase number of max_open_files to more than 32768 (request: 65535)

# Number of files limit. previously [mysqld_safe] open-files-limit
# LimitNOFILE=32768
LimitNOFILE=infinity
# For liburing and io_uring_setup()
#LimitMEMLOCK=524288
LimitMEMLOCK=infinity
# Maximium core size. previously [mysqld_safe] core-file-size
# LimitCore=

Reload the Deamon & MariaDB

# systemctl daemon-reload

# systemctl restart mariadb

Check the MariaDB status

# systemctl status mariadb

Conclusion & Recommendations

This issue is on all Cloudpanel 2.x machines using MariaDB and can be fixed very easily within minutes. The issue is that in debian.start it tries to update on localhost and this should be 127.0.0.1 we only modified 2 configuration files.

You can also run the MariaDB upgrade command manually using the master credentials. To retrieve the master credentials run:

# clpctl db:show:master-credentials

To run the MariaDB upgrade command manually use:

# mariadb-upgrade -u root -p -h 127.0.0.1

More information is available on the Cloudpanel Discord Channel: https://discord.com/channels/696694788883349574/1079749076951761008/1138846643328929924