How to modify `sql_mode` using the configuration file in TiDB?
Master System Design with Codemia
Enhance your system design skills with over 120 practice problems, detailed solutions, and hands-on exercises.
When working with TiDB, a distributed database solution compatible with MySQL, it is often critical to adjust the sql_mode to influence various aspects of SQL behavior. Modifying sql_mode can tailor how SQL statements are processed, affecting things like error handling, data validation, and compatibility with other SQL flavors. This article explains step-by-step how to modify the sql_mode in TiDB using its configuration file, and provides a deeper understanding of why these changes might be necessary.
Understanding sql_mode in TiDB
In TiDB, as in MySQL, sql_mode is a system variable that controls different aspects of SQL execution. It impacts SQL syntax and data validation rules, allowing you to control behavior such as whether to ignore errors, and how strictly data types are checked.
Default sql_mode
TiDB defaults are designed to be as compatible as possible with MySQL, including the default sql_mode. The default setting includes several modes such as STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_ENGINE_SUBSTITUTION, and ONLY_FULL_GROUP_BY.
How to Modify sql_mode in TiDB Configuration File
To customize the SQL behavior in TiDB, modifying the sql_mode can be done by editing the configuration file of TiDB, typically tidb.toml. Below are the precise steps to do it:
- Locate the Configuration File: The TiDB configuration file is usually named
tidb.tomland is often located in/etc/tidbor wherever TiDB was installed. Depending on your installation specifics, you might need to locate this file using a command likefind / -name tidb.toml. - Edit the Configuration File: Open the
tidb.tomlfile using a text editor of your preference. For instance, you can useviornanoon Linux:
- Modify
sql_mode: Within thetidb.tomlfile, look for the section that specifies[performance]. Under this section, you’ll find thesql_mode. You can change it to whatever combination of modes you require. For example:
- Save Changes and Restart TiDB: After making changes to the
sql_mode, save the file and restart the TiDB server for changes to take effect. You can restart TiDB using a command such as:
- Verify Changes: To ensure that your changes have taken effect, connect to the TiDB server and execute:
This statement should reflect the new sql_mode settings.
Impact of Different sql_mode Settings
Modifying sql_mode can have various impacts on how SQL statements are handled in TiDB. Below is a table summarizing a few common modes and their effects:
| SQL Mode | Effect |
| STRICT_TRANS_TABLES | Data must satisfy all tables' conditions of the data types, or the transaction will roll back. |
| NO_ZERO_DATE | Prevents using zero dates ('0000-00-00'). |
| ONLY_FULL_GROUP_BY | Disallows queries with non-aggregated columns listed in the SELECT clause that are not named in the GROUP BY clause. |
| NO_AUTO_CREATE_USER | Prohibits GRANT statements from automatically creating new users, unless accompanied by a CREATE USER statement. |
| NO_ENGINE_SUBSTITUTION | Prevents automatic substitution of the default storage engine if the requested engine is unavailable. |
Conclusion
Setting the appropriate sql_mode in TiDB is crucial for ensuring that SQL operations adhere to expected norms and standards, particularly in multi-database environments or specific compliance scenarios. The straightforward process to modify sql_mode in the configuration file enables fine control over database behavior, enhancing both compatibility and performance.
