How to disable strict mode in mysql using MAMP on a mac

Question

I have spent hours trying to figure this out but am seriously stuck. I installed the latest version of mysql today and some things in my website broke. I believe it has to do with strict settings, IGNORE_SPACE,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

I am trying to disable strict mode just to get my site working but it seems impossible. I have tried this in terminal but no luck.

sudo vim /etc/mysql/conf.d/disable_mysql_strict_mode.cnf


[mysqld] 
    sql_mode="IGNORE_SPACE,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" 

I have looked in every etc folder I can find and other various folders but can't see anything that resembles a .cnf file.

I can do it temporarily through phpmyadmin but if I close MAMP or restart my computer I have to do it again as the change isn't permanent.

Answer 1

MAMP Pro

1. Open MAMP Pro and check your Settings (⌘+,). Make sure Hide Dock Icon is unchecked. If required, uncheck the icon, quit MAMP and restart it.
   Note: This is required to see the menu entries for MAMP. You can hide the Dock Icon again later.

2. Open the main window of MAMP Pro and via the menu go to File > Edit Templates > MySQL > (your version).
   This opens a text editor with your MySQL configuration.

3. In the configuration file look for the line [mysqld] (note the d at the end!).
   Right after this line, add a new line with the following text: sql_mode=""

4. Save the file, close it and restart your MySQL server.

---

MAMP (free)

By default MAMP free starts the MySQL server without a my.cnf file, i.e. it uses the default configuration which comes with the MySQL build. So you have to create a config file manually:

1. Quit MAMP (stop the servers)

2. Run following command in the terminal - it will open the config file inside your text editor app:
   touch /Applications/MAMP/conf/my.cnf && open -t /Applications/MAMP/conf/my.cnf

3. When the file is empty, then add the following content:

   [mysqld]
   sql_mode=""
   

   (When the file is not empty, then simply add the line sql_mode="" right after the line [mysqld])

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