Categories listing in wrong order on *local* server

[Atrus]

This is not an SMF problem, but SMF's behavior on my local "test" server has me baffled.

My active, online SMF forum is hosted on a large, well-known host, and is working perfectly. I also have xampp installed on my home computer and have a copy of my smf forums running on it to test any changes before uploading them to the live forums. I set up a virtual network in xampp, and from any one of the 4 local computers on my home network, I can point a web browser to http://<websitename>.local and go to my xampp version of the forums. All was working perfectly on my local server until I made a few changes to my online, live forums.

Recently, I added new categories and boards to the live forums, and it is working fine. After backing up the online database from SMF's "Forum Maintenance->database->backup database" and importing it to my xampp server, all the changes I made to the online forms were now in my local forums. However, the local version of the forums displays the categories in the wrong order. All posts in each category are in the correct order, but the order of the categories is wrong and cannot be changed. When I go into admin->boards, on the local forums, *there,* the order of everything is correct, but no changes made *there* effect the order of the displayed categories from the home page of the local forums.  They seem to now have their own preferred order, and I am unable to change that preference. On my local forum, It makes no difference which theme is active, the order of the categories does not change. From any of the 4 local computers on my home network, the same incorrect order is displayed. All 4 computers display the categories in the correct order when connected to the live, on-line forums. The problem is definitely somewhere in my local host computer.

Changing browsers does not resolve the problem. I have completely uninstalled SMF forums, and completely uninstalled xampp. After reinstalling everything, the same situation exists.  I'm scratching my head.

I don't expect anyone to lead me by the hand, but can anyone give me a nudge in the right direction as to what else to check that might effect the order in which the categories are displayed, and lock them to their "preferred order" when served from my local xampp server?

Thanks in advance for any help...

   Atrus
 

[margarett]

That is an issue reported some times, related to a specific version of MySQL. I also use xampp but never had that issue, so I should be using a different version from yours. Can you try a different release?

[Atrus]

That is an issue reported some times, related to a specific version of MySQL. I also use xampp but never had that issue, so I should be using a different version from yours. Can you try a different release?

I'm running:

XAMPP
1.8.3
[PHP: 5.5.6]

Database server
•Server: 127.0.0.1 via TCP/IP
•Server type: MySQL
•Server version: 5.6.14 - MySQL Community Server (GPL)
•Protocol version: 10
•User: root@localhost
• Server charset:  UTF-8 Unicode (utf8)


Web server
•Apache/2.4.7 (Win32) OpenSSL/1.0.1e PHP/5.5.6
•Database client version: libmysql - mysqlnd 5.0.11-dev - 20120503 - $Id: 40933630edef551dfaca71298a83fad8d03d62d4 $
•PHP extension: mysqli Documentation


I believe that is the latest version?  Should I try an older version?  Everything was working perfectly on the same version of the local xampp server until I imported the MySQL database from the online forum. 

I hate to go "backwards" but if you think it's worth a try, I'll try it.  I'll wait until I hear back from you.

Thanks for your time.

 

[margarett]

Hummm exactly what I'm using... And yet I've never had that issue. So it's not just MySQL version, it's the version combined with something else.

Can you try the version with php 5.4? Should also feature another version of MySQL.

[Atrus]

But Wait! It gets better!  I uninstalled xampp  1.8.3-2, and blew away SMF.

Downloaded & installed xampp 1.8.3-1. Also upgraded phpMyAdmin to the latest version 4.1.6. After getting everything configured and running, I created my sql database for SMF.

Did a clean install of SMF 2.0.7.  Just for fun, I duplicated the layout of my online forums manually. I created the same four categories, and they appeared on the home page BACKWARDS!!!  The category on the bottom in the "Boards" configuration appeared on the top, and everything was in the exact opposite order.

Then I imported the backup of my online database, and the original "preset" order of the categories returned, and no amount of changing them in "boards" config changes the order on the home page. There are four categories. The board at the TOP in the config section appears at the top of the home page, then the third, fourth then the second.  It will not change.

I just can't get that second category nudged back where it belongs. Something in that backup is not compatible with my local setup, and I was wondering what determines the order in which the categories are displayed. I am less than a novice with php, but I'll see if I can try to find the code that actually lists those categories , as in:
   // Show the navigation tree.
   theme_linktree();

Life's an adventure...

Thanks for your help...

[LiroyvH]

I assume you're using InnoDB for the database?

[青山 素子]

The category list sorting relies on a quirk in how MySQL returns rows in a MyISAM table. If you have created boards and categories and then moved them around, it's likely this is the issue. The import of the rows was likely not in the display order. Same issue if the tables are all InnoDB, but I don't think the quirk works there, so I'm not sure how SMF handles that.

The only real way to fix it is to just re-order the items in your test database.

I think this quirk might have been removed in MySQL 5.6, so you could also try downgrading to 5.5 or an earlier point release.

[Atrus]

The only real way to fix it is to just re-order the items in your test database.

You just hit the original problem square on the head!  I am not able to re-order them on my test server. When I go to Admin->boards the order is correct there. No matter how I rearrange them there, they do not change order on the home page.

I assume you're using InnoDB for the database?

I have to admit I don't know what that is. I used phpMyAdmin from the xampp MySQL Admin to create the database, and SMF was completely happy with it. Every other function of the forum works as advertised, except the order of the displayed categories on the home page.



--------------------------------------------

Update:  I just opened phpMyAdmin and looked in the categories table.

===Database cc2400smf1

== Table structure for table smf_categories

|------
|Column|Type|Null|Default
|------
|//**id_cat**//|tinyint(4)|No|
|cat_order|tinyint(4)|No|0
|name|varchar(255)|No|
|can_collapse|tinyint(1)|No|1
== Dumping data for table smf_categories

|------
|       id_cat| cat_order| name                      | can_collapse
|              1|            3 |The Welcome Center |                 1
|              3|            1 |The Playground         |                 1
|              4|            0 |The Back Room         |                 1
|              5|            2 |Discussion Hall          |                 1



The order in which they are listed in "Admin->boards" and the way I want them to appear on the home page is:

  1 - The Welcome Center
  2 - Discussion Hall
  3 - The Playground
  4 - The Back Room


Should I try manually changing the cat_order with phpMyAdmin?

[青山 素子]

What is the "type" for that table in PMA?

If it's MyISAM, try running these queries:

Code Select Expand

ALTER TABLE smf_categories ORDER BY cat_order;
ALTER TABLE smf_boards ORDER BY board_order;

That's what SMF does to fix the category and board display order. It relies on the fact that MyISAM tables can be stored in a specific row order and when you run a select, it returns the stored order. If those queries don't seem to fix it, it's likely due to that no longer being the case in the newer versions of MySQL.

[Atrus]

If it's MyISAM, try running these queries:

Code Select Expand

ALTER TABLE smf_categories ORDER BY cat_order;
ALTER TABLE smf_boards ORDER BY board_order;

That fixed it!!!  So, it appears that both categories and boards need to be sorted...

Last night I tried manually changing id_cat and cat_order in the categories table from phpMyAdmin, and it did not change the order of the displayed categories on the main page. After changing the numbers back to where they were, I deleted every category, moving all boards into the first category. When I deleted all categories except the first one, which now had all boards in it, I started manually adding the deleted categories back, and moving the boards from the first category back into their respective categories. After all was done, on the "Admin->boards" page, the order on the home page did change- just a little. Now the first and last place categories were in their correct place, but categories 2 and 3 were reversed, and could not be changed.

This morning I read your reply and opened phpMyAdmin. I selected the database and manually ran the two queries. They completed successfully, and the order of the categories is now correct on the home page!  Thank you!!!

I am still puzzled as to what caused this problem in the first place. Now I'm afraid to even try changing the order of the categories from "Admin->boards," and wondering if the next import from my online backup of the database will bring back the same problem.  If it does, thanks to you, I will be able to fix it now.

Thank you for the help!

[青山 素子]

I am still puzzled as to what caused this problem in the first place. Now I'm afraid to even try changing the order of the categories from "Admin->boards,"

Do actually try it. The act of changing the order should run those queries on the database. The only reason they might fail is that the database user you have for SMF has not been granted the alter permission on the database.

and wondering if the next import from my online backup of the database will bring back the same problem.

It may. Running those queries should solve the issue.

[Atrus]

I am still puzzled as to what caused this problem in the first place. Now I'm afraid to even try changing the order of the categories from "Admin->boards,"

Do actually try it. The act of changing the order should run those queries on the database. The only reason they might fail is that the database user you have for SMF has not been granted the alter permission on the database.

I tried it. Changes made in "Admin->boards" are not changing anything on the home page. I had to run the two queries again to change the order on the home page. I checked the permissions for my SQL database user, and it has "ALL" privileges.

and wondering if the next import from my online backup of the database will bring back the same problem.

It may. Running those queries should solve the issue.

It did. I backed up the online database and imported it into my xampp database, and the original, out of sequence returned. As expected, changes to Admin->boards made no difference on the home page. Again, I ran the two queries, and everything is fine.

For some reason, those two particular queries are not being passed on to the database. Do you know where in which file I can check to make sure the code is intact? I doubt the code is corrupt, because I did a clean install of 2.0.7 last night.

It seems that if I ever make changes to "boards," or import a newer backup, I'll have to run those queries.

[青山 素子]

What grants do you have for the database user SMF is configured to use? In PMA you can go to the database and then the "privileges" tab. Directly, you can use the SHOW GRANTS statement.

[Atrus]

What grants do you have for the database user SMF is configured to use? In PMA you can go to the database and then the "privileges" tab. Directly, you can use the SHOW GRANTS statement.

---------------------------------------------

Grants for cc2400smf1@localhost

GRANT ALL PRIVILEGES ON *.* TO 'cc2400smf1'@'localhost' IDENTIFIED BY PASSWORD '*4A9C602C7A1627C49C9C5155C5FAAED2E119100B' WITH GRANT OPTION

GRANT ALL PRIVILEGES ON `cc2400smf1`.* TO 'cc2400smf1'@'localhost' WITH GRANT OPTION

----------------------------------------------

In addition,
 
Database-specific privileges:

Database       Privileges            Grant  Table-specific privileges
cc2400smf1   ALL PRIVILEGES  Yes      No

---------------------------------------------



  Shouldn't that mean I have it all privileges?

[青山 素子]

Yeah, it should work for alter then. Weird.

The function is called reorderBoards and is in Subs-Boards.php.

You might want to look at MySQL's Query Log to see if the order queries are being run.

[Illori]

there is a bug noted to be fixed for 2.1 related to this. the board index has to be recoded to work correctly with mysql 5.6.

[Atrus]

Yeah, it should work for alter then. Weird.

The function is called reorderBoards and is in Subs-Boards.php.

You might want to look at MySQL's Query Log to see if the order queries are being run.

Whew! That was a tough one to get the general_log enabled. No matter what I did, I could not get it working. But with much digging and reading, I finally succeeded!

Yes, it looks like the queries ARE being sent:

Code Select Expand


140208 11:01:34    14 Connect cc2400smf1@localhost on
   14 Init DB cc2400smf1
   14 Query SET NAMES utf8
   14 Query SELECT variable, value
FROM smf_settings
   14 Query SELECT data
FROM smf_sessions
WHERE session_id = '8ipmog4cbfacubc3vk4g3fkaa6'
LIMIT 1
   14 Query SELECT mem.*, IFNULL(a.id_attach, 0) AS id_attach, a.filename, a.attachment_type
FROM smf_members AS mem
LEFT JOIN smf_attachments AS a ON (a.id_member = 2)
WHERE mem.id_member = 2
LIMIT 1
   14 Query SELECT variable, value, id_member, id_theme
FROM smf_themes
WHERE id_member = 2
AND id_theme IN (9, 1)
   14 Query SELECT id_cat, cat_order
FROM smf_categories
ORDER BY cat_order
   14 Query UPDATE smf_categories
SET cat_order = 1
WHERE id_cat = 1
   14 Query UPDATE smf_categories
SET cat_order = 2
WHERE id_cat = 4
   14 Query UPDATE smf_categories
SET cat_order = 3
WHERE id_cat = 3
   14 Query SELECT
IFNULL(b.id_board, 0) AS id_board, b.id_parent, b.name AS board_name, b.description, b.child_level,
b.board_order, b.count_posts, b.member_groups, b.id_theme, b.override_theme, b.id_profile, b.redirect,
b.num_posts, b.num_topics, c.id_cat, c.name AS cat_name, c.cat_order, c.can_collapse
FROM smf_categories AS c
LEFT JOIN smf_boards AS b ON (b.id_cat = c.id_cat)
ORDER BY c.cat_order, b.child_level, b.board_order
   14 Query UPDATE smf_boards
SET board_order = 11
WHERE id_board = 1
   14 Query UPDATE smf_boards
SET board_order = 12
WHERE id_board = 11
   14 Query UPDATE smf_boards
SET board_order = 13
WHERE id_board = 2
   14 Query UPDATE smf_boards
SET board_order = 14
WHERE id_board = 19
   14 Query UPDATE smf_boards
SET board_order = 15
WHERE id_board = 8
   14 Query UPDATE smf_boards
SET board_order = 16
WHERE id_board = 12
   14 Query UPDATE smf_boards
SET board_order = 17
WHERE id_board = 13
   14 Query ALTER TABLE smf_boards
ORDER BY board_order
   14 Query UPDATE smf_categories
SET
name = 'The Playground',
can_collapse = 1
WHERE id_cat = 3
   14 Query UPDATE smf_sessions
SET data = 'session_value|s:32:\"8f90121bbe176467e28882255d77a7d1\";session_var|s:7:\"f0763a4\";id_msg_last_visit|s:2:\"38\";mc|a:7:{s:4:\"time\";i:1391873413;s:2:\"id\";s:1:\"2\";s:2:\"gq\";s:3:\"1=1\";s:2:\"bq\";s:3:\"1=1\";s:2:\"ap\";a:1:{i:0;i:0;}s:2:\"mb\";a:0:{}s:2:\"mq\";s:3:\"0=1\";}rc|a:3:{s:2:\"id\";s:1:\"2\";s:4:\"time\";i:1391864182;s:7:\"reports\";s:1:\"0\";}log_time|i:1391875288;timeOnlineUpdated|i:1391875288;unread_messages|i:0;old_url|s:71:\"http://cc.local/forums/index.php?action=admin;area=manageboards;sa=cat2\";USER_AGENT|s:68:\"Mozilla/5.0 (Windows NT 6.1; WOW64; Trident/7.0; rv:11.0) like Gecko\";last_read_topic|i:35;forms|a:1:{i:0;i:539258;}temp_attachments|a:0:{}admin_time|i:1391872178;', last_update = 1391875294
WHERE session_id = '8ipmog4cbfacubc3vk4g3fkaa6'
   14 Quit
   15 Connect cc2400smf1@localhost on
   15 Init DB cc2400smf1
   15 Query SET NAMES utf8
   15 Query SELECT data
FROM smf_sessions
WHERE session_id = '8ipmog4cbfacubc3vk4g3fkaa6'
LIMIT 1
   15 Query SELECT mem.*, IFNULL(a.id_attach, 0) AS id_attach, a.filename, a.attachment_type
FROM smf_members AS mem
LEFT JOIN smf_attachments AS a ON (a.id_member = 2)
WHERE mem.id_member = 2
LIMIT 1
   15 Query SELECT variable, value, id_member, id_theme
FROM smf_themes
WHERE id_member = 2
AND id_theme IN (9, 1)
   15 Query SELECT
IFNULL(b.id_board, 0) AS id_board, b.id_parent, b.name AS board_name, b.description, b.child_level,
b.board_order, b.count_posts, b.member_groups, b.id_theme, b.override_theme, b.id_profile, b.redirect,
b.num_posts, b.num_topics, c.id_cat, c.name AS cat_name, c.cat_order, c.can_collapse
FROM smf_categories AS c
LEFT JOIN smf_boards AS b ON (b.id_cat = c.id_cat)
ORDER BY c.cat_order, b.child_level, b.board_order
   15 Query UPDATE smf_sessions
SET data = 'session_value|s:32:\"8f90121bbe176467e28882255d77a7d1\";session_var|s:7:\"f0763a4\";id_msg_last_visit|s:2:\"38\";mc|a:7:{s:4:\"time\";i:1391873413;s:2:\"id\";s:1:\"2\";s:2:\"gq\";s:3:\"1=1\";s:2:\"bq\";s:3:\"1=1\";s:2:\"ap\";a:1:{i:0;i:0;}s:2:\"mb\";a:0:{}s:2:\"mq\";s:3:\"0=1\";}rc|a:3:{s:2:\"id\";s:1:\"2\";s:4:\"time\";i:1391864182;s:7:\"reports\";s:1:\"0\";}log_time|i:1391875288;timeOnlineUpdated|i:1391875288;unread_messages|i:0;old_url|s:63:\"http://cc.local/forums/index.php?action=admin;area=manageboards\";USER_AGENT|s:68:\"Mozilla/5.0 (Windows NT 6.1; WOW64; Trident/7.0; rv:11.0) like Gecko\";last_read_topic|i:35;forms|a:1:{i:0;i:539258;}temp_attachments|a:0:{}admin_time|i:1391872178;', last_update = 1391875294
WHERE session_id = '8ipmog4cbfacubc3vk4g3fkaa6'
   15 Quit





...and the order changes correctly in the Admin->boards display, but not from "Home." 

The only way I can get it to change on the "Home" display is to open phpMyAdmin and  run the two queries you gave me.

Thanks for nudging me to the query log. Like I said, I'm less than a novice, and didn't even know it existed. It was a good learning experience just to figure out how to enable the log and then try to understand it.

[Jafo232]

I have run those queries, they ran successfully, no change.  SMF 2.0.7, MySQL 5.6.  Sigh..  What files are these queries taking place in?

[Jafo232]

Okay, I wrote up a quick work around for this for anyone else who has to deal with it.  So far, it seems to work right.  In Sources/Subs-BoardIndex.php find this line:

Code Select Expand

// Find all boards and categories, as well as related information.  This will be sorted by the natural order of boards and categories, which we control.

BEFORE that line, add this code:

Code Select Expand

$extra = '';

if ($boardIndexOptions['include_categories'])
$extra =  ' ORDER BY c.cat_order';

Find this line:

Code Select Expand

AND b.child_level BETWEEN ' . $boardIndexOptions['base_level'] . ' AND ' . ($boardIndexOptions['base_level'] + 1)),

CHANGE to:

Code Select Expand

AND b.child_level BETWEEN ' . $boardIndexOptions['base_level'] . ' AND ' . ($boardIndexOptions['base_level'] + 1)) . $extra,

I guess I am surprised this ORDER BY clause was never considered.  It is one of the core benefits of having your data in a database: retrieving it in the order you want.

Anyway, good luck..

[abgogal]

Okay, I wrote up a quick work around for this for anyone else who has to deal with it. 

Holly crap, Jafo, you're a genius!

That sorted out my problem - to a certain extent.

I've got my Categories all sorted out and in the right order now.

Only the Boards within the Categories are still scrambled - not in the same order on my home page as they are showing in Admin.

Do you have a line of code to add somewhere that will straighten out my Boards?

Thanks!