MySQL Errors

[jakkuh]

Describe the bug Some MySQL errors when running PHP 7.2 and MySQL 5.7

To Reproduce Steps to reproduce the behavior:

1. Go to the stats page for a user and look at the error :P

Error Diagnostic:
Bad query.

Server Error: (1055) Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'hlstats.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=1
                                GROUP BY
                                    hlstats_Events_Frags.weapon
                                ORDER BY
                                    kills desc, headshots desc
                                LIMIT
                                    1

Expected behavior There not to be a big ole errror

Screenshots https://i.imgur.com/pEKTLy9.png

Environment

• Webserver: Apache w/ NGINX reverse proxy
• Hosting environment: dedicated web box
• PHP version: 7.2
• Browser: latest chrome

Additional context Add any other context about the problem here.

[NomisCZ]

Hello, starting from MySQL 5.7, the default value is more restrictive and this the reason why some users have problems with unexpected query failures after migration to 5.7 or 8.0.

Solution:

1. Change SQL mode globally in /etc/mysql/my.cnf

   [mysqld]
   sql_mode=NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

2. Restart MySQL server - systemctl restart mysql OR service mysql restart