PHP7.4.23（Silverstripe）项目接入ProxySQL执行插入报重复条目错误

核心问题原因 #

Silverstripe 3 框架执行写入操作时会默认开启隐式事务，你配置的ProxySQL查询规则仅匹配了显式INSERT语句，未覆盖事务相关语句，导致事务初始化语句被路由至只读从库，后续写入操作在只读事务中执行就会报错。

可落地解决步骤 #

• 步骤1：调整ProxySQL查询规则，补充事务、写操作相关匹配规则，优先级高于只读查询规则
  执行以下SQL操作ProxySQL管理端口（默认6032），注意替换主库主机组ID、从库主机组ID为你实际配置的主机组编号：

  INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply) VALUES 
  (1, 1, '^BEGIN', 主库主机组ID, 1),
  (2, 1, '^START TRANSACTION', 主库主机组ID, 1),
  (3, 1, '^COMMIT', 主库主机组ID, 1),
  (4, 1, '^ROLLBACK', 主库主机组ID, 1),
  (5, 1, '^INSERT', 主库主机组ID, 1),
  (6, 1, '^UPDATE', 主库主机组ID, 1),
  (7, 1, '^DELETE', 主库主机组ID, 1),
  (8, 1, '^SELECT.*FOR UPDATE', 主库主机组ID, 1),
  (9, 1, '^SELECT', 从库主机组ID, 1);
  -- 加载规则到运行环境并持久化
  LOAD MYSQL QUERY RULES TO RUNTIME;
  SAVE MYSQL QUERY RULES TO DISK;
  

• 步骤2：调整ProxySQL应用账号配置，适配Silverstripe 3框架的数据库驱动特性
  替换你的应用数据库账号为实际使用的账号，主库主机组ID同上：

  UPDATE mysql_users SET default_hostgroup=主库主机组ID, client_found_rows=1, client_multi_statements=1 WHERE username='你的应用数据库账号';
  -- 加载用户配置到运行环境并持久化
  LOAD MYSQL USERS TO RUNTIME;
  SAVE MYSQL USERS TO DISK;
  

• 步骤3：开启PDO预处理语句支持（如果你的项目使用了预处理写入）

  SET global mysql-handle_binary_protocol = 1;
  SAVE MYSQL VARIABLES TO DISK;
  

• 步骤4：验证路由是否正常
  执行以下SQL可以查看语句路由情况，确认写入类语句都被路由到主库主机组：

  SELECT digest_text, hostgroup FROM stats_mysql_query_digest WHERE digest_text LIKE '%INSERT%' OR digest_text LIKE '%BEGIN%';
  

内容的提问来源于stack exchange，提问作者Andrean Novan