AO tables are created using the incorrect VARCHAR datatype in SQL server

When a new Confluence database is created on 5.6, 5.7 (older versions do apply as well), the AO tables are created using the VARCHAR datatype even if using the correct dialect for SQL server.

It is known that Confluence must use the NVARCHAR, NTEXT and NCHAR datatypes as stated in this KB article, however AO (Active Objects) tables always used VARCHAR as a pattern.

Recently in Confluence 5.8 version, all AO tables are meant to use NVARCHAR, however if you came from 5.7 or any older version of Confluence, the AO tables that were using VARCHAR datatypes are not altered to the correct NVARCHAR datatype upon upgrading to 5.8.x.

This is causing many issues with functionalities/plugins that depends on AO tables such as Team Calendars for example. If a new version of Team calendar tries to create a new AO table on 5.8.4, it will try to use the NVARCHAR datatype and if it tries to create a constraint (EG. FK) between a column using VACHAR and another using NVARCHAR, the table creation fails, hence the plugin/functionality stops working correctly.

This screenshoot shows the discrepancy between different AO tables created before and after the upgrade to 5.8.x

Steps to replicate the bug

1) Install a Confluence vanilla in 5.7 or any older version on SQL server using the correct dialect in confluence.cfg.xml file (net.sf.hibernate.dialect.SQLServerIntlDialect)
2) Install an older version of team calendar such as 5.2.10 and setup a license for team calendar
3) Upgrade Confluence to 5.8.4
4) Upgrade Team calendar to the latest version (5.2.16)

This will break the team calendar plugin with the following message:

ERROR [active-objects-init-system tenant-0] [net.java.ao.sql] handleUpdateError Exception executing SQL update <CREATE TABLE AO_950DC3_TC_JIRA_REMI_EVENTS (
    ALL_DAY BIT CONSTRAINT df_AO_950DC3_TC_JIRA_REMI_EVENTS_ALL_DAY DEFAULT 0 NOT NULL,
    ASSIGNEE NVARCHAR(255),
    DESCRIPTION NTEXT,
    EVENT_TYPE NVARCHAR(255),
    ID INTEGER IDENTITY(1,1) NOT NULL,
    ISSUE_ICON_URL NVARCHAR(255),
    ISSUE_LINK NVARCHAR(255),
    JQL NVARCHAR(255),
    KEY_ID NVARCHAR(255) NOT NULL,
    STATUS NVARCHAR(255),
    SUB_CALENDAR_ID NVARCHAR(255) NOT NULL,
    TICKET_ID NVARCHAR(255) NOT NULL,
    TITLE NVARCHAR(255),
    USER_ID NVARCHAR(255),
    UTC_END BIGINT CONSTRAINT df_AO_950DC3_TC_JIRA_REMI_EVENTS_UTC_END DEFAULT 0,
    UTC_START BIGINT CONSTRAINT df_AO_950DC3_TC_JIRA_REMI_EVENTS_UTC_START DEFAULT 0,
    CONSTRAINT fk_ao_950dc3_tc_jira_remi_events_sub_calendar_id FOREIGN KEY (SUB_CALENDAR_ID) REFERENCES AO_950DC3_TC_SUBCALS(ID),
CONSTRAINT pk_AO_950DC3_TC_JIRA_REMI_EVENTS_ID PRIMARY KEY(ID)
)>
 -- url: /rest/plugins/1.0/ | referer: XXXXXXXXXXXXXXXXXXXXX | userName: XXXXXXXXXXXXXXXXXX
java.sql.SQLException: Column 'AO_950DC3_TC_SUBCALS.ID' is not the same data type as referencing column 'AO_950DC3_TC_JIRA_REMI_EVENTS.SUB_CALENDAR_ID' in foreign key 'fk_ao_950dc3_tc_jira_remi_events_sub_calendar_id'.

Workaround

If you are in 5.7 or any older version of Confluence, the Team Calendars AO tables are created with VARCHAR datatypes, instead of NVARCHAR. To avoid the errors that has been described above Steps to replicate the bug section, you must then first change the wrongly set datatype to the correct column datatype. Unfortunately, this must be done manually.

You may do so by creating an alteration script and running the script to change the table or follow the exact steps to change the datatypes from this KB article - "Modify each data type manually" section.

Working Workaround

Fix is provided in 5.9.1 but customers who want to upgrade to 5.8 can use steps published onto
https://confluence.atlassian.com/display/CONFKB/How+to+upgrade+active+objects+columns+from+varchar+to+nvarchar+in+SQL+Server to get all columns upgraded from varchar to nvarchar.

QR Notes

• Create confluence compatible test database:

  CREATE DATABASE CONF_38232_14;
  ALTER DATABASE CONF_38232_14 COLLATE SQL_Latin1_General_CP1_CS_AS;
  ALTER DATABASE CONF_38232_14 SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;
  use CONF_38232_14;
  

• Install confluence 5.7 on it.
• Install Team Calendars 5.2.10
• Make sure that there are columns with varchar type:

  SELECT syo.name as table_name, syc.name as column_name 
  FROM sysobjects syo 
  JOIN syscolumns syc
  ON syc.id = syo.id
  JOIN systypes syt
  ON syt.xtype = syc.xtype
  WHERE syt.name = 'varchar'
  and syo.name like 'AO_%'
  ORDER by syo.name, syc.name;
  

• Shutdown confluence
• Install confluence from `issue-58/CONF-38232` branch name re-using old confluence home
• Make sure that upgrade task has run and all ddl queries are logged
• Make sure that no SQL errors on confluence or AO upgrades
• Install Team Calendars 5.2.16
• Make sure that no constraints error in log files
• Make sure that plugin is enabled and all modules (except `event` one) are enabled
• Run query above to check that there are no columns with 'varchar' type left.