11

Using MySQL 5.5.27, in my.cnf I have:

[mysql]
sql_mode=ANSI

Upon starting the mysql console I get:

unknown variable 'sql-mode=TRADITIONAL'

The MySQL doc Server SQL Modes shows that option, so why does it not work?

(If it helps, I am on OSX Lion)

Also, here is a pastebin showing the things another person has tried, with no solution.

Zabba
  • 64,285
  • 47
  • 179
  • 207

4 Answers4

23

sql-mode should be in the [mysqld] section, not in the [mysql] section; if you move sql-mode=ANSI to the [mysqld] section, it should work.

drew
  • 2,949
  • 3
  • 25
  • 27
9

I'm pretty certain the reason it isn't working is because it is an argument for mysqld and not the client. I came up with an alternative way that you could do this instead. Edit your users my.cnf i.e. ~/.my.cnf to be something like this: 

[client]
init-command="set sql_mode='TRADITIONAL'"
cogsmos
  • 806
  • 6
  • 11
  • You can also move the init-command to the command like so `mysql --init-command="set sql_mode='TRADITIONAL'"`. This would allow you to do as an alias and without having to edit my.cnf. – cogsmos May 10 '13 at 04:55
  • 1
    Thanks. This works for the client that is connecting, but does not work for the server as a whole (the `[mysqld]` in my.cnf). – Zabba May 10 '13 at 05:02
  • There must be a script that starts your mysqld process some where. I'm not familiar with how it would launch on Mac, but on Linux it would be some where in /etc/init.d/. If you can find the script you should just be able to add --sql-mode=XXX to the command line. – cogsmos May 13 '13 at 05:02
  • If I add this and try `mysql -e "select @@sql_mode"`, I just get an error: `mysql: unknown variable 'init-command=set sql_mode='TRADITIONAL''` – rriemann Jun 29 '17 at 15:57
  • alternatively, you can set this command in the `mysqld` section like [mysql] sql_mode=TRADITIONAL – Syed Abidur Rahman Oct 29 '17 at 09:53
2

In config file you should use sql-mode name instead (with dash, not underscore)

http://dev.mysql.com/doc/refman/5.5/en/server-options.html#option_mysqld_sql-mode

And as @cogsmos pointed out, you should put it [mysqld] section

zerkms
  • 249,484
  • 69
  • 436
  • 539
  • Tried that too, but it does not work. Same error. Also, the variable is `Variable Name sql_mode` from the docs. `sql-mode` is to be used when at the comment line, such as `mysql --sql-mode=ANSI` (which also does not work). – Zabba May 10 '13 at 04:40
  • @Zabba: It's not a variable, it's am option file directive thus: `Option-File Format sql-mode`. It should work. Show the exact config with `sql-mode` and the exact error message – zerkms May 10 '13 at 04:41
  • I tried as you said, and restarted the server. The client then connected with no error. But when I run an UPDATE SQL, the option seems to have no effect, any ideas? (I am trying to update 300 chars into a 255 char varchar field and it does not throw any errors) – Zabba May 10 '13 at 05:00
  • @Zabba: `SELECT @@GLOBAL.sql_mode; SELECT @@SESSION.sql_mode;` – zerkms May 10 '13 at 05:15
  • That gave 1 row returned, but the value is "blank" i.e. no "NULL" or any other text where the value should be (for both). – Zabba May 10 '13 at 05:23
0

Both in Mysql and mariadb you can try to put in the /etc/mysql/my.cnf file in the section:

[mysqld]
sql_mode=''

for me it solved.

Nebenzahl
  • 351
  • 2
  • 11