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

Upgrading Confluence fails with "insert or update on table "extrnlnks" violates foreign key constraint"

XMLWordPrintable

      Upgrading Confluence from 3.3 to 3.4.7 fails with:

      2011-01-25 07:49:55,914 INFO [main] [springframework.web.context.ContextLoader] initWebApplicationContext Root WebApplicationContext: initialization completed in 21508 ms
2011-01-25 07:49:58,557 ERROR [main] [hibernate.tool.hbm2ddl.SchemaUpdate] execute Unsuccessful: alter table EXTRNLNKS add constraint FK97C10FE78DD41734 foreign key (CONTENTID) references CONTENT
2011-01-25 07:49:58,557 ERROR [main] [hibernate.tool.hbm2ddl.SchemaUpdate] execute ERROR: insert or update on table "extrnlnks" violates foreign key constraint "fk97c10fe78dd41734"
  Detail: Key (contentid)=(1213498) is not present in table "content".
2011-01-25 07:49:58,559 ERROR [main] [hibernate.tool.hbm2ddl.SchemaUpdate] execute could not complete schema update
org.postgresql.util.PSQLException: ERROR: insert or update on table "extrnlnks" violates foreign key constraint "fk97c10fe78dd41734"
  Detail: Key (contentid)=(1213498) is not present in table "content".
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2062)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1795)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:479)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:353)

      Cause

      The extrnlnks table contains stray/outdated entries that reference content in the content table that does not exist anymore.

      Workaround

      The example queries were run against a PostgreSQL database, please adjust them for your particular DBMS

      1. Ensure that you have a backup of the database!
      2. Identify the stray link entries, (there should only be a few entires that point to non-existing content): 
        SELECT e.linkid, e.contentid, c.contentid, e.url FROM extrnlnks as e LEFT OUTER JOIN content as c ON e.contentid = c.contentid WHERE c.contentid IS NULL
      3. Delete the link stray entries 
        DELETE FROM extrnlnks WHERE linkid IN (SELECT e.linkid FROM extrnlnks as e LEFT OUTER JOIN content as c ON e.contentid = c.contentid WHERE c.contentid IS NULL)
      4. Restart Confluence to carry on with the upgrade.

            [CONFSERVER-21697] Upgrading Confluence fails with "insert or update on table "extrnlnks" violates foreign key constraint"

            There are no comments yet on this issue.

              ssaasen Stefan Saasen (Inactive)
              ssaasen Stefan Saasen (Inactive)
              Affected customers:
              0 This affects my team
              Watchers:
              0 Start watching this issue

                Created:
                Updated:
                Resolved: