JIRA executes unnecessary ALTER TABLE operation for AO tables each restart

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