Menu Index Not Sorting Properly With MySQL 4.1.21-standard

137 Posts

AntonL Reply #11, 20 years, 2 months ago

Quote from: PaulGregory at Aug 22, 2006, 08:29 AM

Can you just confirm that you have configured a similar MODx site on the same server which does work with Ajax Menu Index and sorts correctly?

There are a few very similar MODx sites on the same server working fine as far as menuindex/wayfinder/doctree sorting is concerned. However, this was the first test of the Ajax Menu Index. I have since deleted the module from this site, but the sort problem remains!

Anton

1,097 Posts

PaulGregory Reply #12, 20 years, 2 months ago

Have you any idea whether the sort problem was there before you installed Ajax Menu Index?

What happens if you load a document and edit the Menu Index in the traditional way and save it - does this have any effect?

No, I don't know what OpenGeek's saying half the time either.
MODx Documentation: The Wiki | My Wiki contributions | Main MODx Documentation
Forum: Where to post threads about add-ons | Forum Rules
Like MODx? donate (and/or share your resources)
Like me? See my Amazon wishlist
MODx "Most Promising CMS" - so appropriate!

137 Posts

AntonL Reply #13, 20 years, 2 months ago

Quote from: PaulGregory at Aug 22, 2006, 08:38 AM

Have you any idea whether the sort problem was there before you installed Ajax Menu Index?

I don’t remember it being there but I suppose it is possible, although somewhat unlikely, that it arose prior to installing AMI and that I didn’t notice it until I returned to working on the site today.

What happens if you load a document and edit the Menu Index in the traditional way and save it - does this have any effect?

No effect anymore! The docs/folders only seem to remain sorted in doc ID ASC order.

137 Posts

AntonL Reply #14, 20 years, 2 months ago

I’ve now got the same problem happening with another MODx site and this one never had AMI installed. (I have checked in a 3rd site and things still seem OK.) Not sure what I did to trigger it this time. I did delete a few docs and move one doc from inside a folder to the root level. The site in question had just been upgraded from Etomite to MODx. It was fine for a few mins while I started fiddling (post-upgrade) and then the doc tree ’locked up’ and will only sort by doc ID*, just like the other one.

[*Edit: Sorry scratch that - it’s not even sorting by doc ID. I can’t quite work out what it’s sorting by. Perhaps it’s random?! Maybe last-edited... Hard to say.]

Going to call it a night for now. Any suggestions would be gratefully received.

Anton

☆ A M B ☆
24,524 Posts

sottwell Reply #15, 20 years, 2 months ago

Try clearing the cookies from your site out of your browser.

Studying MODX in the desert - http://sottwell.com
Tips and Tricks from the MODX Forums and Slack Channels - http://modxcookbook.com
Join the Slack Community - http://modx.org

137 Posts

AntonL Reply #16, 20 years, 2 months ago

Yes, have tried clearing cookies and cache. Problem from last night is still there now, today, after full reboot and router re-start.

Anton

137 Posts

AntonL Reply #17, 20 years, 2 months ago

A breakthrough of sorts (no pun intended): I just tried running the SQL query (that fetches the doc tree contents) directly in phpMyAdmin and the same result occurred - i.e., the resultset wasn’t sorted. I then tried running the same query without the DISTINCT keyword and it sorted correctly!

The strange thing is that this behaviour seems to have arisen spontaneously. The host I am with has a pretty solid reputation and they haven’t advised of any changes to MySQL. The version is currently "4.1.21-standard" according to cPanel.

Does this scenario sound familiar to anyone? Should I simply delete the DISTINCT keywords from this query here (3ldr.php) and in the wayfinder snippet?

Anton

137 Posts

AntonL Reply #18, 20 years, 2 months ago

OK, more info - this is a known bug in this version of MySQL:

http://bugs.mysql.com/bug.php?id=21456

I have emailed the host. Hopefully they will help by patching it.

Anton

137 Posts

AntonL Reply #19, 20 years, 2 months ago

The host said they are waiting on cPanel’s new release which will include a MySQL upgrade.

What are the likely consequences of removing the DISTINCT keyword from these queries while I am waiting for this to happen?

Any other suggestions?

Thanks,
Anton

137 Posts

AntonL Reply #20, 20 years, 2 months ago

I’ve found a patch that seems to work for MODx in the meantime.

Lines 92-98 in 3ldr.php:

		$sql = "SELECT DISTINCT sc.id, pagetitle, parent, isfolder, published, deleted, type, menuindex, hidemenu, alias, contentType, privateweb, privatemgr 
				FROM $tblsc AS sc 
				LEFT JOIN $tbldg dg on dg.document = sc.id
				WHERE (parent=$parent) 
				AND ($access)
				GROUP BY sc.id
				ORDER BY $orderby";

I just added a GROUP BY clause in. This seems to be working, touch plastic. The query produces unique rows in the correct order, including for the case where a doc is assigned to multiple doc groups. If anyone can spot any potential hiccups with this approach, then please let me know.

Thanks,
Anton