[bug+fix] BoardIndex category and board order wrong

Started by Something like that, December 24, 2007, 04:16:00 AM

Previous topic - Next topic

Something like that

I have no idea why, but sometimes the categories and boards in the board index would not display as intended. Specifically, I had the most recently added category display after all the other categories, regardless of the specified sort order in the forum software. This wasn't working.

I solved the problem by changing a line in Sources/BoardIndex.php
AND b.childLevel <= 1" : ''), __FILE__, __LINE__);
to
AND b.childLevel <= 1" : '') . " ORDER BY c.catOrder,b.boardOrder ASC", __FILE__, __LINE__);

I hope this can be added to the next bug-fix release of 1.1. Thanks!

SleePy

SMF does this when you edit boards by doing this query:
      ALTER TABLE smf_boards
      ORDER BY boardOrder


If it didn't happen then either mysql is disallowing it or the script isn't getting that far due to a modification preventing it from getting this far.
Jeremy D — Site Team / SMF Developer
Support the SMF Support team!
Profiles:GitHub
Join us on IRC Libera.chat/#smf

Something like that

Quote from: SleePy on December 24, 2007, 01:02:16 PM
SMF does this when you edit boards by doing this query:
      ALTER TABLE smf_boards
      ORDER BY boardOrder


If it didn't happen then either mysql is disallowing it or the script isn't getting that far due to a modification preventing it from getting this far.

What would cause the categories to be out of order then? Even if I manually run
ALTER TABLE `smf_categories`  ORDER BY `catOrder`
ALTER TABLE `smf_boards`  ORDER BY `boardOrder`

in MySQL, I still end up with categories/boards showing in the wrong order. I have no modifications installed.

However, the results of
SELECT * FROM `smf_boards`
and
SELECT * FROM `smf_boards` ORDER BY `smf_boards`.`boardOrder` ASC
are in a different order.

I'm using InnoDB tables, MySQL 5.0.24a-standard-log, with utf8_unicode_ci collation throughout.

Something like that

So perhaps this is a MySQL bug? In any event, the sort takes next to no time for MySQL to run, so I don't see the harm in having an ORDER BY clause.

SleePy

I wonder if it is a mysql bug. What version of mysql are you using?

What happens if you convert the smf_boards and smf_categories back to myisam?
Jeremy D — Site Team / SMF Developer
Support the SMF Support team!
Profiles:GitHub
Join us on IRC Libera.chat/#smf

Something like that

Quote from: SleePy on December 24, 2007, 03:54:58 PM
I wonder if it is a mysql bug. What version of mysql are you using?

MySQL 5.0.24a as stated above.

QuoteWhat happens if you convert the smf_boards and smf_categories back to myisam?

Converting back, then running ALTER TABLE .. ORDER BY .. fixes the problem. It appears that InnoDB is not honouring ALTER TABLE .. ORDER BY.

SleePy

I converted my smf_boards and smf_categories on a test install of SMF. It did restore its order. But I was able to edit it back to the way it should be.

I did report this as it seems this could be an issue that would make people using Big Boards and converting over to InnoDB for what ever. For team Reference the bug is 1165
Jeremy D — Site Team / SMF Developer
Support the SMF Support team!
Profiles:GitHub
Join us on IRC Libera.chat/#smf

Ben_S

InnoDB does not support ALTER TABLE ORDER BY.

It was done that way to make the query that little bit more efficient, although as people are finding out, it causes issues when the table is InnoDB.
Liverpool FC Forum with 14 million+ posts.

Something like that

It also causes a sorting issue when you're moving posts, too.

Something like that

Also, the sort order appears wrong in the Move Topic function when using InnoDB tables. This can be fixed changing line 95 in MoveTopic.php from

AND $user_info[query_see_board]" /* . (!in_array(0, $boards) ? "

to

AND $user_info[query_see_board] ORDER BY c.catOrder,b.boardOrder ASC" /* . (!in_array(0, $boards) ? "

This should really be fixed for the next release of 1.1 and 2.

DMNT

Also $context['jump_to'] seems to be affected. My proposed fix:
Load.php
replace
                WHERE $user_info[query_see_board]", __FILE__, __LINE__);
with
                WHERE $user_info[query_see_board] ORDER BY c.catOrder,b.boardOrder ASC", __FILE__, __LINE__);
in function loadJumpTo()

Advertisement: