3

mysql Ver 14.14 Distrib 5.7.15, for Linux (x86_64) using EditLine wrapper

I'm trying to configure my MYSQL instance to turn off some of the stricter settings currently I've done this:

   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 |

sudo vi /etc/mysql/mysql.cnf

[mysqld]
sql-mode=""

mysqld restart. Then try to see what the global SQL mode is:

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 |

Anyone any ideas on what to try next. Can't seem to get this setting to stick permanently and can't see any other files to try.

Edit: locate returns:

/etc/alternatives/my.cnf
/etc/mysql/my.cnf
/etc/mysql/my.cnf.fallback
/var/lib/dpkg/alternatives/my.cnf

Default options are read from the following files in the given order: /etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf

  • Please specify what version of MySQL. Is it MySQL 5.7, 5.6, or 5.5 ??? – RolandoMySQLDBA Apr 27 '17 at 20:59
  • @RolandoMySQLDBA - done. added a --version – Squiggs. Apr 27 '17 at 21:01
  • 1
    Don't turn off only_full_group_by ! Fix your queries that are returning incorrect results – Philᵀᴹ Apr 27 '17 at 21:08
  • Please run ls -l /usr/my.cnf. Does that file exist ??? – RolandoMySQLDBA Apr 27 '17 at 21:08
  • ls -l /usr/my.cnf returns No such file or directory. @Philᵀᴹ full_group_by is likely fine its a no_default_value thing I'm trying to fix but the code base is old so Im replicating a previous server config tht works. – Squiggs. Apr 27 '17 at 21:11
3

On Aug 05, 2015, I answered the post Set sql_mode “blank” after upgrading to MySQL 5.6

In my answer, I explained how Oracle created an additional my.cnf called /usr/my/cnf.

It has this (keep in mind this is MySQL 5.6 I am answering back then)

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

[mysqld]

# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin

# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
# port = .....
# server_id = .....
# socket = .....

# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

I guess some developer just gave up putting it in the code and slapped up a configure file as some demented shortcut.

If you have such a file, please comment out the last line and restart mysqld.

MySQL 5.7's default value for sql_mode is as mentioned

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

UPDATE 2017-04-27 17:23 EDT

I just ran mysqld --help --verbose | head -13 and got this

$ mysqld --help --verbose | head -13
2017-04-27T21:17:13.237941Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000)
2017-04-27T21:17:13.238060Z 0 [Warning] Changed limits: max_connections: 214 (requested 500)
2017-04-27T21:17:13.238065Z 0 [Warning] Changed limits: table_open_cache: 400 (requested 2048)
mysqld  Ver 5.7.17-log for Linux on x86_64 (MySQL Community Server (GPL))
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Starts the MySQL database server.

Usage: mysqld [OPTIONS]

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf

Please run mysqld --help --verbose 2>/dev/null | head -13 | tail -1

Check every file mentioned

UPDATE 2017-04-29 19:03 EDT

The /usr/my.cnf trick I mentioned in Set sql_mode “blank” after upgrading to MySQL 5.6 comes from a post written by Morgan Tocker under the heading "Changes in MySQL 5.6". This bait-and-switch method for sql_mode may not apply in this case since this is MySQL 5.7.

As I mentioned in my earlier comment, I have successfully sql_mode by adding this to /etc/my.cnf

[mysqld]
sql_mode=''

You should not have to restart mysqld. You login to mysql and run

SET GLOBAL sql_mode='';
SELECT @@GLOBAL.sql_mode;

This will set incoming connections to a blank sql_mode

This will not change the sql_mode of currently established connections.

Restarting mysqld will guarantee the all incoming connections will have it blank.

If you cannot restart mysqld, you must restart your app/web servers and make them disconnect. Then,m restart your app/web servers to establish connections.

Please try this and let us the results.

  • I stumbled upon your answer after a Google. Unfortunately I have no such file. – Squiggs. Apr 27 '17 at 21:09
  • Please run locate my.cnf and locate .my.cnf to scan the entire DB Server. See if there is another my.cnf or .my.cnf. My guess is that there is yet another my.cnf present. – RolandoMySQLDBA Apr 27 '17 at 21:13
  • updated question with results. I've had a look through those and no obvious over-riding setting :( – Squiggs. Apr 27 '17 at 21:19
  • Also, if the default setting is the same as the docs..would that suggest the .cnf files not being picked up at all? – Squiggs. Apr 27 '17 at 21:22
  • 1
    @RolandoMySQLDBA - a separate search for .my.cnf is unnecessary. From man locate: "If any PATTERN contains no globbing characters, locate behaves as if the pattern were *PATTERN*." – Rick James Apr 29 '17 at 22:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.