Crazy Horse
asked on
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_DA TE,NO_ZERO _DATE,ERRO R_FOR_DIVI SION_BY_ZE RO,NO_AUTO _CREATE_US ER,NO_ENGI NE_SUBSTIT UTION
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_stri ct_mode.cn f
[mysqld]
sql_mode="IGNORE_SPACE,NO_ ZERO_IN_DA TE,NO_ZERO _DATE,ERRO R_FOR_DIVI SION_BY_ZE RO,NO_AUTO _CREATE_US ER,NO_ENGI NE_SUBSTIT UTION"
Please help!
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_
[mysqld]
sql_mode="IGNORE_SPACE,NO_
Please help!
ASKER
Sorry, I forgot to mention that I am using MAMP so not sure I can use this
mysql -e "SELECT @@sql_mode;"
ASKER
And the problem is that I can't find a .cnf file!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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:
I stopped MAMP, closed it, reopened it, restarted the servers and it didn't fix my problem :(
I put this in the file:
[mysqld]sql_mode=""
I stopped MAMP, closed it, reopened it, restarted the servers and it didn't fix my problem :(
ASKER
Can you believe it. All I had to do was create a new line and it works. Seriously...
[mysqld]
sql_mode=""
ASKER
Thanks so much to both of you
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:
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:
>>> 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
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
ASKER
Here are the results of sudo find ....
/private/etc/my.cnf
find: /private/var/db/Configurat ionProfile s/Store: Operation not permitted
/Applications/MAMP/tmp/mys ql/my.cnf
find: /dev/fd/3: Not a directory
find: /dev/fd/4: Not a directory
/private/etc/my.cnf
find: /private/var/db/Configurat
/Applications/MAMP/tmp/mys
find: /dev/fd/3: Not a directory
find: /dev/fd/4: Not a directory
ASKER
I tried this in Mac terminal: mysqld --verbose --help
and it just gave me an error
and it just gave me an error
-bash: mysqld: command not found
ASKER
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/2 010/02/how -to-set-my sql-defaul t-storage- engine.htm l
http://blog-en.mamp.info/2
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.
ASKER
For sure. If something weird happens then the cnf file I created will be the first thing I will go back to.
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,