Resolved MYSQL server loses connection while trying to drop psa database to import dump.

[RMoto]

Title might be a bit misleading but thats where I was at trying to fix it last.

I have tried all manner of things

• Tried to restore dump
• Reinstalled mysql
• Tried to drop PSA to import the new dump

Mariadb is latest, no upgrade available.
Mysql folder backed up and so are the dumps just incase.
Copying over the backed up mysql folder caused issues like "Table 'psa.accounts' doesn't exist in engine,"
Locally testing the psa inside the backup is bricked but the dump is fine.


So obviously the next step after I figured out the dump is fine I tried to import it following plesk guidelines

zcat mysql.daily.dump.0.gz | sed -n '/-- Current Database: `psa`/,/-- Current Database:*/p' | MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysql -uadmin --default-character-set=utf8

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

So I tried to export it locally into a .sql and then went off to server to import it with " plesk db < psa.sql " which resulted in tables already existing.

After this the snag I have hit, mysql server loses connection / restarts whenever I try to drop psa.

MariaDB [(none)]> drop database psa;
ERROR 2013 (HY000): Lost connection to MySQL server during query

Any suggestions how to overcome this ?

PS. /var/logs/mysql has nothing more than errors about corrupt database aka the doesnt exist engine errors I assume because rest of the databases are fine, only psa is the one affected.

/var/logs/syslog only has mariadb starting, but not why it stopped.


EDIT

I tried to move over the working database on local to server. Now some tables show OK in mysqlcheck but others still not found in engine.

EDIT 2

# zcat mysql.daily.dump.0.gz | sed -n '/-- Current Database: `psa`/,/-- Current Database:*/p' | MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysql -uadmin --default-character-set=utf8
ERROR 1813 (HY000) at line 15: Tablespace for table '`psa`.`APSApplicationItems`' exists. Please DISCARD the tablespace before IMPORT.

and

MariaDB [psa]> drop table APSApplicationItems;
ERROR 1051 (42S02): Unknown table 'psa.APSApplicationItems'

 

[RMoto]

Resolved.

After being hesitant to do a full innodb recovery I decided its probably best to start from the getgo. So when following the guide linked below, I still could not backup the database and mysql dump it. Mysql server lost connection on PSA database.

 How to fix InnoDB corruption cases for the MySQL databases on Plesk for Linux?

Applicable to: Plesk for Linux Symptoms The following error is shown in Plesk: PLESK_INFO: ERROR: PleskMainDBExceptionMySQL query failed: Incorrect information in file: './psa/misc.frm' PLESK...

support.plesk.com

SOLUTION to that.

I exported psa from a dump locally and moved it over as a .sql for the replacement backup as the replacement with the suggested command ended up being 1byte.

After finishing the guide, I did

create database psa;

on mysql and after which I did

mysql -uadmin -p`cat /etc/psa/.psa.shadow` psa < bb/psa.sql

Now I had a day old dump of the sql database ready to go but plesk still didnt play ball. Turns out the exported table names got lowercased somewhere.

so I did a whole lot of renaming and now plesk is running like its new.

RENAME TABLE sessioncontexts TO SessionContexts;RENAME TABLE servicenodes TO ServiceNodes;
RENAME TABLE servicenodeenvironment TO ServiceNodeEnvironment;
RENAME TABLE servicenodeconfiguration TO ServiceNodeConfiguration;
RENAME TABLE backendcache TO BackendCache;
RENAME TABLE modules TO Modules;
RENAME TABLE ip_addresses TO IP_Addresses;
RENAME TABLE subscriptions TO Subscriptions;
RENAME TABLE smb_generalpermissions TO smb_generalPermissions;
RENAME TABLE smb_rolegeneralpermissions TO smb_roleGeneralPermissions;
RENAME TABLE subscriptionproperties TO SubscriptionProperties;
RENAME TABLE permissions TO Permissions;
RENAME TABLE limits TO Limits;
RENAME TABLE smb_apspackages TO smb_apsPackages;
RENAME TABLE domainservices TO DomainServices;
RENAME TABLE ipcollections TO IpCollections;
RENAME TABLE ipaddressescollections TO IpAddressesCollections;
RENAME TABLE parameters TO Parameters;
RENAME TABLE domainstraffic TO DomainsTraffic;
RENAME TABLE databaseservers TO DatabaseServers;
RENAME TABLE apsresources TO apsResources;
RENAME TABLE configurations TO Configurations;
RENAME TABLE smb_productupgrades TO smb_productUpgrades;
RENAME TABLE packageupdatelocks TO PackageUpdateLocks;
RENAME TABLE smb_apsbundlefilters TO smb_apsBundleFilters;
RENAME TABLE apscontexts TO apsContexts;
RENAME TABLE externalwebmails TO externalWebmails;
RENAME TABLE webmails TO Webmails;
RENAME TABLE templates TO Templates;
RENAME TABLE planssubscriptions TO PlansSubscriptions;
RENAME TABLE modulesettings TO ModuleSettings;RENAME TABLE persistentcache TO PersistentCache;
RENAME TABLE repository TO Repository;
RENAME TABLE webserversettings TO WebServerSettings;
RENAME TABLE webserversettingsparameters TO WebServerSettingsParameters;
RENAME TABLE tmpldata TO TmplData;
RENAME TABLE phpsettings TO PhpSettings;
RENAME TABLE clicallsstat TO CliCallsStat;