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
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:
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.
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.
- Create confluence compatible test database:
- Install confluence 5.7 on it.
- Install Team Calendars 5.2.10
- Make sure that there are columns with varchar type:
- 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.