Confluence: Lock wait timeout exceeded; try restarting transaction

WARNING! Atlassian themselves recommend STRONGLY against this procedure. If any action, take the action that shows you which table is locking, DO NOT DELETE anything unless you are 100% confident you can reverse your deletions. DO NOT DELETE, DO NOT DELETE!

Seeing this?

2013-05-14 16:39:55,581 ERROR [QuartzScheduler_Worker-1] [sf.hibernate.util.JDBCExceptionReporter] logExceptions Lock wait timeout exceeded; try restarting transaction
2013-05-14 16:39:55,581 ERROR [QuartzScheduler_Worker-1] [sf.hibernate.impl.SessionImpl] execute Could not synchronize database state with session


The first is actually reported from MySQL itself, the second from Hibernate, which wraps databases for Java apps.

If you are desperate, try deleting all rows from mysql's crowd.cwd_membership table after backing it up, worked for me, syncs started working again in under 16ms.

1. mysqldump crowd | bzip2 -c > /mnt/dump_crowd_`date +%Y%m%d`.sql.bz2
2. mysql crowd -e 'delete from cwd_membership'

If that doesn't help, try deleting old users from any confluence groups that are still in your LDAP dir, be brutal. While your at it, delete old users period from LDAP.

To spot the problem table, this might help, if another table is your problem:

1. watch "mysql -e 'show processlist'"
2. Then, run LDAP sync update via admin web GUI, and watch to see which table is locking

Other things you might be seeing in your logs if you have this issue:

"batch failed falling back to individual processing java.lang.RuntimeException: could not flush session"

"Error occurred while refreshing the cache for directory"
"synchroniseCache full synchronisation for directory [ XXXX ] starting"
"could not insert: [com.atlassian.crowd.embedded.hibernate2.HibernateMembership#YYYYY]"
"Lock wait timeout exceeded; try restarting transaction"
"Could not synchronize database state with session"
"could not flush session"

keytool: put your SSL key into a new keystore

1. openssl pkcs12 -export -in mycert.crt -inkey mykey.key -out myp12blob.p12 -name mykeystorealias -CAfile mycascert.crt
1. Set the password to "changeit"
2. keytool -importkeystore -deststorepass changeit -destkeypass changeit -destkeystore mykeystore -srckeystore myp12blob.p12 -srcstoretype PKCS12 -srcstorepass changeit -alias myalias
3. keytool -list -v -keystore mykeystore

Taken: https://confluence.atlassian.com/display/DOC/Running+Confluence+Over+SSL+or+HTTPS

Searching with an LDAP filter

1. Set the dn you wish to search through
1. e.g., ou=Employees,dc=mycompaniesdomain,dc=com
2. Set the filter
1. e.g., (&(objectclass=inetorgperson)(uid=myfirstname.mylastname))
1. inetorgperson is an LDAP standard "object", btw, there are a bunch of others

Btw: one can also -- quick and dirty -- dump the whole LDAP db to a ldif file, and do a text search on that.