Database error 1140 in Dashboard and 1055 in Statistics

ℹ️ Support
1

Hi, I’m new to PHPList. I have just done the installation and initial configuration of v3.5.5 on a system with Ubuntu 18.04.5 LTS, MariaDB (mysql Ver 15.1 Distrib 10.3.24-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2) and PHP 7.3 (PHP7.3.21-1+ubuntu18.04.1+deb.sury.org+1).

There were no errors during the setup of PHPList, but now when i go to the Dasboard I get the following message:
Database error 1140 while doing query Mixing of GROUP columns (MIN(),MAX(),COUNT(),…) with no GROUP columns is illegal if there is no GROUP BY clause

When I go to Campaigns/List of campaigns I get the same message:
Database error 1140 while doing query Mixing of GROUP columns (MIN(),MAX(),COUNT(),…) with no GROUP columns is illegal if there is no GROUP BY clause
and a draft campaign I created does not show up in the Draft tab (but it shows up in the Dashboard)

When I go to Statistics/Statistics overview I get the message:
Database error 1055 while doing query ‘phplist.msg.owner’ isn’t in GROUP BY

When I go to Statistics/View opens I get the message:
Database error 1055 while doing query ‘phplist.msg.subject’ isn’t in GROUP BY

When I go to Statistics/Campaign click statistics I get the message:
Database error 1055 while doing query ‘phplist.message.subject’ isn’t in GROUP BY

When I go to Statistics/URL click statistics I get the message:
Database error 1055 while doing query ‘phplist.forward.url’ isn’t in GROUP BY

When I go to Statistics/ msgbounces I get the message:
Database error 1055 while doing query ‘phplist.msg.subject’ isn’t in GROUP BY

I’m not sure why these database errors. Can anybody help me figure them out?
Thank you.

2

@gpagn001 probably caused by the mysql strict mode but I thought that those had been caught a while ago, https://mantis.phplist.org/view.php?id=18341

If you run these in an sql window to see what the mode is

SELECT @@GLOBAL.sql_mode 
SELECT @@SESSION.sql_mode;
3

The output for both is the same

±--------------------------------------------------------------+
| @@GLOBAL.sql_mode |
±--------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
±--------------------------------------------------------------+

±--------------------------------------------------------------+
| @@SESSION.sql_mode |
±--------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
±--------------------------------------------------------------+

4

This is the mode that is causing some of the problems. Can you change the database default? Otherwise would need to modify the phplist code.

5

I can change the database, as long as the changes do not conflict with other programs, mainly Moodle, that wants the following:
[client]
default-character-set = utf8mb4

[mysqld]
innodb_file_format = Barracuda
innodb_file_per_table = 1
innodb_large_prefix = 1

character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
skip-character-set-client-handshake

[mysql]
default-character-set = utf8mb4

How should I change it?

6

Sorry for the previous reply, for some reason your reply did not display before I sent mine.
Anyway, removing ONLY_FULL_GROUP_BY from sql_mode= in /etc/mysql/my.cnf and restarting the mysql solved the issue.
Thank you very much for your help!

7

@gpagn001 ONLY_FULL_GROUP_BY differs in MariaDB from Mysql. The errors do not occur with Mysql. https://jira.mariadb.org/browse/MDEV-11588

8

Yes, but as stated in the same https://jira.mariadb.org/browse/MDEV-11588

“ONLY_FULL_GROUP_BY is now a default both in Maria and MySQL”

Perhaps this should be indicated in the installation instructions, or addressed in the code of future releases.

9

I am facing the same issue. Can not change the database default.
Which files need to be modified to make it work?

10

@juan22 Look in file admin/mysqli.inc for this line

mysqli_query($db, "SET NAMES 'utf8'");

Try adding this line immediately after to change the sql mode by removing the full group by

mysqli_query($db, "SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));");

I don’t know whether it will have the desired effect though. I use mariadb and cannot find anywhere that the default mode is specified, and phplist works fine.

11

Yesterday I upgraded from 3.3.7 to 3.6.10 and ran into the same problem as topicstarter and adding that line to mysqli.inc solved my problem instantly, so thank you!
I’m on MariaDB.