0

in my /etc/my.cnf file i have, under the [client-server] section, the following:

sql_mode=NO_ENGINE_SUBSTITUTION

when i try to run any mysql commands, such as mysqldunmp or even mysql --help, i get the following error:

mysql: unknown variable 'sql_mode=NO_ENGINE_SUBSTITUTION'

i understand that mysql ignores [client] settings in my.cnf (as per this post), but i see nowhere where this is the case for the [client-server] settings. personally, i've never seen this before, but assume the previous admin knew what he was doing since everything, operationally, is running tickety-boo.

at any rate, can someone kindly suggest how to remedy this as it is preventing any terminal-based commands from executing... which apparently includes cron scripts... :P

WhiteRau
  • 107
  • 7

1 Answers1

2

Options inside the [client-server] option group get interpreted by client tools like mysql and mysqldump as well as the mysql/mariadb server. Since sql_mode is a server-only option you should:

  • remove the option from the [client-server] group
  • add the option to the [mysqld] group

see also: https://mariadb.com/kb/en/sql-mode/

Marc
  • 121
  • 4
  • ok. excellent. thank you. upon further digging, it appears that `NO_ENGINE_SUBSTITUTION` is a MariaDB default. do I even need to have this or can I remove it completely? – WhiteRau Jan 11 '21 at 18:45
  • maybe it's a holdover from a past setup where they were straight MySQL? – WhiteRau Jan 11 '21 at 18:46
  • The MariaDB default in recent versions is `STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION`. This setting is actually modifying the default settings. – Marc Jan 11 '21 at 18:52
  • so it's negating the other settings by their absence? – WhiteRau Jan 11 '21 at 18:54
  • it overwrites the default settings, thereby removing `STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER` – Marc Jan 11 '21 at 18:56
  • gotcha. thank you for clarifying. much appreciated. – WhiteRau Jan 11 '21 at 18:58