[opencms-dev] openCms 5 - properties vanish on the wayfromofflineto online project

[Olli Aro]

The patch should work. Now thinking it more it will only work for new
properties you create not the old ones you already have with wrong ids in
your system. In order to fix the old ones you need to run the following SQL:

Check whether any property definitions have incorrect IDs by executing the
following SQL:
 
SELECT 
  OFFL.PROPERTYDEF_NAME,
  ONL.PROPERTYDEF_ID ONL_ID,
  OFFL.PROPERTYDEF_ID OFFL_ID
FROM 
  CMS_PROPERTYDEF OFFL,
  CMS_ONLINE_PROPERTYDEF ONL
WHERE 
  OFFL.PROPERTYDEF_NAME = ONL.PROPERTYDEF_NAME AND
  OFFL.RESOURCE_TYPE = ONL.RESOURCE_TYPE  AND
  OFFL.PROPERTYDEF_ID <> ONL.PROPERTYDEF_ID

If any property definitions are listed with different IDs in online and
offline mode, the ID of the online definition must be updated. 

If a definition has <offl_id> as its offline ID and <onl_id> as its online
ID, follow these steps:
 
Check the Online ID is not in use
 
select * from CMS_ONLINE_PROPERTYDEF where PROPERTYDEF_ID = <offl_id>
 
If this returns an empty set, then continue with the next statement.
Otherwise, the existing property with this ID is also incorrect and needs to
be fixed first.
 
Update the Online Property Definition ID 
 
update CMS_ONLINE_PROPERTYDEF set PROPERTYDEF_ID = <offl_id> where
PROPERTYDEF_ID = <onl_id>
 
After each fix, run the top SQL to ensure everything is correct.
 
Commit the changes, if any.

Finally, check that the sequences are not affected:
 
SELECT TABLE_KEY,ID
FROM CMS_SYSTEMID
WHERE TABLE_KEY = 'CMS_PROPERTYDEF' OR TABLE_KEY = 'CMS_ONLINE_PROPERTYDEF'
 
After running the fixes, I always make sure that the IDs in the above table
are at least as high as the highest used ID, which you can get from:
SELECT MAX(PROPERTYDEF_ID) FROM CMS_PROPERTYDEF;    
SELECT MAX(PROPERTYDEF_ID) FROM CMS_ONLINE_PROPERTYDEF;

I believe you need to restart the Tomcat server for the new values in the
System ID table to kick in (I believe it caches these values). However, the
system IDs have usually been okay for me and didn't require updating.
Sometimes you need to republish the content after doing these fixes,
although a restart may fix this.

Please let me know if you still have a problem and I can send you the source
code for my changes, so you can debug yourself why it is not working for
you.

The root cause for the problem is:

The property id values are initiated from CMS_SYSTEMID table, which includes
value for the next available property id for both online and offline tables.
For some reason the values in CMS_SYSTEMID table will get out of sync and
therefore you end up with different IDs for the same property between online
and offline tables. This causes the
com.opencms.file.genericSql.CmsDbAccess.readPropertyDefinition method not
function properly since it cannot match the properties between online and
offline tables.

Fix that worked for me:

Rewriting method
com.opencms.file.genericSql.CmsDbAccess.readPropertyDefinition, so that it
reads the next available ID always from offline (even for online and backup
tables) seemed to fix the problem. 

Regards,

Olli



-- 
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.300 / Virus Database: 265.8.8 - Release Date: 14/02/2005