Set the sql_mode for a specific database

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 设置指定数据库的sql_mode

| username: 随缘天空

[TiDB Usage Environment] Production Environment / Testing / POC
[TiDB Version] V7.1.1
[Encountered Problem: Problem Description and Impact]
In the running TiDB cluster, there are currently dozens of databases. I want to modify the sql_mode for only 2 specified databases. How should I proceed?

| username: lemonade010 | Original post link

It doesn’t support specifying sql_mode for a specific database, right?
TiDB supports modifying the sql_mode system variable at the session or global scope.

  • Modifications to global scope variables will apply to other servers in the cluster and remain effective after a restart. Therefore, you don’t need to change the sql_mode value on each TiDB server.
  • Modifications to session scope variables will only affect the current session and will disappear after a restart.
| username: zhaokede | Original post link

This is not a database parameter.
In the TiDB server layer, configure sql_mode at the session level or global level.

| username: DBAER | Original post link

Globally, it’s not supported. You might need to consider splitting it.

| username: fanderchan | Original post link

I am a MySQLer. There are dozens of databases inside, and each database is actually used by different applications, right? They use their own independent JDBC connections, right? The sql_mode can be set at the session level. You can solve it using JDBC.

Reference example:
String url = “jdbc:mysql://localhost:3306/yourDatabase?sessionVariables=sql_mode=‘’&user=yourUsername&password=yourPassword”;

| username: zhaokede | Original post link

This method is good.

| username: TiDBer_QYr0vohO | Original post link

The sql_mode is at the session or global level, so what you’re talking about probably can’t be achieved, right?

| username: tidb菜鸟一只 | Original post link

System variables are at least at the session level… There are no database-level variables…

| username: tony5413 | Original post link

Develop your own session-level settings.

| username: Kongdom | Original post link

:thinking: This method should be the best solution.

| username: yytest | Original post link

You can set it at the session level or implement it using front-end development.

| username: 随缘天空 | Original post link

Okay, thank you very much. I have already asked the development team to try it.

| username: 随缘天空 | Original post link

Okay, the development team wants to set it at the user or specified database level.

| username: 随缘天空 | Original post link

I guess they won’t either.

| username: 随缘天空 | Original post link

Front-end development?

| username: 随缘天空 | Original post link

It seems that it is not supported.

| username: 随缘天空 | Original post link

How to split?

| username: DBAER | Original post link

Split different TiDB clusters or other databases based on different sql_mode.

| username: zhaokede | Original post link

Find a JDBC-like client to test, add sql_mode to the connection configuration and see if it works.

| username: 随缘天空 | Original post link

There aren’t that many clusters, and it doesn’t seem very reasonable. It’s manageable if there are fewer SQL modes, but if there are many modes, how many clusters would be needed?