error due to mysql version change.

[ajoo]

Hi all, 

 

I am getting the following error :

Uncaught mysqli_sql_exception: In aggregated query without GROUP BY, expression #1 of SELECT list contains 
nonaggregated column 'AO.co.country_name'; this is incompatible with sql_mode=only_full_group_by in

Googling showed that this is because of sql_mode in the new version.

 

I would like to ask if I should reset the sql_mode or make changes to the query? Which would be the right thing to do? 

 

 

Here is the current sql_mode 

 

 

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_                      DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |

 

 

If I was to go setting the sql_mode for compatibility way, then should I just remove the  

ONLY_FULL_GROUP_BY

from the set of values above to get this working? Where (in the program) should I make the change to sql_mode ? At the time of creating a connection to the database? Is there another better way to set it ?

 

Thanks all !

 

 

 

 

 

[Jacques1]

The right thing to do is to repair the query.

 

Listing columns which aren't in the GROUP BY clause is simply wrong, because the result is undefined. Other database systems won't even allow this and immediately trigger an error. MySQL does allow it depending on the configuration, but that doesn't change the fact that it's a hack which shouldn't be used (except maybe for very, very special cases).

[requinix]

Listing columns which aren't in the GROUP BY clause is simply wrong, because the result is undefined. Other database systems won't even allow this and immediately trigger an error. MySQL does allow it depending on the configuration, but that doesn't change the fact that it's a hack which shouldn't be used (except maybe for very, very special cases).

There is one particular case that I wish SQL supported: grouping by a primary key (or even unique key) and allowing other columns in that same table. Mostly happens with joins.

SELECT users.id, users.username, COUNT(1) AS posts
FROM users
JOIN posts ON users.id = posts.user_id
GROUP BY users.id

[Jacques1]

MySQL has this feature (detection of functional dependencies) since 5.7.5.

[ajoo]

hmmm  Why would I get this error on a query that does not even use the "GROUP BY" in the statement of the query ? I can only guess that it has something to do with the count(*) that I use. 

 

My query is : 

		$query = "SELECT co.country_name, st.state_name, ct.city_name, p.pin, count(*)
					FROM country co
					JOIN state st ON co.id = st.country_id
					JOIN city ct ON st.id = ct.state_id
					JOIN pin p ON ct.id = p.city_id
					WHERE co.id = ? AND st.id = ? AND ct.id = ? AND p.id = ?";

Thanks !

Edited May 6, 2017 by ajoo

[Jacques1]

The query doesn't make much sense. What exactly are you trying to count?

 

You select exactly one pin, which means the count is 1. All the other stuff is redundant, because the pin determines the city which determines the state etc. -- unless this is some kind of strange check to see if the supplied IDs are valid.

[ajoo]

Hi Guru Jacques !!!

 

unless this is some kind of strange check to see if the supplied IDs are valid.

 

 

exactly that sir, but why strange?  It is of-course an additional final check to verify that the values are valid before the selected values are inserted into a table that holds the addresses. 

 

Would there be a better way to accomplish it ?

 

Thanks loads.  

Edited May 6, 2017 by ajoo

[Jacques1]

You want to insert what values?

 

Inserting the entire "path" with country, state, city etc. makes no sense, because this data can be calculated. A pin knows its city, a city knows its state, a state knows its country. So all you have to do is assign the data to, say, the pin.

[ajoo]

Right sir, I get your point. I'll make amends

 

Thanks !!