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

Improve this question

1 Answer 1

Reset to default
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/

Improve this answer
6
  • 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? Commented Jan 11, 2021 at 18:45
  • maybe it's a holdover from a past setup where they were straight MySQL? Commented Jan 11, 2021 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. Commented Jan 11, 2021 at 18:52
  • so it's negating the other settings by their absence? Commented Jan 11, 2021 at 18:54
  • it overwrites the default settings, thereby removing STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER Commented Jan 11, 2021 at 18:56

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.