Let us take a closer look at how to set sql_mode in my.cnf for MySQL in detail. With the assistance of our MySQL support services at Bobacres, we can give you a step-by-step guide on how to set it up

Mysql set sql_mode in my.cnf

MariaDB has a variety of modes, allowing us to tailor it to our specific needs. The most essential methods are to use SQL MODE (controlled by the SQL mode system variable) and OLD MODE (controlled by the old mode system variable).

SQL MODE is used to make MariaDB replicate the behavior of other SQL servers, whereas OLD MODE is used to emulate the behavior of previous MariaDB or MySQL versions.

SQL MODE is a string with options separated by commas (‘,’) and no spaces. The choices are not case-sensitive.

Permanently changing SQL mode

To set sql_mode for MySQL firstly, we must determine which configuration file our MySQL installation prefers. We need the binary’s location for this:

$ which mysqld
/usr/sbin/mysqld

Then, to perform the lookup, we must follow the path outlined below:  $ /usr/sbin/mysqld --verbose --help | grep -A 1 "Default options"

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf

We can see here that the first favored configuration file is one in the etc folder’s root. However, if that file did not exist on the system, then chose the second option to set sql_mode for MySQL.

Firstly, we must determine the current SQL mode:

mysql -u homestead -psecret -e "select @@sql_mode"  | @@sql_mode | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_

BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION


Then we must replicate the current text produced by this query and eliminate whatever we don’t want. We must remove NO ZERO IN DATE, NO ZERO DATE, and, of course, ONLY FULL GROUP BY in this situation. The resulting string looks like this:

STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

After that, we have to open the configuration file that we have already chosen and decided on before (/etc/mysql/my.cnf) and add the following line into the [mysqld] section:

[mysqld] # ... other stuff will probably be here sql_mode = "STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

After the configurations save the process and exit. After that restart, the MySQL to complete the set up of the MySQL set sql_mode in my.cnf process.

sudo service mysql restart

[Need assistance with similar queries? We are here to help]

Conclusion

To sum up it is easy to set sql_mode in my.cnf for MySQL and it allows the user to prevent it from going back to its original values. We can set up the whole process within a few simple steps.