3

I'm not super experienced at this but I've been trying to get the mySQL strict_mode setting to be enforced permanently so that I can install and set up the database to install WHMCS.

Problem is I have seen many different posts and people saying you should alter the my.cnf file and put in the following sql_mode setting:

[mysqld]
sql_mode="TRADITIONAL,NO_AUTO_CREATE_USER"

I've also tried just the following:

sql_mode= "TRADITIONAL"

There doesn't seem to be any effect from these. When I restart mySQL and then check the mode using this:

mysql> SELECT @@GLOBAL.sql_mode;

I get the following which apparently means I'm still in strict mode:

+------------------------------------------------------------------------------------------------------------------------------------------------------+ | @@GLOBAL.sql_mode | +------------------------------------------------------------------------------------------------------------------------------------------------------+ | STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | +------------------------------------------------------------------------------------------------------------------------------------------------------+

I can however set the strict mode temporarily off for the session using this:

mysql> SET GLOBAL sql_mode ="";

The problem is that setting won't hold when the machine is restarted and then it reverts back to strict mode.

Does anyone have some more definitive suggestion on how I can get strict mode permanently off in MySQL on ubuntu to allow me to use WHMCS?

I can't seem to find any other config files that may be overriding this strict mode setting, but I could be missing something that is.

Jacob Budin
  • 9,753
  • 4
  • 32
  • 35
Marco Polo
  • 123
  • 2
  • 11
  • Isn't that supposed to be `sql-mode` (with a dash) in the config file? – fejese Dec 20 '14 at 01:01
  • Also, did you check that there's no other file included that overrides this setting? (eg. files in `/etc/mysql/conf.d`) – fejese Dec 20 '14 at 01:03
  • Hi fejese. In Linux I was under the impression it's supposed to be sql_mode unless I'm mistaken. I think it's dash "-" with Windows. Under my folder /etc/mysql/conf.d/ there is one file called mysqld_safe_syslog.cnf inside it is only 2 lines: [mysqld_safe]syslog – Marco Polo Dec 20 '14 at 01:21

1 Answers1

1

There are two files initially followed by mysql after restarting. You can look for "sql_mode" in these following files and update them as per your requirements: /etc/my.cnf or /usr/my.cnf

You have to do it as a root user and then it will never change after mysql restart again.

jewelfarazi
  • 460
  • 4
  • 10