-
Bug
-
Resolution: Won't Fix
-
Medium
-
None
-
5.8.4
-
24
-
Severity 2 - Major
-
3
-
Confluence uses query like following query which contains OR operation when users are copying a page:
select attachment0_.CONTENTID as CONTENTID, attachment0_.SPACEID as SPACEID, attachment0_.TITLE as TITLE, attachment0_.VERSION as VERSION, attachment0_.CREATOR as CREATOR, attachment0_.CREATIONDATE as CREATION6_, attachment0_.LASTMODIFIER as LASTMODI7_, attachment0_.LASTMODDATE as LASTMODD8_, attachment0_.VERSIONCOMMENT as VERSIONC9_, attachment0_.PREVVER as PREVVER, attachment0_.CONTENT_STATUS as CONTENT11_, attachment0_.PAGEID as PAGEID from CONTENT attachment0_ where attachment0_.CONTENTTYPE='ATTACHMENT' and ((attachment0_.PREVVER=559639759 )or(attachment0_.CONTENTID=559639759 )) order by attachment0_.VERSION desc
Above query cause a performance problem in large instances when so many users try to copy different pages.
Following appears in atlassian-confluence.log:
2015-06-08 09:40:18,326 ERROR [http-nio-18009-exec-105] [atlassian.confluence.servlet.ConfluenceServletDispatcher] sendError Could not execute action -- referer: https://confluence.rakuten-it.com/confluence/pages/copypage.action?idOfPageToCopy=564860534&spaceKey=KEY | url: /confluence/pages/docopypage.action | userName: user org.springframework.jdbc.UncategorizedSQLException: Hibernate operation: Could not execute JDBC batch update; uncategorized SQLException for SQL []; SQL state [41000]; error code [1205]; Lock wait timeout exceeded; try restarting transaction; nested exception is java.sql.BatchUpdateException: Lock wait timeout exceeded; try restarting transaction at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:83) at org.springframework.orm.hibernate.HibernateAccessor.convertJdbcAccessException(HibernateAccessor.java:364) at org.springframework.orm.hibernate.HibernateAccessor.convertHibernateAccessException(HibernateAccessor.java:351) at org.springframework.orm.hibernate.HibernateTemplate.execute(HibernateTemplate.java:375) at org.springframework.orm.hibernate.HibernateTemplate.executeFind(HibernateTemplate.java:341) at com.atlassian.hibernate.HibernateObjectDao.findNamedQueryStringParams(HibernateObjectDao.java:477) at com.atlassian.hibernate.HibernateObjectDao.findNamedQueryStringParam(HibernateObjectDao.java:331) at com.atlassian.confluence.pages.persistence.dao.hibernate.AbstractHibernateAttachmentDao.findAllVersions(AbstractHibernateAttachmentDao.java:221) at com.atlassian.confluence.pages.persistence.dao.hibernate.AbstractHibernateAttachmentDao.findAllVersions(AbstractHibernateAttachmentDao.java:215) at com.atlassian.confluence.pages.attachments.CachingAttachmentDao.findAllVersions(CachingAttachmentDao.java:245) at com.atlassian.confluence.pages.DefaultAttachmentManager.getAllVersions(DefaultAttachmentManager.java:308) at com.atlassian.confluence.pages.DefaultAttachmentManager.moveAttachment(DefaultAttachmentManager.java:372) at com.atlassian.confluence.pages.DelegatorAttachmentManager.moveAttachment(DelegatorAttachmentManager.java:166) at com.atlassian.confluence.pages.CachingAttachmentManager.moveAttachment(CachingAttachmentManager.java:128) at sun.reflect.GeneratedMethodAccessor2963.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:497) ........... at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) at java.lang.Thread.run(Thread.java:745) Caused by: java.sql.BatchUpdateException: Lock wait timeout exceeded; try restarting transaction at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1819) at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1281) at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeBatch(NewProxyPreparedStatement.java:1723) at net.sf.hibernate.impl.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:54) at net.sf.hibernate.impl.BatcherImpl.executeBatch(BatcherImpl.java:128) at net.sf.hibernate.impl.SessionImpl.executeAll(SessionImpl.java:2465) at net.sf.hibernate.impl.SessionImpl.execute(SessionImpl.java:2420) at net.sf.hibernate.impl.SessionImpl.autoFlushIfRequired(SessionImpl.java:1837) at net.sf.hibernate.impl.SessionImpl.getQueries(SessionImpl.java:1594) at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1559) at net.sf.hibernate.impl.QueryImpl.list(QueryImpl.java:49) at com.atlassian.hibernate.HibernateObjectDao.lambda$findNamedQueryStringParams$6(HibernateObjectDao.java:504) at com.atlassian.hibernate.HibernateObjectDao$$Lambda$85/1225870716.doInHibernate(Unknown Source) at org.springframework.orm.hibernate.HibernateTemplate.execute(HibernateTemplate.java:370) ... 327 more Caused by: java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:998) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3835) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3771) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2435) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2582) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2535) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1911) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2145) at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1777) ... 340 more
Possible workaround:
Upgrade MySQL server to MySQL 5.6.x
- mentioned in
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...