Imported a big forum, 500 server error when loading more topics scrolling down

(Rodrigo Farcas) #1

Imported a big forum 2.500.000 posts, many categories, etc.

When scrolling down in some categories and the system tries to load more topics, I am getting a 500 server error on console. Out of memory? any ideas appreciated.

PasatiempoS - Comunidad Secundarios (scroll down)

(Michael Downey) #2

Some random questions

  • What are your server specs?
  • Anything else running on the host?
  • Why is each topic pinned in the category?

(Sam Saffron) #3

step #1, look at /logs when you get 500 errors it will tell you what went wrong.

(Robbo) #4

The server is a linode 8G instance running multiple forums but this one is the biggest by far. I believe the queue is basically “flooded” from the import so we can’t even get our admin accounts yet due to emails being low in the queue for resetting a password (was a mistake to do this on a fresh instance instead of making my account first and not adding another admin email jsut for this).

Restarting the server to see if it helps incase it was a memory issue from the very big import which took days.

(Robbo) #5

Looks like the import corrupted some stuff? Rebaking posts to see if that helps.

ActiveRecord::StatementInvalid (PG::InvalidRowCountInResultOffsetClause: ERROR:  OFFSET must not be negative
: SELECT  "topics"."id" AS t0_r0, "topics"."title" AS t0_r1, "topics"."last_posted_at" AS t0_r2, "topics"."created_at" AS t0_r3, "topics"."updated_at" AS t0_r4, "topics"."views" AS t0_r5, "topics"."posts_count" AS t0_r6, "topics"."user_id" AS t0_r7, "topics"."last_post_user_id" AS t0_r8, "topics"."reply_count" AS t0_r9, "topics"."featured_user1_id" AS t0_r10, "topics"."featured_user2_id" AS t0_r11, "topics"."featured_user3_id" AS t0_r12, "topics"."avg_time" AS t0_r13, "topics"."deleted_at" AS t0_r14, "topics"."highest_post_number" AS t0_r15, "topics"."image_url" AS t0_r16, "topics"."off_topic_count" AS t0_r17, "topics"."like_count" AS t0_r18, "topics"."incoming_link_count" AS t0_r19, "topics"."bookmark_count" AS t0_r20, "topics"."category_id" AS t0_r21, "topics"."visible" AS t0_r22, "topics"."moderator_posts_count" AS t0_r23, "topics"."closed" AS t0_r24, "topics"."archived" AS t0_r25, "topics"."bumped_at" AS t0_r26, "topics"."has_summary" AS t0_r27, "topics"."vote_count" AS t0_r28, "topics"."archetype" AS t0_r29, "topics"."featured_user4_id" AS t0_r30, "topics"."notify_moderators_count" AS t0_r31, "topics"."spam_count" AS t0_r32, "topics"."illegal_count" AS t0_r33, "topics"."inappropriate_count" AS t0_r34, "topics"."pinned_at" AS t0_r35, "topics"."score" AS t0_r36, "topics"."percent_rank" AS t0_r37, "topics"."notify_user_count" AS t0_r38, "topics"."subtype" AS t0_r39, "topics"."slug" AS t0_r40, "topics"."auto_close_at" AS t0_r41, "topics"."auto_close_user_id" AS t0_r42, "topics"."auto_close_started_at" AS t0_r43, "topics"."deleted_by_id" AS t0_r44, "topics"."participant_count" AS t0_r45, "topics"."word_count" AS t0_r46, "topics"."excerpt" AS t0_r47, "topics"."pinned_globally" AS t0_r48, "topics"."auto_close_based_on_last_post" AS t0_r49, "topics"."auto_close_hours" AS t0_r50, "categories"."id" AS t1_r0, "categories"."name" AS t1_r1, "categories"."color" AS t1_r2, "categories"."topic_id" AS t1_r3, "categories"."topic_count" AS t1_r4, "categories"."created_at" AS t1_r5, "categories"."updated_at" AS t1_r6, "categories"."user_id" AS t1_r7, "categories"."topics_year" AS t1_r8, "categories"."topics_month" AS t1_r9, "categories"."topics_week" AS t1_r10, "categories"."slug" AS t1_r11, "categories"."description" AS t1_r12, "categories"."text_color" AS t1_r13, "categories"."read_restricted" AS t1_r14, "categories"."auto_close_hours" AS t1_r15, "categories"."post_count" AS t1_r16, "categories"."latest_post_id" AS t1_r17, "categories"."latest_topic_id" AS t1_r18, "categories"."position" AS t1_r19, "categories"."parent_category_id" AS t1_r20, "categories"."posts_year" AS t1_r21, "categories"."posts_month" AS t1_r22, "categories"."posts_week" AS t1_r23, "categories"."email_in" AS t1_r24, "categories"."email_in_allow_strangers" AS t1_r25, "categories"."topics_day" AS t1_r26, "categories"."posts_day" AS t1_r27, "categories"."logo_url" AS t1_r28, "categories"."background_url" AS t1_r29, "categories"."allow_badges" AS t1_r30, "categories"."name_lower" AS t1_r31, "categories"."auto_close_based_on_last_post" AS t1_r32 FROM "topics" LEFT OUTER JOIN "categories" ON "categories"."id" = "topics"."category_id" WHERE ( = 96 or (categories.parent_category_id = 96 AND categories.topic_id <> AND (topics.archetype <> 'private_message') AND "topics"."visible" = 't' AND (topics.deleted_at IS NULL) AND (topics.category_id IS NULL or topics.category_id IN (54,12,16,39,11,1,13,22,23,3,30,20,182,44,35,64,79,60,9,129,143,136,70,154,76,145,156,7,98,17,131,97,89,100,103,27,138,42,82,49,37,128,84,118,124,115,10,135,94,51,127,91,46,140,52,90,88,61,112,96,14,28,171,179,40,26,32,78,141,176,158,130,93,18,152,92,157,164,117,165,151,80,108,34,41,161,68,168,174,123,169,173,149,73,122,101,69,72,107,77,120,113,50,48,102,159,25,8,71,181,147,126,153,180,15,86,24,29,160,110,172,87,104,125,167,31,175,116,63,106,99,148,58,139,67,114,134,38,133,177,119,95,150,162,66,163,65,53,121,85,105,36,21,55,43,62,170,144,166,6,178,47,81,146,5,56,111,155,33,59,137,45,132,74,109,83,75,19,57)) AND (NOT (  pinned_at IS NOT NULL  ))  ORDER BY topics.bumped_at DESC LIMIT 30 OFFSET -1)

(Sam Saffron) #6

interesting this would be related to pinning and work I did something about your pinned topics is out-of-whack post import.

I just added a safeguard

(Robbo) #7

I was about to post saying that our server must be out of date because I pulled locally and saw it did a check for < 0. Thanks for the quick fix :smile:

(Kane York) #8

Wouldn’t that mean… that @robbo has a whole page of pinned topics? :scream:

(Sam Saffron) #9

yeah … probably.

@robbo keep in mind, if you have more than 10 pinned topics in a category or globally you are running an unsupported setup, we are going to blanked disallow this as it mucks up optimisations we can make and is hostile towards users.

(Robbo) #10

This is from an import, obviously not yet optimized/changed from after the import. There are also a large amount of categories, the pinned topics are from sub categories of the category that was broken. I don’t think it is fair to simply not support this as big forums can easily have a large amount of categories and thus a large amount of pinned topics on the parent category. Restricting discourse to small to medium sized forums doesn’t seem like a very smart idea. It would be better to simply pin the topic only in the direct category it is in.

(Mittineague) #11

This reminds me of
"When everything is ___, nothing is ___"

medical: When everything is STAT, nothing is STAT
CSS: When everything is !important, nothing is !important

or in this case
When everything is Pinned, nothing is Pinned

Anyway, to get more on-topic, would it be acceptable to have the migration succeed but at the cost of losing the Pinned?

As far as size, SitePoint migrated a large number of posts in batches so I’m sure it can be done.

(Sam Saffron) #12

The 10 rule works just fine for enormous forums.

You get max of 10 globally pinned topics before stuff breaks. (its actually 30, but really anything more than 5 is egregiously bad)

You get max of 10 per category pinned topics. (meaning if you have thousands of categories you can have 10s of thousands of pins)

There is ZERO reason to need more than 10 pinned topics per category. You are creating a hostile forum, in fact I am going to check in code that stops you from being able to pin that much stuff.

REMEMBER Discourse has 2 levels of pinning, you can either pin on a category or globally.

(Mittineague) #13

Agreed 110+%

But it should be possible in such cases to remove the Pins and let the migration happen, no?

(Sam Saffron) #14

Sure, no problem with that, technically what we are going to do is just tell users they can not pin anymore beyond a certain point.

(Robbo) #15

We don’t have many pins per category, just checked some and there is usually less than 5 in what I saw (most have none, not sure why there are lots here, it is from an import and my job is to get everything imported from XenForo not to manage content). However the problem category only has 3 direct pins. The rest are coming from the categories children. So from what you have just said this is no problem for the optimizations?

It is however a problem for the end user obviously, would it be possible to get a setting to decide if parent categories should show the children pins or not? I believe in XenForo they were only showing in the direct category and not in parents and that is why this wasn’t an issue to the end user there.

The forums will be cleaned up a lot by the editors of the site and my job is done, it is imported and working. But just want to clarify those things regardless.

(Sam Saffron) #17

I am not sure if the concept of floating up pins from child to parent category makes sense I think we should change that

(Sam Saffron) #18

@codinghorror was reading through my bookmarks and wanted to mention this one.

At the moment when you look at a “parent” category we float up all the pins in the “children” both on /categories and /latest for the category.

This means that if you have a broad parent category with tons of child categories pinning becomes quite useless cause you quickly flood the parent category with pinned topics.

Should we change this behavior and stop this inheritance?

(Jeff Atwood) #19

Probably fine anything to suppress pins is good in my book.

(Sam Saffron) #20


pinned sub-category topics no longer float up to the category.