Jump to content

error due to mysql version change.


ajoo

Recommended Posts

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 !

 

 

 

 

 

Link to comment
Share on other sites

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).

Link to comment
Share on other sites

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
Link to comment
Share on other sites

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 !

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.  

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.