topic Re: Sync deleting users but not deleting users in Alfresco Archive

Sync deleting users but not deleting users

jriker1 — Wed, 09 Sep 2009 12:19:03 GMT

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 so

Re: Sync deleting users but not deleting users

dward — Wed, 09 Sep 2009 14:27:20 GMT

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.

Re: Sync deleting users but not deleting users

jriker1 — Wed, 09 Sep 2009 21:55:06 GMT

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

Re: Sync deleting users but not deleting users

jriker1 — Thu, 10 Sep 2009 13:25:13 GMT

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

Re: Sync deleting users but not deleting users

jriker1 — Thu, 10 Sep 2009 14:04:34 GMT

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

Re: Sync deleting users but not deleting users

dward — Thu, 10 Sep 2009 14:51:24 GMT

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?

Re: Sync deleting users but not deleting users

jriker1 — Thu, 10 Sep 2009 15:07:04 GMT

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

Re: Sync deleting users but not deleting users

dward — Thu, 10 Sep 2009 15:38:42 GMT

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.

Re: Sync deleting users but not deleting users

jriker1 — Thu, 10 Sep 2009 18:46:41 GMT

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

Re: Sync deleting users but not deleting users

jriker1 — Fri, 11 Sep 2009 18:03:38 GMT

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

Re: Sync deleting users but not deleting users

dward — Sat, 12 Sep 2009 16:47:58 GMT

It's not on HEAD yet because there is a freeze on non-RM check ins at the moment. Will let you know when it is.

Re: Sync deleting users but not deleting users

jriker1 — Mon, 14 Sep 2009 03:01:51 GMT

It's not on HEAD yet because there is a freeze on non-RM check ins at the moment. Will let you know when it is.

Understood. If you think it may be a while please feel free to post or msg me the changes or files that need to be updated. Every night when the sync job runs it kills the prod box and Alfresco has to be restarted.

Thanks.

JR

Re: Sync deleting users but not deleting users

dward — Mon, 14 Sep 2009 15:23:27 GMT

I've attached a diff to the JIRA issue.

Re: Sync deleting users but not deleting users

jriker1 — Tue, 15 Sep 2009 17:38:17 GMT

I've attached a diff to the JIRA issue.

Thanks you rock. I incorporated your changes and it worked like a charm. All 84k users are gone except the ones in my Alfresco group. If you want me to test the latest build when this gets incorporated in officially to see if I still have the DB problems, you can still feel free to let me know if you remember.

Thanks.

JR

Re: Sync deleting users but not deleting users

jriker1 — Thu, 29 Oct 2009 14:19:08 GMT

Dave,

I am updating my code to the latest release and went to verify if these changes were in there. Doesn't look so, however JIRA says the problem has been fixed and resolved. Can you verify? The ChainingUserRegistrySynchronizer.java looks like it may be different than the original one however does not match your code updates included in the ticket.

Thanks.

JR

Re: Sync deleting users but not deleting users

jriker1 — Fri, 08 Jan 2010 03:37:46 GMT

Curious if it's safe to assume the code is now in the current HEAD version or if I still have to keep updating based on provided change log.

Thanks.

JR

Re: Sync deleting users but not deleting users

dward — Tue, 12 Jan 2010 12:25:31 GMT

Sorry I didn't update this. Yes the code in HEAD (and 3.2r2) now contains the fix. Since I made the original fix we tuned the synchronizer and schema to be even more fault tolerant and scalable to even larger user directories. I hope you like it.