Dealing with an error I've not seen before...

[Jim R]

Up until a week ago the below worked just fine.  I migrated my site to a new server space, and now the below is throwing an error.  

When I remove the GROUP BY, the error goes away.  

 

I marked the line throwing the error.  

Quote

Fatal error: Uncaught TypeError: mysqli_fetch_assoc(): Argument #1 ($result) must be of type mysqli_result, bool given in /home1/nitoaute/csi/wp-content/plugins/csi_reviews.php:1088 Stack trace: #0 /home1/nitoaute/csi/wp-content/plugins/csi_reviews.php(1088):

$query="SELECT *, e.id as eid,event
	FROM a_events e
	INNER JOIN a_players_reviews pr
		ON pr.eventID = e.id
	WHERE date_format(start, '%y') = '". $current_season ."'
	
	GROUP BY event
	ORDER BY start desc
	";

	echo '<div class="events_header"><h3>Events & Tournaments</h3></div>';
	echo '<div class="events_list">';

	
		$results = mysqli_query($con,$query);
	LINE 1088	while($line = mysqli_fetch_assoc($results)) {	
		

 

Thank you

 

[mac_gyver]

the php error you are getting is a follow-on error, because the query is failing, but there is no error handling for the query. the easiest way of adding error handling for all the mysqli statements that can fail - connection, query, exec, prepare, and execute, is to use exceptions for errors (this is the default setting now in php8+). to enabled exceptions for the mysqli extension, add the following line of code before the point where you make the database connection (or upgrade to php8) -

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

you should then be getting an uncaught exception error with the raw database error information in it about a non-groupby/non-aggerate column being referenced. the correct way of fixing this is to a) only select the columns you are using, and b) every column you are selecting needs to be either in the GROUP BY term or used in an aggerate function. there is a database server mode setting that control if this condition produces a query error (the current setting) or if it is just a warning. you may or may not have access to this database mode setting. 

[Jim R]

I'm getting a lot of this...

Quote

Fatal error: Uncaught mysqli_sql_exception: In aggregated query without GROUP BY, expression #55 of SELECT list contains nonaggregated column 'nitoaute_csi.po.id'; this is incompatible with sql_mode=only_full_group_by in /home1/nitoaute/csi/wp-content/plugins/csi_custom.php:1266 

The GROUP BYs are what is causing this, at least that's what reflected in the errors.  

 

 

The above code, doesn't have a current instance, but it still shouldn't throw an error.  I'm not sure if anything has changed with PHP 8.2 or MySQL 8+ to cause these errors.  I read something about a column in the GROUP BY not specifically called in the SELECT.  I would say that has never given me an error before, even though it makes sense.  

 

[Moorcam]

Try some proper error handling to get more insight:

if ($results === false) {
    // Handle the error
    echo "Error executing query: " . mysqli_error($con);
} else {
    while ($line = mysqli_fetch_assoc($results)) {
        // Process each line of results
        // Example: echo $line['event'];
    }
}

 

[Jim R]

That's the error I'm getting.

Quote

Error executing query: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'nitoaute_csi.e.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

 

[mac_gyver]

in case you didn't do a web search for the database server setting that's specifically listed in the error messages - https://dev.mysql.com/doc/refman/8.4/en/group-by-handling.html

[Jim R]

23 hours ago, mac_gyver said:

in case you didn't do a web search for the database server setting that's specifically listed in the error messages - https://dev.mysql.com/doc/refman/8.4/en/group-by-handling.html

This is something new?  Because the code I published above was written in and has been utilized since 2020.  

Seems to be counterintuitive to limit what can be in a Group By.  I don't recall what my previous MySQL version was before moving it up to 8.*.  

 

 

[gizmola]

Did you recently upgrade mysql version on the server? Did you change servers?  Is this on a host that upgraded MySQL?

It's a central idea of change management, that when you change something, and something breaks, it's almost always traceable back to the thing(s) you changed.

Most likely this is the reason your query no longer works, as the option that would ALLOW it to work, is no longer enabled.

Mac pretty much nailed this in his first reply to you.  It doesn't appear to me that you read his reply carefully, as the next thing you did was reply that the problem was the very thing he'd already alerted you to.

The mysql manual link he provided explains the issue in detail.  It is NOT counter intuitive at all, and here is why.

When you GROUP by, the database must have a result set, which it then condenses during the grouping operation.  Consider this table.

 

id	Type	Name	Category
1	fruit	Apple	Breakfast
2	fruit	Orange	Lunch
3	vegetable	Carrot	Snack

 

So now you write a query to group by Type:

SELECT Type, count(*) as count_of FROM food GROUP BY Type

The SQL 92 Standard considers this fine because you'll get a row for each Type, along with the aggregation of COUNT()

Instead you try to do this:

SELECT TYPE, Name, COUNT(*) as count_of FROM food GROUP BY Type

Looking at the table above, and knowing that the rows have to be reduced to one row for each Type, what Name should MySQL arbitrarily decide to use for the fruit group?  There's Apple and Orange.  Only one row will be returned, so out of all the possible names in this food table that are of Type fruit, there can only be one Name value.  In the context of grouping, it doesn't make sense for it to be included.

 

 

 

 

[Jim R]

5 hours ago, gizmola said:

Did you recently upgrade mysql version on the server? Did you change servers?  Is this on a host that upgraded MySQL?

 

I'm going to dig into the rest of your post later tonight, but to answer this quickly, odds are yes.  I have migrated my sites to a different server space.  I have done this once before without any issues.  I'm pretty sure I went from MySQL 7+ to 8+.  

Looks like I've opened up a learning curve.  

[Jim R]

5 hours ago, gizmola said:

Did you recently upgrade mysql version on the server? Did you change servers?  Is this on a host that upgraded MySQL?

It's a central idea of change management, that when you change something, and something breaks, it's almost always traceable back to the thing(s) you changed.

Most likely this is the reason your query no longer works, as the option that would ALLOW it to work, is no longer enabled.

Mac pretty much nailed this in his first reply to you.  It doesn't appear to me that you read his reply carefully, as the next thing you did was reply that the problem was the very thing he'd already alerted you to.

I read it carefully, I just didn't understand it or why.  When something works as intended from the start and for a prolonged period of time, you can certainly understand the confusion and/or frustration when it doesn't work.  So it might make sense to you, but it broke what was working.  That matters to ME and my Users, but I'm here trying to learn and perhaps understand the more functional way this works.

 

5 hours ago, gizmola said:

When you GROUP by, the database must have a result set, which it then condenses during the grouping operation.  Consider this table.

 

id	Type	Name	Category
1	fruit	Apple	Breakfast
2	fruit	Orange	Lunch
3	vegetable	Carrot	Snack

 

So now you write a query to group by Type:

SELECT Type, count(*) as count_of FROM food GROUP BY Type

The SQL 92 Standard considers this fine because you'll get a row for each Type, along with the aggregation of COUNT()

Instead you try to do this:

SELECT TYPE, Name, COUNT(*) as count_of FROM food GROUP BY Type

Looking at the table above, and knowing that the rows have to be reduced to one row for each Type, what Name should MySQL arbitrarily decide to use for the fruit group?  There's Apple and Orange.  Only one row will be returned, so out of all the possible names in this food table that are of Type fruit, there can only be one Name value.  In the context of grouping, it doesn't make sense for it to be included.

When I have used Group By I didn't care about the other column per se.  Maybe I didn't know that I could more easily differentiate is the true answer.  

Example:  I have 37 instances of one event.  Whether I have 1 or 37, all I cared about was there was an instance of that one event, so that knew to list that event. 

Continued example:  I have future lists lined up for later this year, which of course have zero instances yet.  So my code knows not to print them out yet. 

In other, your example, I would just be printing FRUIT.  The apple and orange of it gets used somewhere else on the site in a different list.  

I wasn't using Group By to its full potential, but it's only in three functions that I use.  I guess I'll learn later if my new query works.  

$query = "SELECT e.event, COUNT(pr.eventID) AS review_count, MAX(e.start) AS latest_start
FROM a_events e
INNER JOIN a_players_reviews pr ON pr.eventID = e.id
WHERE DATE_FORMAT(e.start, '%y') = '". $current_season ."'
GROUP BY e.event
ORDER BY latest_start DESC";

 

 

That said, I also have a few functions that rely on group_concat that appear to have been broken too.  

Edited February 25 by Jim R

[gizmola]

I guess the important thing is that you understand what GROUP BY does.  It reduces the results, so that you get one row PER group.    I hope you understand my intentionally simple but straightforward example and what it means for your queries.

Going back to what mac linked you, the reason your old code doesn't work is because, the MySQL server "default" behavior changed.  The error you provided tells you this:

Quote

this is incompatible with sql_mode=only_full_group_by

So this is the setting that is now on where before it was off.

The MySQL manual explains that you can turn this setting off on a per session basis, but also includes this warning:

  

Quote

If ONLY_FULL_GROUP_BY is disabled, a MySQL extension to the standard SQL use of GROUP BY permits the select list, HAVING condition, or ORDER BY list to refer to nonaggregated columns even if the columns are not functionally dependent on GROUP BY columns. This causes MySQL to accept the preceding query. In this case, the server is free to choose any value from each group, so unless they are the same, the values chosen are nondeterministic, which is probably not what you want.

So it's telling you, that when you reduce the result set with group by, any fields that are not part of the group by (or part of a summary function) are "nondeterministic", which means, "we include whatever random column value out of all the available ones that were in the original result set before it was grouped."

In other words, that data could change from query to query for no apparent reason.

So you have a couple of options here.  One is to rewrite the query so that you aren't just using SELECT *.  You use SELECT * and then you specify a few columns again for reasons that aren't clear to me.  In general, it is best to only provide the fields you need in the select list. 

Your query does a join to a table with a 1-M relationship (a_players_reviews) for reasons unknown to me.  It is an inner join, which means that unless there is at least 1 a_players_reviews rows for an event, there will not be a row in the result set (group by or not) for that event.  I don't know if that is by design, or not, but it isn't really clear why you'd design the query to make that join only to group out the results, while also knowing that without a review, the event won't even appear in the list.