Cannot Add or Remove a Favourite Space from the Dashboard
Symptoms
When you click on the star icon on the dashboard, the icon will change from gray to yellow, but when you refresh the page, the change is lost.
- There are no errors in the log.
Diagnosis
Run the following query:
select count(*) from CONTENT where contentid in (select spaceid from SPACES);If this returns any rows, you are affected by this issue.
Cause
In Confluence 2.3, we introduced a Hi-Lo generator for page and space IDs and ensured that the two were staggered and would never overlap to allow the introduction of clustering. The upgrade process, however, did not change the existing content and space IDs, which previously could overlap one another. The code that works behind the scenes of the star icon on the dashboard makes the assumption that a contentID will never be the same as a spaceID. The overlap in IDs causes the code to break.
Resolution
These queries are tested for Confluence 3.5 and 4.0. You may need to make adjustments depending on your Confluence version and database specific query language. If you have any questions, please raise a support issue.
Make sure you backup your database prior to applying any of the DDLs below.
Run the following queries in a test instance:
For PostgreSQL:
# If you installed Confluence prior to the release of 2.0, you may need to uncomment the three excluded queries. Otherwise, the DESCSPACEID column should not be present in the database. ALTER TABLE spacepermissions DROP CONSTRAINT fkd33f23beb2dc6081; ALTER TABLE content DROP CONSTRAINT fk6382c059b2dc6081; ALTER TABLE notifications DROP CONSTRAINT fk594acc8b2dc6081; ALTER TABLE pagetemplates DROP CONSTRAINT fkbc7ce96a17d4a070; ALTER TABLE pagetemplates DROP CONSTRAINT fkbc7ce96ab2dc6081; # ALTER TABLE content DROP CONSTRAINT fk6382c059598614d0; update spaces set spaceid = (spaceid + (select max(contentid) * 10 from content)); update spacepermissions set spaceid = (spaceid + (select max(contentid) * 10 from content)); update notifications set spaceid = (spaceid + (select max(contentid) * 10 from content)); update pagetemplates set spaceid = (spaceid + (select max(contentid) * 10 from content)); update content set spaceid = (spaceid + (select max(contentid) * 10 from content)); #See note below # update content set descspaceid = (descspaceid + (select max(contentid) * 10 from content)) where contenttype='SPACEDESCRIPTION' and descspaceid is not null; # ALTER TABLE content ADD CONSTRAINT fk6382c059598614d0 FOREIGN KEY (descspaceid) REFERENCES spaces; ALTER TABLE spacepermissions ADD CONSTRAINT fkd33f23beb2dc6081 FOREIGN KEY (spaceid) REFERENCES spaces; ALTER TABLE content ADD CONSTRAINT fk6382c059b2dc6081 FOREIGN KEY (spaceid) REFERENCES spaces; ALTER TABLE notifications ADD CONSTRAINT fk594acc8b2dc6081 FOREIGN KEY (spaceid) REFERENCES spaces; ALTER TABLE pagetemplates ADD CONSTRAINT fkbc7ce96a17d4a070 FOREIGN KEY (prevver) REFERENCES pagetemplates(templateid); ALTER TABLE pagetemplates ADD CONSTRAINT fkbc7ce96ab2dc6081 FOREIGN KEY (spaceid) REFERENCES spaces;In MySQL (and possibly some other DBs), you will need to replace the update to the spaces table with this query:
UPDATE CONTENT SET SPACEID = (SPACEID + (SELECT * FROM (SELECT max(CONTENTID) * 10 FROM CONTENT) as tmptable));For MySQL:
alter table SPACEPERMISSIONS drop foreign key FKD33F23BEB2DC6081; alter table CONTENT drop foreign key FK6382C059B2DC6081; alter table NOTIFICATIONS drop foreign key FK594ACC8B2DC6081; alter table PAGETEMPLATES drop foreign key FKBC7CE96A17D4A070; alter table PAGETEMPLATES drop foreign key FKBC7CE96AB2DC6081; update SPACES set spaceid = (spaceid + (select max(contentid) * 10 from CONTENT)); update SPACEPERMISSIONS set spaceid = (spaceid + (select max(contentid) * 10 from CONTENT)); update NOTIFICATIONS set spaceid = (spaceid + (select max(contentid) * 10 from CONTENT)); update PAGETEMPLATES set spaceid = (spaceid + (select max(contentid) * 10 from CONTENT)); update CONTENT set SPACEID = (SPACEID + (select * from (select max(CONTENTID) * 10 FROM CONTENT) as tmptable)); alter table SPACEPERMISSIONS add CONSTRAINT FKD33F23BEB2DC6081 FOREIGN KEY (`SPACEID`) REFERENCES `SPACES` (`SPACEID`); alter table CONTENT add CONSTRAINT FK6382C059B2DC6081 FOREIGN KEY (`spaceid`) REFERENCES `SPACES` (`spaceid`); alter table NOTIFICATIONS add CONSTRAINT FK594ACC8B2DC6081 FOREIGN KEY (`SPACEID`) REFERENCES `SPACES` (`SPACEID`); alter table PAGETEMPLATES add CONSTRAINT FKBC7CE96A17D4A070 FOREIGN KEY (`PREVVER`) REFERENCES `PAGETEMPLATES` (`TEMPLATEID`); alter table PAGETEMPLATES add CONSTRAINT FKBC7CE96AB2DC6081 FOREIGN KEY (`SPACEID`) REFERENCES `SPACES` (`SPACEID`);- Follow the instructions in the resolution section of the 'How to resolve missing attachments in Confluence' to ensure no attachments have been orphaned by the process