Introduction

sql_mode controls how MySQL validates data and interprets certain SQL behaviors. Changing it globally can be useful when you want every new connection to follow the same rules, especially in environments that need consistent handling of invalid dates, truncation, or legacy syntax.

Check the Current Global and Session Values

Before you change anything, inspect what the server is already using. MySQL tracks both a global value and a session value.

SELECT @@GLOBAL.sql_mode AS global_mode,
       @@SESSION.sql_mode AS session_mode;

The global value becomes the default for new connections. The session value is what the current connection is actually using right now.

Set the Global Value for New Connections

To change the global mode immediately until the server restarts, use SET GLOBAL.

SET GLOBAL sql_mode =
  'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

This change does not rewrite the configuration file. It also does not retroactively change existing client sessions. Any connection that was already open keeps its current session mode until it reconnects or you change it explicitly.

If you want the current session to match at once, run:

SET SESSION sql_mode =
  'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

That pattern is important during testing because many people run SET GLOBAL and then think the change failed when their current shell still shows the old mode.

Make the Change Persistent

For a durable configuration, use one of these approaches:

Option 1: SET PERSIST in MySQL 8

SET PERSIST writes the value so MySQL can restore it after a restart.

SET PERSIST sql_mode =
  'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

This is often the easiest operational choice when you have the right privileges and want to avoid editing server config files manually.

Option 2: Edit my.cnf or mysqld.cnf

You can also set the server default in the MySQL configuration file:

[mysqld]
sql_mode=STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

After editing the config file, restart MySQL for the change to take effect.

Choosing the Right Modes

Many teams enable strict modes because they catch bad data early. For example, STRICT_TRANS_TABLES prevents silent truncation in many cases, and ERROR_FOR_DIVISION_BY_ZERO surfaces arithmetic mistakes instead of letting them pass quietly.

That said, do not copy a mode string blindly from another environment. Some legacy applications depend on relaxed behavior, and removing or adding one mode can change inserts, date handling, or grouping semantics in ways that break older code.

Verify the Change

After updating the value, reconnect with a fresh client and check again:

SELECT @@GLOBAL.sql_mode, @@SESSION.sql_mode;

It is also smart to run a small insert or update that exercises the behavior you care about. For example, if strict mode matters, test a value that would previously have been truncated or coerced silently.

Privileges and Rollout Planning

Changing global server variables usually requires elevated privileges, so treat the change like an operational configuration update rather than an ad hoc query. In shared environments, coordinate with application owners first because one mode change can affect every new database connection created after the rollout.

Common Pitfalls

• 'SET GLOBAL affects only new connections, not sessions that are already open.'
• Removing defaults without understanding them can change application behavior in subtle ways.
• You need sufficient privileges to change global server variables.
• Configuration-file changes require a restart, while runtime changes do not survive restart unless persisted.

Summary

• Use SET GLOBAL sql_mode = '...' to change the default for new connections immediately.
• Use SET SESSION if the current connection also needs the new mode right away.
• For persistence, use SET PERSIST in MySQL 8 or edit the server config file.
• Verify the final behavior with a fresh connection and a real query, not just by reading the variable value.