Missing Team Calendar After Migrating the database from MYSQL to Postgres

お困りですか?

アトラシアン コミュニティをご利用ください。

コミュニティに質問

プラットフォームについて: Server と Data Center のみ - この記事は、サーバーおよびデータセンター プラットフォームのアトラシアン製品にのみ適用されます。

問題

The Calendar does not display and the following error is visible from the logs.

Caused by: org.postgresql.util.PSQLException: ERROR: column ao_950dc3_tc_reminder_users.SUB_CALENDAR_ID does not exist

診断

診断ステップ

  • List all the table in the Database and check if the table which are show in the error exist. In this case, the table isao_950dc3_tc_reminder_users. Ensure that the table exist in the list and the table name is in the same casing as the one shown in the error(lower/upper case)

    \dt
  • If the table exist, check the table structure  to see if the column exist and it is in the correct casing. If the column exist but it is in a different casing. Please proceed to the workaround.

    \d+ ao_950dc3_tc_reminder_users

 

原因

Postgres is case sensitive, therefore if the column is in a different casing, the Confluence will not be able to query it. 

回避策

To fix this issue, we will need to recreate the table to change the casing of all the tables column. Please ensure that you have backup your Confluence database before running the query below:

 

  1. Backup all the Team Calendar Table by creating a backup table:

    CREATE TABLE "AO_950DC3_TC_CUSTOM_EV_TYPES_BACKUP" AS
    SELECT * FROM "AO_950DC3_TC_CUSTOM_EV_TYPES"
    CREATE TABLE "AO_950DC3_TC_EVENTS_BACKUP" AS
    SELECT * FROM "AO_950DC3_TC_EVENTS" ;
    CREATE TABLE "AO_950DC3_TC_DISABLE_EV_TYPES_BACKUP" AS
    SELECT * FROM "AO_950DC3_TC_DISABLE_EV_TYPES" ; 
    CREATE TABLE "AO_950DC3_TC_EVENTS_EXCL_BACKUP" AS
    SELECT * FROM "AO_950DC3_TC_EVENTS_EXCL" ; 
    CREATE TABLE "AO_950DC3_TC_EVENTS_INVITEES_BACKUP" AS
    SELECT * FROM "AO_950DC3_TC_EVENTS_INVITEES" ; 
    CREATE TABLE "AO_950DC3_TC_JIRA_REMI_EVENTS_BACKUP" AS
    SELECT * FROM "AO_950DC3_TC_JIRA_REMI_EVENTS" ; 
    CREATE TABLE "AO_950DC3_TC_REMINDER_SETTINGS_BACKUP" AS
    SELECT * FROM "AO_950DC3_TC_REMINDER_SETTINGS" ; 
    CREATE TABLE "AO_950DC3_TC_REMINDER_USERS_BACKUP" AS
    SELECT * FROM "AO_950DC3_TC_REMINDER_USERS" ; 
    CREATE TABLE "AO_950DC3_TC_SUBCALS_BACKUP" AS
    SELECT * FROM "AO_950DC3_TC_SUBCALS" ; 
    CREATE TABLE "AO_950DC3_TC_SUBCALS_IN_SPACE_BACKUP" AS
    SELECT * FROM "AO_950DC3_TC_SUBCALS_IN_SPACE" ; 
    CREATE TABLE "AO_950DC3_TC_SUBCALS_PRIV_GRP_BACKUP" AS
    SELECT * FROM "AO_950DC3_TC_SUBCALS_PRIV_GRP" ; 
    CREATE TABLE "AO_950DC3_TC_SUBCALS_PRIV_USR_BACKUP" AS
    SELECT * FROM "AO_950DC3_TC_SUBCALS_PRIV_USR" ; 
    CREATE TABLE "AO_950DC3_TC_SUBCALS_PROPS_BACKUP" AS
    SELECT * FROM "AO_950DC3_TC_SUBCALS_PROPS" ; 
  2. Drop all the Team Calendar Table

    DROP TABLE AO_950DC3_TC_CUSTOM_EV_TYPES;
    DROP TABLE AO_950DC3_TC_DISABLE_EV_TYPES;
    DROP TABLE AO_950DC3_TC_EVENTS;
    DROP TABLE AO_950DC3_TC_EVENTS_EXCL;
    DROP TABLE AO_950DC3_TC_EVENTS_INVITEES;
    DROP TABLE AO_950DC3_TC_JIRA_REMI_EVENTS;
    DROP TABLE AO_950DC3_TC_REMINDER_SETTINGS;
    DROP TABLE AO_950DC3_TC_REMINDER_USERS;
    DROP TABLE AO_950DC3_TC_SUBCALS_IN_SPACE;
    DROP TABLE AO_950DC3_TC_SUBCALS;
    DROP TABLE AO_950DC3_TC_SUBCALS_PRIV_GRP;
    DROP TABLE AO_950DC3_TC_SUBCALS_PRIV_USR;
    DROP TABLE AO_950DC3_TC_SUBCALS_PROPS;
  3.  Uninstall your Team Calendar 
  4. Reinstall the Team Calendar so it will recreate the table
  5. Insert the data from the backup table to the newly created table:

    INSERT INTO "AO_950DC3_TC_CUSTOM_EV_TYPES" 
    SELECT * FROM "AO_950DC3_TC_CUSTOM_EV_TYPES_BACKUP";
    INSERT INTO "AO_950DC3_TC_EVENTS" 
    SELECT * FROM "AO_950DC3_TC_EVENTS_BACKUP" ;
    INSERT INTO "AO_950DC3_TC_DISABLE_EV_TYPES" 
    SELECT * FROM "AO_950DC3_TC_DISABLE_EV_TYPES_BACKUP" ;  
    INSERT INTO "AO_950DC3_TC_EVENTS_EXCL" 
    SELECT * FROM "AO_950DC3_TC_EVENTS_EXCL_BACKUP" ;   
    INSERT INTO "AO_950DC3_TC_EVENTS_INVITEES" 
    SELECT * FROM "AO_950DC3_TC_EVENTS_INVITEES_BACKUP" ;  
    INSERT INTO "AO_950DC3_TC_JIRA_REMI_EVENTS" 
    SELECT * FROM "AO_950DC3_TC_JIRA_REMI_EVENTS_BACKUP" ;  
    INSERT INTO "AO_950DC3_TC_REMINDER_SETTINGS" 
    SELECT * FROM "AO_950DC3_TC_REMINDER_SETTINGS_BACKUP" ;  
    INSERT INTO "AO_950DC3_TC_REMINDER_USERS" 
    SELECT * FROM "AO_950DC3_TC_REMINDER_USERS_BACKUP" ;  
    INSERT INTO "AO_950DC3_TC_SUBCALS" 
    SELECT * FROM "AO_950DC3_TC_SUBCALS_BACKUP" ;  
    INSERT INTO "AO_950DC3_TC_SUBCALS_IN_SPACE" 
    SELECT * FROM "AO_950DC3_TC_SUBCALS_IN_SPACE_BACKUP" ;  
    INSERT INTO "AO_950DC3_TC_SUBCALS_PRIV_GRP" 
    SELECT * FROM "AO_950DC3_TC_SUBCALS_PRIV_GRP_BACKUP" ; 
    INSERT INTO "AO_950DC3_TC_SUBCALS_PRIV_USR" 
    SELECT * FROM "AO_950DC3_TC_SUBCALS_PRIV_USR_BACKUP" ;   
    INSERT INTO "AO_950DC3_TC_SUBCALS_PROPS" 
    SELECT * FROM "AO_950DC3_TC_SUBCALS_PROPS_BACKUP" ;   
  6. Drop all the backup table that we previously created. 

    DROP TABLE "AO_950DC3_TC_CUSTOM_EV_TYPES_BACKUP";
    DROP TABLE "AO_950DC3_TC_DISABLE_EV_TYPES_BACKUP";
    DROP TABLE "AO_950DC3_TC_EVENTS_BACKUP";
    DROP TABLE "AO_950DC3_TC_EVENTS_EXCL_BACKUP";
    DROP TABLE "AO_950DC3_TC_EVENTS_INVITEES_BACKUP";
    DROP TABLE "AO_950DC3_TC_JIRA_REMI_EVENTS_BACKUP";
    DROP TABLE "AO_950DC3_TC_REMINDER_SETTINGS_BACKUP";
    DROP TABLE "AO_950DC3_TC_REMINDER_USERS_BACKUP";
    DROP TABLE "AO_950DC3_TC_SUBCALS_IN_SPACE_BACKUP";
    DROP TABLE "AO_950DC3_TC_SUBCALS_BACKUP";
    DROP TABLE "AO_950DC3_TC_SUBCALS_PRIV_GRP_BACKUP";
    DROP TABLE "AO_950DC3_TC_SUBCALS_PRIV_USR_BACKUP";
    DROP TABLE "AO_950DC3_TC_SUBCALS_PROPS_BACKUP";

 

最終更新日 2016 年 4 月 1 日

この内容はお役に立ちましたか?

はい
いいえ
この記事についてのフィードバックを送信する
Powered by Confluence and Scroll Viewport.