Sync deleting users but not deleting users

[jriker1]

OK, so the subject is wierd but it denotes the problem.  Due to an accidental setting with my ldap settings, I sucked in my entire company off the AD into Alfresco.  I only wanted one group.  Now I have it set correctly and nightly it goes thru and shows in the tomcat logs that it deleted like 50 some thousand users from the system:

01:19:37,390  WARN  [security.sync.ChainingUserRegistrySynchronizer] Deleting user '<network_id_here>'

Problem is, said user still exists in the system and enabled.  It is not applying the additional attributes that the users in the group I want to keep get, but when it tries to delete it as I would expect it should since these users are not in my domain group I am pointing to, it tells me the above without an error and the user is still there.  Any ideas?

Thanks.

JR

[dward]

It could be that the transaction is failing and is rolled back at the end.

Are there any errors near the end of the log when the sync finishes?

Are you using a recent build from HEAD? Builds after the 6th of August split the changes into small transactions for improved performance.

[jriker1]

I'm definitely using a HEAD build after the 6th Its reporting build 3002 if that helps. Should I see a message at the end if it finished?  Last thing I see is a delete line like thud above.  Or is there a way to see if it's doing this in batches?  Or log4j settings I can enable?

Thanks

JR

[jriker1]

By the way, not sure if this is unusual but get these in between some of the synch processing.  It seems to continue on after the error though.  Put in bits and pieces so the thread wasn't overloaded with the length of the exception.

00:35:02,516  ERROR [quartz.core.JobRunShell] Job DEFAULT.ldapPeopleJobDetail threw an unhandled Exception:
org.hibernate.exception.GenericJDBCException: could not insert: [org.alfresco.repo.attributes.LongAttributeImpl]
        at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:103)
        at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:91)
        at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
        at org.hibernate.id.insert.AbstractSelectingDelegate.performInsert(AbstractSelectingDelegate.java:40)
        at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2163)
        at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2643)
        at org.hibernate.action.EntityIdentityInsertAction.execute(EntityIdentityInsertAction.java:51)
        at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:322)‍‍‍‍‍‍‍‍‍‍

Caused by: java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1055)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3491)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3423)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1936)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2060)‍‍‍‍‍‍‍

00:35:02,554  ERROR [quartz.core.ErrorLogger] Job (DEFAULT.ldapPeopleJobDetail threw an exception.
org.quartz.SchedulerException: Job threw an unhandled exception. [See nested exception: org.hibernate.exception.GenericJDBCException: could not insert: [org.alfresco.repo.attributes.LongAttributeImpl]]
        at org.quartz.core.JobRunShell.run(JobRunShell.java:213)
        at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:529)
Caused by: org.hibernate.exception.GenericJDBCException: could not insert: [org.alfresco.repo.attributes.LongAttributeImpl]
‍‍‍‍‍‍‍

Caused by: java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1055)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3491)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3423)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1936)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2060)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2548)
‍‍‍‍‍‍‍‍‍

Thanks

JR

[jriker1]

Also this inquiry seems to have killed all other threads, but is there a good way if I shut down Alfresco to cleanly delete users manually in the MySQL DB?

Thanks.

JR

[dward]

No. Do not modify the database yourself or you may corrupt it. Why not just sync with a query that returns no users?

The errors above seem to show transaction contention. Was the server heavily loaded at the time? Are you using a recent build?

[jriker1]

No. Do not modify the database yourself or you may corrupt it. Why not just sync with a query that returns no users?

The errors above seem to show transaction contention. Was the server heavily loaded at the time? Are you using a recent build?

If I sync with a query that returns no users it's still going to go thru one at a time and remove them from Alfresco right?  If that's the case it will still do what its doing now.  As for heavy use, alfresco's sync job was killing the system so a couple other systems died trying to connect.  The DB itself is not heavily memory used, just disk I/O on the DB from the sync cleanup.  It started around midnight.  seemed to stop at 6:50:02 and then without a line in between in the logs, continued at 13:43:45.

As for the version, did you see my posts higher up this thread?  I did also try and use a recent HEAD edition from last Saturday however it had issues running due to missing columns in the DB that did not exist in any of the scripts.

Thanks.

JR

[dward]

I've just checked the code and it seems that the 'batching' improvement I added didn't make it to the deletion code. I guess I never conceived that someone might want to remove thousands of users in one shot!

I've logged a JIRA and will try to get the fix into HEAD as soon as I'm allowed!

https://issues.alfresco.com/jira/browse/ETHREEOH-2835

Were your upgrade problems related to audit tables? If you post the error message you got we might be able to help.

[jriker1]

Thanks Dave.  Hopefully it will be put in there soon.  Odd part is the deletes run and run and run and never error.  I see the 80,000 entries in the log being deleted but nothing is gone when it stops outputting delete lines in the log.  There was a 15 - 20 second spike in the processing on the DB after it finished but didn't catch the query fast enough to tell if it was a rollback.

With the error, don't have it saved anymore.  Just remember it was audit related, was a missing column in an existing table, and although it said everything was started in the logs, you couldn't pull up the login screen.  Not unusual though from other errors I have seen.  If it's stll there when your update is available in the HEAD revision, I will let you know what I am seeing.

JR

[jriker1]

I noticed that the JIRA ticket showed it was fixed a little bit ago.  Has that made it into SVN yet?  If so what is the build number in the SVN repository so I can look it up and do you know if they will have a HEAD release by this weekend?

Thanks.

JR