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

JIRA executes unnecessary ALTER TABLE operation for AO tables each restart

XMLWordPrintable

      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

            [JRASERVER-63126] JIRA executes unnecessary ALTER TABLE operation for AO tables each restart

            Jeffrey Gordon added a comment -

            Shouldn't this have been fixed in the current Enterprise Release of 7.6.x?

            Jeffrey Gordon added a comment - Shouldn't this have been fixed in the current Enterprise Release of 7.6.x?

            Nuno Santos added a comment - - edited

            I totally agree with @Reece.

            One of our Data Center customers reported this issue today and it's kinda annoying to tell them (a company whose Jira is being used by thousands of users daily and that must have the least downtime possible) to stop their work and update their Jira to the latest version to get rid of this issue.

            Cheers,
            – Nuno Santos

            Nuno Santos added a comment - - edited I totally agree with @Reece. One of our Data Center customers reported this issue today and it's kinda annoying to tell them (a company whose Jira is being used by thousands of users daily and that must have the least downtime possible) to stop their work and update their Jira to the latest version to get rid of this issue. Cheers, – Nuno Santos

            Deleted Account (Inactive) added a comment -

            This could have a potentially large performance impact for plugins with lots of data stored in AO. I'd argue that this really should be considered for backporting.

            With Atlassian's recent push to get developers testing their plugins with large data sets for Jira Data Center etc, we would really like to see Atlassian being proactive too to ensure that our plugins can perform optimally for our customers.

            Surely this should go back to the last enterprise release at the very least?

            Deleted Account (Inactive) added a comment - This could have a potentially large performance impact for plugins with lots of data stored in AO. I'd argue that this really should be considered for backporting. With Atlassian's recent push to get developers testing their plugins with large data sets for Jira Data Center etc, we would really like to see Atlassian being proactive too to ensure that our plugins can perform optimally for our customers. Surely this should go back to the last enterprise release at the very least?

            Aggelos Paraskevopoulos [Relational] added a comment -

            Quoting from ER Bug fixing policy

            Backport critical security fixes, as outlined in our current security bug fix policy, and fixes relating to stability, data integrity or critical performance issues.

            You could say that this one qualifies for "data integrity or critical performance issue".

            Cheers,

            // Aggelos

            Aggelos Paraskevopoulos [Relational] added a comment - Quoting from ER Bug fixing policy Backport critical security fixes, as outlined in our current security bug fix policy, and fixes relating to stability, data integrity or critical performance issues. You could say that this one qualifies for "data integrity or critical performance issue". Cheers, // Aggelos

            Ilya Zinoviev (Inactive) added a comment - - edited

            Hi angel6,

            This issue was resolved under Atlassian generic bugfix policy, which means 'last stable version'.
            From my side, I can tell that it's not difficult to backport it to Enterprise Release.
            We would be happy to do it if this issue gathers enough interest among the customers using ER.

            –
            Ilya Zinoviev
            Jira Development

            Ilya Zinoviev (Inactive) added a comment - - edited Hi angel6 , This issue was resolved under Atlassian generic bugfix policy, which means 'last stable version'. From my side, I can tell that it's not difficult to backport it to Enterprise Release. We would be happy to do it if this issue gathers enough interest among the customers using ER. – Ilya Zinoviev Jira Development

            Ilya Zinoviev (Inactive) added a comment - - edited

            I'm glad to announce that this issue is going to be fixed in the next bugfix release(7.11.2).
            While working on this issue following unnecessary migrations were discovered:

            • Numeric columns with @Default value in Oracle DB: AO-3460
            • @Default @NotNull columns in Oracle DB: AO-3463
            • DOUBLE PRECISION columns in Oracle DB: AO-3464
            • LONGTEXT columns in MySQL: AO-3406

            This fix includes all of the mentioned bugs and bumps the AO lib version from 1.5.0 to 2.0.0

            –
            Ilya Zinoviev
            Jira Development

            Ilya Zinoviev (Inactive) added a comment - - edited I'm glad to announce that this issue is going to be fixed in the next bugfix release(7.11.2). While working on this issue following unnecessary migrations were discovered: Numeric columns with @Default value in Oracle DB: AO-3460 @Default @NotNull columns in Oracle DB: AO-3463 DOUBLE PRECISION columns in Oracle DB: AO-3464 LONGTEXT columns in MySQL: AO-3406 This fix includes all of the mentioned bugs and bumps the AO lib version from 1.5.0 to 2.0.0 – Ilya Zinoviev Jira Development

            Aggelos Paraskevopoulos [Relational] added a comment -

            Same here, this has really severe performance effects on instances with large AO tables. I agree with Francis the priority should be revised.

            Cheers,
            --AP

            Aggelos Paraskevopoulos [Relational] added a comment - Same here, this has really severe performance effects on instances with large AO tables. I agree with Francis the priority should be revised. Cheers, --AP

            francis.kennedy288830924 added a comment -

            Atlassian Team,

            We hit this problem in our data center JIRA. Some nodes take about 50 minutes to restart JIRA, at random times.  The severity of this bug needs to revised, let me know, if you need additional information to investigate.  We have SQL server as DB.

            Thanks

            Francis

             

            francis.kennedy288830924 added a comment - Atlassian Team, We hit this problem in our data center JIRA. Some nodes take about 50 minutes to restart JIRA, at random times.  The severity of this bug needs to revised, let me know, if you need additional information to investigate.  We have SQL server as DB. Thanks Francis  

              izinoviev Ilya Zinoviev (Inactive)
              ayakovlev@atlassian.com Andriy Yakovlev [Atlassian]
              Affected customers:
              17 This affects my team
              Watchers:
              28 Start watching this issue

                Created:
                Updated:
                Resolved: