Resolved After the Update | Error: DB query failed: SQLSTATE[HY000] [2002] Connection refused

[zihniates]

Hi,

After updating plesk to the latest version, I cannot make a backup and db import...

Here is my backup logs.

WARNING:      (Database object 'db_name')      Unable to make database dump. Error: Failed to exec mysqldump: Exit code: 3: mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `table_posts` at row: 41
WARNING:      (Database object 'db_name')      Unable to back up database db_name. Error: DB query failed: SQLSTATE[HY000] [2002] Connection refused
WARNING:      (Subscription object 'domain')      Unable to back up databases. Error: SQLSTATE[HY000] [2002] Connection refused
WARNING:      (Subscription object 'domain')      Unable to back up extensions. Error: Failed to exec backup_restore_helper: Exit code: 1: DB query failed: SQLSTATE[HY000] [2002] Connection refused
WARNING:      (Subscription object 'domain')      SQLSTATE[HY000] [2002] Connection refused
WARNING:      (Subscription object 'domain')      SQLSTATE[HY000] [2002] Connection refused
WARNING:      (Subscription object 'domain')      SQLSTATE[HY000] [2002] Connection refused
WARNING:      (Subscription object 'domain')      SQLSTATE[HY000] [2002] Connection refused
WARNING:      (Subscription object 'domain')      SQLSTATE[HY000] [2002] Connection refused
ERROR:      (Server object 'domain')      SQLSTATE[HY000] [2002] Connection refused

I can export the database that i mention above, but when I try to import it i have some errors.
do you have any idea? how can i fix it?

 

[Bitpalast]

Is it always the same error
"when dumping table `table_posts` at row: 41"
when dumping? Then what is in table_posts in the 41st dataset or line?

 

[zihniates]

When I try to see what is in line 41, this following error occurs in phpmyadmin.

1. Missin Expression. (near "ON" at position 25)
SET FOREIGN_KEY_CHECKS = ON;
#2006 - MySQL server has gone away

Warning in ./libraries/classes/Dbi/DbiMysqli.php#199
 Error while sending QUERY packet. PID=24903

Backtrace

./libraries/classes/Dbi/DbiMysqli.php#199: mysqli->query(
   string 'SHOW WARNINGS',
   integer 0,
)
./libraries/classes/DatabaseInterface.php#316: PhpMyAdmin\Dbi\DbiMysqli->realQuery(
   string 'SHOW WARNINGS',
   ,
   integer 0,
)
./libraries/classes/DatabaseInterface.php#1866: PhpMyAdmin\DatabaseInterface->tryQuery(
   string 'SHOW WARNINGS',
   integer 256,
   integer 0,
   boolean false,
)
./libraries/classes/DatabaseInterface.php#1950: PhpMyAdmin\DatabaseInterface->fetchResult(
   string 'SHOW WARNINGS',
   NULL,
   NULL,
   integer 256,
)
./libraries/classes/Operations.php#1972: PhpMyAdmin\DatabaseInterface->getWarnings()
./libraries/classes/Sql.php#2232: PhpMyAdmin\Operations->getWarningMessagesArray()
./libraries/classes/Sql.php#2100: PhpMyAdmin\Sql->executeQueryAndGetQueryResponse(
   array,
   boolean true,
   string 'db_name',
   string 'benana_posts',
   NULL,
   NULL,
   NULL,
   NULL,
   NULL,
   NULL,
   string '',
   string './themes/pmahomme/img/',
   NULL,
   NULL,
   NULL,
   string 'SELECT * FROM `benana_posts`',
   NULL,
   NULL,
)
./sql.php#241: PhpMyAdmin\Sql->executeQueryAndSendQueryResponse(
   array,
   boolean true,
   string 'db_name',
   string 'benana_posts',
   NULL,
   NULL,
   NULL,
   NULL,
   NULL,
   NULL,
   string '',
   string './themes/pmahomme/img/',
   NULL,
   NULL,
   NULL,
   string 'SELECT * FROM `benana_posts`',
   NULL,
   NULL,
)

 

[Bitpalast]

I must have misunderstood the question. Your question was why the database server cancels the connection when line 41 of the table that ought to be dumped is reached.

"Lost connection to MySQL server during query when dumping table `table_posts` at row: 41"

So we need to examine what row 41 is. A row is the equivalent to a dataset. So we need to look at the content of the 41st dataset of the table_posts table (and just to make sure we get it right to the 40s and 42nd, too).

You are writing
"When I try to see what is in line 41, this following error occurs in phpmyadmin".
So I understand that you try to open the 41st dataset and an error happens?
Can you see the dataset in the normal phpMyAdmin row view, e.g. when you don't click on "edit" or anything else, but simply browse the table?

Here are two things you should consider:
- Try to run a "repair" on the table and the database. The table structure might be broken.
- Did you upgrade your database version along with the upgrade of Plesk? I am asking, because the error does not seem to be Plesk-related at all. It more seems like a MySQL or MariaDB database issue with a specific table that has a specific content. If you upgraded your database, did your complete that process by
MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysql_upgrade -uadmin
so that all the internal table structures are ugraded, too?

 

[zihniates]

I just updated only plesk

I'll try

MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysql_upgrade -uadmin

command.

 

[Bitpalast]

If you did not upgrade the database, do not run the mysql_upgrade command. This is only one step needed when upgrading the database, e.g. from MariaDB 10.1 to 10.3. If you have not done that upgrade, do not run mysql_upgrade.

 

[zihniates]

Can you see the dataset in the normal phpMyAdmin row view, e.g. when you don't click on "edit" or anything else, but simply browse the table?

benana_post cannot be displayed, but other tables displayed.

 

[zihniates]

If you did not upgrade the database, do not run the mysql_upgrade command. This is only one step needed when upgrading the database, e.g. from MariaDB 10.1 to 10.3. If you have not done that upgrade, do not run mysql_upgrade.

no i didn't upgrade

 

[Bitpalast]

The issue is the foreign_key_check constraint. The table seems to be linked with another table that is missing or damaged or the table itself is damaged.

Could you please attempt to run this SQL query directly:

Set foreign_key_checks = 0;

and see what happens? Then try to open the table again?

 

[zihniates]

The issue is the foreign_key_check constraint. The table seems to be linked with another table that is missing or damaged or the table itself is damaged.

Could you please attempt to run this SQL query directly:

Set foreign_key_checks = 0;

and see what happens? Then try to open the table again?

I ran the query but there was no change.

 

[Bitpalast]

The table still does not open???

 

[zihniates]

The table still does not open???

not open..

Same Error:
Missin Expression. (near "ON" at position 25)
SET FOREIGN_KEY_CHECKS = ON;
MySQL output:
#2006 - MySQL server has gone away

 

[Bitpalast]

The problem is likely that a constraint on a field was defined, but the referenced table no longer exists, is inaccessible or the data in that field is out of range of the constraint. It is also still possible that the table is simply damaged. First thing to do is to check the table

check table benana_post;

and if it is damaged, then run

repair table benana_post;

If the table is good, the issue is caused by a constraint that is no longer working correclty. You'll need to find out what constraint in that table structure is causing the problem. For that you need admin permissions on the database. Then you can try this:

select * from information_schema.referential_constraints where constraint_schema = '<databasename>';

The output shows all constraints of the <databasename> (insert real database name instead of the <...> segment). Look into "table_name" of the output to find your table benana_post. Now, in that line for the benana_post table, look for the constraint_name column and note the constraint_name given there.
Make a backup of all tables but the benana_post that is causing trouble, before you alter anything. Then try to remove the constraint with

ALTER TABLE benana_post DROP FOREIGN KEY constraint_name

Afterwards, the table should become accessible again.

There is another thing that could be tried, but it is risky. You can try to set a global "Off" to foreign_key_checks. However, if you are not the only user on the machine or other software is currently using the database, e.g. adding or removing data, this could lead to data inconsistencies. If you want to try it as a last resort you could

SET GLOBAL FOREIGN_KEY_CHECKS=0;

No warranty for this one though. And make sure you set it back to ...=1 after successfully solving the benana_post-issue.

 

[Bitpalast]

SQL admin user name: admin (not root)

SQL admin password:

How to connect to a MySQL server on a Plesk for Linux server using a MySQL admin password in plain text

Applicable to: Plesk for Linux Question How to connect to a MySQL server on Linux using a MySQL admin password in plain text? How to get MySQL admin password in plain text? Answer For securi...

support.plesk.com

 

[zihniates]

The problem is likely that a constraint on a field was defined, but the referenced table no longer exists, is inaccessible or the data in that field is out of range of the constraint. It is also still possible that the table is simply damaged. First thing to do is to check the table

check table benana_post;

and if it is damaged, then run

repair table benana_post;

If the table is good, the issue is caused by a constraint that is no longer working correclty. You'll need to find out what constraint in that table structure is causing the problem. For that you need admin permissions on the database. Then you can try this:

select * from information_schema.referential_constraints where constraint_schema = '<databasename>';

The output shows all constraints of the <databasename> (insert real database name instead of the <...> segment). Look into "table_name" of the output to find your table benana_post. Now, in that line for the benana_post table, look for the constraint_name column and note the constraint_name given there.
Make a backup of all tables but the benana_post that is causing trouble, before you alter anything. Then try to remove the constraint with

ALTER TABLE benana_post DROP FOREIGN KEY constraint_name

Afterwards, the table should become accessible again.

There is another thing that could be tried, but it is risky. You can try to set a global "Off" to foreign_key_checks. However, if you are not the only user on the machine or other software is currently using the database, e.g. adding or removing data, this could lead to data inconsistencies. If you want to try it as a last resort you could

SET GLOBAL FOREIGN_KEY_CHECKS=0;

No warranty for this one though. And make sure you set it back to ...=1 after successfully solving the benana_post-issue.

Here is result,
MariaDB [wwwbalaban_li]> check table benana_posts;
+----------------------------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+----------------------------+-------+----------+----------+
| wwwbalaban_li.benana_posts | check | status | OK |
+----------------------------+-------+----------+----------+

I still tried to repair it,

MariaDB [wwwbalaban_li]> repair table benana_posts;
+----------------------------+--------+----------+---------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+----------------------------+--------+----------+---------------------------------------------------------+
| wwwbalaban_li.benana_posts | repair | note | The storage engine for the table doesn't support repair |
+----------------------------+--------+----------+---------------------------------------------------------+

In the meantime, i have some errors in my vps.
I cannot connect to mysql shell as root.
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

When I stop the mysql server and try to change the mysql root password, I still get an error.

systemctl stop mysqld.service

Failed to stop mysqld.service: Unit mysqld.service not loaded.

systemctl status mysqld.service

Unit mysqld.service could not be found.

 

[Bitpalast]

Please don't get confused or nervous about the case. Your database is fine, your database is online. You are using the wrong admin user name. As I added in my previous post (see above), use "admin" as the user name. In case you don't remember the password, the article describes how to find the database password off the "admin" user. There is no need to restart the database.

 

[zihniates]

Please don't get confused or nervous about the case. Your database is fine, your database is online. You are using the wrong admin user name. As I added in my previous post (see above), use "admin" as the user name. In case you don't remember the password, the article describes how to find the database password off the "admin" user. There is no need to restart the database.

ok, im login in admin in mysql shell.


MariaDB [db_name]>

select * from information_schema.referential_constraints where constraint_schema = '<db_name>';

Empty set (0.00 sec)

MariaDB [db_name]>

ALTER TABLE benana_posts DROP FOREIGN KEY constraint_name;

ERROR 2013 (HY000): Lost connection to MySQL server during query

 

[Bitpalast]

In the email notification I see that you had entered <db_name>. I was unclear in instructions here. The < and > are only an indication that something needs to be entered here that is individual. ut the < and > shoudl not be there in the statement that you actually run. Could you please repeat the last two statements (the select and maybe the alter) with correct parameters? The <...> means "insert database name here", but without the < and >, so it should not be <db_name>, but db_name like 'db_name'.

It should then yield a result and no empty set.

The "constraint_name" is a placeholder for the constraint_name determined by the select statement for that very table. So in the second query, replace constraint_name with the constraint name that is display ed in the output of the first query.

 

[zihniates]

Hi Peter, I can little speak English so I might have misunderstood or spelled some wrong. I am grateful to you for trying to help me.
I took the steps you mentioned respectively

MariaDB [wwwbalaban_li]>

SET GLOBAL FOREIGN_KEY_CHECKS=0;

Query OK, 0 rows affected (0.00 sec)

After running the above command, I still cannot see the content of the 41st line.

SET FOREIGN_KEY_CHECKS = OFF;
MySQL Output:
#2006 - MySQL server has gone away

check table benana_posts;

+----------------------------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+----------------------------+-------+----------+----------+
| wwwbalaban_li.benana_posts | check | status | OK |
+----------------------------+-------+----------+----------+
1 row in set (0.67 sec)

repair table benana_posts;

+----------------------------+--------+----------+---------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+----------------------------+--------+----------+---------------------------------------------------------+
| wwwbalaban_li.benana_posts | repair | note | The storage engine for the table doesn't support repair |
+----------------------------+--------+----------+---------------------------------------------------------+

select * from information_schema.referential_constraints where constraint_schema = 'wwwbalaban_li';

ALTER TABLE benana_posts DROP FOREIGN KEY fk_benana_wc_download_log_permission_id;

(fk_benana_wc_download_log_permission_id / CONSTRAINT_NAME)

I am getting the following error.

ERROR 2013 (HY000): Lost connection to MySQL server during query
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 4
Current database: wwwbalaban_li


I checked aborted connections via

SHOW GLOBAL STATUS LIKE  'Aborted_connects';

+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| Aborted_connects | 0 |
+------------------+-------+

here is my.cnf

[mysqld]
bind-address = ::ffff:127.0.0.1
local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in This page has moved

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid
innodb_buffer_pool_size=100M
max_allowed_packet=1024M
net_read_timeout=3600
net_write_timeout=3600
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
innodb_buffer_pool_size=100M
max_allowed_packet=1024M
net_read_timeout=3600
net_write_timeout=3600

 

[Bitpalast]

This is really bad. If dropping the constraints does not resolve the issue, I have no solution in what other way this could be solved but to remove the table or even the whole database and restore it from a backup.

Have you looked into /var/log/messages if mysqld or mariadb is logging anything useful there?

In some posts on the Internet I have read that for the constraints issue a bug is logged with MariaDB. It seems that the constraint data is stored in a certain part of the InnoDB table file that is missing or has a different length than expected. For that reason the issue with the constraint can occur even when the data in the table is actually consistent.