« SMF bug 4770 (top level menu items should be clickable)
Board order issues
SMF bug 4961 (no index.php or .htaccess file when new attach… »

Arantor

  • As powerful as possible, as complex as necessary.
  • Posts: 14,278
Board order issues
« on March 22nd, 2012, 12:27 PM »
I'm really not sure why this is the case but it needs fixing. Some of these boards I'm about to mention are not public visible.

Board index lists Public Area category as:
 - The Pub
     FAQs
     Features
     Bug reports
     Plugins
     Off-topic
     SMF
 - Dev Blog
 - Salvaged Topics
 - Salvaged Board

Going to The Pub itself also shows its sub-boards correctly, as does the view in Boards & Categories. But if I view the Public Area category as a whole[1] I get a very different order.

 - Salvaged Topics
 - Salvaged Board
 - The Pub
     FAQs
     Off-topic
     Features
     SMF
     Plugins
     Bug reports
 - Dev Blog


Now, I'm not 100% sure but for each level of hierarchy, that would be consistent with MyISAM ordering rather than correct ordering (i.e. order of creation).

I'm not sure how best to fix this. Part of me says we could just fix it by using ORDER BY board_order but I have a feeling we might be better served using Mark Rose's suggestion and applying the board order to the primary key (so that we end up with id_board as a UNIQUE, but board_order, id_board as physical primary key, so that when it's reordered, regardless of whether that's MyISAM[2] or InnoDB[3] it works properly)
 1. Also, why doesn't the header on the board index link to the category itself? I think it really should.
 2. MyISAM orders by creation, but there's a physical ALTER TABLE to reorder it in the manage boards code.
 3. InnoDB orders by primary key, so making the board_order the first half of the primary key, it will automatically be sorted into order.
When we unite against a common enemy that attacks our ethos, it nurtures group solidarity. Trolls are sensational, yes, but we keep everyone honest. | Game Memorial

Pandos

  • Living on the edge of Wedge
  • Posts: 635
Re: Board order issues
« Reply #1, on March 22nd, 2012, 12:36 PM »
Yes. Had the same problem by switching to INOODB a long time ago.
Just set the Primary Key to board_order AND id_board.
Works great.
# dpkg-reconfigure brain
error: brain is not installed or configured

Arantor

  • As powerful as possible, as complex as necessary.
  • Posts: 14,278
Re: Board order issues
« Reply #2, on March 22nd, 2012, 12:39 PM »
There are other issues with merely doing that, namely that it screws up other optimisations in the pile because MySQL considers a composite key from left to right.

Pandos

  • Living on the edge of Wedge
  • Posts: 635
Re: Board order issues
« Reply #3, on March 22nd, 2012, 12:57 PM »

I can give you a dump of my structure from this table. Perhaps it is usefull for you.

Code: [Select]
CREATE TABLE `smf_boards` (  `id_board` smallint(5) unsigned NOT NULL AUTO_INCREMENT, `id_cat` tinyint(4) unsigned NOT NULL DEFAULT '0', `child_level` tinyint(4) unsigned NOT NULL DEFAULT '0', `id_parent` smallint(5) unsigned NOT NULL DEFAULT '0', `board_order` smallint(5) NOT NULL DEFAULT '0', `id_last_msg` int(10) unsigned NOT NULL DEFAULT '0', `id_msg_updated` int(10) unsigned NOT NULL DEFAULT '0', `member_groups` varchar(255) NOT NULL DEFAULT '-1,0', `id_profile` smallint(5) unsigned NOT NULL DEFAULT '1', `name` varchar(255) NOT NULL, `description` text NOT NULL, `num_topics` mediumint(8) unsigned NOT NULL DEFAULT '0', `num_posts` mediumint(8) unsigned NOT NULL DEFAULT '0', `count_posts` tinyint(4) NOT NULL DEFAULT '0', `id_theme` tinyint(4) unsigned NOT NULL DEFAULT '0', `override_theme` tinyint(4) unsigned NOT NULL DEFAULT '0', `countMoney` tinyint(1) unsigned NOT NULL DEFAULT '1', `lastUpdated` varchar(255) NOT NULL DEFAULT '', `redirect` varchar(255) NOT NULL DEFAULT '', `unapproved_posts` smallint(5) NOT NULL DEFAULT '0', `unapproved_topics` smallint(5) NOT NULL DEFAULT '0', `rateTopic_board_enable` tinyint(4) NOT NULL DEFAULT '0', PRIMARY KEY (`board_order`,`id_board`), UNIQUE KEY `id_board_2` (`id_board`), KEY `ID_PARENT` (`id_parent`), KEY `ID_MSG_UPDATED` (`id_msg_updated`), KEY `memberGroups` (`member_groups`(48)), KEY `categories` (`id_cat`), KEY `ID_BOARD` (`id_board`), KEY `ID_CAT` (`id_cat`) ) ENGINE=InnoDB AUTO_INCREMENT=680 DEFAULT CHARSET=latin1 AUTO_INCREMENT=680 ;

Arantor

  • As powerful as possible, as complex as necessary.
  • Posts: 14,278
Re: Board order issues
« Reply #4, on March 22nd, 2012, 01:00 PM »
Quote
PRIMARY KEY (`board_order`,`id_board`), UNIQUE KEY `id_board_2` (`id_board`)
You've done exactly what I was speculating about: created a composite key on the board_order + id_board with a unique key on just the id_board to preserve other optimisations :)

Thanks for posting, that gives me a lot more reassurance about doing it in Wedge now :)

Pandos

  • Living on the edge of Wedge
  • Posts: 635

Arantor

  • As powerful as possible, as complex as necessary.
  • Posts: 14,278
Re: Board order issues
« Reply #6, on March 29th, 2012, 02:00 AM »
I've committed this in r1519, so that should fix it - but it needs Nao to apply the changes here before I can say this one is fixed.

Pandos

  • Living on the edge of Wedge
  • Posts: 635
Re: Board order issues
« Reply #7, on March 29th, 2012, 07:35 AM »
By the way. For use with InnoDB the categories table must be updated too:
Code: [Select]
ALTER TABLE `$prefix_categories` ADD UNIQUE KEY (`id_cat`), DROP PRIMARY KEY, ADD PRIMARY KEY ( `cat_order`, `id_cat` ), ENGINE=InnoDB;


Arantor

  • As powerful as possible, as complex as necessary.
  • Posts: 14,278
Re: Board order issues
« Reply #8, on March 29th, 2012, 12:01 PM »
Oh, I didn't know about that part, thanks for letting me know.

(Of course, I need to fix it in the installer, which means changing the original key definitions rather than writing change statements.)

Nao

  • Dadman with a boy
  • Posts: 16,079
Re: Board order issues
« Reply #9, on March 29th, 2012, 10:01 PM »
Just FYI I applied the database structure change manually this morning. Did it fix the problem as expected?

Arantor

  • As powerful as possible, as complex as necessary.
  • Posts: 14,278
Re: Board order issues
« Reply #10, on March 29th, 2012, 10:19 PM »
No, it hasn't. How did you apply the changes, exactly?

(You may have to move a board, then move it back to force MySQL to rebuild the indexes, assuming it's in a MyISAM table)

Pandos

  • Living on the edge of Wedge
  • Posts: 635

Nao

  • Dadman with a boy
  • Posts: 16,079
Re: Board order issues
« Reply #12, on March 29th, 2012, 10:58 PM »
Through admin? I'll let you do it. Cant access my pc tonight ;)

Arantor

  • As powerful as possible, as complex as necessary.
  • Posts: 14,278
Re: Board order issues
« Reply #13, on March 29th, 2012, 11:05 PM »
I've moved a board from admin, moved it back.

There were other places that weren't quite right - namely the jumpto could be misordered at times and so could move-topics. But the category page is still wrong, even if everything else is correct, which says to me there is something wrong with the category listing page itself. I'm doing some other oddments tonight, so I'll look at that code too.

Nao

  • Dadman with a boy
  • Posts: 16,079
Re: Board order issues
« Reply #14, on March 29th, 2012, 11:13 PM »
Okay ;)

My, we still have a lot of small bugs to fix... :(
« SMF bug 4770 (top level menu items should be clickable)
SMF bug 4961 (no index.php or .htaccess file when new attach… »