Jump to content
Sign in to follow this  
ajoo

error due to mysql version change.

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 !

 

 

 

 

 

Share this post


Link to post
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).

  • Like 1

Share this post


Link to post
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

Share this post


Link to post
Share on other sites

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

  • Like 1

Share this post


Link to post
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 !

Edited by ajoo

Share this post


Link to post
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.

  • Like 1

Share this post


Link to post
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.  

Edited by ajoo

Share this post


Link to post
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.

  • Like 1

Share this post


Link to post
Share on other sites

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

 

Thanks !!

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

×
×
  • 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.