[Resolved] Debug error 1111 - Invalid use of group function

Full Board URL: http://forums.bajanomad.com
XMB Version: 1.9.11

With Debug flag on in config.php, I'm getting this specific error when attempting to view member profile page:

Code:

MySQL encountered the following error: Invalid use of group function(errno = 1111) In the following query: SELECT fid, COUNT(*) AS posts FROM xmb19_posts WHERE author='John' AND fid IN (47, 41, 17, 2, 26, 21, 12, 1, 28, 38, 35, 46, 51, 50, 40, 4, 43, 24, 45, 8, 48, 32, 39, 16, 33, 18, 14, 3, 7, 36, 49, 27, 34, 25, 6, 23, 15, 22, 13, 19, 53, 20, 56, 9, 55, 5, 54) GROUP BY fid HAVING COUNT(*) > 0 ORDER BY COUNT(*) DESC LIMIT 1

Will attach current member.php file.

Just upgraded to 1.9.11 from 1.9.1.



Thank you for any assistance.

Attachment: member.php (38kB)
This file has been downloaded 681 times

PHP: 5.0.4
MySQL: 4.1.20

Your attached member.php "change" worked fine.

The site's worked fine on the same dedicated system since 2006, so hadn't updated much.

Tried to move the site to a newer server recently, and the site didn't work with the latest PHP version that was setup - thus a reason for the latest XMB upgrade... getting prepared to move to another system a.s.a.p. I presume I need to revert back to the original member.php when that happens.

Noticing that some of the older usernames that have carried through since I first put this site up in 2002 (using XMB 1.6) have profiles that can't be viewed. The ones I've identified thus far are those with one-character or two-character usernames (these were setup years ago). The usernames that have spaces in them (also from long-ago) are working fine though. Any suggestions?

Thank you.

I'm also noticing that the theme's "Header Text Color" isn't being used in the new(er) "rquote" link in the quoted text box. Can you help point out where I can manually update that?

Thank you again.

Quote: Originally posted by Q87

PHP: 5.0.4 MySQL: 4.1.20 Your attached member.php "change" worked fine. The site's worked fine on the same dedicated system since 2006, so hadn't updated much. Tried to move the site to a newer server recently, and the site didn't work with the latest PHP version that was setup - thus a reason for the latest XMB upgrade... getting prepared to move to another system a.s.a.p. I presume I need to revert back to the original member.php when that happens. Noticing that some of the older usernames that have carried through since I first put this site up in 2002 (using XMB 1.6) have profiles that can't be viewed. The ones I've identified thus far are those with one-character or two-character usernames (these were setup years ago). The usernames that have spaces in them (also from long-ago) are working fine though. Any suggestions? Thank you.

In regard to the mysql error in member.php, yes, it's due to the old mysql version you run, so after upgrading mysql, you can revert it back to the original 1.9.11 member.php, but take into consideration the proposed change below for your issue when viewing member names with 1 or 2 characters.

Not sure what the reasoning was, but the allowed member user name was changed to allow a minimum of 3, so any new signups won't allow 1 or 2 character user names. In addition, it was changed so you could only view user names with a minimum 3 characters (which as you discovered, affects sites that already have 2 character user names)!

Anyhow... in member.php look for this:

case 'viewpro':
$member = postedVar('member', '', TRUE, FALSE, FALSE, 'g');
if (strlen($member) < 3 || strlen($member) > 32) {
header('HTTP/1.0 404 Not Found');
error($lang['nomember']);
}

and change the < 3 to < 2, unless you have any 1 character user names, in which case chance the < 3 to < 1

and if you have any existing member user names with more than 32 characters, you can alter the 32 upwards.

Quote: Originally posted by Q87

PHP: 5.0.4 MySQL: 4.1.20 Your attached member.php "change" worked fine.

We still officially support MySQL 4.1.20, though I don't have one that old to test anymore.

Did you have more information about that GROUP error?

Quote: Originally posted by miqrogroove

Quote: Originally posted by Q87   PHP: 5.0.4 MySQL: 4.1.20 Your attached member.php "change" worked fine. We still officially support MySQL 4.1.20, though I don't have one that old to test anymore. Did you have more information about that GROUP error?

Thank you for the response.

As three years have passed, I've already migrated servers twice during that time... the last time from a bad drive and choosing to restore to a new system (which upgraded the PHP/MySQL again - to further "problems"). I'm just limping along right now and not addressing the current "minor" issues.

Currently running:
XMB 1.9.11.13
PHP 5.6.32
MySQL 5.0.11

I have had a fairly heavy traffic site over time, now down to about 15,000 unique visitors/month. 9500 registered users right now. Been running this XMB site since 2002.

Unless there's some new magic and life to XMB (and perhaps old features returned and new vision for the future), I will be looking to migrate platforms sometime later in 2018.

Thank you again for the reply.

Quote: Originally posted by miqrogroove

Which old features are missing?

Primarily that the previous developer removed the ability for admin to review user u2u. I have abusive u2u reported to me off and on over time - and now have to go look in the database which takes considerably more time.

As far as future vision, is there any hope of being more "mobile friendly" and/or being modular with Wordpress or MediaWiki?

Seems reasonable if restricted to Super Admins. But this was what, 20 years ago? I might dig that up just for curiosity.

Future vision isn't exactly my department. Open source projects have massive time and money prerequisites.

Quote: Originally posted by miqrogroove

Seems reasonable if restricted to Super Admins. But this was what, 20 years ago? I might dig that up just for curiosity.

No, the feature was removed by the immediately past developer - perhaps removed in the versions released in the past few years.

The background:

[Fixed] u2uadmin.php - against EU privacy laws  

Privacy Act in Australia  

Wow. This little nugget from the original file...

Code:

eval("\$$u2us .= \"".template('u2u_row_admin')."\";");

I had no idea that was even valid syntax. That's a template assignment via double-quoted string-literal escaped variable variable.

It's sneaky though. PHP doesn't actually escape variable variables in this manner. "\$$u2us" is semantically different from "\$\$u2us"