服务器重装后Web应用报1411错误：STR_TO_DATE日期值无效求助

问题描述 #

服务器崩溃重装后，Web应用运行出现如下错误：

[1411]; Incorrect datetime value: '' for function str_to_date; 嵌套异常：Incorrect datetime value: '' for function str_to_date

此前应用运行正常，本地环境也能正常运行。已尝试以下SQL语句调整sql_mode，但未解决问题：

SET SESSION sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
SET SESSION sql_mode = 'ALLOW_INVALID_DATES';

SET SESSION sql_mode = '@old_sql_mode';
SET @old_sql_mode = @@sql_mode;

执行的插入SQL语句如下：

insert into at_ph_stockist_invoice(stockist_id, invoice_no, invoice_date, invoice_amt, invoice_disc_per, invoice_disc,taxable_amt,gst_amt,
    final_invoice_amt,invoice_status, pymnt_type, po_amt, po_id, po_num, po_dt,final_amt, added_by, added_on, added_ip, status_id, 
    pay_bill_date, amount_received, change_given, balance) 
    values(#{stockistId}, #{invoiceNumber},  DATE_FORMAT(STR_TO_DATE(#{invoiceDate}, '%e-%b-%Y'), '%Y-%m-%d'), 
    #{invoiceAmmount}, #{discountPercentage}, #{discountAmmount},#{taxableAmt}, #{gstAmount}, #{finalInvoiceAmount},if(#{paymentType}='credit', 'pending', 'paid'), #{paymentType}, 
    #{poAmt}, #{poId}, #{poNumber}, DATE_FORMAT(STR_TO_DATE(#{poDate}, '%e-%b-%Y'), '%Y-%m-%d'),#{finalAmount}, 
    #{addedBy}, now(), #{addedIp}, fn_getcodevalue_id('STS','Active'), DATE_FORMAT(STR_TO_DATE(#{payBillDt}, '%e-%b-%Y'), '%Y-%m-%d'), 
    #{amountReceived}, #{chngGiven}, #{balance});

问题根因 #

1. 核心问题是传入STR_TO_DATE的参数为空字符串''，重装后的MySQL默认启用了严格模式（如STRICT_TRANS_TABLES、NO_ZERO_DATE），这类模式下空值转换日期会直接报错；而之前的服务器或本地环境的sql_mode未启用严格校验，允许将空值转为0000-00-00或忽略错误。
2. 你尝试的sql_mode设置存在逻辑错误：SET @old_sql_mode = @@sql_mode;应该放在设置新sql_mode之前，否则@old_sql_mode是空值，恢复操作无效；且ALLOW_INVALID_DATES仅允许格式合法但值无效的日期（如2023-02-30），不允许空字符串传入STR_TO_DATE。

解决方案 #

方案1：修改SQL，处理空值参数 #

在STR_TO_DATE外层添加空值判断，当参数为空时返回NULL（或业务允许的默认日期），示例：

-- 以invoiceDate为例，poDate、payBillDt字段同理处理
DATE_FORMAT(IF(#{invoiceDate} = '', NULL, STR_TO_DATE(#{invoiceDate}, '%e-%b-%Y')), '%Y-%m-%d')

若数据库字段允许NULL则返回NULL，若需要默认值可替换为'1970-01-01'这类合法日期。

方案2：调整MySQL全局sql_mode（不推荐） #

若必须允许空字符串转换日期，修改MySQL配置文件（Linux为my.cnf，Windows为my.ini），移除严格模式相关参数，重启MySQL生效：

sql_mode = ONLY_FULL_GROUP_BY,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

注意：此方式会降低数据校验严格性，可能产生脏数据，谨慎使用。

方案3：修复应用参数传入逻辑 #

检查Web应用中invoiceDate、poDate、payBillDt三个参数的生成逻辑，确保不会传入空字符串。若业务允许日期为空，应用层直接传入NULL而非空字符串，避免触发STR_TO_DATE的空值报错。

内容的提问来源于stack exchange，提问作者srinuvasarao atribiz