CODEWITHSUNDEEP
mysqlmacosoperating-systemsql-mode
bircastri
bircastri

Reputation: 2159

How can set SQL_MODE permanently on my mac

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 ?

Upvotes: 5

Views: 11276

Answers (4)

iMezied
iMezied

Reputation: 492

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

Upvotes: 1

Toby Beresford
Toby Beresford

Reputation: 1038

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.

Upvotes: 4

Skyd
Skyd

Reputation: 555

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.

Upvotes: 2

ScaisEdge
ScaisEdge

Reputation: 133370

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.

Upvotes: 7

Related Questions