Uploaded image for project: 'Jira Server and Data Center'
  1. Jira Server and Data Center
  2. JRASERVER-63126

JIRA executes unnecessary ALTER TABLE operation for AO tables each restart

    XMLWordPrintable

    Details

      Description

      Summary

      JIRA executes unnecessary ALTER TABLE operation for AO tables related to plugin during plugin install / upgrade / uninstall / initialisation and also during JIRA restart. If AO table has a lot of data, this will slow down JIRA start-up or plugin initialization significantly and cause application to stall.
      Problem has specific conditions for some DB:

      • Problem with MySQL DB and specific tables with LONGTEXT columns
      • Problem with Oracle, specific tables with NUMBER(20,0) DEFAULT 0 and DOUBLE PRECISION columns
      • Problem with MSSQL, conditions <TDB>

      Environment

      This is example for MySQL, for other DB please use corresponding conditions:

      • JIRA
      • MySQL DB
      • Some plugins creating table with LONGTEXT columns: eg: DVCS, webhook

      Steps to Reproduce

      1. Setup JIRA with mentioned enviroment
      2. Enable AO debug logging or MySQL all log
      3. Restart JIRA

      Expected Results

      JIRA doesn't do unnecessary table modification. JIRA s

      Actual Results

      JIRA does unnecessary table modification. Those AO tables have already correct LONGTEXT column type.

      • The below can found in mysql log file:
        Query ALTER TABLE AO_E8B6CC_COMMIT CHANGE COLUMN MESSAGE MESSAGE LONGTEXT DEFAULT NULL
        Query ALTER TABLE AO_E8B6CC_CHANGESET_MAPPING CHANGE COLUMN MESSAGE MESSAGE LONGTEXT DEFAULT NULL
        Query ALTER TABLE AO_E8B6CC_REPOSITORY_MAPPING CHANGE COLUMN LOGO LOGO LONGTEXT DEFAULT NULL
        Query ALTER TABLE AO_E8B6CC_SYNC_EVENT CHANGE COLUMN EVENT_JSON EVENT_JSON LONGTEXT NOT NULL
        Query ALTER TABLE AO_E8B6CC_CHANGESET_MAPPING CHANGE COLUMN FILES_DATA FILES_DATA LONGTEXT DEFAULT NULL
        Query ALTER TABLE AO_E8B6CC_MESSAGE CHANGE COLUMN PAYLOAD PAYLOAD LONGTEXT NOT NULL
        Query ALTER TABLE AO_E8B6CC_SYNC_EVENT CHANGE COLUMN EVENT_CLASS EVENT_CLASS LONGTEXT NOT NULL
        Query ALTER TABLE AO_E8B6CC_SYNC_AUDIT_LOG CHANGE COLUMN EXC_TRACE EXC_TRACE LONGTEXT DEFAULT NULL
        Query ALTER TABLE AO_4AEACD_WEBHOOK_DAO CHANGE COLUMN PARAMETERS PARAMETERS LONGTEXT DEFAULT NULL
        Query ALTER TABLE AO_4AEACD_WEBHOOK_DAO CHANGE COLUMN NAME NAME LONGTEXT NOT NULL
        Query ALTER TABLE AO_4AEACD_WEBHOOK_DAO CHANGE COLUMN URL URL LONGTEXT NOT NULL
        Query ALTER TABLE AO_4AEACD_WEBHOOK_DAO CHANGE COLUMN FILTER FILTER LONGTEXT DEFAULT NULL
        Query ALTER TABLE AO_4AEACD_WEBHOOK_DAO CHANGE COLUMN ENCODED_EVENTS ENCODED_EVENTS LONGTEXT DEFAULT NULL
        Query ALTER TABLE AO_5FB9D7_AOHIP_CHAT_LINK CHANGE COLUMN CONNECT_DESCRIPTOR CONNECT_DESCRIPTOR LONGTEXT DEFAULT NULL
        Query ALTER TABLE AO_563AEE_ACTIVITY_ENTITY CHANGE COLUMN CONTENT CONTENT LONGTEXT DEFAULT NULL
        
      • Running show processlist; against the mysql server shows the following copy jobs are running and copying data to tmp directory to perform an alter:
        Query	56	copy to tmp table	ALTER TABLE AO_E8B6CC_CHANGESET_MAPPING CHANGE COLUMN MESSAGE MESSAGE LONGTEXT DEFAULT NULL
        
      • Java thread related to DB modifucation
        "active-objects-init-JiraTenantImpl{id='system'}-0" #160 prio=5 os_prio=0 tid=0x00007f729c65e000 nid=0x53ee runnable [0x00007f730adeb000]
           java.lang.Thread.State: RUNNABLE
        	at java.net.SocketInputStream.socketRead0(Native Method)
        ...
        	at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1647)
        	- locked <0x0000000691f36d88> (a java.lang.Object)
        	at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1566)
        	at org.apache.commons.dbcp2.DelegatingStatement.executeUpdate(DelegatingStatement.java:234)
        	at org.apache.commons.dbcp2.DelegatingStatement.executeUpdate(DelegatingStatement.java:234)
        	at net.java.ao.DatabaseProvider.executeUpdate(DatabaseProvider.java:2238)
        	at net.java.ao.DatabaseProvider.executeUpdateForAction(DatabaseProvider.java:2294)
        	at net.java.ao.DatabaseProvider.executeUpdatesForActions(DatabaseProvider.java:2266)
        	at net.java.ao.schema.SchemaGenerator.migrate(SchemaGenerator.java:91)
        	at net.java.ao.EntityManager.migrate(EntityManager.java:128)
        	at com.atlassian.activeobjects.internal.EntityManagedActiveObjects.migrate(EntityManagedActiveObjects.java:51)
        	at com.atlassian.activeobjects.internal.AbstractActiveObjectsFactory$1.doInTransaction(AbstractActiveObjectsFactory.java:77)
        	at com.atlassian.activeobjects.internal.AbstractActiveObjectsFactory$1.doInTransaction(AbstractActiveObjectsFactory.java:72)
        ...
        

      Notes

      • MySQL bug is caused by bug in AO framework, see AO-3406
      • ALTER table for Oracle, see AO-3460, AO-3463 and AO-3464:
        ALTER TABLE "AO_60DB71_LEXORANK" MODIFY ("FIELD_ID" DEFAULT 0)
        ALTER TABLE "AO_60DB71_ISSUERANKING" MODIFY ("ISSUE_ID" DEFAULT 0)
        ALTER TABLE "AO_60DB71_ISSUERANKING" MODIFY ("CUSTOM_FIELD_ID" DEFAULT 0)
        ALTER TABLE "AO_60DB71_COLUMN" MODIFY ("MAXIM" DOUBLE PRECISION)
        ALTER TABLE "AO_60DB71_COLUMN" MODIFY ("MINIM" DOUBLE PRECISION)
        ALTER TABLE "AO_60DB71_LEXORANK" MODIFY ("ISSUE_ID" DEFAULT 0)
        
        • Stack
          java.sql.SQLException: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
          	at com.atlassian.activeobjects.internal.EntityManagedActiveObjects.migrate(EntityManagedActiveObjects.java:53)
          	at com.atlassian.activeobjects.internal.AbstractActiveObjectsFactory$1.doInTransaction(AbstractActiveObjectsFactory.java:77)
          	at com.atlassian.activeobjects.internal.AbstractActiveObjectsFactory$1.doInTransaction(AbstractActiveObjectsFactory.java:72)
          	at com.atlassian.sal.core.transaction.HostContextTransactionTemplate$1.doInTransaction(HostContextTransactionTemplate.java:21)
          	at com.atlassian.jira.DefaultHostContextAccessor.doInTransaction(DefaultHostContextAccessor.java:34)
          ...
          	at com.atlassian.activeobjects.internal.AbstractActiveObjectsFactory.create(AbstractActiveObjectsFactory.java:72)
          	at com.atlassian.activeobjects.internal.DelegatingActiveObjectsFactory.create(DelegatingActiveObjectsFactory.java:32)
          	at com.atlassian.activeobjects.osgi.TenantAwareActiveObjects$1$1$1.call(TenantAwareActiveObjects.java:91)
          	... 6 more
          Caused by: java.sql.SQLException: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
          ...
          	at oracle.jdbc.driver.OracleStatement.executeUpdateInternal(OracleStatement.java:1845)
          	at oracle.jdbc.driver.OracleStatement.executeUpdate(OracleStatement.java:1810)
          	at oracle.jdbc.driver.OracleStatementWrapper.executeUpdate(OracleStatementWrapper.java:294)
          	at org.apache.commons.dbcp2.DelegatingStatement.executeUpdate(DelegatingStatement.java:234)
          	at org.apache.commons.dbcp2.DelegatingStatement.executeUpdate(DelegatingStatement.java:234)
          	at net.java.ao.DatabaseProvider.executeUpdate(DatabaseProvider.java:2238)
          	at net.java.ao.DatabaseProvider.executeUpdateForAction(DatabaseProvider.java:2294)
          	at net.java.ao.DatabaseProvider.executeUpdatesForActions(DatabaseProvider.java:2266)
          	at net.java.ao.schema.SchemaGenerator.migrate(SchemaGenerator.java:91)
          	at net.java.ao.EntityManager.migrate(EntityManager.java:128)
          	at com.atlassian.activeobjects.internal.EntityManagedActiveObjects.migrate(EntityManagedActiveObjects.java:51)
          	... 45 more
          
      • MSSQL tables <TBD>
        • Stack
          "active-objects-init-JiraTenantImpl{id='system'}-0" #126 prio=5 tid=0x00007f1e309aa000 nid=0xab97 runnable [0x00007f180b1fa000]
             java.lang.Thread.State: RUNNABLE
          	at java.net.SocketInputStream.socketRead0(Native Method)
          ...
          	at net.sourceforge.jtds.jdbc.JtdsStatement.executeUpdate(JtdsStatement.java:1288)
          	at net.sourceforge.jtds.jdbc.JtdsStatement.executeUpdate(JtdsStatement.java:1241)
          	at org.apache.commons.dbcp2.DelegatingStatement.executeUpdate(DelegatingStatement.java:234)
          	at org.apache.commons.dbcp2.DelegatingStatement.executeUpdate(DelegatingStatement.java:234)
          	at net.java.ao.DatabaseProvider.executeUpdate(DatabaseProvider.java:2238)
          	at net.java.ao.DatabaseProvider.executeUpdateForAction(DatabaseProvider.java:2294)
          	at net.java.ao.DatabaseProvider.executeUpdatesForActions(DatabaseProvider.java:2266)
          	at net.java.ao.schema.SchemaGenerator.migrate(SchemaGenerator.java:91)
          	at net.java.ao.EntityManager.migrate(EntityManager.java:128)
          	at com.atlassian.activeobjects.internal.EntityManagedActiveObjects.migrate(EntityManagedActiveObjects.java:51)
          	at com.atlassian.activeobjects.internal.AbstractActiveObjectsFactory$1.doInTransaction(AbstractActiveObjectsFactory.java:77)
          	at com.atlassian.activeobjects.internal.AbstractActiveObjectsFactory$1.doInTransaction(AbstractActiveObjectsFactory.java:72)
          	at com.atlassian.sal.core.transaction.HostContextTransactionTemplate$1.doInTransaction(HostContextTransactionTemplate.java:21)
          	at com.atlassian.jira.DefaultHostContextAccessor.doInTransaction(DefaultHostContextAccessor.java:34)
          ...
          	at com.atlassian.activeobjects.internal.AbstractActiveObjectsFactory.create(AbstractActiveObjectsFactory.java:72)
          ...
          

      Workaround

      None

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              izinoviev Ilya Zinoviev
              Reporter:
              ayakovlev@atlassian.com Andriy Yakovlev
              Votes:
              17 Vote for this issue
              Watchers:
              28 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: