GreatSQL修改配置文件参数无法生效

一、问题描述

客户需要创建无主键表，因提供默认模板设置了参数sql_require_primary_key = ON（创建新表或更改现有表结构的语句强制要求表具有主键），当创建无主键表时会提示ERROR 3750 (HY000): Unable to create or change a table without a primary key, when the system variable 'sql_require_primary_key' is set. 无法创建，现需要将参数修改为sql_require_primary_key = OFF，但更改文件重启实例后失效。

greatsql> SHOW VARIABLES LIKE 'sql_require_primary_key';
 ------------------------- ------- 
| Variable_name           | Value |
 ------------------------- ------- 
| sql_require_primary_key | ON    |
 ------------------------- ------- 
1 row in set (0.00 sec)

greatsql> CREATE TABLE `test_table` (
    ->   `id` int NOT NULL,
    ->   `k` int NOT NULL DEFAULT '0',
    ->   `c` char(120) NOT NULL DEFAULT '',
    ->   `pad` char(60) NOT NULL DEFAULT '',
    ->   KEY `k_1` (`k`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=10000001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
ERROR 3750 (HY000): Unable to create or change a table without a primary key, when the system variable 'sql_require_primary_key' is set. Add a primary key to the table or unset this variable to avoid this message. Note that tables without a primary key can cause performance problems in row-based replication, so please consult your DBA before changing this setting.

二、问题排查

使用命令修改先解决问题

因该参数sql_require_primary_key支持动态修改，先使用 SET global sql_require_primary_key = OFF修改，进行表创建。

# Session修改 
greatsql> SET sql_require_primary_key = OFF ; 
# Global修改（需重新登录session）
greatsql> SET GLOBAL sql_require_primary_key = OFF ; 
# 建表语句
greatsql> CREATE TABLE `test_table` (
  `id` int NOT NULL,
  `k` int NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=10000001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

查询参数生效文件

修改配置文件重启实例复现操作后，进行问题排查。

greatsql> SELECT * FROM performance_schema.variables_info WHERE variable_name = 'sql_require_primary_key';
 ------------------------- ----------------- ---------------------------------------- ----------- ----------- ---------------------------- ---------- ---------- 
| VARIABLE_NAME           | VARIABLE_SOURCE | VARIABLE_PATH                          | MIN_VALUE | MAX_VALUE | SET_TIME                   | SET_USER | SET_HOST |
 ------------------------- ----------------- ---------------------------------------- ----------- ----------- ---------------------------- ---------- ---------- 
| sql_require_primary_key | PERSISTED       | /greatsql/dbdata/data/mysqld-auto.cnf | 0         | 0         | 2024-11-07 10:44:11.545289 | root     | NULL     |
 ------------------------- ----------------- ---------------------------------------- ----------- ----------- ---------------------------- ---------- ---------- 

• VARIABLE_NAME:参数名称；
• VARIABLE_SOURCE：最近设置参数的来源，以下是几个常见值说明；
  • PERSISTED：由实例 mysqld-auto.cnf 文件设置的参数；
  • EXPLICIT：由实例启动指定配置文件设置的参数，即--defaults-file指定的配置文件；
  • DYNAMIC：在实例运行期间动态设置的参数；
  • COMPILED：实例默认值；
  • COMMAND_LINE：命令行设置的参数;
• VARIABLE_PATH：如果参数由配置文件而来，则为配置文件路径，否则为空；
• MIN_VALUE && MIN_VALUE：参数允许设置的最小值和最大值，非数值类型的保持为0；
• SET_TIME：最近设置参数的时间；
• SET_USER && SET_HOST：最近设置参数的用户和主机。

通过查询performance_schema.variables_info获取到该参数生效配置文件为 mysqld-auto.cnf，且由SET PERSIST指令配置的，GreatSQL支持使用PERSIST命令修改持久化参数【persisted_globals_load参数需要为ON】，配置后该参数会写入到mysqld-auto.cnf一个 JSON 文件内，当实例启动后该文件内参数配置会覆盖默认实例启动指定的配置文件。

$ python -m json.tool mysqld-auto.cnf
{
    "Version": 2,
    "mysql_dynamic_variables": {
        "sql_require_primary_key": {
            "Metadata": {
                "Host": "",
                "Timestamp": 1730947451545289,
                "User": "root"
            },
            "Value": "ON"
        }
    }
}

查询performance_schema.persisted_variables也可以看到该参数被PERSIST指令设定为了 ON。

greatsql> SELECT * FROM  performance_schema.persisted_variables WHERE variable_name = 'sql_require_primary_key';
 ------------------------- ---------------- 
| VARIABLE_NAME           | VARIABLE_VALUE |
 ------------------------- ---------------- 
| sql_require_primary_key | ON             |
 ------------------------- ---------------- 

取消 persis 参数

通过RESET PERSIST sql_require_primary_key;后，修改配置文件后重启正常生效。

greatsql> RESET PERSIST sql_require_primary_key;
Query OK, 0 rows affected (0.01 sec)

# persisted_variables 表中已经没有了 sql_require_primary_key 参数的记录了
greatsql> SELECT * FROM  performance_schema.persisted_variables WHERE variable_name = 'sql_require_primary_key';
Empty set (0.00 sec)

# 由 mysqld_safe 启动的 greatsql 实例可以通过 restart 进行重启
greatsql> RESTART; 
Query OK, 0 rows affected (0.00 sec)

# 重启后，sql_require_primary_key 生效方式变成了 EXPLICIT 读取实例启动配置文件
greatsql>  SELECT * FROM performance_schema.variables_info WHERE variable_name = 'sql_require_primary_key';
 ------------------------- ----------------- ---------------------------------------- ----------- ----------- ---------- ---------- ---------- 
| VARIABLE_NAME           | VARIABLE_SOURCE | VARIABLE_PATH                          | MIN_VALUE | MAX_VALUE | SET_TIME | SET_USER | SET_HOST |
 ------------------------- ----------------- ---------------------------------------- ----------- ----------- ---------- ---------- ---------- 
| sql_require_primary_key | EXPLICIT        | /greatsql/dbdata/conf/gip18713315.cnf | 0         | 0         | NULL     | NULL     | NULL     |
 ------------------------- ----------------- ---------------------------------------- ----------- ----------- ---------- ---------- ---------- 
1 row in set (0.01 sec)

greatsql> SHOW VARIABLES LIKE 'sql_require_primary_key';
 ------------------------- ------- 
| Variable_name           | Value |
 ------------------------- ------- 
| sql_require_primary_key | OFF   |
 ------------------------- ------- 

三、问题结论

该环境sql_require_primary_key参数执行过SET PERSIST sql_require_primary_key = ON;持久化，在后续修改配置文件内容为 OFF 时，并未执行RESET PERSIST sql_require_primary_key;取消该参数的持久化配置，因此当实例启动时mysqld-auto.cnf文件中的配置会覆盖指定配置文件中的相同配置。