How to disable strict mode in mysql

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"

Please help!
LVL 1
Black SulfurAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

spmtCommented:
Hello,


backup your cnf file

edit cnf file  like this

sql_mode=""

then restart mysql

/etc/init.d/mysql restart


verify

mysql -e "SELECT @@sql_mode;"

it should work

Thanks and Regards,
0
Black SulfurAuthor Commented:
Sorry, I forgot to mention that I am using MAMP so not sure I can use this

mysql -e "SELECT @@sql_mode;"

Open in new window

0
Black SulfurAuthor Commented:
And the problem is that I can't find a .cnf file!
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Steve BinkCommented:
Review this doc page: https://dev.mysql.com/doc/refman/5.7/en/option-files.html

Once you locate your configuration file, set the sql_mode option under the "[mysqld]" section of the file.  If the section does not already exist, create it.  Once the new setting is saved, restart the mysql service and verify the new setting.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Black SulfurAuthor Commented:
That file doesn't exist. The only folder I could find was etc. In the etc folder I create my.cnf

I put this in the file:

[mysqld]sql_mode=""

Open in new window


I stopped MAMP, closed it, reopened it, restarted the servers and it didn't fix my problem :(
0
Black SulfurAuthor Commented:
Can you believe it. All I had to do was create a new line and it works. Seriously...

[mysqld]
sql_mode=""

Open in new window

0
Black SulfurAuthor Commented:
Thanks so much to both of you
0
Steve BinkCommented:
I'm happy to hear you got it working, but...

>>> That file doesn't exist. The only folder I could find was etc. In the etc folder I create my.cnf

While that is a valid approach, you should know that you could be removing other valid configuration points in the existing .cnf file.  You should go back and try to locate the file being used.

If you run `mysqld --verbose --help`, you should see a snippet near the top explaining which files the server will look for as it starts:
$> mysqld --verbose --help
mysqld  Ver 5.7.23-0ubuntu0.16.04.1 for Linux on x86_64 ((Ubuntu))
Copyright (c) 2000, 2018, 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 ~/.my.cnf
The following groups are read: mysqld server mysqld-5.7

Open in new window


Search explicitly for those files to see which exist.  If you still can't locate it, you can try a last ditch effort with find:
sudo find / -iname *my.cnf

Open in new window

0
Black SulfurAuthor Commented:
Here are the results of sudo find ....

/private/etc/my.cnf
find: /private/var/db/ConfigurationProfiles/Store: Operation not permitted
/Applications/MAMP/tmp/mysql/my.cnf
find: /dev/fd/3: Not a directory
find: /dev/fd/4: Not a directory
0
Black SulfurAuthor Commented:
I tried this in Mac terminal: mysqld --verbose --help

and it just gave me an error

-bash: mysqld: command not found
0
Black SulfurAuthor Commented:
I found this related to mamp but not sure if it will help. Seems like you are meant to create your own file with MAMP?

http://blog-en.mamp.info/2010/02/how-to-set-mysql-default-storage-engine.html
0
Steve BinkCommented:
Based on that article, it looks to be entirely possible that you're running on straight-up defaults from mysqld.  You only returned two reasonable candidates for an existing cnf file, and one of them is in a tmp directory.  You're probably good to go.  Just keep this in mind if you discover any odd behavior.
0
Black SulfurAuthor Commented:
For sure. If something weird happens then the cnf file I created will be the first thing I will go back to.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.