Troubleshooting SQL Server Errors on Plesk

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:

choose TCP dynamic port - troubleshooting SQL Server Errors on Plesk

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

unable to copy error on plesk - troubleshooting SQL Server Errors on Plesk

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

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.