News:

Please do not feed the trolls.

Main Menu

Stats page sql error

Started by Nuggs, February 21, 2019, 04:38:04 PM

Previous topic - Next topic

0 Members and 1 Guest are viewing this topic.

Nuggs

You're probably aware, but when I goto the stats page I'm greeted with the below error after clicking on a profile:


ERROR
Database Error

Server Address: localhost
Server Username: esclanweb

Error Diagnostic:
Bad query.

Server Error: (1055) Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'esclanweb_stats.hlstats_Weapons.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Last SQL Query:
                        SELECT
                           hlstats_Events_Frags.weapon,
                           hlstats_Weapons.name,
                           COUNT(hlstats_Events_Frags.weapon) AS kills,
                           SUM(hlstats_Events_Frags.headshot=1) as headshots
                        FROM
                           hlstats_Events_Frags
                        LEFT JOIN
                           hlstats_Weapons
                        ON
                           hlstats_Weapons.code = hlstats_Events_Frags.weapon
                        WHERE
                           hlstats_Events_Frags.killerId=29805
                        GROUP BY
                           hlstats_Events_Frags.weapon
                        ORDER BY
                           kills desc, headshots desc

Gimpy

It's because your stats are bad

Master

When would you say you first noticed this?

Nuggs

Just noticed it today when I went to plug in a steam Id to find out who was aliased as player

froob



"The first blessing is peace, as is agreed by all men who have even a small share of reason...."

Guts

Quote from: Master on February 21, 2019, 06:16:23 PM
When would you say you first noticed this?

This might not help but I think i notice it about 2 days ago, maybe 3. Then I also didn't help by not making a forum post. I meant to but I forgot. What I'm trying to say is that I'm generally not helpful it seems.

pizzahut

From what I read ( https://privatstrand.dirkschmidtke.de/2016/06/20/mysql-5-7-sql_mode-mit-straffen-zuegeln/ ), two ways to fix this:

1) Add hlstats_Weapons.name to "group by".
2) Change sql_mode to omit "only_full_group_by".

For 2), question is where the SQL mode got set this way. Depending on this, it could changed in /etc/mysql/my.cnf , or it may be necessary to change this setting in hlstats somewhere, e.g. right before the select command.

Also, sql_mode can be set globally and per session.

Google query used: https://www.google.com/search?q=sql_mode%3Donly_full_group_by

Master

Quote from: pizzahut on February 22, 2019, 11:11:48 AM
From what I read ( https://privatstrand.dirkschmidtke.de/2016/06/20/mysql-5-7-sql_mode-mit-straffen-zuegeln/ ), two ways to fix this:

1) Add hlstats_Weapons.name to "group by".
2) Change sql_mode to omit "only_full_group_by".

For 2), question is where the SQL mode got set this way. Depending on this, it could changed in /etc/mysql/my.cnf , or it may be necessary to change this setting in hlstats somewhere, e.g. right before the select command.

Also, sql_mode can be set globally and per session.

Google query used: https://www.google.com/search?q=sql_mode%3Donly_full_group_by
The alternative is to ignore change and stick with our broken outdated tools, the stats page has been 'fixed' but there still remains one PHP Warning I cant seem to silence.