Please whitelist cPanel in your adblocker so that you’re able to see our version release promotions, thanks!

The Community Forums

Interact with an entire community of cPanel & WHM users!

SOLVED Mysql 5.7.25 default functions issue

Discussion in 'Database Discussion' started by wonder_wonder, Feb 8, 2019.

  1. wonder_wonder

    wonder_wonder Well-Known Member

    Joined:
    Jan 16, 2019
    Messages:
    51
    Likes Received:
    23
    Trophy Points:
    8
    Location:
    Spain
    cPanel Access Level:
    Root Administrator
    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:
    Code:
    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:
    Code:
    [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.
     
  2. cPanelMichael

    cPanelMichael Technical Support Community Manager Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    47,299
    Likes Received:
    2,155
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    Twitter:
    Hello @wonder_wonder,

    ONLY_FULL_GROUP_BY is enabled by default in MySQL version 5.7:

    Code:
    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.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    wonder_wonder likes this.
  3. wonder_wonder

    wonder_wonder Well-Known Member

    Joined:
    Jan 16, 2019
    Messages:
    51
    Likes Received:
    23
    Trophy Points:
    8
    Location:
    Spain
    cPanel Access Level:
    Root Administrator
    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!!
     
    cPanelMichael likes this.
Loading...

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice