Database publishing help: child table rows not deleting

Rhythmyx Technical Discussion
1

I have a database publish that I’ve been working on for a few months (the whole system, not just the publish), and in testing everything seemed fine until a user noticed that an item in a child table was removed from the CMS, but failed to be removed from the database. I started looking in to it, and I just can’t quite make heads or tails of what is going on.

I have two suspected theories of what might be happening:

  1. I’m publishing to a MySQL database, and currently am using the MyISAM engine. In the “Rhythmyx Implementation Guide Version 7.0.3.PDF” documentation, it states on page 336 “Both the parent and the child tables are required to have primary/foreign keys.” When I look at the MySQL 5.1 documentation I notice that MyISAM doesn’t support Foreign keys, but the InnoDB engine does. I am wondering if the lack of foreign keys is the cause of my failed deletes

  2. I have read a few threads in the Percussion forums indicating that they use a separate template for unpublishing child data rows. I followed the 7.0.3 PDF documentation pretty explicitly in creating my database template, and it seemed to be working perfectly until I discovered this issue… so is the documentation too simplistic? Is an unpublish template required?

Do either of these scenarios explain why I’m having this problem?

Thanks,

-J

2

J:

I set up child database publishing 2012 Q4 and ran into this same problem. It did, in fact, turn out to be lack of primary/foreign keys being established in the database tables. Once those were set, and the publishing template’s source regenerated, deletes worked fine.

I wonder if you can manually add the key definitions to your source even though your DB doesn’t support foreign keys. They are added after the <rowdef> and look like:

<primarykey action="c">
    <name>ACCESSORY_LINK_CMS_ID</name>
    <name>ACCESSORY_ID</name>
</primarykey>
<foreignkey action="c">
    <fkColumn>
        <name>ACCESSORY_ID</name>
        <externalTable>CMS_PRODUCT_DATA</externalTable>
        <externalColumn>CMS_ID</externalColumn>
    </fkColumn>
</foreignkey>

This example is from one of my child tables (CMS_ACCESSORY_LINKS) where the ACCESSORY_LINK_CMS_ID and the ACCESSORY_ID are the primary keys and ACCESSORY_ID is the foreign key to the parent table’s (CMS_PRODUCT_DATA) CMS_ID column.

Hope this helps.
-Cade

3

Thanks for the tip Cade, It did not work, but it got me thinking if I changed my tables to use InnoDB rather than MyISAM that I might be able to get it running right. I’ve changed the database, and now I’m trying to re-build the template, but the “re-generate code” button on the General tab of my template is grayed out. Anybody have any ideas why it’s not letting me re-generate?

Thanks,

-Jason

4

Nevermind… Answered my own question: I was using some variables to determine which $db.resource and $db.database to use. After changing them to static strings, I was able to re-gen.