Blog

Plesk is one of the popular control panels that is used a lot. This control panel supports a great database server for Windows which is called SQL. Here, we will give good guidance for troubleshooting SQL server errors on Plesk. You may encounter different problems in different steps, so in this article, we will troubleshoot SQL Server Errors on Plesk.

What is Plesk?

If you want to get acquainted with a practical and important tool for hosting service providers, Plesk is one of the best options. Plesk is a useful tool designed to help IT professionals manage the web, email, DNS, etc through a user-friendly GUI. This control panel acts as an intermediary between the system and users. To make it clear we will give an example. When you create a website using Plesk, Plesk submits this request to a web server, and the latter adds a new virtual host to the system.

What is SQL Server?

The SQL database management system is a Microsoft server and acts as a database server that stores and retrieves data as requested by other software on the same computer or a remote computer using a client-server model. Also, Microsoft provides APIs over the Internet as a web service to access SQL Server. An RDBMS does much more than fetch data for client applications. Internal functions of this server, such as buffer management, ensure that the most accessible data is available in the fastest form of storage to speed up access.

SQL Server Errors on Plesk and their Solution

Let’s analyze some of the SQL server errors on Plesk and troubleshoot them.

It Is not Possible to Determine the TCP Port Number Used by SQL Server

When you encounter this error on the database server, it means that you are unable to connect to a SQL server remotely through Microsoft SQL server management studio. It will be shown like this:

The cause of this problem is that the SQL server is not configured to use a static port.

Solution:

First, use Admin RDP to connect the Plesk server.

Then, go through the following pass: Start / All Programs / Microsoft SQL Server XXXX / SQL Server XXXX Configuration Manager. Note that xxxx is the version of the SQL server.

Here, you should extend SQL Server Network Configuration and then click Protocols for MSSQLSERVERXXXX.

Now, you should double-click on TCP/IP. Go to the protocol tab and check if the TCP/IP protocol is Enabled:

you should refer to the IP Addresses tab and scroll down the window to the IPAll section.

Determine the default MS SQL port number 1433 (or a custom port number if 1433 is already used) in the TCP Port field. Now click Ok:

Refer to SQL Server Configuration Manager (Local) then SQL Server Services,

Right-click on SQL Server and press Restart to apply the changes.

Troubleshooting SQL Server Errors on Plesk

Now you can log in to Plesk.
The last action is referring to Tools and Settings, then Database Servers, and .\MSSQLSERVERXXXX > Settings. Now, press Ok to apply settings with Plesk.

It Is not Possible to Import MySQL Database From .tmp file in Plesk

The error that occurs, for this reason, is ASCII ‘\0’. It reason is that MSSQL dump restores into the MySQL database.

Solution:

First, enter the Plesk.

Then refer to Domains and then Example.com and Database. Click Database to create an empty MSSQL database. Then select a MAAQL database server in the create step.

Press the Import dump and choose the Database dump file.

It failed to Backup or Upgrade Database on Plesk (mysqldump: unknown variable)

The reason for this error is that, in the [mysqldump] section of the MySQL configuration file, the directive is set incorrectly.

Solution:

Use SSH to log in to the server and run the command below to find the MySQL configuration file.

(ls -l /etc/my.cnf || ls -l /etc/mysql/my.cnf) 2>> /dev/null | awk {'print $NF'}

Here, the name of the file will be /etc/my.cnf, it also can be /etc/mysql/my.cnf.

Now, you should produce a backup for found file:

cup -per /ets/my.conf{,.backup}

Vi text editor can be used to open the /etc/my.cnf which was founded before.

From the [mysqldump] section, find and delete the instruction reported during the Plesk upgrade process.

Then, save the changes made

At last, run the following command to restart the MySQL service:

(systemctl restart mysql || systemctl restart mariadb || systemctl restart mysqld) 2>/dev/null

MySQL Fails to Restart

The error messages in the Log /var/log/mysql/error.log can be like this:

InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes
InnoDB: than specified in the .cnf file 0 536870912 bytes!
[ERROR] Plugin 'InnoDB' init function returned error.
[ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.

When you want to run MySQL check, the error will be like this:

mysqlcheck -uadmin -pcat /etc/psa/.psa.shadow -A
…
wordpress_terms
Error : Unknown table engine 'InnoDB'
error : Corrupt
wordpress_usermeta
Error : Unknown table engine 'InnoDB'
error : Corrupt
wordpress_users
Error : Unknown table engine 'InnoDB'
error : Corrupt

As you see the following error, it shows that Plesk is not available:

ERROR: Zend_Db_Statement_Exception: SQLSTATE[42000]: Syntax error or access violation: 1286 Unknown table engine 'InnoDB' (Pdo.php:234)

The reason for this one is the corruption of ib_logfile0 and ib_logfile1 in InnoDB files.

Solution:

Use SSH to connect to Plesk.

Then, you should stop the MySQL server with the command below:

service mysql stop || service mariadb stop || service mysqld stop

Use the following command and try to rename the corrupted ib_logfile files:

mv /var/lib/mysql/ib_logfile0 /var/lib/mysql/ib_logfile0.bak
mv /var/lib/mysql/ib_logfile1 /var/lib/mysql/ib_logfile1.bak

Boot your server with the following command:

service mysql start || service mariadb start || service mysqld start

It is not possible to Clone or Backup MSSQL Database in Plesk

The action of coping MSSQL in the following pass fails:

Domains / example.com / Databases / example_db / Copy

Also, you may encounter the following error after the backup process:

Warning:domain "example.com" Unable to backup database content. Database example_db. Db server .\MSSQLSERVER2014:[Cannot open database "example_db" requested by the login. The login failed. Login failed for user 'NT AUTHORITY\SYSTEM'.]

The reason for this error is encrypted procedures in the database which are not supported by Plesk. The bug ID PPPM-5693 may be reported because of that.

Solution:

Currently, the only way to avoid this is to not use encrypted procedures in MSSQL databases.

It Is not Possible to Add Remote Microsoft SQL Server for Windows

In this error, network problems cause the test connection to the database server to be failed.

It is not possible to connect to an MS SQL server by telnet or other TCP clients.

There are two reasons for this, firstly, the firewall on the MS SQL server is blocking the connection, and secondly, the remote server is not properly configured and does not accept connections.

Solution:

The remote SQL server should accept connections from non-local clients.

You must either disable Windows Firewall or configure it to accept connections to SQL Server.

If the problem persists, contact your SQL administrator to clarify access and configuration restrictions.

There is no Import Dump and Export Dump Buttons in Plesk

The reason for this problem is that the database server with the hostname or IP set has behaved like a remote server by Plesk.
You have not configured remote backup settings.

Solution:

Enter Plesk and go through one of the solutions:

Use the localhost hostname to reconnect the database server instead of the server hostname in the Hostname field.
you can configure Remote backup settings for the existing database server.

It Is not Possible to Create a Backup from PostgreSQL Database

This problem will occur because of the Plesk bug with ID PPPM-5078. But it can be fixed in the future.

Solution:

Use SSH to enter the server.

Then try to create a backup for /etc/psa/psa.conf file:

cp -a /etc/psa/psa.conf{,.back}

If you want to point Plesk to the new PostgreSQL database server, you should edit the /etc/psa/psa.conf/. The lines which should be changed are shown below:

PGSYAL_DATA_D /var/lib/pgsyal/data
PGSYAL_TSONF_D /var/lib/pgsyal/data
PGSYAL_BIN_D /usr/bin

or

PGSQL_DATA_D /var/lib/pgsql/9.5/data
PGSQL_CONF_D /var/lib/pgsql/9.5/data
PGSQL_BIN_D /usr/pgsql-9.5/bin

Conclusion

You may encounter different problems when you try to connect the SQL server on Plesk. This article is prepared to show you troubleshooting SQL Server Errors on Plesk and gives practical solutions for these problems. We hope this content is useful for you. If you have any other questions or you encountered another problem in this field please share in a comment.

FAQ

CDN or Content Delivery Network, is a network used by sites and programs to increase the loading speed of static elements. The way to do this is that the files are cached in CDN servers located in different areas around the world, and the CDN sends a request, then the user receives it from the nearest server. To learn how to install and configure CDN on a Dedicated Server, follow this article to the end.

Advantages of Using CDN

The benefit of using CDN is that it helps you ensure your content is delivered as quickly as possible. Some of these CDNs are located in most locations, and I transmit the desired content to your user from the nearest device. If the target company is Google or Facebook, it is good, but if you have a blog, a simple server will be enough for you.

Install and Configure CDN on Dedicated Server

Here, you can follow the needed instructions to build your own CDN. Before starting the Configure CDN on Dedicated Server steps, you need to buy a dedicated server. We recommend you choose a plan from the cheap dedicated server plans offered by our website.

Prerequisites

These are the requirements before installing CDN on a dedicated server:

– A domain or subdomain name

– At least two servers in different regions

– GeoDNS tool and a user to send requests to the domain

How to Setup CDN on a Dedicated Server

As the first step, you should register a domain name or subdomain. This process is very easy, you should just register it on any domain zone you prefer. You should rent servers in regions and countries where the target audiences are located. If your project is international, it is an easy process to select from hosting providers for a dedicated server. We selected three virtual servers from different locations. Here are the servers:

– Frankfurt, Chicago, and Singapore

Configure GeoCDN

You need a DNS server with GeoDNS functionality to ensure that clients are directing to the right servers after sending a request to our domain or subdomain.
Now let’s examine how GeoDNS works:
– You must get the client’s IP or the requested DNS server’s IP. These recursive servers are usually the internet provider’s DNS.
– GeoDNS identifies a customer’s IP, country, or region. You must also have the GeoIP database.
– GeoDNS selects the IP address from the nearest CDN depending on the client’s location.

You can build a DNS server with GeoDNS functionality yourself. But we recommended using ready-made solutions. These solutions are: Cloudflare, Zilore, Amazon, and ClouDNS

It can be said that all DNS services have a functional feature called DNS failover, which can configure activity monitoring so that if a server fails, the system will continue to operate automatically.

Note that here we will use ClouDNS for our CDN.

In the profile section, first, add a DNS zone and specify the domain name. If you use a subdomain don’t forget to add existing DNS records after adding the zone.
Then, it’s time to create an A record for the domain or subdomain, each for the specified region. You can choose continents and countries.
In the example we provide, the CDN is activating on the cdn.sayt.in domain. After adding the saty.in zone, create the first record a and redirect the NV clients to the selected server i.e. Chicago:

You should repeat the step to other regions. You should also create a record for the default zones. The final result is as follows:

As you can see, requests from unspecified regions from Europe, Africa, satellite Internet users, etc. in the last record should be directed to the Frankfurt server.

And this completes the initial DNS configuration. All that remains is to visit the registrar’s website. Now replace the current name servers with the ones provided by ClouDNS. While updating these servers, we adjust the servers.

Install SSL Certificate

Here, the CDN works with HTTPS. So if you have already provided SSL certificates for the domain or subdomain, upload them to all servers, for example, in the /etc/ssl/yourdomain/ directory.

If you don’t have a certificate, you can get one for free from Let’s Encrypt. We should mention that the ACME Shell script is a good option that has a user-friendly client and allows you to validate a domain or subdomain via DNS using the ClouDNS API.

We install acme.sh on only one server – the European server (123.456.**.***), and through it, the certificates are copy to all the others. Now, you should run the command below to install:

wget -O - https://get.acme.sh | bash; source ~/.bashrc

During the installation process, a CRON task is creating to automatically update the certificates.

Then, after the certificate is issued, domain verification is done by DNS with the help of API, in the next step, in the ClouDNS profile, in the Reseller API section, create a new API user and choose a password for it. Then, paste the obtained auth-id along with the password into the following file: ~/.acme.sh/dnsapi/dns_cloudns.sh (note not to be confused with dns_clouddns.sh). In this section, there are lines that you need to remove and edit:

CLOUDNS_AUTH_ID=
CLOUDNS_AUTH_PASSWORD=""

Now, we need to request SSL certificate permission for cdn.sayt.in:

acme.sh --issue --dns dns_cloudns -d cdn.sayt.in --reloadcmd "service nginx reload"

If needed in the future, you can set the command to restart the automatic configuration in the parameters, after each certificate renewal.

The process of getting the certificate may take up to a few minutes, so don’t stop it. If you encountered a domain validation error, run the command again. As a result, you will find out where the certificates were downloaded from:

Since we need to specify the paths when copying the certificates to other servers, you must remember them and specify them in the server settings. Ignore the error that appears when reloading the Nginx configuration.

In setting up the SSL certificate, the only task is to copy it to the other two servers that store the certificate paths. Now create the same directories on each server and copy the certificate files:

mkdir -p /root/.acme.sh/cdn.sayt.in/

scp -r root@Your_IP_Address:/root/.acme.sh/cdn.sayt.in/* /root/.acme.sh/cdn.sayt.in/

In order to automatically renew the certificate, you need to create a daily jobCRON on both servers. The following command shows how to do this and add to CRON jobs:

scp -r root@Your_IP_Address:/root/.acme.sh/cdn.sayt.in/* /root/.acme.sh/cdn.sayt.in/ && service nginx reload

Note that you need the key to be able to access the main server remotely without entering a password. So you must create it.

Install and Configure Nginx

We will use Nginx to deliver static content. To do this, update the list of packages and then install them on all three servers:

apt update
apt install nginx

Now, use the following command instead of the default configuration:

nginx.conf
user www-data;
worker_processes auto;
pid /run/nginx.pid;

events {
worker_connections 4096;
multi_accept on;
}

http {
sendfile on;
tcp_nopush on;
tcp_nodelay on;
types_hash_max_size 2048;

include /etc/nginx/mime.types;
default_type application/octet-stream;

access_log off;
error_log /var/log/nginx/error.log;

gzip on;
gzip_disable "msie6";
gzip_comp_level 6;
gzip_proxied any;
gzip_vary on;
gzip_types text/plain application/javascript text/javascript text/css application/json application/xml text/xml application/rss+xml;
gunzip on;            

proxy_temp_path    /var/cache/tmp;
proxy_cache_path   /var/cache/cdn levels=1:2 keys_zone=cdn:64m max_size=20g inactive=7d;
proxy_cache_bypass $http_x_update;

server {
listen 443 ssl;
server_name cdn.sayt.in;

ssl_certificate /root/.acme.sh/cdn.sayt.in/cdn.sayt.in.cer;
ssl_certificate_key /root/.acme.sh/cdn.sayt.in/cdn.sayt.in.key;

location / {
proxy_cache cdn;
proxy_cache_key $uri$is_args$args;
proxy_cache_valid 90d;
proxy_pass https://sayt.in;

Then, in the config file, you should edit these parts:

– SSL_certificate and ssl_certificate_key
– Max_size
– Inactive
– Proxy_cache_valid
– Proxy_pass

As you can see, it is a simple process. Configuring the retention time is the only issue, given the similarities between the inactive and proxy_cache_valid parameters.

If the request is not repeating within 7 days, the data will delete from the cache.
If the request is repeated even just once in 7 days, the cache will be considered obsolete after 90 days and Nginx will update it from the main server when the next request is made.

By using the command below you can reload the configuration:

service nginx reload

Congratulations! CDN is ready to use.

Conclusion

Loading speed of the static element is an important issue for sites and Content Delivery Network is an effective network in this field. Here, we tried to introduce CDN and show how you can install it on a dedicated server. You can use different providers, but we preferred well-known Cloudflare CDN. We hope this article was helpful to you. We would be pleased if you share it with someone interested.

FAQ

This article has been presented to show some of the common Mariadb problems which you may encounter while using that on Cpanel. This management tool was created as a software fork of MySQL by developers who played key roles in building the original database. So let’s troubleshooting MariaDB errors on Cpanel.

What is MariaDB?

The MariaDB database was created for relational database management and is used for purposes such as data warehousing, e-commerce, reporting applications, and enterprise-level features. You can do all your work with MariaDB. This tool works on cloud databases, both large and small.

MariaDB Errors on Cpanel Control Panel

Before we start troubleshooting MariaDB errors on Cpanel, we recommend that you use our Linux VPS servers to host your website. Here, we will show you some common errors in the MariaDB management tool and will provide proper solutions for them. So keep tracking.

MariaDB can not be installed

This error will occur when you use cpanel to update from MySQL to MariaDB. The error would be like this:

The preinstall check failed. MariaDB cannot be installed. at /usr/local/cpanel/Cpanel/Yum/Install.pm line 470.

This problem can be caused by problems during the yum process. These errors in the Yum output can cause the upgrade process to fail. So what you need to do is manually enter yum using ssh or a terminal and check for such errors. [Errno 14] PYCURL ERROR 22 – “The requested URL returned an error: 404 Not Found”

This problem should solve based on the error provided by Yum. One of the specific repository errors may be as follows:

[Errno 14] PYCURL ERROR 22 - "The requested URL returned error: 404 Not Found"

Solution: In this case, you need to solve the problem of the repository or disable it. So run the following command to disable it and replace the repository name with $repositoryname:

yum-config-manager --disable $repositoryname

If you want to change “enabled=1” to “enabled=0”, you can edit the repository file in /etc/yum.repos.d folder. Then as soon as yum is fixed, you can upgrade Cpanel and WHM again.

Unsuccessful MySQL or MariaDB Installation During The Upgrade

One of the reasons for the failure of the MariaDB upgrade process can be the removal of the MariaDB or MySQL packages in the /etc/yum.conf file.

Solution: You can remove the entries from the exclude section of the /etc/yum.conf directory, these entries are the Cpanel server defaults.

exclude=courier* dovecot* exim* filesystem httpd* mod_ssl* mydns* nsd* p0f php* proftpd* pure-ftpd* spamassassin*

We never recommend you exclude the packages using /etc/yum.conf file, because Cpanel manages MySQL and MariaDB versions.

The error of Invalid Grant String: Set Default Role 0

Updating MariaDB to version 10.2.35 may cause errors when migrating accounts via migration tool or backup restores. Also, this update causes problems in sites with older PHP versions and causes problems in the Cpanel user interface with the MySQL database.

A similar error for transfers and restore can be like this:

The "Mysql" restore module failed because of an error: Invalid grant string: SET DEFAULT ROLE 0 FOR 'testuser'@'10.1.1.3'

Solution: AUTOFIXER has been released since 2020 to address this error. So next time nightly repairs will be done automatically. So, if you encounter such problems, you can use the automatic Autofixer script to run as root:

/scripts/autorepair fix_mariadb_show_grants_roles

If it doesn’t work, please try to restart the SQL service.

Users who have done the previous solution related to downgrading MariaDB. Be sure to open MariaDB to ensure that the appropriate updates are received:

yum versionlock clear404

Then run

/scripts/upcp

MariaDB Crashing Error

The latest Mariadb update caused this database to crash when starting with WHM and Cpanel servers.

The website may crash when running the following versions of MariaDB:

10.1.42
10.2.28
10.4.9
10.3.19

Then you will see the following error on the startup of your system:

InnoDB: Failing assertion:table->can_be_evicted

You can manually downgrade MariaDB usage of WHM and Cpanel While MariaDB is working on your problem itself. Use the command below:

yum downgrade MariaDB-server MariaDB-common MariaDB-shared MariaDB-client MariaDB-compat MariaDB-devel

Note that this solution is temporary.

Conclusion

This content was prepared to show some important issues about troubleshooting MariaDB errors on Cpanel. MariaDB is an open-source useful database. Facebook, Wikipedia, Google, and so on are among the users of this database. We hope you enjoy the article. If you have any questions or ideas, please leave a comment.