search

sn3p / utstats

Stats for Unreal Tournament 99
Other
13 stars 10 forks source link

Issues with new mysql 5.7 defaults #22

Open randommonk opened 5 years ago

randommonk commented 5 years ago

The new default sql_mode in 5.7 onwards has 2 modes that cause issues for UTStats: ONLY_FULL_GROUP_BY and STRICT_TRANS_TABLES.

https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html

ONLY_FULL_GROUP_BY causes issues due to a slightly incorrect query in player_search.php:

Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'pgaming_1.pi.country' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

STRICT_TRANS_TABLES causes issues with the graph imports:

Generating ctf graphs: Field 'title' doesn't have a default value

dizzy-egg commented 5 years ago

Can't you use SET sql_mode = ''?

JorisBroll commented 5 years ago

So I haven't tried any code on this but looked it up manually.

It looks like the player search groups all players with the same name into one result. That means if one player called 'Player' from France (what a coincidence) and a player called 'Player' from the Uk play on the server, when searching 'Player', the database will fuse their results and won't know what country to put in the grouped "country" column (France or Uk ?).

The workaround is to put ANY_VALUE() around "pi.country" (and possibly other fields) to ignore the conflict and use the first country that comes up.

JorisBroll commented 5 years ago

About the graphs it looks like a simple database fix but I could use the exact file location where the problem happens to look further :)