DB Error problem

[beegee-tokyo]

I installed statistics as shown in Statistics Plugin.
Database was created and tables created.
When I go to the Access and Usage Statistics of my wiki, then on some statistics I get:

DB Error: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'stats.A.ref' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by SELECT COUNT(*) as cnt, ref as url FROM stats_access as A WHERE A.dt >= '2017-12-25 00:00:00' AND A.dt <= '2018-01-24 23:59:59' AND ua_type = 'browser' AND ref_type = 'external' GROUP BY ref_md5 ORDER BY cnt DESC, url LIMIT 0,151

I get this kind of error on:

• Incoming Links
• New Incoming Links
• Countries
• Screen Size
• Browser Viewport

Other statistics works.

MySQL:

• mysql Ver 14.14 Distrib 5.7.13, for Linux (x86_64)
  OS:
• Ubuntu 16.04.3 LTS (GNU/Linux 4.4.0-31-generic x86_64)
  Webserver:
• Server version: Apache/2.4.18 (Ubuntu)
• Server built: 2017-07-27T14:34:01
  WikiDoc:
• Release 2017-02-19e "Frusterick Manners"

[cgalo5758]

Same issue on a fresh install

DB Error: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'stats.B.ref' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by SELECT COUNT(*) as cnt, ref as url FROM stats_access as B, stats_refseen as A WHERE A.dt >= '2019-12-10 00:00:00' AND A.dt <= '2019-12-11 23:59:59' AND ua_type = 'browser' AND ref_type = 'external' AND A.ref_md5 = B.ref_md5 GROUP BY A.ref_md5 ORDER BY cnt DESC, url LIMIT 0,151

Not sure how to debug this.

• Debian (9)
• Apache (2.4.41)
• MySQL (8.0.18)
• PHP (7.3.11)
• 2018-04-22b "Greebo"

Same statistics work for me and same statistics don't work for me as @beegee-tokyo

[cgalo5758]

Additional Issue

Additionally, we are getting the following error every single time a user makes a search or edits a page:

DB Error: Field 'dt' doesn't have a default value REPLACE INTO stats_lastseen SET `user` = 'user'

Additional data:

• Incoming Links

  DB Error: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'stats.A.ref' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by SELECT COUNT(*) as cnt, ref as url FROM stats_access as A WHERE A.dt >= '2020-01-07 00:00:00' AND A.dt <= '2020-01-07 23:59:59' AND ua_type = 'browser' AND ref_type = 'external' GROUP BY ref_md5 ORDER BY cnt DESC, url LIMIT 0,151

• New Incoming Links

  DB Error: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'stats.B.ref' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by SELECT COUNT(*) as cnt, ref as url FROM stats_access as B, stats_refseen as A WHERE A.dt >= '2020-01-07 00:00:00' AND A.dt <= '2020-01-07 23:59:59' AND ua_type = 'browser' AND ref_type = 'external' AND A.ref_md5 = B.ref_md5 GROUP BY A.ref_md5 ORDER BY cnt DESC, url LIMIT 0,151

• Countries

  DB Error: Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'stats.B.country' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by SELECT COUNT(DISTINCT session) as cnt, B.code AS cflag, B.country FROM stats_access as A, stats_iplocation as B WHERE A.dt >= '2020-01-07 00:00:00' AND A.dt <= '2020-01-07 23:59:59' AND A.ip = B.ip GROUP BY B.code ORDER BY cnt DESC, B.country LIMIT 0,151

• Screen Size

  DB Error: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'stats.A.screen_x' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by SELECT COUNT(DISTINCT uid) as cnt, ROUND(screen_x/100)*100 as res_x, ROUND(screen_y/100)*100 as res_y, CONCAT(ROUND(screen_x/100)*100,'x',ROUND(screen_y/100)*100) as resolution FROM stats_access as A WHERE A.dt >= '2020-01-07 00:00:00' AND A.dt <= '2020-01-07 23:59:59' AND ua_type = 'browser' AND screen_x != 0 AND screen_y != 0 GROUP BY resolution ORDER BY cnt DESC LIMIT 0,151

• Browser Viewport

  DB Error: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'stats.A.view_x' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by SELECT COUNT(DISTINCT uid) as cnt, ROUND(view_x/100)*100 as res_x, ROUND(view_y/100)*100 as res_y, CONCAT(ROUND(view_x/100)*100,'x',ROUND(view_y/100)*100) as resolution FROM stats_access as A WHERE A.dt >= '2020-01-07 00:00:00' AND A.dt <= '2020-01-07 23:59:59' AND ua_type = 'browser' AND view_x != 0 AND view_y != 0 GROUP BY resolution ORDER BY cnt DESC LIMIT 0,151

As you may notice, the non-aggregated columns are all different. Maybe there are issues with those columns specifically? There might be other changes that I am not catching.

Additionally, the error on the dashboard might have changed for me: (not sure if the text I posted on my first post in this thread was from the dashboard) This is what I get now (if my original post was indeed an error message from the dashboard:)

 DB Error: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'stats.B.ref' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by SELECT COUNT(*) as cnt, ref as url FROM stats_access as B, stats_refseen as A WHERE A.dt >= '2020-01-07 00:00:00' AND A.dt <= '2020-01-07 23:59:59' AND ua_type = 'browser' AND ref_type = 'external' AND A.ref_md5 = B.ref_md5 GROUP BY A.ref_md5 ORDER BY cnt DESC, url LIMIT 0,16

The number at the end (after url LIMIT) has changed for some reason.

MySQL version info

The statistics plugin worked before migrating servers, I think the most relevant change here is that we moved from MySQL (5.6.41-84.1) to MySQL (8.0.18)

[cgalo5758]

Adding the following to my.cnf (MySQL's configuration file) makes everything work... for some reason, at least on my install. I am not sure why.

[mysqld]
sql_mode='NO_ENGINE_SUBSTITUTION'

Additionally, I also ran

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

I restarted MySQL like 10 times and I am not sure if that command only applied to the first session I tried it in.