Python MYSQL GROUP BY issue

Question

I have 2 Mysql databases:

• #1 is created on Raspbian Raspberry pi 4
• #2 is created on virtual Ubuntu server.

I have been interacting with Mysql DB that is created by Raspberry pi 4 without any issues but now I want to migrate all the database to the virtual Ubuntu server to make my system more flexible.

After installing all the required modules on my ubuntu server, I have launched my python code and notice that it is not working properly. I get some mysql error regarding the GROUP BY statement:

mysql.connector.errors.ProgrammingError: 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.Operation_1109360548.ID' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

The query that I am trying to execute:

sql = "SELECT Serial,ID,count(*) FROM {table} GROUP BY Serial"
cur.execute(sql.format(table=table_name))

And my database table looks like:

QUESTIONS

1. Could someone suggest why it would allow me to execute this query on mysql DB running on Raspbian but doesn't allow me on UBUNTU mysql?

2. How to solve this issue? I do not see any problems with this query. I have heard that you can modify mysql.cnf but that is not the best solution

Answer 1

SERIAL is a DATATYPE in MySQL. So you must put it in backquotes to use as fieldname:

sql = "SELECT `Serial`,ID,count(*) 
        FROM {table} 
        GROUP BY `Serial`"
cur.execute(sql.format(table=table_name))

see : https://mariadb.com/docs/reference/mdb/data-types/SERIAL/

Answer 2

It depends on what you are trying to achieve, if for example you have a database: S-Serial, I-ID

S|I
1|2
1|3
2|4
3|5
3|4

You aggregate serial to values 1,2,3, but what to do with ID? should S=1 have ID=2 or ID=3? You could tell your query what to do with ID, what type of aggregation (e.g. MAX, MIN, AVG)

You can let the database handle it, but to do so you need to disable ONLY_FULL_GROUP_BY clause, and that's the consequence: The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate. And to do this I'll just give you a link: Disable ONLY_FULL_GROUP_BY

Example:

SELECT Serial, MIN(ID), COUNT(*) FROM TABLE_NAME
GROUP BY Serial

On the other hand, if your database looks like that:

S|I
1|2
2|3
3|1
4|4
5|5

So the values of Serial and ID are unique couples, you should group by both of these columns, so your query could look like this:

SELECT Serial, ID, COUNT(*) FROM TABLE_NAME
GROUP BY Serial,ID