topic LDAP sync fails after CleanAlfPropTablesPostExec.sql/CleanAlfPropTables.sql in Alfresco Forum

LDAP sync fails after CleanAlfPropTablesPostExec.sql/CleanAlfPropTables.sql

idwright — Tue, 03 Sep 2019 10:11:25 GMT

My ldap sync is broken with the error message:2019-08-10 03:16:18,271 ERROR [org.quartz.core.JobRunShell] [DefaultScheduler_Worker-3] Job DEFAULT.ldapPeopleJobDetail threw an unhandled Exceptionrg.springframework.dao.DataIntegrityViolationException: No property value exists for ID 2927643 at org.a

Re: LDAP sync fails after CleanAlfPropTablesPostExec.sql/CleanAlfPropTables.sql

angelborroy — Tue, 03 Sep 2019 10:31:23 GMT

It looks like there are some properties missing in the database.

Try to remove them manually, something like this:

delete from alf_prop_unique_ctx where prop1_id = 2927643;

delete from alf_prop_root where id = 2927643;‍‍‍‍‍‍

Re: LDAP sync fails after CleanAlfPropTablesPostExec.sql/CleanAlfPropTables.sql

yreg — Tue, 03 Sep 2019 12:25:14 GMT

I have solved this a while back by executing a JS snippet in JS Console, I couldn't find the final one, but I found the initial draft which might work from the first try or might require some fine-tuning first. PLEASE TEST IT ON A NON CRUCIAL ENVIRONMENT FIRST.

var context = Packages.org.springframework.web.context.ContextLoader.getCurrentWebApplicationContext();
var SyncStatus = Packages.org.alfresco.repo.security.sync.SyncStatus;
var attributeService = context.getBean('attributeService', Packages.org.alfresco.service.cmr.attributes.AttributeService);
 
var ROOT_ATTRIBUTE_PATH = ".ChainingUserRegistrySynchronizer";
var STATUS_ATTRIBUTE = "STATUS";
 
logger.log(attributeService.getAttribute(ROOT_ATTRIBUTE_PATH, STATUS_ATTRIBUTE));
 
 
 
/* experimental stuff */
 
attributeService.removeAttribute(ROOT_ATTRIBUTE_PATH, STATUS_ATTRIBUTE);
 
attributeService.createAttribute(SyncStatus.COMPLETE_ERROR.toString() ,ROOT_ATTRIBUTE_PATH, STATUS_ATTRIBUTE);
 
logger.log(attributeService.getAttribute(ROOT_ATTRIBUTE_PATH, STATUS_ATTRIBUTE));‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: LDAP sync fails after CleanAlfPropTablesPostExec.sql/CleanAlfPropTables.sql

afaust — Tue, 03 Sep 2019 13:44:44 GMT

Please, always be aware that the in-memory caches of Alfresco can still contain some values (e.g. mapping String value x to ID y) which CleanAlfPropTables may delete. The whole prop_value code in Alfresco never checks if the cached entries still exist before (re-)linking and will throw up if they don't. Typically, a restart is required / advisable after running CleanAlfPropTables to ensure caches are clear, or you should manually clear the caches via the OOTBee Support Tools.

Problems like this had motivated me to write an improved cleanup process, which also includes clearing any old cache entries, so it is safe to run any synchronisation processes afterwards.

Re: LDAP sync fails after CleanAlfPropTablesPostExec.sql/CleanAlfPropTables.sql

idwright — Wed, 04 Sep 2019 06:57:07 GMT

Unfortunately not the caches.

The script from Younes REGAIEG‌ provided a good start but more than one property was involved. By using the stack trace, either from the logs or in the javascript console, I could look up the names of the relevant properties in the code using the line numbers. (Easiest way to see the full stack trace is to not catch the exception from getAttribute)

This should be safe to run as it only does something if an exception is thrown when trying to get an attribute.

You may need to change the value of zone to match your configuration.

var context = Packages.org.springframework.web.context.ContextLoader.getCurrentWebApplicationContext();
var SyncStatus = Packages.org.alfresco.repo.security.sync.SyncStatus;
var attributeService = context.getBean('attributeService', packages.org.alfresco.service.cmr.attributes.AttributeService);

var ROOT_ATTRIBUTE_PATH = ".ChainingUserRegistrySynchronizer";
var STATUS_ATTRIBUTE = "STATUS";
var SUMMARY_ATTRIBUTE = "SUMMARY";
var zone = "ldap1";
try {
logger.log(attributeService.getAttribute(ROOT_ATTRIBUTE_PATH, STATUS_ATTRIBUTE));
} catch(excep) {
  logger.log("Missing attribute:" + ROOT_ATTRIBUTE_PATH + " " + STATUS_ATTRIBUTE + excep);
  attributeService.removeAttribute(ROOT_ATTRIBUTE_PATH, STATUS_ATTRIBUTE);
  attributeService.createAttribute(SyncStatus.COMPLETE_ERROR.toString() ,ROOT_ATTRIBUTE_PATH, STATUS_ATTRIBUTE);
  logger.log("Created attribute");
  logger.log(attributeService.getAttribute(ROOT_ATTRIBUTE_PATH, STATUS_ATTRIBUTE));
}
try {
  logger.log(attributeService.getAttribute(ROOT_ATTRIBUTE_PATH, STATUS_ATTRIBUTE, zone));
} catch(excep) {
  logger.log("Missing attribute:" + ROOT_ATTRIBUTE_PATH + " " + STATUS_ATTRIBUTE + " " + zone + excep);
  attributeService.removeAttribute(ROOT_ATTRIBUTE_PATH, STATUS_ATTRIBUTE, zone);
  attributeService.createAttribute(SyncStatus.COMPLETE_ERROR.toString() ,ROOT_ATTRIBUTE_PATH, STATUS_ATTRIBUTE, zone);
  logger.log("Created attribute");
  logger.log(attributeService.getAttribute(ROOT_ATTRIBUTE_PATH, STATUS_ATTRIBUTE, zone));
}
try {
  logger.log(attributeService.getAttribute(ROOT_ATTRIBUTE_PATH, SUMMARY_ATTRIBUTE, zone));
} catch(excep) {
  logger.log("Missing attribute:" + ROOT_ATTRIBUTE_PATH + " " + SUMMARY_ATTRIBUTE + " " + zone + excep);
  attributeService.removeAttribute(ROOT_ATTRIBUTE_PATH, SUMMARY_ATTRIBUTE, zone);
  logger.log("Removed attribute");
}

Re: LDAP sync fails after CleanAlfPropTablesPostExec.sql/CleanAlfPropTables.sql

heiko_robert — Thu, 05 Sep 2019 08:07:36 GMT

Ian Wright‌, thanks for sharing your solution!
just a small typo: in line #3 "packages.org.alfresco..." should start with a upper case letter.

Re: LDAP sync fails after CleanAlfPropTablesPostExec.sql/CleanAlfPropTables.sql

ainsof — Mon, 06 Apr 2020 04:03:47 GMT

Thanks so much. This script worked like magic. Had to run it twice for some reason but so happy it worked. Been looking for a fix for a few days. Saved me from restoring an old backup.

Seems the issue was triggered by an incomplete running of the "propTablesCleanupJobDetail" scheduled task

Re: LDAP sync fails after CleanAlfPropTablesPostExec.sql/CleanAlfPropTables.sql

michaelzietlow — Fri, 25 Jun 2021 21:39:55 GMT

Thanks you @angelborroy ,
Deleting the ID(s) from the db as you suggested cleared my errors...

Re: LDAP sync fails after CleanAlfPropTablesPostExec.sql/CleanAlfPropTables.sql

shriyam_gupta — Tue, 28 Nov 2023 11:49:21 GMT

Where and how did you run this cript can you provide with me with steps i am facing the same issue and trying to figure out the solution

Re: LDAP sync fails after CleanAlfPropTablesPostExec.sql/CleanAlfPropTables.sql

shriyam_gupta — Wed, 29 Nov 2023 19:09:31 GMT

I was able to fix this issue by manually updating the Id's to Null in table for coloumn prop1_id
found out the root cause of this issue was breaking of alfresco schedule job called PostpropCleanerDetail job
the system was unable to find any property values of ID's in prop1_id coloumn and was throwing the error, on putting it to null the alfresco created new prop1_id automatically and all the errors disappeared and ldap sync started working. Deleting these Id's works the same way.