本地运行正常的MySQL Insert查询在线上MySQL数据库无法插入数据

问题排查与修复方案 #

常见原因及对应解决方法 #

• 数据库账号权限不足
  线上环境为了安全通常会收紧数据库账号权限，SELECT查询有权限不代表拥有INSERT权限。登录线上MySQL执行如下命令验证权限：

  SHOW GRANTS FOR '你的业务数据库账号'@'连接主机地址';
  

  若没有users表的INSERT权限，执行授权命令即可：

  GRANT INSERT ON 你的库名.users TO '你的业务数据库账号'@'连接主机地址';
  FLUSH PRIVILEGES;
  

• 本地与线上表结构不一致
  这是高频出现的问题，常见差异点包括：
  • password字段长度不足：password_hash()生成的哈希串至少需要60位存储，建议设为varchar(255)，若线上该字段长度小于60会插入失败
  • 存在额外非空必填字段：线上users表可能多了本地没有的非空字段，INSERT语句未传入对应值会触发非空约束报错
  • 字段大小写不匹配：本地如果是Windows环境的MySQL默认不区分表名、字段名大小写，线上Linux环境的MySQL区分大小写，若字段名大小写不匹配会报错
  • 字段类型不匹配：比如created_on本地是DATE类型，线上是DATETIME类型，或者activate_code字段长度小于12位
    可以分别导出本地和线上的users表结构逐一对比修复。
• PDO配置问题
  两种常见的PDO配置会导致插入无报错但不生效：
  • 未开启异常模式：PDO默认是静默报错模式，如果初始化时未设置ERRMODE_EXCEPTION，即使插入失败也不会抛出异常，你无法在catch块拿到错误信息。需要在PDO初始化时添加配置：

    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    

  • 关闭了自动提交：如果线上PDO配置关闭了自动提交autocommit=0，插入操作后未手动执行commit()会导致事务回滚，数据不会真正写入。可以开启自动提交或者插入后手动提交：

    // 开启自动提交
    $pdo->setAttribute(PDO::ATTR_AUTOCOMMIT, 1);
    // 或者插入后手动提交
    $stmt->execute($params);
    $conn->commit();
    

• 线上SQL模式更严格
  线上MySQL通常会开启严格SQL模式STRICT_TRANS_TABLES，不符合字段要求的插入操作会直接报错，而本地宽松模式下会自动兼容处理。可以执行SELECT @@sql_mode;对比本地和线上的配置，要么调整线上SQL模式，要么修正插入值符合字段要求。
• 触发器或事务回滚问题
  若线上users表配置了INSERT触发器，触发器执行失败会导致插入操作回滚；另外检查代码中是否有其他地方开启了事务未提交，也会导致插入数据不落地。

快速定位技巧 #

临时在INSERT执行语句后添加错误输出，直接拿到具体报错信息：

$stmt = $conn->prepare("INSERT INTO users (email, password, firstname, lastname, activate_code, created_on) VALUES (:email, :password, :firstname, :lastname, :code, :now)");
$execRes = $stmt->execute(['email' => $email, 'password' => $password, 'firstname' => $firstname, 'lastname' => $lastname, 'code' => $code, 'now' => $now]);
if(!$execRes){
    print_r($stmt->errorInfo());
    exit;
}

根据输出的错误信息可以直接定位问题根源。

内容的提问来源于stack exchange，提问作者M.S