Issues Related to SQL_MODE in TiDB

Translated
1

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

Original topic: 关于TIDB的SQL_MODE的问题

| username: TiDBer_FXYGSWF8

[TiDB Usage Environment] Production Environment (Pre-production)
[TiDB Version] 6.1.5

[Resource Configuration]
3 TiDB/PD, 8 TiKV, 32C/64GB/1.5TB (SSD), 10Gbps network

Today, when using TiDB to execute a query, the following situation occurred:
SELECT list is not in GROUP BY clause and contains nonaggregated column ‘xxx.xxx.xxx’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

First of all, I admit that this query (a large batch) is non-standard and was developed by a former colleague. It had no issues on version 3.X. If we force them to modify all the business logic, it will take a long time. We can go online while urging colleagues to modify the queries.

Secondly, I checked the sql_mode of version 6.1.5 and 3.1.15, which are O and S respectively.

I have two questions:

  1. What do O and S in sql_mode represent? I couldn’t find this information on the official website.
  2. How can I disable SQL_MODE? Because my TiDB is used as a read-only replica for MySQL synchronization and doesn’t require many strict constraints.
    Thank you, everyone.
2
| username: WalterWj | Original post link

It is not recommended to disable it; modify the SQL instead.
If it doesn’t matter, then go ahead and change it. Just use set global sql_mode.

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

You can change this at will, just like MySQL, by changing the global variable.

4
| username: TiDBer_FXYGSWF8 | Original post link

Okay, thank you.

5
| username: TiDBer_FXYGSWF8 | Original post link

Thank you.

6
| username: liuis | Original post link

MySQL has the same issue. SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,‘ONLY_FULL_GROUP_BY’,‘’)); Disabling it doesn’t matter.