Issue Upgrade to 18.0.75 Integrity constraint violation: 1062 Duplicate entry 'localhost-database\_-user' for key 'PRIMARY':

[AmaZili Communication]

Server operating system version

Rocky linux 8.10

Plesk version and microupdate number

18.0.75

Hi Pleskians !

After updating to plesk 18.0.75 on Rocky 8.10, We have an error message about duplicate keys :
ERR [panel] SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'localhost-database\_-user' for key 'PRIMARY':
0: /usr/local/psa/admin/plib/Upgrade/upgrade.php:46
ERROR: Plesk\Upgrade\Exception: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'localhost-database\_-user' for key 'PRIMARY' (upgrade.php:46)
0: /usr/local/psa/admin/plib/Upgrade/upgrade.php:46

Some steps of upgrade failed. Run upgrade with option --repair to rerun failed steps.

Plesk repair DB does nothing, no error,

/usr/local/psa/admin/sbin/autoinstaller --select-release-current --reinstall-patch --upgrade-installed-components gives the same error message.

Are you aware of similar issue ?
We have found nothing in plesk litterature.

Thanks for your help

 

[AmaZili Communication]

Please note that we replaced the name of "database" and "user" for privacy reasons

 

[Raul A.]

Hi,

Please run:

plesk repair db -v

Did it detect any issues?

 

[AmaZili Communication]

Thanks Raul for your answer,
Plesk repair db does not détect anything, but plesk repair installtion give the same message about duplicate entries
I can't run the repair db currently since its a prod machine, but the check give nos error as you can see below :

plesk repair db -n -v

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

Checking the structure of the Plesk database ........................ [OK]

Checking the consistency of the Plesk database ...................... [OK]

Error messages: 0; Warnings: 0; Errors resolved: 0

 

[Sebahat.hadzhi]

@AmaZili Communication , could you please access the Plesk database and execute:

plesk db "SELECT Host, Db, User FROM mysql.db WHERE Db LIKE 'database_-user%';"

where database_-user stands for what you see in the error.

 

[AmaZili Communication]

Hi,
Thanks for your help.
The amended command with real values returns nothing
Absolutly nothing.

 

[sistemas@chequemotiva]

Hi:

I have the same error, but other databases.

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '%-db\_ex1-ex1' for key 'PRIMARY':
0: /opt/psa/admin/plib/Upgrade/upgrade.php:46

I see the database via command line or phpmyadmin, but dont appear the table.

If posible that one table of psa have a invalid argument or space that when finish update of plesk?

thanks!

 

[Sebahat.hadzhi]

@AmaZili Communication thank you for the confirmation. In Plesk Obsidian 18.0.74, an upgrade step was introduced to address a security issue where database names containing underscores were not properly escaped in the mysql.db table. This could allow unintended access to databases.

The process attempts to fix this by converting names like db_name to db\_name. However, if an entry with the escaped name already exists, the upgrade fails during the FixGrantPrivileges task.

Could you please provide me with the exact entry from the error in a private message?

 

[AmaZili Communication]

Of course, I will send you the messages on a prive message