Issue I lost panel access after changing mysql directory

[nexance]

Server operating system version

CentOS 7.9.2009 x86_64

Plesk version and microupdate number

Plesk Obsidian 18.0.49.2

I lost painel access after change mysql directory, I did all mysql's socket configurations, but the issue persist

 

[Peter Debik]

Could you please be more specific what exactly was changed? There are at least four different basic directories from where MySQL or MariaDB files and directories descend. Have you tried to login to the MySQL client on the console? Is the database server up?

 

[nexance]

A change has been made to the location of the MySQL database. Previously, it was located in the /var/lib/mysql directory, but it has now been moved to /home/mysql/. This change required updates to the /usr/local/psa/admin/conf/php.ini and /etc/psa/psa.conf files to ensure that the MySQL socket was pointing to the correct location. Although the MySQL database is functioning properly and the websites are also running smoothly, the Plesk web interface has stopped due to a permission error in accessing the database.

DB query failed: SQLSTATE[HY000] [2002] Permission denied
I executed the plesk repair db command and the plesk repair web, both of then return OK, but the problem persist.

 

[scsa20]

Do you have SELinux enabled? If so, disable it. SELinux is known to stop things from working correctly if you're placing things in places it usually isn't (like, for example, putting database files from the standard /var/lib/ to a /home directory since /home should be used for the user's directories and not for actual application or database files).

 

[Peter Debik]

- What is the output of # getfacl /home/mysql?
- What is the output of # ll /home/mysql/mysql.sock?

 

[nexance]

getfacl: Removing leading '/' from absolute path names
# file: home/mysql
# owner: mysql
# group: mysql
user::rwx
group::---
other::---

srwxrwxrwx 1 mysql mysql 0 Feb 10 09:37 /home/mysql/mysql.sock

 

[nexance]

Do you have SELinux enabled? If so, disable it. SELinux is known to stop things from working correctly if you're placing things in places it usually isn't (like, for example, putting database files from the standard /var/lib/ to a /home directory since /home should be used for the user's directories and not for actual application or database files).

SELinux status: disabled

 

[nexance]

- What is the output of # getfacl /home/mysql?
- What is the output of # ll /home/mysql/mysql.sock?

getfacl: Removing leading '/' from absolute path names
# file: home/mysql
# owner: mysql
# group: mysql
user::rwx
group::---
other::---

srwxrwxrwx 1 mysql mysql 0 Feb 10 09:37 /home/mysql/mysql.sock

 

[Peter Debik]

The first should be

user::rwx
group::r-x
other::r-x

So in your system "group" and "other" permissions are missing. One problem is that with each restart the mysql.sock permissions are changed to what's set in the ACL configuration. And that has maybe been inherited from your /home directory when you moved the DB directory there. It can be a reason why access fails.

I think it should be safe to do:
# setfacl -R -m g::rx /home/mysql
# setfacl -R -m o::rx /home/mysql
It should recursively allow group read and executable access for all groups and others on /home/mysql and all its subdirectories.

But as always: Disclaimer: Have a backup, please, and I'm not on your system so I cannot be sure whether other factors are there to take into account.

 

[nexance]

# setfacl -R -m g::rx /home/mysql
# setfacl -R -m o::rx /home/mysql

I tried do this, but dont works, after this I tried to exec plesk repair installation, follow response

ERROR: Upgrade step 2019-04-25-14-46-58_ReportUsage.php failed with code 1 and output:
[2023-02-10 14:46:16.903] 31228:63e682e8dc6dc ERR [panel] DB query failed: SQLSTATE[HY000] [2002] Permission denied:
0: /usr/local/psa/admin/plib/Upgrade/upgrade.php:46

ERROR: Plesk\Upgrade\Exception: DB query failed: SQLSTATE[HY000] [2002] Permission denied (upgrade.php:46)
0: /usr/local/psa/admin/plib/Upgrade/upgrade.php:46

 

[Peter Debik]

After setting ACL, checking that mode of the socket file is 777 and restart of database this should not occur. Especially as you say that other databases can be accessed. I assume that # plesk db also fails? Or does it let you login to the database console?

 

[nexance]

After setting ACL, checking that mode of the socket file is 777 and restart of database this should not occur. Especially as you say that other databases can be accessed. I assume that # plesk db also fails? Or does it let you login to the database console?

plesk db works, I can use mysql in command line normally, the problem is just to open plesk web interface, like a attached image...

 

[Peter Debik]

When you are logged in to # plesk db, can you see that the psa database is shown in
> show database;
?
Can you open it like
> use psa;
> show tables;
?

 

[Peter Debik]

Did you update /etc/passwd so that for the mysql user the new own home is known there? Like from
mysql:x:997:995:MySQL server:/var/lib/mysql:/sbin/nologin
to then new
mysql:x:997:995:MySQL server:/home/mysql:/sbin/nologin
?

I wonder if you have not also done some changes to /etc/my.cnf, because actually that would be necessary. How can it be that you didn't change the "datadir=/var/lib/mysql" to "datadir=/home/mysql" yet your databases are accessible? There must be information missing from the posts above. Are you sure you went through all necessary changes?

 

[nexance]

> use psa;
> show tables;

+-----------------------------------+
| Tables_in_psa |
+-----------------------------------+
| APSApplicationItems |
| APSCatalogUpdates |
| APSClientApplicationItems |
| APSLicenseTypes |
| APSLicenses |
| ApiRpcCallsStat |
| ApsTokens |
| BackendCache |
| BackupExcludeFiles |
| BackupsScheduled |
| BackupsSettings |
| BlUpgradeActions |
| BlUpgrades |
| CertificateRepositories |
| CliCallsStat |
| ClientsTraffic |
| Configurations |
| DatabaseCustomHosts |
| DatabaseServers |
| DatabaseUserRemoteAccessRules |
| DomainAliasesParameters |
| DomainOutgoingMessagesPeaks |
| DomainOutgoingMessagesStats |
| DomainServices |
| DomainsTraffic |
| DynamicIpSecurity |
| EmailActivations |
| GL_remote_domains |
| GL_settings |
| IP_Addresses |
| IisAppPoolDomains |
| IisAppPools |
| IisAppPoolsPolicy |
| IpAddressesCollections |
| IpCollections |
| Limits |
| LimitsReservation |
| Logos |
| MailLists |
| MailMessagesStat |
| MailOutgoingMessagesPeaks |
| MailOutgoingMessagesStats |
| ModuleSettings |
| Modules |
| Notes |
| Notifications |
| PackageUpdateLocks |
| PackageUpdateNotifications |
| PanelNotificationMessages |
| PanelNotificationTemplateSettings |
| Parameters |
| Permissions |
| PersistentCache |
| PhpSettings |
| PhpSettingsCustom |
| PhpSettingsParameters |
| PlanItemProperties |
| PlanItems |
| PlansSubscriptions |
| PleskStats |
| Repository |
| RestrictedDomains |
| SBConfig |
| SBResellers |
| ScheduledTasks |
| SchemaVersions |
| SecretKeys |
| SecretKeysIpAddresses |
| ServiceNodeCache |
| ServiceNodeCertificates |
| ServiceNodeConfiguration |
| ServiceNodeEnvironment |
| ServiceNodes |
| SessionContexts |
| SiteAppsHitsStat |
| SitePagesStat |
| Skins |
| SubscriptionOutgoingMessagesPeaks |
| SubscriptionOutgoingMessagesStats |
| SubscriptionProperties |
| Subscriptions |
| Templates |
| TmplData |
| TraceFailedRequestsProviderAreas |
| TraceFailedRequestsProviders |
| TraceFailedRequestsRules |
| UserSettings |
| WebServerSettings |
| WebServerSettingsParameters |
| Webmails |
| WebsitesDiagnostic |
| WebsitesDiagnosticDomains |
| accounts |
| actions |
| admin_aliases |
| admin_aliases_param |
| anon_ftp |
| apsContexts |
| apsContextsApplications |
| apsInstancesParameters |
| apsResources |
| apsResourcesParameters |
| apscategories |
| avstatistics |
| badmailfrom |
| certificates |
| cl_param |
| clients |
| cp_access |
| custom_buttons |
| data_bases |
| db_users |
| disk_usage |
| dns_recs |
| dns_recs_t |
| dns_refs |
| dns_zone |
| dom_param |
| domain_aliases |
| domains |
| dsn |
| event_handlers |
| exp_event |
| externalWebmails |
| forwarding |
| ftp_users |
| hosting |
| hotlink_friends |
| hotlink_prot |
| ip_pool |
| key_history |
| key_history_params |
| locales |
| log_actions |
| log_components |
| log_rotation |
| longtaskparams |
| longtasks |
| mail |
| mail_aliases |
| mail_redir |
| mail_resp |
| mass_mail |
| mass_mail_clients |
| mass_mail_domains |
| misc |
| mn_param |
| password_secrets |
| pd_users |
| protected_dirs |
| report |
| report_auto |
| report_section |
| resp_attach |
| resp_forward |
| resp_freq |
| secret_keys |
| sessions |
| smb_apsBundleFilterItems |
| smb_apsBundleFilters |
| smb_apsCategories |
| smb_apsContexts |
| smb_apsInstanceErrors |
| smb_apsInstances |
| smb_apsMetas |
| smb_apsPackageUpdates |
| smb_apsPackages |
| smb_apsPackagesCategories |
| smb_apsProvisionEnvironments |
| smb_apsProvisions |
| smb_apsResourceParameters |
| smb_apsResources |
| smb_apsSettings |
| smb_componentUpdates |
| smb_generalPermissions |
| smb_locales |
| smb_productUpgrades |
| smb_roleGeneralPermissions |
| smb_roleServicePermissions |
| smb_roles |
| smb_serviceEntryPoints |
| smb_serviceInstances |
| smb_servicePermissionAccounts |
| smb_servicePermissions |
| smb_serviceProviders |
| smb_settings |
| smb_userServicePermissions |
| smb_users |
| smtp_poplocks |
| spamfilter |
| spamfilter_preferences |
| stat |
| subdomains |
| suspend_handler_history |
| sys_users |
| upgrade_history |
| web_users |
| webalizer_group_referrer |
| webalizer_hidden_referrer

 

[Peter Debik]

O.k., so we now know that the issue is not due to a wrong db configuration. In 2017 there was a post on the same topic, and instructions from an even odler article have been quoted:

Question - MariaDB change data directory location

Hi, I'm trying to change the MariaDB data directory from /var/lib/mysql to /my_folder/mysql. Everything is good I can log into mysql in command line but plesk give me a hard time with Zend Errors. I've change my /etc/my.cnf and my /etc/psa/psa.conf to match with the new mysql location but...

talk.plesk.com

Please pay special attention to steps (3) and (5). It seems that Plesk expects the old paths and settings (for unknown reason). Maybe the given instructions can help you to get it working.

 

[nexance]

Please pay special attention to steps (3) and (5). It seems that Plesk expects the old paths and settings (for unknown reason). Maybe the given instructions can help you to get it working.

ok thank, I will try this