Uploaded image for project: 'Confluence Data Center'
  1. Confluence Data Center
  2. CONFSERVER-10759

'KEYSTORE' table doesn't exist. BackupEntities couldn't backup database data

      Upon Confluence upgrade to 2.7 or later, admin may discover that the KEYSTORE table does not exist.

      An upgrade misconfiguration is known to can cause this. For example:

      1. Admin created a test Confluence instance of the latest version and new database, then shut it down
      2. Admin imported production database into this new instance
      3. Admin copied production instance's Confluence Home but kept the test instance's confluence.cfg.xml. This xml file contains the target version's build number.
      4. Admin started test instance in the hope that this will upgrade Confluence.
      5. Because Confluence see that the build number in confluence.cfg.xml is the same as the running Confluence version, the upgrade is skipped.

      Step 3 is wrong. The confluence.cfg.xml should also be reverted to the old build number (click here for Confluence Build Information).

      Sample error
      ERROR [DefaultQuartzScheduler_Worker-6] [confluence.importexport.impl.BackupExporter] backupEntities Couldn't backup database data.
      org.springframework.jdbc.BadSqlGrammarException: Hibernate operation: Could not execute query; bad SQL grammar []; nested exception is com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Table 'confluencedb271.KEYSTORE' doesn't exist
      Caused by: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Table 'confluencedb271.KEYSTORE' doesn't exist
      	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1027)
      

      This issue can be prevented had we have CONF-18085 implemented.

      See also CONF-13798

            [CONFSERVER-10759] 'KEYSTORE' table doesn't exist. BackupEntities couldn't backup database data

            Superceded by CONF-18085

            Roy Hartono [Atlassian] added a comment - Superceded by CONF-18085

            Jeremy Largman added a comment - - edited

            I have a theory on why this could be happening to some users but only a small percentage.

            In one particular support session, I did a copilot session, and noticed this oddity:

            the user had a confluence.cfg.xml file with a build number 1101, which corresponds to something around version 2.7. They were pointing a 2.5.3 instance at this though, and it was working. I've noticed before that this would-be-downgrade hack actually launches confluence successfully. Perhaps either the upgrade task was incomplete between 2.5.3 and 2.7, or somehow this downgrade operation left the db/confluence.cfg.xml file in an incompatible, unknown state. When the 2.8.1 upgrade tried to kick in, it was expecting a KEYSTORE table to already exist?

            It might at least explain how this could happen to a few users but not most.

            The workaround here is probably still the valid workaround.

            There's a improvement request to prevent this unsupported upgrade technique.

            Jeremy Largman added a comment - - edited I have a theory on why this could be happening to some users but only a small percentage. In one particular support session, I did a copilot session, and noticed this oddity: the user had a confluence.cfg.xml file with a build number 1101, which corresponds to something around version 2.7. They were pointing a 2.5.3 instance at this though, and it was working. I've noticed before that this would-be-downgrade hack actually launches confluence successfully. Perhaps either the upgrade task was incomplete between 2.5.3 and 2.7, or somehow this downgrade operation left the db/confluence.cfg.xml file in an incompatible, unknown state. When the 2.8.1 upgrade tried to kick in, it was expecting a KEYSTORE table to already exist? It might at least explain how this could happen to a few users but not most. The workaround here is probably still the valid workaround. There's a improvement request to prevent this unsupported upgrade technique.

            Roy Hartono [Atlassian] added a comment - - edited

            To create the keystore table in mysql:

            CREATE TABLE `KEYSTORE` (
              `KEYID` bigint(20) NOT NULL,
              `ALIAS` varchar(255) NOT NULL,
              `TYPE` varchar(32) NOT NULL,
              `ALGORITHM` varchar(32) NOT NULL,
              `KEYSPEC` text NOT NULL,
              PRIMARY KEY  (`KEYID`)
            ) ;
            

            To create the keystore table in postgresql:

            CREATE TABLE KEYSTORE (
              keyid int8 PRIMARY KEY,
              ALIAS varchar(255) NOT NULL,
              TYPE varchar(32) NOT NULL,
              ALGORITHM varchar(32) NOT NULL,
              KEYSPEC text NOT NULL
            );
            

            To create the keystore table in ORACLE:

            CREATE TABLE KEYSTORE ( 
                    KEYID NUMBER(19) NOT NULL, 
                    ALIAS VARCHAR2(255) NOT NULL, 
                    TYPE VARCHAR2(32) NOT NULL, 
                    ALGORITHM VARCHAR2(32) NOT NULL, 
                    KEYSPEC CLOB NOT NULL, 
                    PRIMARY KEY (KEYID) 
                );
            

            Roy Hartono [Atlassian] added a comment - - edited To create the keystore table in mysql: CREATE TABLE `KEYSTORE` ( `KEYID` bigint (20) NOT NULL , ` ALIAS ` varchar (255) NOT NULL , ` TYPE ` varchar (32) NOT NULL , `ALGORITHM` varchar (32) NOT NULL , `KEYSPEC` text NOT NULL , PRIMARY KEY (`KEYID`) ) ; To create the keystore table in postgresql: CREATE TABLE KEYSTORE ( keyid int8 PRIMARY KEY , ALIAS varchar (255) NOT NULL , TYPE varchar (32) NOT NULL , ALGORITHM varchar (32) NOT NULL , KEYSPEC text NOT NULL ); To create the keystore table in ORACLE: CREATE TABLE KEYSTORE ( KEYID NUMBER (19) NOT NULL , ALIAS VARCHAR2 (255) NOT NULL , TYPE VARCHAR2 (32) NOT NULL , ALGORITHM VARCHAR2 (32) NOT NULL , KEYSPEC CLOB NOT NULL , PRIMARY KEY (KEYID) );

            Yes Paul,

            Basically, one will need to run an SQL command to recreate an empty keystore table and then run the JSP file from CONF-11074 to initialise the content.
            I have tested the script and it initializes an empty keystore table successfully.

            Roy Hartono [Atlassian] added a comment - Yes Paul, Basically, one will need to run an SQL command to recreate an empty keystore table and then run the JSP file from CONF-11074 to initialise the content. I have tested the script and it initializes an empty keystore table successfully.

            Roy, Chris' suggestion of exporting the table from a new confluence 2.7.1 in conjunction with the JSP file available here should provide a work around for this problem.

            Is that sufficient?

            Paul Curren added a comment - Roy, Chris' suggestion of exporting the table from a new confluence 2.7.1 in conjunction with the JSP file available here should provide a work around for this problem. Is that sufficient?

            Regarding the missing keystore table, I have discussed this issue with Chris Owen and suggested recreation of the missing keystore table using the follow steps:

            1. Create a new confluence 2.7.1 instance
            2. Export its keystore table using database dump
            3. Import this table to the production database

            Which will not work.

            Chris said that the keystore table's data is dependent on the server id. We need to give them a way to regenerate the data once the table is created.

            There's an upgrade task to do it and it would be possible to trigger this via a JSP patch

            Roy Hartono [Atlassian] added a comment - Regarding the missing keystore table, I have discussed this issue with Chris Owen and suggested recreation of the missing keystore table using the follow steps: Create a new confluence 2.7.1 instance Export its keystore table using database dump Import this table to the production database Which will not work . Chris said that the keystore table's data is dependent on the server id. We need to give them a way to regenerate the data once the table is created. There's an upgrade task to do it and it would be possible to trigger this via a JSP patch

            Ivan Benko [Atlassian] added a comment - - edited

            Changing the priority as this bug effects quietly the confluence backup as the KEYSTORE table is not created.
            As Roy stated, and Chris seconded, we should not allow the longrunning upgrade task to finish till all tables are created correctly in the DB.

            I can login Confluence after this and the version is showing 2.7.1. But I will get the same error when trying to backup the site.

            How is this possible? If the upgrade task fails, it should not allow 'safe' start of the new version?

            Ivan Benko [Atlassian] added a comment - - edited Changing the priority as this bug effects quietly the confluence backup as the KEYSTORE table is not created. As Roy stated, and Chris seconded, we should not allow the longrunning upgrade task to finish till all tables are created correctly in the DB. I can login Confluence after this and the version is showing 2.7.1. But I will get the same error when trying to backup the site. How is this possible? If the upgrade task fails, it should not allow 'safe' start of the new version?

            This problem is exacerbated by CONF-8876. None of these instances should have got off the ground during upgrade with these problems.

            Christopher Owen [Atlassian] added a comment - This problem is exacerbated by CONF-8876 . None of these instances should have got off the ground during upgrade with these problems.

              Unassigned Unassigned
              rhartono Roy Hartono [Atlassian]
              Affected customers:
              1 This affects my team
              Watchers:
              4 Start watching this issue

                Created:
                Updated:
                Resolved: