-
Bug
-
Resolution: Resolved Locally
-
High
-
None
-
5.0, 4.4
-
SQL server 2012 using the SQLServerIntlDialect
Confluence 5.5.3
Team calendar 4.2.4
-
Severity 3 - Minor
-
NOTE: This bug report is for Confluence Server. Using Confluence Cloud? See the corresponding bug report.
After upgrading team calendar to 4.4 or a newer version, when you reach Team calendar page, this message appears only and the calendar fails to start.
$calendarHtml
If the SQL server is using the newly data types such as nchar, nvarchar and ntext, when you upgrade a Team calendar from a version 4.2.4 to a 4.4 onwards, it throws this error message:
ERROR [active-objects-ddl-0] [net.java.ao.sql] handleUpdateError Exception executing SQL update <CREATE TABLE AO_950DC3_TC_DISABLE_EV_TYPES (
EVENT_KEY VARCHAR(255) NOT NULL,
ID INTEGER IDENTITY(1,1) NOT NULL,
SUB_CALENDAR_ID VARCHAR(255) NOT NULL,
CONSTRAINT fk_ao_950dc3_tc_disable_ev_types_sub_calendar_id FOREIGN KEY (SUB_CALENDAR_ID) REFERENCES AO_950DC3_TC_SUBCALS(ID),
CONSTRAINT pk_AO_950DC3_TC_DISABLE_EV_TYPES_ID PRIMARY KEY(ID)
and right after
java.sql.SQLException: Column 'AO_950DC3_TC_SUBCALS.ID' is not of same collation as referencing column 'AO_950DC3_TC_DISABLE_EV_TYPES.SUB_CALENDAR_ID' in foreign key 'fk_ao_950dc3_tc_disable_ev_types_sub_calendar_id'
The problem here is that the table being created with the incorrect column type VARCHAR where it should be created as NVARCHAR type.
This causes the upgrade to not create the table in the database.
Workaround
The team calendar 4.3.13 does not create the new table yet, so uninstall the new version and install 4.3.13 for now.
- relates to
-
-
- Closed
-
| Form Name | |||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|
[CONFSERVER-48946] Upgrading Team calendar using SQL server does not create new tables due to incorrect column type
This issue results from incorrect DB Collations on the columns:
https://confluence.atlassian.com/display/CONFKB/Can%27t+Start+or+Upgrade+Confluence+Due+to+an+Error+With+MS+SQL+Server+Database+Collation
Simply updating the collation on the database is sufficient to get an upgrade to complete but it is not sufficient going forward since this setting applies only to newly created tables (eg. any tables a Team Calendar upgrade is creating). Updating that setting doesn't flow down to existing columns. The columns that pre-existed that setting change are still keeping the old collation. You can verify this by running that query in the article.
So as a result, the Team Calendar update is trying to create new tables with foreign key links to existing tables which still have the old collation.
As a tentative solution, creating the tables manually before doing the automated update does "work". So instead of trying to create the tables automatically, the update process realizes the tables are already there and skips them. However, this still generates other collation errors in my logs. Functionally, this seems to work but I don't know how safe this is.
CREATE TABLE AO_950DC3_TC_DISABLE_EV_TYPES ( EVENT_KEY VARCHAR(255) NOT NULL, ID INTEGER IDENTITY(1,1) NOT NULL, SUB_CALENDAR_ID VARCHAR(255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT fk_ao_950dc3_tc_disable_ev_types_sub_calendar_id FOREIGN KEY (SUB_CALENDAR_ID) REFERENCES AO_950DC3_TC_SUBCALS(ID), CONSTRAINT pk_AO_950DC3_TC_DISABLE_EV_TYPES_ID PRIMARY KEY(ID)) CREATE TABLE AO_950DC3_TC_REMINDER_USERS ( ID INTEGER IDENTITY(1,1) NOT NULL, SUB_CALENDAR_ID VARCHAR(255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, USER_KEY VARCHAR(255) NOT NULL, CONSTRAINT fk_ao_950dc3_tc_reminder_users_sub_calendar_id FOREIGN KEY (SUB_CALENDAR_ID) REFERENCES AO_950DC3_TC_SUBCALS(ID), CONSTRAINT pk_AO_950DC3_TC_REMINDER_USERS_ID PRIMARY KEY(ID)) CREATE TABLE AO_950DC3_TC_CUSTOM_EV_TYPES ( BELONG_SUB_CALENDAR_ID VARCHAR(255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, CREATED VARCHAR(255), ICON VARCHAR(255) NOT NULL, ID VARCHAR(255) NOT NULL, TITLE VARCHAR(255) NOT NULL, CONSTRAINT fk_ao_950dc3_tc_custom_ev_types_belong_sub_calendar_id FOREIGN KEY (BELONG_SUB_CALENDAR_ID) REFERENCES AO_950DC3_TC_SUBCALS(ID), CONSTRAINT pk_AO_950DC3_TC_CUSTOM_EV_TYPES_ID PRIMARY KEY(ID))
The proper resolution is to update all the columns to use the same collation as the database default so that when Team Calendar adds new tables, it will link to old ones properly. Unfortunately, as far as I can tell, this requires dropping all indexes and constraints, updating the collation on all the columns, then re-creating them. I'm trying to get through some scripts but there's a lot of missing pieces that I still can't get past. Alternative suggestions involve scripting the database to a fresh one that starts out with the right collation. Again, not sure how safe that is.
Rodrigo, any possibility that KnowledgeBase article could get updated to highlight the need to update the collation on the database and the collation on existing columns and why? If that information was in the article, it could have saved me quite a bit of time.
WSST
added a comment - @Diego Rui Diaz the TEAMCAL-2807 bug was me submitting a duplicate of this bug, they closed it to say they will manage the bug in this ticket. Right now the only solution is the above work around "install 4.3.13 for now"
WSST
added a comment - @Diego Rui Diaz the TEAMCAL-2807 bug was me submitting a duplicate of this bug, they closed it to say they will manage the bug in this ticket. Right now the only solution is the above work around "install 4.3.13 for now" 
Diego Rui Diaz
added a comment - TEAMCAL-2807 does not exist!
I have the same problem, please help!
Resolved by following this doc: https://confluence.atlassian.com/display/CONFKB/Confluence+Upgrade+Fails+Because+SQL+Server+Could+Not+Complete+Schema+Update