Categories disappear

Communities ContentBox CMS
1

Hi guys,

we suddenly have a problem that occurred from one day to another.

We now found out, that it happened when a page is saved. The problem here is, that several pages were saved at the same time.

So we are adding an existing category to a page. In the ORM log we saw that the categories are detached and attached. Why are they detached? –For me this is making no sense. We have now a lot of content in Contentbox. Saving the pages is taking more and more time, because the delete and add process on the database runs longer…
Since we have this problem we switched from CF10 to Railo 4.3.
But anyhow: The ORM log is the same, even on different environments.

It seems that each save process selects the categories that need to be reattached. But when one process selects the, in a state were the other process already detached a couple of them, they will never be written back completely.

Is there are a solution for it? It would be the best, just to add the categories that were not attached to the page before.

`
contentbox-admin\handlers\pages.cfc:

// detach categories and re-attach
page.setCategories( categories );
`

Thanks!
Andy

2

Sounds like the process needs to be locked, raise a ticket.

3

I think locking will not help much, because it first deletes all categories with a certain number, then they are reinserted.

The whole application blows up if you have a bit more traffic.

So the solution should be remove all the overhead and just add or delete single entries.

4

Ticket opened! -See
https://ortussolutions.atlassian.net/browse/CONTENTBOX-534

5

First of all locking will be required, at least maybe optimistic or pessimistic, the reason being is that if two people load the data from the DB and try to modify it at the same time, they could potential contaminate the data.

I have looked at the code for setCategories() and I don’t see it removing and re-adding the categories and as categories are many to many this could be a technical issue with ORM as well. Regardless of that, if there is no optimistic or pessimistic locking then two people editing the data at the same time will be an issue of contaminated data. Which is what I was referring too.

6

I have looked at the code for setCategories() and I don’t see it removing and re-adding the categories and as categories are many to many this could be a technical issue with ORM as well

Thats is why I turned on ORM Log. The first entry shows the delete on DB.

`
Hibernate:
delete
from
cb_contentCategories
where
FK_categoryID=?
Hibernate:
insert
into
cb_contentCategories
(FK_categoryID, FK_contentID)
values
(?, ?)
Hibernate:
insert
into
cb_contentCategories
(FK_categoryID, FK_contentID)
values
(?, ?)

.
.
.

`

When I comment out
page.setCategories( categories );

No delete and insert happens. So delete comes from the function above. This behavior is the same on CF and railo.

Making a select on database directly: Here a see that during save process the number of categories decreases.

select fk_contentId,fk_categoryId from cb_contentCategories

Result count normally 10045. During save process, result count decreases.

7

Regardless, you still need to consider locking as I stated for the reasons you’re facing.

8

Yes locking might still be necessary.

As Gunnar already said: The query needs to be written better. Just add new categories, but leave out the delete.

We already have so many relations in the database, that with some traffic the site will go down when the internal application locks…

9

Well not sure you can, Luis may need to explain why he did it that way. But setCategories() is an ORM method to the entity which Luis overrides. So I am still going to say it is a technical issue, here is why.

/**

  • Override the setCategories
    */
    BaseContent function setCategories(required array categories){

// Relate the incoming suckers
for( var oCat in arguments.categories ){
if( !oCat.hasContent( this ) ){
oCat.addContent( this );
}
}

if( hasCategories() ){
// loop and remove yourself from categories
for( var oCat in variables.categories ){
if( !arrayContains( arguments.categories, oCat ) ){
oCat.removeContent( this );
}
}
}

variables.categories = arguments.categories;

return this;
}

The only deletion that is occurring here is the removeContent, which again is an ORM entity method. I would be happy to chase this further, but again I think this a technical issue with Hibernate.

10

On our live site the save leads to a deletion of thousands of categories
and reinsert of them. It has to be rewritten somehow, otherwise Contentbox
will not be
able to handle sites with larger traffic and lots of categories.

Let's wait and see what Luis says.

11

ContentBox already has that problem now.

http://www.andyscott.id.au/blog/coldfusion-orm-and-things-one-should-consider-and-is-criteria-building-a-good-option

I have been very vocal about this in here already, so far nobody else appears to be seeing this problem.

12

Just read your post, Brad mentioned that it’s the ORMExecuteQuery. So what to do with it?
Replace performance critical queries by normal SQL?

13

Yeah, well on my machines (development, staging and testing) as well as production and many other customers I have installed ContentBox for, its the Criteria Builder that is the issue.

14

I even had a link that ran that code on my site that people could see the times for themselves.

15

Ok, guys, few things.

History:

The way it is done right now levies the fact of just removing the old relationships (many-to-many) and rebuilding the graph. As Gunnar points out, this is not optimal when you have lots of categories. I agree with him, as the initial solution worked, but never imagined people would have such a tremendous amount of categories attached to a single piece of content. How many are attached Gunnar, may I ask?

I can definitely see improvement in doing list comparisons and only adding/removing the difference, which does indeed make it more complex. Thus, why I went with the initial solution.

Second, the SQL is executed in a transaction, it does not mean that when I remove the relationship Hibernate fires the SQL. The manipulation is done in the Hibernate session for that request.

The issue that might cause overlaps are that a user is waiting to save a page when another thread is saving the same page and overlaps occur. I thought I had transferred this overlap code from codexwiki, but I cannot see it now in ContentBox. What we did is the following: Each submit for save is marked with the current version they are working on. If for some reason another thread beats you to the punch and saves the content before you a check is made pre-insertion if the version is still the same. If not, it means somebody beat you to it and your state is no longer valid and must NOT be saved and redirected with the message.

CodexWiki worked this way to avoid version collisions and actually is how the majority of content software works. I will have to add this back into the save operations of the content objects as it seems that it is not there anymore.

/* Check for Version Modifications just before saving */

if( oActiveContent.getVersion() neq rc.pageVersion ){

getPlugin("MessageBox”)

.setMessage(type="warning”,

message="Page was not saved as you where editing an old version of the page.

Displaying current version");

/* ReRoute */

setNextEvent(showKey & rc.pageName);

}

Thoughts?

signature0.jpg

Luis F. Majano
CEO
Ortus Solutions, Corp
www.ortussolutions.com

ColdBox Platform: http://www.coldbox.org
Linked In: http://www.linkedin.com/pub/3/731/483
Blog: http://www.luismajano.com
IECFUG Manager: http://www.iecfug.com

Social: twitter.com/lmajano facebook.com/lmajano

16

Hi Luis,

we are currently running Contentbox on a non official release of Railo which is 4.3.003 which supports multiple datasources for ORM.
It really started to become bad with Railo (before it ran under Windows and ACF 9, everything was migrated to Linux and Railo)

As we can see from the SQL monitor the transaction is run on the database directly.

The table cb_contentCategories contains roughly 10000 entries. We have a lot of content but each entry has just 1-3 categories.

So if Hibernate runs this query:
Hibernate:
delete
from
cb_contentCategories
where
FK_categoryID=?

about 3000 entries are deleted from the database and then reinserted.

For my feeling the the FK_contentID is missing here in this query, but if
you say that the manipulation is done in the Hiberate session then I’m
wondering if something goes wrong with Railo and ORM?

Best regards,

Gunnar

17

Gunnar, I would guess ORM. It’s not clear but it seems to fit.

For every entry, you have up to 3 categories. That would bring the many to many table too 3000 roughly, which for some reason hibernate will always delete a many to many table and re-create it.

Can you confirm if that is what you’re seeing?

18

So when contentbox removes the categories for a A SINGLE piece of content, that is being flushed to the database automatically? In addition to that, it’s removing the contents of the category table as well?

It is possible to distill that down to a simple, reproducible bit of code that we can show the Railo guys outside of ContentBox and ColdBox?

Thanks!

~Brad

ColdBox Platform Evangelist
Ortus Solutions, Corp

E-mail: brad@coldbox.org
ColdBox Platform: http://www.coldbox.org
Blog: http://www.codersrevolution.com

19

Wow. That is very strange gunnar. I would have expected the content ID to be there. I will have to verify on Adobe and rail but it should not do that. It should drop only those with that content id

20

If that is a straight copy from the logs Luis, from ColdFusion it will always place the bound query, that means the question mark will appear with no values. I would assume Railo is the same here.