You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
140 lines
4.8 KiB
140 lines
4.8 KiB
-- 支付配置诊断SQL脚本
|
|
-- 用于诊断"Value must not be null!"错误
|
|
|
|
-- 1. 检查所有租户的支付配置完整性
|
|
SELECT
|
|
tenant_id,
|
|
name,
|
|
type,
|
|
mch_id,
|
|
app_id,
|
|
merchant_serial_number,
|
|
api_key,
|
|
apiclient_key,
|
|
apiclient_cert,
|
|
pub_key,
|
|
pub_key_id,
|
|
status,
|
|
-- 配置完整性检查
|
|
CASE
|
|
WHEN mch_id IS NULL OR mch_id = '' THEN '❌ 商户号缺失'
|
|
WHEN app_id IS NULL OR app_id = '' THEN '❌ 应用ID缺失'
|
|
WHEN merchant_serial_number IS NULL OR merchant_serial_number = '' THEN '❌ 证书序列号缺失'
|
|
WHEN api_key IS NULL OR api_key = '' THEN '❌ API密钥缺失'
|
|
WHEN LENGTH(api_key) != 32 THEN '❌ API密钥长度错误'
|
|
ELSE '✅ 基础配置完整'
|
|
END AS basic_config_status,
|
|
-- 证书配置模式检查
|
|
CASE
|
|
WHEN pub_key IS NOT NULL AND pub_key != '' AND pub_key_id IS NOT NULL AND pub_key_id != ''
|
|
THEN '🔑 公钥模式'
|
|
WHEN apiclient_key IS NOT NULL AND apiclient_key != '' AND apiclient_cert IS NOT NULL AND apiclient_cert != ''
|
|
THEN '📜 证书模式'
|
|
ELSE '⚠️ 自动证书模式'
|
|
END AS cert_mode,
|
|
-- 状态检查
|
|
CASE
|
|
WHEN status = 1 THEN '✅ 已启用'
|
|
ELSE '❌ 未启用'
|
|
END AS status_check
|
|
FROM sys_payment
|
|
WHERE type = 0 -- 微信支付
|
|
ORDER BY tenant_id;
|
|
|
|
-- 2. 检查特定租户的详细配置（请替换为实际的租户ID）
|
|
-- 如果您知道具体的租户ID，请取消注释并修改下面的查询
|
|
/*
|
|
SELECT
|
|
'=== 租户配置详情 ===' as section,
|
|
tenant_id,
|
|
name,
|
|
mch_id as '商户号',
|
|
app_id as '应用ID',
|
|
merchant_serial_number as '证书序列号',
|
|
CASE
|
|
WHEN api_key IS NOT NULL AND api_key != ''
|
|
THEN CONCAT('已配置(长度:', LENGTH(api_key), ')')
|
|
ELSE '未配置'
|
|
END as 'API密钥状态',
|
|
apiclient_key as '私钥文件',
|
|
apiclient_cert as '证书文件',
|
|
pub_key as '公钥文件',
|
|
pub_key_id as '公钥ID',
|
|
status as '状态'
|
|
FROM sys_payment
|
|
WHERE tenant_id = 10547 AND type = 0; -- 请替换为实际的租户ID
|
|
*/
|
|
|
|
-- 3. 查找可能导致"Value must not be null!"的问题
|
|
SELECT
|
|
'=== 潜在问题检查 ===' as section,
|
|
tenant_id,
|
|
CASE
|
|
WHEN mch_id IS NULL THEN '商户号为NULL'
|
|
WHEN mch_id = '' THEN '商户号为空字符串'
|
|
ELSE NULL
|
|
END as mch_id_issue,
|
|
CASE
|
|
WHEN app_id IS NULL THEN '应用ID为NULL'
|
|
WHEN app_id = '' THEN '应用ID为空字符串'
|
|
ELSE NULL
|
|
END as app_id_issue,
|
|
CASE
|
|
WHEN merchant_serial_number IS NULL THEN '证书序列号为NULL'
|
|
WHEN merchant_serial_number = '' THEN '证书序列号为空字符串'
|
|
ELSE NULL
|
|
END as serial_number_issue,
|
|
CASE
|
|
WHEN api_key IS NULL THEN 'API密钥为NULL'
|
|
WHEN api_key = '' THEN 'API密钥为空字符串'
|
|
WHEN LENGTH(api_key) != 32 THEN CONCAT('API密钥长度错误(', LENGTH(api_key), ')')
|
|
ELSE NULL
|
|
END as api_key_issue
|
|
FROM sys_payment
|
|
WHERE type = 0
|
|
HAVING mch_id_issue IS NOT NULL
|
|
OR app_id_issue IS NOT NULL
|
|
OR serial_number_issue IS NOT NULL
|
|
OR api_key_issue IS NOT NULL;
|
|
|
|
-- 4. 生成修复建议
|
|
SELECT
|
|
'=== 修复建议 ===' as section,
|
|
tenant_id,
|
|
CONCAT(
|
|
'-- 租户 ', tenant_id, ' 的修复SQL:\n',
|
|
'UPDATE sys_payment SET \n',
|
|
CASE WHEN mch_id IS NULL OR mch_id = '' THEN ' mch_id = ''YOUR_MERCHANT_ID'',\n' ELSE '' END,
|
|
CASE WHEN app_id IS NULL OR app_id = '' THEN ' app_id = ''YOUR_APP_ID'',\n' ELSE '' END,
|
|
CASE WHEN merchant_serial_number IS NULL OR merchant_serial_number = '' THEN ' merchant_serial_number = ''YOUR_SERIAL_NUMBER'',\n' ELSE '' END,
|
|
CASE WHEN api_key IS NULL OR api_key = '' THEN ' api_key = ''YOUR_32_CHAR_API_KEY'',\n' ELSE '' END,
|
|
' status = 1\n',
|
|
'WHERE tenant_id = ', tenant_id, ' AND type = 0;\n'
|
|
) as fix_sql
|
|
FROM sys_payment
|
|
WHERE type = 0
|
|
AND (mch_id IS NULL OR mch_id = ''
|
|
OR app_id IS NULL OR app_id = ''
|
|
OR merchant_serial_number IS NULL OR merchant_serial_number = ''
|
|
OR api_key IS NULL OR api_key = '');
|
|
|
|
-- 5. 检查证书文件路径配置
|
|
SELECT
|
|
'=== 证书文件路径检查 ===' as section,
|
|
tenant_id,
|
|
apiclient_key as '私钥文件路径',
|
|
apiclient_cert as '证书文件路径',
|
|
pub_key as '公钥文件路径',
|
|
CASE
|
|
WHEN apiclient_key IS NOT NULL AND apiclient_key != ''
|
|
THEN '✅ 私钥路径已配置'
|
|
ELSE '❌ 私钥路径未配置'
|
|
END as private_key_status,
|
|
CASE
|
|
WHEN pub_key IS NOT NULL AND pub_key != ''
|
|
THEN '✅ 公钥路径已配置'
|
|
ELSE '⚠️ 公钥路径未配置(将使用自动证书)'
|
|
END as public_key_status
|
|
FROM sys_payment
|
|
WHERE type = 0
|
|
ORDER BY tenant_id;
|