How can set SQL_MODE permanently on my mac

Question

I'm install MySql on my OsX 10.11 El Capitan. If I try to start my query I have a strange error if my query have Group By.

So if I try to execute this query from Terminal:

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

I can execute my query never problem. Now if I try to re-start my macbook, I have the some problem and I can re-execute the query.

There is any way to fix this sql_mode permanently ?

I have tro to open "etc/mysql/my.cnf" file but there isn't this file on my mac. How can I apply this change ?

Wishful

Answer 1

You could set my.cnf and add your sql_mode config

  [mysqld]
  sql_mode=ONLY_FULL_GROUP_BY

By default, the OS X installation does not use a my.cnf, and MySQL just uses the default values. To set up your own my.cnf, you could just create a file straight in /etc.

Corneille

how can I create this file ? – Wishful

create the my.cnf in /ect .. /etc/my.cnf – Corneille

I have open a new file on TextWrangler program, but if I try to save it on etc I have an error – Wishful

I have open Finder APP, Go-> Go To: etc/ then I have create a mysql folder then I have create my.cnf file with this code: [mysqld] sql_mode "" but I'm not able to fix my problem – Wishful

Ok, I have create a my.cnf file under /etc folder. I restart my computer but the problem is the same – Wishful

I don't have insert this file in never path. There is a way to find all file named my.cnf ? – Wishful

You check for other place using mysql --help or mysql --help | grep my.cnf – Corneille

I have execute your command and the result is this: order of preference, my.cnf, $MYSQL_TCP_PORT, /etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf /.my.cnf – Wishful

know you know where the files could be placed ..and based on the result the /ect.my.cnf should be the preferred ..check if you correctly placed and configured your file – Corneille

Answer 2

If you're using MAMP PRO it's worth noting this from the guide:

You cannot edit your my.cnf file directly. You must use the MAMP PRO interface to edit your my.cnf file. In the menu go to File > Edit Template > MySQL > my.cnf.

Then you can add the sql_mode you want under the [mysqld] heading. e.g:

[mysqld]
sql_mode=ONLY_FULL_GROUP_BY

This is what worked for me.

Pakistan

Answer 3

If you want to turn ONLY_FULL_GROUP_BY off, you need to check first the value of the sql_mode variable :

(default values)

 mysql> show variables like 'sql_mode'\G
 *************************** 1. row ***************************
 Variable_name: sql_mode
    Value: 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,01 sec)

Edit MySQL config file my.conf . Usually in /etc/my.cnf like scaisEdge said. but, you must write :

[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

(all values without ONLY_FULL_GROUP_BY - you can copy/paste this line)

Restart your MySQL, and enjoy.

Trombidiasis

Answer 4

the right way to do that by following these steps

1. Quit MAMP.

2. Run following command in the Terminal:

   touch /Applications/MAMP/conf/my.cnf && open -t /Applications/MAMP/conf/my.cnf

3. When this file is empty, then add the following below. If the file is not empty, then simply add the line sql_mode="" right after the line [mysqld]

   [mysqld] sql_mode=""

4. Save the config file and close the text editor; restart MAMP and start the servers.

reference: https://mampsupportforum.com/forums/latest/mamp-mamp-pro-disable-mysql-strict-mode

Tribal