APS database (APSC) inconsistency after upgrade on Linux

Refers to:

  • Plesk for Linux
  • MG:02814af87bd6c62d16bb0079561931e9
  • MT:010d529f88bf0cfb3e4c9a1065ce7c5e
  • MT:1a0647b4b9eba91e746a77079852e0e1
  • MT:56f53c6aa2479a79a8b8181a63a8e36b
  • MT:795cba9abefafde5d3f47a7e3835eefe
  • MT:7c6c056a88816fde04debd8b8e2555ae
  • MT:81d2b8386c1500e879e369658fb6d1d3
  • MT:98b169639fdd5376755824512f0be1a3
  • MT:f93b6aeb8ea1025dfe7f3042d45a2ad1
  • MG:b26a2fce54efca4847a7d205b675642c
  • MT:25cba3673235305ea3bb0b350a57b412
  • MT:34cd7ef2749410e4041f213f4c3cb199
  • MT:c88d939c651296f4c67475ef107872ab

Created:

2016-11-16 13:04:42 UTC

Modified:

2017-02-13 06:36:45 UTC

0

Was this article helpful?


Have more questions?

Submit a request

APS database (APSC) inconsistency after upgrade on Linux

Symptoms

The following types of messages are shown in Plesk after upgrade:

Error: New web server configuration for some domains within the marked subscriptions was not built due to errors in configuration templates

List of applications is not loaded

The following messages are shown in the Plesk logs:

APS Catalog error: Unable to parse metadata

ERR (3): The service instance was not found.

Cause

The apsc database became inconsistent after upgrade and the connections between psa and apsc databases have been broken.

Resolution

Before manipulations back up psa and apsc databases. Use #213904125 article as reference.

  1. In order to remove the application from the database, please do the following:

1.1. Get the domains with broken configuration.

    # MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysql -u admin psa -Nse "SELECT d.name,c.status FROM Configurations c LEFT JOIN domains d ON c.objectId=d.id WHERE c.status='error'"

1.2. Get the UID for the application with missing data. The required data can be found in /var/log/plesk/panel.log or /var/log/plesk/sw-cp-server/error_log , when going to the Applications tab of the domain:

    2011-10-07T11:21:01-06:00 ERR (3): Problem retrieving application's 0b2ff3e6-0585-4f17-ac10-ded4cb5c73b1 information: resource of type aps.database with id 518e38c8-6e05-4926-a271-b157be43d472 not found by brokers

1.3 Gather all required information from the database.

    mysql> select reg.id as REGISTRY_OBJECT_ID,reg.uid as APPLICATION_UID,reg.type as TYPE,ap.id as APPLICATION_ID from apsc.aps_registry_object reg left join apsc.aps_application ap on reg.id=ap.registry_object_id where reg.uid='89d578dc-9669-4629-a3fa-6f9ea9210e8d';

+--------------------+--------------------------------------+-----------------+----------------+
| REGISTRY_OBJECT_ID | APPLICATION_UID | TYPE | APPLICATION_ID |
+--------------------+--------------------------------------+-----------------+----------------+
| 666 | 89d578dc-9669-4629-a3fa-6f9ea9210e8d | aps.application | 12 |
+--------------------+--------------------------------------+-----------------+----------------+

1.4 Remove the rest of the data.

    mysql> delete from apsc.aps_application_resource where app_id=12;
mysql> delete from apsc.aps_application where id=12;
mysql> delete from apsc.aps_registry_object where id=666;
mysql> delete from psa.apsContextsApplications WHERE (apsContextId IN ('12'));

mysql> select psa.d.id as pleskId, psa.s.object_id as subscriptionId from psa.domains d left join psa.Subscriptions s on psa.d.id=psa.s.object_id where psa.d.name = 'domain.tld';
+---------+----------------+
| pleskId | subscriptionId |
+---------+----------------+
| 229 | 221 |
+---------+----------------+

mysql> delete from psa.apsContexts where (pleskType = "hosting" AND pleskId IN (229)) OR (pleskType = "subscription" AND subscriptionId IN ('221'));

1.5. Reconfigure the domain.

    # /usr/local/psa/admin/sbin/websrvmng --reconfigure-vhost --vhost-name=domain.tld
  1. If you only have the resource ID:

2.1. The required data can be found in the "Applications" tab of the domain in question:

    https://example.com:8443/smb/service/index/resourceId/e7928006-33c9-4575-8ab8-b9a485468313

2.2. Get the resource ID.

    mysql> SELECT `apsc`.`id`, `apsc`.`uid`, `apsc`.`type`, `apsc`.`creation_time`, `apsc`.`enabled` from `aps_registry_object` `apsc` WHERE (`apsc`.`uid`) = ('e7928006-33c9-4575-8ab8-b9a485468313');
+----+--------------------------------------+--------------+---------------------+---------+
| id | uid | type | creation_time | enabled |
+----+--------------------------------------+--------------+---------------------+---------+
| 16 | e7928006-33c9-4575-8ab8-b9a485468313 | aps.resource | 2011-10-11 07:35:33 | y |
+----+--------------------------------------+--------------+---------------------+---------+

2.3. Get the application UID.

    mysql> SELECT `1`.`id`, `1`.`resource_type`, `1`.`parent_resource_id`, `2`.`uid`, `1`.`service_id` from (((`aps_application_resource` `3` RIGHT JOIN `aps_resource` `1` ON (`1`.`id`) = (`3`.`resource_id`)) LEFT JOIN `aps_application` `4` ON (`3`.`app_id`) = (`4`.`id`)) LEFT JOIN `aps_registry_object` `2` ON (`4`.`registry_object_id`) = (`2`.`id`)) WHERE (`1`.`registry_object_id`) = (16);

2.4. Gather the required information.

    mysql> SELECT reg.id AS REGISTRY_OBJECT_ID,reg.uid AS APPLICATION_UID,reg.type AS TYPE,ap.id as APPLICATION_ID FROM apsc.aps_registry_object reg LEFT JOIN apsc.aps_application ap ON reg.id=ap.registry_object_id WHERE reg.uid='a0fe2a37-1b57-4588-9571-20e2d639ea82';
+--------------------+--------------------------------------+-----------------+----------------+
| REGISTRY_OBJECT_ID | APPLICATION_UID | TYPE | APPLICATION_ID |
+--------------------+--------------------------------------+-----------------+----------------+
| 15 | a0fe2a37-1b57-4588-9571-20e2d639ea82 | aps.application | 6 |
+--------------------+--------------------------------------+-----------------+----------------+

2.5. Remove the rest of the data.

    mysql> DELETE FROM apsc.aps_application_resource WHERE app_id=6;
mysql> DELETE FROM apsc.aps_application WHERE id=6;
mysql> DELETE FROM apsc.aps_registry_object WHERE id=15;
mysql> DELETE FROM psa.apsContextsApplications WHERE (apsContextId IN ('6'));

mysql> SELECT psa.d.id AS pleskId, psa.s.object_id as subscriptionId FROM psa.domains d LEFT JOIN psa.Subscriptions s ON psa.d.id=psa.s.object_id WHERE psa.d.name = 'example.com';
+---------+----------------+
| pleskId | subscriptionId |
+---------+----------------+
| 1 | 1 |
+---------+----------------+

mysql> DELETE FROM psa.apsContexts WHERE (pleskType = "hosting" AND pleskId IN (1)) OR (pleskType = "subscription" AND subscriptionId IN ('1'));

2.6. Reconfigure the domain.

    # /usr/local/psa/admin/sbin/websrvmng --reconfigure-vhost --vhost-name=example.com
  1. If you need to remove all existing APS packages, the following bash script may be useful:

    echo "STARTED";
    date;
    MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysqldump -u admin psa > ~/psa.`date +%F_%H:%M`.sql;
    MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysqldump -u admin apsc > ~/apsc.`date +%F_%H:%M`.sql;

    MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysqldump -u admin apsc --no-data > apsc.schema.sql;
    MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysql -u admin apsc < apsc.schema.sql;

    MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysqldump -u admin psa apsContexts apsContextsApplications apsResources apsResourcesParameters aps_application aps_package aps_registry_object aps_resource aps_resource_requirement smb_apsCategories smb_apsContexts smb_apsInstanceErrors smb_apsInstances smb_apsMetas smb_apsPackageUpdates smb_apsPackages smb_apsPackagesCategories --no-data > psa.tables.schema.sql;
    MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysql -u admin psa < psa.tables.schema.sql;

    MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysql -u admin psa -e"delete from smb_userServicePermissions where servicePermissionId in (select id from smb_servicePermissions where serviceProviderId in (select id from smb_serviceProviders where classname='Smb_Service_Provider_Aps'))";

    MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysql -u admin psa -e"delete from smb_serviceEntryPoints where servicePermissionId in (select id from smb_servicePermissions where serviceProviderId in (select id from smb_serviceProviders where classname='Smb_Service_Provider_Aps'))";

    MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysql -u admin psa -e"delete from smb_servicePermissions where serviceProviderId in (select id from smb_serviceProviders where classname='Smb_Service_Provider_Aps')";

    MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysql -u admin psa -e"delete from smb_serviceInstances where serviceProviderId in (select id from smb_serviceProviders where classname='Smb_Service_Provider_Aps')";

    echo "DONE"
  2. If a client removed the application directly from the server without removing it from the databases:

4.1. Get the application UID:

    mysql> select psa.d.id as pleskId, psa.s.object_id as subscriptionId from psa.domains d left join psa.Subscriptions s on psa.d.id=psa.s.object_id where psa.d.name = 'domain.tld';
+---------+----------------+
| pleskId | subscriptionId |
+---------+----------------+
| 180 | 141 |
+---------+----------------+

mysql> select * from psa.apsContexts where (pleskType = "hosting" AND pleskId IN (180)) OR (pleskType = "subscription" AND subscriptionId IN ('141'));
+----+-----------+---------+-------+----------------+
| id | pleskType | pleskId | ssl | subscriptionId |
+----+-----------+---------+-------+----------------+
| 45 | hosting | 180 | false | 141 |
+----+-----------+---------+-------+----------------+

mysql> select * from psa.apsContextsApplications where apsContextId = 45;
+----+--------------+--------------------------------------+
| id | apsContextId | registryApplicationId |
+----+--------------+--------------------------------------+
| 52 | 45 | b2e46a1d-342e-472a-88c0-21b0e494207d |
+----+--------------+--------------------------------------+

mysql> select reg.id as REGISTRY_OBJECT_ID,reg.uid as APPLICATION_UID,reg.type as TYPE,ap.id as APPLICATION_ID from apsc.aps_registry_object reg left join apsc.aps_application ap on reg.id=ap.registry_object_id where reg.uid='b2e46a1d-342e-472a-88c0-21b0e494207d';
--------------------+--------------------------------------+-----------------+----------------+
| REGISTRY_OBJECT_ID | APPLICATION_UID | TYPE | APPLICATION_ID |
+--------------------+--------------------------------------+-----------------+----------------+
| 156 | b2e46a1d-342e-472a-88c0-21b0e494207d | aps.application | 52 |
+--------------------+--------------------------------------+-----------------+----------------+

4.2. Remove the rest of the data.

    mysql> delete from apsc.aps_application_resource where app_id=52;
mysql> delete from apsc.aps_application where id=52;
mysql> delete from apsc.aps_registry_object where id=156;
mysql> delete from psa.apsContextsApplications WHERE (apsContextId IN ('52'));

mysql> select s.object_id as pleskId,s.id as subscriptionId from psa.Subscriptions s left join psa.domains d on s.object_id=d.id where name = 'domain.tld';
+---------+----------------+
| pleskId | subscriptionId |
+---------+----------------+
| 180 | 141 |
+---------+----------------+

mysql> delete from psa.apsContexts where (pleskType = "hosting" AND pleskId IN (180)) OR (pleskType = "subscription" AND subscriptionId IN ('141'));

4.3. Reconfigure the domain

    # /usr/local/psa/admin/sbin/websrvmng --reconfigure-vhost --vhost-name=example.com
Have more questions? Submit a request
Please sign in to leave a comment.