Uploaded image for project: 'Confluence Data Center'
  1. Confluence Data Center
  2. CONFSERVER-37829

Improve select query that used in Confluence for copying a page in MYSQL

XMLWordPrintable

      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

              Unassigned Unassigned
              sparsa Saleh Parsa (Inactive)
              Votes:
              8 Vote for this issue
              Watchers:
              16 Start watching this issue

                Created:
                Updated:
                Resolved: