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

Problem installing on Oracle schema when other schemas in the same database have one or more tables sharing the same name as any of Confluence's tables

XMLWordPrintable

      This is a list of the tables in used in Confluence as of version 1.4.2:

      SPACEPERMISSIONS
      TRACKBACKLINKS
      CONTENTLOCK
      PAGETEMPLATES
      os_user_group
      EXTRNLNKS
      NOTIFICATIONS
      ATTACHMENTS
      os_user
      SPACES
      os_group
      CONTENT
      ANCESTORS
      LINKS
      OS_PROPERTYENTRY

      If any of these tables in exist in other oracle schemas in the "same database", those tables will not be created, impairing bits of Confluence functionality that depend on those table(s).

      The bug is tied to this hibernate SchemaUpdate bug:

      http://opensource.atlassian.com/projects/hibernate/browse/HB-534

      This problem also affects PostgreSQL.

      Workaround

      This issue only occurs if the database user you are connecting to Oracle with has permission to view all tables across the database, not just those that relate to its own schema. You need to ensure you have followed all the steps in the Database Setup for Oracle document. The relevant ones here are the local all objects view, and ensuring that 'select any table' is not granted. However, all steps in that document need to be followed to avoid running into other issues down the track.

        1. logs.zip
          26 kB

            [CONFSERVER-3613] Problem installing on Oracle schema when other schemas in the same database have one or more tables sharing the same name as any of Confluence's tables

            Minh Tran added a comment -

            Dear All,

            Thanks for taking the time to raise and comment on this issue.

            I believe this problem could be easily avoided if we follow the required Oracle DB setup document https://confluence.atlassian.com/doc/database-setup-for-oracle-173821.html when installing Confluence
            I decided to close this as won't fix.

            Regards
            Minh Tran
            Confluence Bugmaster
            Atlassian

            Minh Tran added a comment - Dear All, Thanks for taking the time to raise and comment on this issue. I believe this problem could be easily avoided if we follow the required Oracle DB setup document https://confluence.atlassian.com/doc/database-setup-for-oracle-173821.html when installing Confluence I decided to close this as won't fix. Regards Minh Tran Confluence Bugmaster Atlassian

            Zachary Buckholz added a comment -

            Hi guys we just upgraded to v5.6.3 and have 22 matches when running the log scanner.

            Zachary Buckholz added a comment - Hi guys we just upgraded to v5.6.3 and have 22 matches when running the log scanner.

            ctbto added a comment -

            Any news on this problem?

            ctbto added a comment - Any news on this problem?

            Steve Haffenden (Inactive) added a comment -

            Hi Rodrigo

            This issue is currently on our backlog and we should be addressing it in the near future.

            Regards
            Steve Haffenden
            Confluence Bugmaster
            Atlassian

            Steve Haffenden (Inactive) added a comment - Hi Rodrigo This issue is currently on our backlog and we should be addressing it in the near future. Regards Steve Haffenden Confluence Bugmaster Atlassian

            Rodrigo Prado Brandao [Atlassian] added a comment -

            Logs from upgrade from 3.1.2 to 3.5.5

            Rodrigo Prado Brandao [Atlassian] added a comment - Logs from upgrade from 3.1.2 to 3.5.5

            Rodrigo Prado Brandao [Atlassian] added a comment -

            Faced the same error when upgrading from 3.1.2 to 3.5.5.

            When upgrading a Confluence instance configured with Oracle with the following scenario, the upgrade process is stopped and an error is thrown on the log file (attached).

            • table (e.g. cwd_user) already exists on the same database but belongs to another user (e.g. jira)
            • the confluence user on the database as grant to select any table.

            Rodrigo Prado Brandao [Atlassian] added a comment - Faced the same error when upgrading from 3.1.2 to 3.5.5. When upgrading a Confluence instance configured with Oracle with the following scenario, the upgrade process is stopped and an error is thrown on the log file (attached). table (e.g. cwd_user) already exists on the same database but belongs to another user (e.g. jira) the confluence user on the database as grant to select any table.

            Cyril Phillips added a comment -

            We also ran into this issue when upgrading to Confluence v2.7.4 as an interim upgrade to v3.5.9 with an Oracle database.

            Our error logs showed an Oracle insufficient privileges error (see below) but since the SQL scripts are auto-generated in the upgrade process, we could not show our DBAs exactly what DB transactions/SQL commands were being executed...for them to be able to grant any particular privilege needed.

            net.sf.hibernate.HibernateException: java.sql.SQLException: ORA-01031: insufficient privileges

            In the end, we resolved the issue by restricting DB privileges/grants (not adding grants) to the Confluence schema that we were using so that it could only see it's own DB objects.

            Also, our resolution was contrary to the Atlassian recommendation that we found in the Schema Requirements section of the doc here...

            http://confluence.atlassian.com/display/CONF27/Database+Setup+for+Oracle

            We suggest that it be good to update that part of the Atlassian docs which states '...Confluence must be deployed to a schema in it's own, separate Oracle instance...' since that was not necessary at all in our case.

            Cyril Phillips added a comment - We also ran into this issue when upgrading to Confluence v2.7.4 as an interim upgrade to v3.5.9 with an Oracle database. Our error logs showed an Oracle insufficient privileges error (see below) but since the SQL scripts are auto-generated in the upgrade process, we could not show our DBAs exactly what DB transactions/SQL commands were being executed...for them to be able to grant any particular privilege needed. net.sf.hibernate.HibernateException: java.sql.SQLException: ORA-01031: insufficient privileges In the end, we resolved the issue by restricting DB privileges/grants (not adding grants) to the Confluence schema that we were using so that it could only see it's own DB objects. Also, our resolution was contrary to the Atlassian recommendation that we found in the Schema Requirements section of the doc here... http://confluence.atlassian.com/display/CONF27/Database+Setup+for+Oracle We suggest that it be good to update that part of the Atlassian docs which states '...Confluence must be deployed to a schema in it's own, separate Oracle instance...' since that was not necessary at all in our case.

            AnonAtlassianUser added a comment -

            It's been my experience with other projects using Hibernate and Oracle that this behavior is not necessarily a bug - it's caused by the Oracle user having access to more than one schema. For example, if the Confluence user has 'select any table' privileges, and there is more than one table on the database with the same name, and Hibernate does not explicitly specify the schema to query, then you'll get a namespace collision. You can work around this by setting the hibernate.default_schema property.

            AnonAtlassianUser added a comment - It's been my experience with other projects using Hibernate and Oracle that this behavior is not necessarily a bug - it's caused by the Oracle user having access to more than one schema. For example, if the Confluence user has 'select any table' privileges, and there is more than one table on the database with the same name, and Hibernate does not explicitly specify the schema to query, then you'll get a namespace collision. You can work around this by setting the hibernate.default_schema property.

            Jeremy Largman added a comment -

            Alternatively, an error message might look like:

            execute ORA-01430: column being added already exists in table

            Jeremy Largman added a comment - Alternatively, an error message might look like: execute ORA-01430: column being added already exists in table

            Jeremy Largman added a comment -

            This appears somewhat infrequently, perhaps because the documentation on Oracle installation includes a warning, or perhaps because the chance of it happening are more or less rare. Nonetheless it does continue to occur, and when it does the error might look like:

            (HibernateTemplate): data integrity violated by SQL ''; nested exception is java.sql.BatchUpdateException: ORA-00001: unique constraint (CONFLUENCE.SYS_C003601) violated

            Jeremy Largman added a comment - This appears somewhat infrequently, perhaps because the documentation on Oracle installation includes a warning, or perhaps because the chance of it happening are more or less rare. Nonetheless it does continue to occur, and when it does the error might look like: (HibernateTemplate): data integrity violated by SQL ''; nested exception is java.sql.BatchUpdateException: ORA-00001: unique constraint (CONFLUENCE.SYS_C003601) violated

              Unassigned Unassigned
              dave@atlassian.com dave (Inactive)
              Affected customers:
              22 This affects my team
              Watchers:
              34 Start watching this issue

                Created:
                Updated:
                Resolved: