XF 1.4 [URGENT] Missing Posts/Nodes, What is Going on Here?

[Live Free]

An hour ago I noticed the forum post count was half what it should be and discovered an entire category and all posts were missing from the homepage. I'll start from the beginning to better identify the issue.

Tonight I upgraded from XenPorta 1 to XenPorta 2. I did the import from XenPorta 1 to 2 as recommended in the add-ons thread, without finalizing the import. It asked for database information for import purposes, and seemed to work fine and I didn't notice anything awry.

I later attempted to uninstall the following add-ons as they were already disabled (cleaning house for eventual 1.5 upgrade):

[RT]Staff Room Unread
[rellect] Nodes Gride
[FreddyHouse] Two Factor Authentication

There was a timeout during this process and I'm not sure if uninstallation was successfully.

Soon thereafter I notice I'm missing post count is way off (half what it should be). I'm taken about but I assume I have to rebuild the cache after the XenPorta 2 import. I go to rebuild the cache but once I click the Rebuild Cache sidebar link under tools, it starts to (apparently) rebuild all cache sections at once. See screenshot.

I'm allowing this process to continue though it's unusual and then I see something along the lines of [Rebuild: Deleting threads ... (17 of 213)].

I attempted to cancel the rebuild/thread delete. At this point I realize I'm missing an entire forum category.

I ran a file health check, no issue. I am running the latest secure version of XF 1.4. I just checked my database, and xf_node IS missing several nodes. Does this mean all posts/threads are gone, or just the node itself?

I'm at a loss at the moment, I'm unsure what happened and what is going on, and how to rectify it. I could seriously use some help.

Was there a security compromise during the Xenporta import, since database information was submitted onto the page?

Is this a bug with one of the add-ons I installed/uninstalled? Malware from an add-on? A XF issue with the uninstallation process being potentially interrupted?

Is my security compromised?

What is my course of action? Should I allow the unusual rebuild process to continue to completion?

How do I determine if my content is still in the database, just not displaying properly?

I do have a backup from several days ago, but I'd like to avoid that if necessary.

 

[Jake Bunce]

You can run this query on your database to check for orphaned threads:

SELECT t.thread_id
FROM xf_thread AS t
LEFT JOIN xf_node AS n ON (n.node_id = t.node_id)
WHERE n.node_id IS NULL

If the query returns any records then there are orphaned threads (ie the associated node is missing).

A backup is the solution here. If the backup is too old then a merge may be possible but that requires some dexterity. I can probably do this for you if you give me access to both databases (live and backup).

 

[Live Free]

@Jake Bunce

I ran the query I the results show 81311 orphaned threads, but there should be closer to 813,110.

I'm not an expert on this sort of thing, so I would appreciate your help. The backup is 8 days old, so if the data can't be recovered then ideally I'd merge the old data for the missing categories and keep the up-to-date existing for the rest of the forum.

I'm exhausted and will pick this up tomorrow asap.

Any thoughts on the cause of this?

 

[Paul B]

Any thoughts on the cause of this?

If all you did was upgrade one add-on and uninstall others, and didn't to anything else, one of those actions must be the likely cause.

You would need to post in the respective add-on threads to check whether there is any code in the add-ons which could account for it.

 

[Mike]

If you triggered an import related to XenPorta, my guess is that it could be this. When importing forums and maintaining IDs, we clear out some of the initial data from installation, which includes nodes 1 and 2. This can be overridden by other importers to control what they clear out (if anything) in this situation. My guess is that this hasn't been overridden, so it triggered the deletion of 2 nodes.

This is something that would need to be resolved via a backup. We don't generally recommend attempting to do backup merges as there are many intertwined pieces of data. It may be doable, but the specific data required would need to be very carefully extracted across a number of tables. If this is an approach you want to take, your best shot might be to hire someone with experience to help.

 

[Live Free]

You can run this query on your database to check for orphaned threads:

SELECT t.thread_id
FROM xf_thread AS t
LEFT JOIN xf_node AS n ON (n.node_id = t.node_id)
WHERE n.node_id IS NULL

If the query returns any records then there are orphaned threads (ie the associated node is missing).

A backup is the solution here. If the backup is too old then a merge may be possible but that requires some dexterity. I can probably do this for you if you give me access to both databases (live and backup).

Okay so it was late last night and I may have misinterpreted what you said and the query results. The above query shows orphaned threads, not total posts in orphaned threads. The 813110 number I referenced was my estimated missing posts, not threads). If that's the case, all content may still exist in the database.

Still, the numbers don't quite work, going strictly by the forum statistic totals. Before this issue there were approximately 69,000 threads and 1,280,000 posts (April 16th). Presently, there are approximately 49,000 threads and 607,000 posts (April 27). The query showed 81311 orphaned threads. I'm assuming that number includes orphaned threads created by the deleted nodes, but also by past deleted threads or other pre-existing orphaned threads? Duplicate content? Is there a way to query the number of posts associated with the orphaned threads, or some other way to determine if all the missing content is still in the database?

Is there a way I can better confirm the missing node content is in those orphaned threads?

If the content still exists in the database, is a recovery possible without a backup or merger? If all threads that were previously in a node were still associated in some way, couldn't create a new node and associate them with it?

If you triggered an import related to XenPorta, my guess is that it could be this. When importing forums and maintaining IDs, we clear out some of the initial data from installation, which includes nodes 1 and 2. This can be overridden by other importers to control what they clear out (if anything) in this situation. My guess is that this hasn't been overridden, so it triggered the deletion of 2 nodes.

This is something that would need to be resolved via a backup. We don't generally recommend attempting to do backup merges as there are many intertwined pieces of data. It may be doable, but the specific data required would need to be very carefully extracted across a number of tables. If this is an approach you want to take, your best shot might be to hire someone with experience to help.

After more thought and research, this is my opinion as well. I believe there was an option for maintaining IDs and it may or may have not been overwritten. Sucks, but better than a malicious actor. Just out of curiosity, what sorts of content are these "intertwined pieces of data" and what are the potential consequences of a insufficient merger?

When I log onto my AdminCP there are still "manual rebuild process that have been stopped before completion." Any idea what would happen if I continued this rebuild? Why did it indicate it was deleting threads when it started running before? Would it delete the 81311 orphans from the database or cause any harm?

Before I take any action, @Chris D would you mind weighing in on this? I found this thread that referenced this post and I believe this is a similar situation (but caused by a XenPorta import rather than XenMedia Gallery). It sounds like you were able to recover nodes that were deleted in an import. In your opinion, is such a recovery possible in this situation?

 

[Chris D]

It is going to be far more preferable if you're able to restore from an entire backup.

That said, when @Veer had this problem a couple of years ago, we were able to get things back up and running.

I have to add, though, he was able to test this fully in a test environment before doing it on his live site.

The solution was to recreate the xf_node and xf_forum entry for the forum with node_id 2, and also to recreate the xf_node entry for the category with node_id 1:

INSERT IGNORE INTO `xf_node` (`node_id`, `title`, `description`, `node_type_id`, `parent_node_id`, `display_order`, `display_in_list`, `lft`, `rgt`, `depth`, `style_id`, `effective_style_id`, `breadcrumb_data`) VALUES ('2', 'Forum Title', 'Forum Description', 'Forum', '0', '1', '1', '0', '0', '0', '0', '0', 'a:0:{}');

INSERT IGNORE INTO `xf_forum` (`node_id`, `discussion_count`, `message_count`, `last_post_id`, `last_post_date`, `last_post_user_id`, `moderate_messages`, `allow_posting`, `count_messages`, `find_new`, `prefix_cache`, `default_prefix_id`, `default_sort_order`, `default_sort_direction`, `require_prefix`, `allowed_watch_notifications`) VALUES ('2', '0', '0', '0', '0', '0', '0', '1', '1', '1', 'a:0:{}', '0', 'last_post_date', 'desc', '0', 'all');

INSERT IGNORE INTO `xf_node` (`node_id`, `title`, `description`, `node_type_id`, `parent_node_id`, `display_order`, `display_in_list`, `lft`, `rgt`, `depth`, `style_id`, `effective_style_id`, `breadcrumb_data`) VALUES ('1', 'Category Title', 'Category Description', 'Category', '0', '1', '1', '0', '0', '0', '0', '0', 'a:0:{}');

After creating these queries it will be advisable to adjust the title, description and permissions of the forum and category.

I'm fairly certain that this should restore everything. It's possible you may experience some issues, especially if some add-ons are expecting certain data to still be in these tables -- the above queries pretty much just add the default data in.

At the very least, if you can't test this in a test environment, please ensure you take backups. However, if this doesn't work, you may be looking at restoring a backup anyway.

Good luck.

 

[Live Free]

It is going to be far more preferable if you're able to restore from an entire backup.

That said, when @Veer had this problem a couple of years ago, we were able to get things back up and running.

I have to add, though, he was able to test this fully in a test environment before doing it on his live site.

The solution was to recreate the xf_node and xf_forum entry for the forum with node_id 2, and also to recreate the xf_node entry for the category with node_id 1:

INSERT IGNORE INTO `xf_node` (`node_id`, `title`, `description`, `node_type_id`, `parent_node_id`, `display_order`, `display_in_list`, `lft`, `rgt`, `depth`, `style_id`, `effective_style_id`, `breadcrumb_data`) VALUES ('2', 'Forum Title', 'Forum Description', 'Forum', '0', '1', '1', '0', '0', '0', '0', '0', 'a:0:{}');

INSERT IGNORE INTO `xf_forum` (`node_id`, `discussion_count`, `message_count`, `last_post_id`, `last_post_date`, `last_post_user_id`, `moderate_messages`, `allow_posting`, `count_messages`, `find_new`, `prefix_cache`, `default_prefix_id`, `default_sort_order`, `default_sort_direction`, `require_prefix`, `allowed_watch_notifications`) VALUES ('2', '0', '0', '0', '0', '0', '0', '1', '1', '1', 'a:0:{}', '0', 'last_post_date', 'desc', '0', 'all');

INSERT IGNORE INTO `xf_node` (`node_id`, `title`, `description`, `node_type_id`, `parent_node_id`, `display_order`, `display_in_list`, `lft`, `rgt`, `depth`, `style_id`, `effective_style_id`, `breadcrumb_data`) VALUES ('1', 'Category Title', 'Category Description', 'Category', '0', '1', '1', '0', '0', '0', '0', '0', 'a:0:{}');

After creating these queries it will be advisable to adjust the title, description and permissions of the forum and category.

I'm fairly certain that this should restore everything. It's possible you may experience some issues, especially if some add-ons are expecting certain data to still be in these tables -- the above queries pretty much just add the default data in.

At the very least, if you can't test this in a test environment, please ensure you take backups. However, if this doesn't work, you may be looking at restoring a backup anyway.

Good luck.

Thank you very much sir.

I have a few questions. It appears I'm not missing just node 1 and node 2, but all 5 forums and 4 sub-forums that were nested under the node 1 category. Is this expected and what impact would running such a query have on multiple missing nodes? What I'm wondering is:

1. Will I be able to associate all threads previously associated under a specific node with their respective node, or would all 5 forums be compiled into one, the recreated node 2 forum? How does the database/query identify which forums used to be in the past node?
2. Since I'm missing several nodes, I would have to recreate all 9 by running repeated queries with different values (i.e. node #3 or #7 instead of 2) inserted into your first two queries, for each missing node?
3. I would need to identify the node number for each missing forum and then run the query, correct? I imagine this can be done by Inspect Element on an archived version of the page.
4. What sort of issues might one experience and what should I be on the lookout for?
5. If I run this on a test environment, would you do any sort of testing beyond checking that the data appears intact and checking total and individual forum post counts?

 

[Chris D]

If this was caused by an import retain keys reset, only node 1 and node 2 would have been deleted. All the child nodes below those are likely still there; you just can't see them because their parent nodes are missing.

IMO, you just need to create those two missing nodes, and all of their children will become visible also.

 

[Live Free]

Thank you much Chris. I'll try it on a test environment and report back in a few hours.

 

[Mike]

It's possible those node records have been removed as well. That would likely be the default behavior. They would need to be recreated with similar queries to the ones Chris posted.

In terms of the manual rebuild processes message, to be safe, I would empty the xf_deferred table. You don't want to accidentally condition the thread deletion processes (that would be triggered by the node deletions).

 

[Live Free]

So I set up a test installation of a backup that was created after the issue happened. I ran @Chris D's queries but there seems to be no result. I'm wondering what I'm missing here. No new node is created either in the XF admincp or in the xf_node or xf_forum database tables.

However, I'm relatively confident most of the data is still there.

I identified the missing node IDs and ran the following query:

SELECT * FROM xf_thread WHERE node_id = 3

Running this for each of the missing nodes returned hundreds of results, showing hundreds of threads still associated with the missing node ids, but the missing nodes don't exist in xf_node. I know it's possible that some of the data was deleted and is missing, but it appears that at least some of it is still there. That said, I can't get Chris's query or my reiterations of it (i.e. changing INSERT IGNORE to INSERT, running the same query with the missing node id instead of node id #2)

Thoughts?

Edit:

Since running it as a query didn't work I tried the Insert tab in phpMyadmin. In this testing I was able to restore a specific missing node id (node 6), but only partially, plus threads are listed out of order in the forum.

It shows 448 threads and 49385 posts. It should be closer to 485 threads and 103,404 posts according to the most recent cache on TheWayBack Machine. If content was deleted, it'd delete it by the thread, not posts, correct? In which case the counter is off or there are still orphaned threads that should be in node #6. I'm not sure how to interpret this information.

 

[Chris D]

Remove the "IGNORE" from the queries. When running them there should be some sort of output. Even if it's an error. What exactly is the output? I suspect there is an error, and the error may be related to columns not having a default value, or similar. We may need to adjust the query, slightly.

Aside from everything else, don't worry about the little details like threads and posts being off - it may not be that significant and it can be rebuilt anyway.

Also, remember, I don't at all believe that nodes 3 and 6 are actually missing. I'm fairly certain they are still there. But if they are a child of node 1 or 2 they will just not be visible.

To be clear, I only ever anticipated you would run the three queries in my previous post. I don't believe you have to run any of the others.

Please try again, use the exact queries I posted without the IGNORE, and then paste here exactly the queries you ran, and the output you got when you ran them.

 

[Live Free]

@Chris D Okay, ran the queries without IGNORE and it seems to have added the rows as intended. Looking in the admincp I see a new category and a new forum in the node tree. After adjusting the permissions they are visible on the forum homepage but without any content and without the other missing nodes.

Queries ran:

INSERT INTO `xf_node` (`node_id`, `title`, `description`, `node_type_id`, `parent_node_id`, `display_order`, `display_in_list`, `lft`, `rgt`, `depth`, `style_id`, `effective_style_id`, `breadcrumb_data`) VALUES ('2', 'Forum Title', 'Forum Description', 'Forum', '0', '1', '1', '0', '0', '0', '0', '0', 'a:0:{}');

INSERT INTO `xf_forum` (`node_id`, `discussion_count`, `message_count`, `last_post_id`, `last_post_date`, `last_post_user_id`, `moderate_messages`, `allow_posting`, `count_messages`, `find_new`, `prefix_cache`, `default_prefix_id`, `default_sort_order`, `default_sort_direction`, `require_prefix`, `allowed_watch_notifications`) VALUES ('2', '0', '0', '0', '0', '0', '0', '1', '1', '1', 'a:0:{}', '0', 'last_post_date', 'desc', '0', 'all');

INSERT INTO `xf_node` (`node_id`, `title`, `description`, `node_type_id`, `parent_node_id`, `display_order`, `display_in_list`, `lft`, `rgt`, `depth`, `style_id`, `effective_style_id`, `breadcrumb_data`) VALUES ('1', 'Category Title', 'Category Description', 'Category', '0', '1', '1', '0', '0', '0', '0', '0', 'a:0:{}');

Outputs:

INSERT  INTO  `xf_node`(  `node_id`,  `title`,  `description`,  `node_type_id`,  `parent_node_id`,  `display_order`,  `display_in_list`,  `lft`,  `rgt`,  `depth`,  `style_id`,  `effective_style_id`,  `breadcrumb_data`  )
VALUES(
'2',  'Forum Title',  'Forum Description',  'Forum',  '0',  '1',  '1',  '0',  '0',  '0',  '0',  '0',  'a:0:{}'
)

#1054 - Unknown column 'moderate_messages' in 'field list'

Changed moderate_messages to _moderate_replies

INSERT  INTO  `xf_forum`(  `node_id`,  `discussion_count`,  `message_count`,  `last_post_id`,  `last_post_date`,  `last_post_user_id`,  `moderate_replies`,  `allow_posting`,  `count_messages`,  `find_new`,  `prefix_cache`,`default_prefix_id`,  `default_sort_order`,  `default_sort_direction`,  `require_prefix`,  `allowed_watch_notifications`  )
VALUES(
'2',  '0',  '0',  '0',  '0',  '0',  '0',  '1',  '1',  '1',  'a:0:{}',  '0',  'last_post_date',  'desc',  '0',  'all'
)

INSERT  INTO  `xf_node`(  `node_id`,  `title`,  `description`,  `node_type_id`,  `parent_node_id`,  `display_order`,  `display_in_list`,  `lft`,  `rgt`,  `depth`,  `style_id`,  `effective_style_id`,  `breadcrumb_data`  )
VALUES(
'1',  'Category Title',  'Category Description',  'Category',  '0',  '1',  '1',  '0',  '0',  '0',  '0',  '0',  'a:0:{}'
)

Note: While the category that housed all the missing forums was node #1, I don't believe node #2 was in use.

 

[Chris D]

This is probably ok. It might be that the nested set info needs to be updated now.

The easiest way to do this is to go into the newly re-created node in the Admin CP and change its display order (to any number, go back and change it back again if necessary). This triggers all the nested set info to rebuild.

Has it now made the other nodes visible?

 

[Live Free]

First attempt of changing the display order for both the category and the forum, then back again, yielded no results.

I tried adding the forum as a child of node category #1 then changed the order, which triggered the deferred action to delete threads. I canceled this process, when to xf_deferred, emptied the table, then tried to change the display order once again with no result.

I originally deleted xf_deferred as recommend but had to reinstall the test installation and forgot to re-empty xf_deferred. This is a test installation, fortunately. In my opinion any thread deletions that were triggered just now wouldn't have been enough to delete all the nodes so we should still get some results, especially since they were still listed in xf_deferred. If you think this spoiled the process I can restore the database and try again from this point after ensuring xf_deferred is re-emptied.

 

[Live Free]

Should I restore the database and try the display order method again after ensuring xf_deferred is emptied? Is there a rebuild process that might restore the missing nodes/posts?

Or would the next step be to run the follow two queries for each of the missing node id's?

INSERT INTO `xf_node` (`node_id`, `title`, `description`, `node_type_id`, `parent_node_id`, `display_order`, `display_in_list`, `lft`, `rgt`, `depth`, `style_id`, `effective_style_id`, `breadcrumb_data`) VALUES ('MISSINGNODEID', 'Forum Title', 'Forum Description', 'Forum', '1', '1', '1', '0', '0', '0', '0', '0', 'a:0:{}');

INSERT INTO `xf_forum` (`node_id`, `discussion_count`, `message_count`, `last_post_id`, `last_post_date`, `last_post_user_id`, `moderate_messages`, `allow_posting`, `count_messages`, `find_new`, `prefix_cache`, `default_prefix_id`, `default_sort_order`, `default_sort_direction`, `require_prefix`, `allowed_watch_notifications`) VALUES ('MISSINGNODEID', '0', '0', '0', '0', '0', '0', '1', '1', '1', 'a:0:{}', '0', 'last_post_date', 'desc', '0', 'all');

Substituting "MISSINGNODEID" for one of the below missing node ids, and repeating both queries for all nodes. Would it also be okay to change the inserted value for parent_node_id from 0 to 1 to place the forums under the category instead of without a category?

Missing nodes: 1,3,4,6,63,66,83,84,91,125

 

[Chris D]

Yeah, it looks like Mike was right.

I'm not sure if the behaviour has changed since I helped someone with something similar, or it was just coincidentally only those two nodes.

But, certainly, it looks like those nodes need recreating too, so yes, run all those queries.

Just be cautious about the exact queries you run. Specifically, note the difference between forums and categories. By default node ID 1 is a category, so instead of the node_type_id being 'Forum' it should be 'Category'. Also categories don't have an entry in the xf_forum table.

 

[Live Free]

Yeah, it looks like Mike was right.

I'm not sure if the behaviour has changed since I helped someone with something similar, or it was just coincidentally only those two nodes.

But, certainly, it looks like those nodes need recreating too, so yes, run all those queries.

Just be cautious about the exact queries you run. Specifically, note the difference between forums and categories. By default node ID 1 is a category, so instead of the node_type_id being 'Forum' it should be 'Category'. Also categories don't have an entry in the xf_forum table.

Got it, thank you sir.

So I ran the queries only for node forums/sub-forums. I adjusted permissions, display order, and placed them under category node #1. I then ran cron to rebuild forum statistics and rebuild forum information.

It looks like for the most part, content is preserved. However, I'm about 100,000 posts of the 800,000 or so that went missing.



What are your thoughts? Was that missing content deleted by the deferred process triggered originally by the add-on? Is it still in the database somewhere as an orphaned thread? Are there any other rebuild processes that might bring the missing threads to light? If it's gone for good, should I accept this as a victory or attempt a merger?

 

[Chris D]

I would probably be close to calling this one a victory.

Whatever you do next, make sure you take a full back up just in case anything goes wrong that didn't happen during this testing.

But, yeah, I suspect you might not get much better than this. The whole idea of the process is it aims to not leave orphaned threads so the ones which are missing are likely gone for good.