Mysql 5.7.25 default functions issue

Hello. I hope to be brief and explain myself well :) 2 or 3 weeks ago I made a server migration, same cpanel / whm, all the same, the migration was done correctly. In previous server, the site worked fine, in the new one, with the same version of everything (php 7.1 and mysql 5.7.25) all good except one function of the site. After investigating I thought it was due to a reconversion of tables from the database to innodb. The fault continues to exist and during these 2 or 3 weeks I have continued looking for information until finally today, I have discovered the reason, nothing to do with the conversion to innodb. One of the errors, or the main error was:

PHP Warning: mysqli_query(): (42000/1055): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'mydatabase.c.champ_gid' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by in ....

I found the reason and the solution. This problem is given in previous versions of 5.7.25 of mysql, for example 5.7.5. This is a copy paste of the explanation: The explanation / solution of the copy / paste extracted it from this topic: Error related to only_full_group_by when executing a query in MySql Reply number 258. Ok, I added this code to my.cnf:


[mysqld]
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Ok, perfect, problem solved !! But here comes my doubt: If in theory this is a function that comes by default in mysql 5.7.25 (and some previous ones, I do not remember since that version) why did I have to add this code to my.cnf? In theory, I should not have added this code because this version of mysql already implements it, as read in the indicated link (and in others). Therefore, the title of the case, it is as if on this server, this function (and I do not know if any more) did not work with respect to the version of mysql that is installed. On the previous server, which I remember, I did not have to add that code to work. But in this new server, despite having the same version of mysql is like running an old version. I have checked several times that it has installed and is running version 5.7.25 of mysql, but this function, which is by default in this version, does not run unless I add that code in my.cnf. Do you know what may be happening? Thank you in advance and excuse this long topic.

cPanelMichael

February 13, 2019 17:58

Hello @wonder_wonder, ONLY_FULL_GROUP_BY is enabled by default in MySQL version 5.7:

mysql> SELECT @@GLOBAL.sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@GLOBAL.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 |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

The change you made disabled ONLY_FULL_GROUP_BY because your script was not compatible with it. Thank you.

wonder_wonder

February 13, 2019 18:24

Hello @wonder_wonder, ONLY_FULL_GROUP_BY is enabled by default in MySQL version 5.7: ... Thank you.

I was think are default in 5.7 and older, but, its by default in 5.7.x, true? In this case, my doubt/question was a little "stupid question"...sorry :) Thanks at you!!

Mysql 5.7.25 default functions issue

Comments

Didn't find what you were looking for?