Monthly Archives: April 2024

MYSQL Changes

The new versions of mysql apparently have changed the default sql_mode. The new options may cause older code to break, and require resetting.

To make changes to the sql_mode variable in MySQL persist between server restarts, you can modify the MySQL configuration file (my.cnf or my.ini, depending on your operating system).

Here are the steps to modify the sql_mode variable in the MySQL configuration file:

  1. Open the MySQL configuration file in a text editor. The location of the file may vary depending on your operating system and MySQL installation. On Linux systems, the file is typically located at /etc/my.cnf or /etc/mysql/my.cnf. On Windows systems, the file is typically located at C:\ProgramData\MySQL\MySQL Server x.x\my.ini.
  2. Locate the [mysqld] section of the configuration file. This section contains server options that apply to the MySQL server daemon.
  3. Add the following line to the [mysqld] section to set the sql_mode variable:
sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"

Replace the value of the sql_mode variable with the desired mode. In this example, we’re setting the mode to STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, and NO_ENGINE_SUBSTITUTION. 4. Save the changes to the configuration file and exit the text editor. 5. Restart the MySQL server to apply the changes. On Linux systems, you can use the following command to restart the MySQL server:

sudo systemctl restart mysql

On Windows systems, you can use the Services app to restart the MySQL server.

After restarting the MySQL server, the changes to the sql_mode variable should be persisted and applied to all new connections. You can verify the current value of the sql_mode variable by running the following SQL query:

SELECT @@sql_mode;

This will return the current value of the sql_mode variable. If the value matches the value that you set in the configuration file, the changes have been successfully applied.