How to turn on/off MySQL strict mode in localhost (xampp)?

Open or Close MySQL Strict Mode

1. Locate the Configuration File

When using XAMPP, the primary configuration file for MySQL is my.ini on Windows or my.cnf on Linux or Mac. This file is typically located within the mysql\bin folder of the XAMPP installation directory.

2. Modify the Configuration

First, open this configuration file with any text editor, such as Notepad++ or VS Code.

3. Find the Strict Mode Settings

In the my.ini or my.cnf file, locate the [mysqld] section and find sql_mode. This setting defines MySQL's operating mode, and strict mode can be enabled by including STRICT_TRANS_TABLES or STRICT_ALL_TABLES in it.

• Enable Strict Mode: Ensure that sql_mode includes STRICT_TRANS_TABLES or STRICT_ALL_TABLES. For example:

  ini
  sql_mode=STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION

• Disable Strict Mode: Remove STRICT_TRANS_TABLES and STRICT_ALL_TABLES from sql_mode. For example:

  ini
  sql_mode=NO_ENGINE_SUBSTITUTION

4. Save and Restart MySQL

After modifying the configuration file, save and close the editor. Then, restart the MySQL service to apply the changes. In the XAMPP Control Panel, stop and then start the MySQL service.

5. Verify the Changes

To confirm that the changes have been applied correctly, run an SQL query to check the current sql_mode:

sql
SHOW VARIABLES LIKE 'sql_mode';

This will display the currently active SQL mode, allowing you to verify if it matches your configuration.

Example Scenario

For instance, if a development team discovers that their application frequently encounters errors in the development environment due to MySQL's strict mode, they may need to disable strict mode locally to facilitate easier debugging and development. By following the steps above, they can quickly implement this configuration change, ensuring that development progress is not hindered by unnecessary database strictness checks.

By handling database configuration in this manner, it demonstrates technical proficiency in database management while showcasing the ability to flexibly adjust the development environment to meet varying stages of development needs. This approach is crucial for ensuring smooth project progression.