在渠道发布中，报错：SQLSTATE[01000]: Warning: 1265 Data truncated for column ‘pub_log_code’ at row 1 的排查(根源在于 Yii 2.0 框架响应的 code 值等于：42S22，不为数字类型)

1、在渠道发布中，报错：SQLSTATE[01000]: Warning: 1265 Data truncated for column ‘pub_log_code’ at row 1。如图1

图1

{
"name": "Database Exception",
"message": "SQLSTATE[01000]: Warning: 1265 Data truncated for column 'pub_log_code' at row 1/nThe SQL being executed was: INSERT INTO `cpa_pre_pub_log` (`group_id`, `task_group_id`, `task_group_uuid`, `channel_id`, `channel_code`, `type`, `run_model`, `article_type_id`, `article_type_code`, `article_type_name`, `article_category_id`, `article_category_name`, `article_title`, `article_author`, `source`, `source_uuid`, `source_pub_user_id`, `source_callback_url`, `source_article_id`, `pub_log_code`, `pub_log_message`, `pub_log_body`, `status`, `is_deleted`, `created_at`, `updated_at`, `deleted_at`, `uuid`, `channel_type_id`, `channel_type_code`, `channel_app_source_id`, `channel_app_source_uuid`) VALUES ('59b8833e28c267350c8c0fa5d890c4a4', 0, '', 7, 'douyin', 'pub', 'yii2_queue', 3, 'video', '视频', 0, '', '', '', 'spider', '825e6d5e36468cc4bf536799ce3565c1', 1, 'https://api.dev.chinamcloud.cn/scms/api/thirdPush/callBack', 1, '42S22', 'SQLSTATE[42S22]: Column not found: 1054 Unknown column //'channel_app_source_uuid//' in //'where clause//'//nThe SQL being executed was: SELECT * FROM `cpa_douyin_web_app_user_access_token` WHERE (`channel_app_source_uuid`=//'8957c4e204c111ec97ac54ee75d2ebc1//') AND (`is_deleted`=0)', 'a:13:{s:24:///"channel_app_source_uuids///";a:1:{i:0;s:32:///"8957c4e204c111ec97ac54ee75d2ebc1///";}s:6:///"source///";s:6:///"spider///";s:17:///"source_article_id///";i:1;s:19:///"source_callback_url///";s:58:///"https://api.dev.chinamcloud.cn/scms/api/thirdPush/callBack///";s:18:///"source_pub_user_id///";s:1:///"1///";s:11:///"source_uuid///";s:32:///"825e6d5e36468cc4bf536799ce3565c1///";s:18:///"video_absolute_url///";s:90:///"https://www.shuijingwanwq.com/wp-content/uploads/2021/07/318公路上的进藏人.divx.avi///";s:4:///"text///";s:23:///"交契税了0.avchd.mts///";s:9:///"cover_tsp///";i:0;s:12:///"micro_app_id///";s:0:///"///";s:15:///"micro_app_title///";s:0:///"///";s:13:///"micro_app_url///";s:0:///"///";s:8:///"at_users///";a:0:{}}', 3, 0, 1629802670, 1629802670, 0, '1ff2c90804ca11ecafc554ee75d2ebc1', 17, 'douyin_web', 80, '8957c4e204c111ec97ac54ee75d2ebc1')",
"code": "01000",
"type": "yii//db//Exception",
"file": "E://wwwroot//ccp_api_dev_new//vendor//yiisoft//yii2//db//Schema.php",
"line": 678,
"stack-trace": [
"#0 E://wwwroot//ccp_api_dev_new//vendor//yiisoft//yii2//db//Command.php(1304): yii//db//Schema->convertException(Object(PDOException), 'INSERT INTO `cp...')",
"#1 E://wwwroot//ccp_api_dev_new//vendor//yiisoft//yii2//db//Command.php(1099): yii//db//Command->internalExecute('INSERT INTO `cp...')",
"#2 E://wwwroot//ccp_api_dev_new//common//services//PrePubLogService.php(166): yii//db//Command->execute()",
"#3 E://wwwroot//ccp_api_dev_new//douyin//filters//PrePubLogFilter.php(50): common//services//PrePubLogService->createMultiple('59b8833e28c2673...', '42S22', 'SQLSTATE[42S22]...', 'douyin', 'pub', 'video', Array, 'yii2_queue')",
"#4 E://wwwroot//ccp_api_dev_new//vendor//yiisoft//yii2//base//ActionFilter.php(92): douyin//filters//PrePubLogFilter->afterAction(Object(douyin//rests//article//VideoCreateAction), Array)",
"#5 [internal function]: yii//base//ActionFilter->afterFilter(Object(yii//base//ActionEvent))",
"#6 E://wwwroot//ccp_api_dev_new//vendor//yiisoft//yii2//base//Component.php(628): call_user_func(Array, Object(yii//base//ActionEvent))",
"#7 E://wwwroot//ccp_api_dev_new//vendor//yiisoft//yii2//base//Controller.php(329): yii//base//Component->trigger('afterAction', Object(yii//base//ActionEvent))",
"#8 E://wwwroot//ccp_api_dev_new//vendor//yiisoft//yii2//rest//Controller.php(76): yii//base//Controller->afterAction(Object(douyin//rests//article//VideoCreateAction), Array)",
"#9 E://wwwroot//ccp_api_dev_new//vendor//yiisoft//yii2//base//Controller.php(183): yii//rest//Controller->afterAction(Object(douyin//rests//article//VideoCreateAction), Array)",
"#10 E://wwwroot//ccp_api_dev_new//vendor//yiisoft//yii2//base//Module.php(534): yii//base//Controller->runAction('video-create', Array)",
"#11 E://wwwroot//ccp_api_dev_new//vendor//yiisoft//yii2//web//Application.php(104): yii//base//Module->runAction('v1/article/vide...', Array)",
"#12 E://wwwroot//ccp_api_dev_new//vendor//yiisoft//yii2//base//Application.php(392): yii//web//Application->handleRequest(Object(yii//web//Request))",
"#13 E://wwwroot//ccp_api_dev_new//douyin//web//index.php(17): yii//base//Application->run()",
"#14 {main}"
],
"error-info": [
"01000",
1265,
"Data truncated for column 'pub_log_code' at row 1"
],
"previous": {
"name": "Exception",
"message": "SQLSTATE[01000]: Warning: 1265 Data truncated for column 'pub_log_code' at row 1",
"code": "01000",
"type": "PDOException",
"file": "E://wwwroot//ccp_api_dev_new//vendor//yiisoft//yii2//db//Command.php",
"line": 1299,
"stack-trace": [
"#0 E://wwwroot//ccp_api_dev_new//vendor//yiisoft//yii2//db//Command.php(1299): PDOStatement->execute()",
"#1 E://wwwroot//ccp_api_dev_new//vendor//yiisoft//yii2//db//Command.php(1099): yii//db//Command->internalExecute('INSERT INTO `cp...')",
"#2 E://wwwroot//ccp_api_dev_new//common//services//PrePubLogService.php(166): yii//db//Command->execute()",
"#3 E://wwwroot//ccp_api_dev_new//douyin//filters//PrePubLogFilter.php(50): common//services//PrePubLogService->createMultiple('59b8833e28c2673...', '42S22', 'SQLSTATE[42S22]...', 'douyin', 'pub', 'video', Array, 'yii2_queue')",
"#4 E://wwwroot//ccp_api_dev_new//vendor//yiisoft//yii2//base//ActionFilter.php(92): douyin//filters//PrePubLogFilter->afterAction(Object(douyin//rests//article//VideoCreateAction), Array)",
"#5 [internal function]: yii//base//ActionFilter->afterFilter(Object(yii//base//ActionEvent))",
"#6 E://wwwroot//ccp_api_dev_new//vendor//yiisoft//yii2//base//Component.php(628): call_user_func(Array, Object(yii//base//ActionEvent))",
"#7 E://wwwroot//ccp_api_dev_new//vendor//yiisoft//yii2//base//Controller.php(329): yii//base//Component->trigger('afterAction', Object(yii//base//ActionEvent))",
"#8 E://wwwroot//ccp_api_dev_new//vendor//yiisoft//yii2//rest//Controller.php(76): yii//base//Controller->afterAction(Object(douyin//rests//article//VideoCreateAction), Array)",
"#9 E://wwwroot//ccp_api_dev_new//vendor//yiisoft//yii2//base//Controller.php(183): yii//rest//Controller->afterAction(Object(douyin//rests//article//VideoCreateAction), Array)",
"#10 E://wwwroot//ccp_api_dev_new//vendor//yiisoft//yii2//base//Module.php(534): yii//base//Controller->runAction('video-create', Array)",
"#11 E://wwwroot//ccp_api_dev_new//vendor//yiisoft//yii2//web//Application.php(104): yii//base//Module->runAction('v1/article/vide...', Array)",
"#12 E://wwwroot//ccp_api_dev_new//vendor//yiisoft//yii2//base//Application.php(392): yii//web//Application->handleRequest(Object(yii//web//Request))",
"#13 E://wwwroot//ccp_api_dev_new//douyin//web//index.php(17): yii//base//Application->run()",
"#14 {main}"
]
}
}

2、SQL 报错，根源在于表：cpa_pre_pub_log 的字段：pub_log_code 的类型：int(11)，但是插入的值为字符串：42S22。如图2

图2

3、值：42S22 取自于：$e->getCode()。如图3

图3

try {
} catch(/Throwable $e) {
echo $e->getCode();
exit;
Yii::$app->response->statusCode = $e->statusCode ?? 200;
return ['code' => $e->getCode(), 'message' => $e->getMessage()];
}

4、注释掉捕获异常的代码。确定 Yii 2.0 框架响应的 code 值等于：42S22。在此之前，我一直认为响应的值类型仅为数字类型。如图4

图4

// try {
// } catch(/Throwable $e) {
//     echo $e->getCode();
//     exit;
//     Yii::$app->response->statusCode = $e->statusCode ?? 200;
//     return ['code' => $e->getCode(), 'message' => $e->getMessage()];
// }

{
"name": "Database Exception",
"message": "SQLSTATE[42S22]: Column not found: 1054 Unknown column 'channel_app_source_uuid' in 'where clause'/nThe SQL being executed was: SELECT * FROM `cpa_douyin_web_app_user_access_token` WHERE (`channel_app_source_uuid`='8957c4e204c111ec97ac54ee75d2ebc1') AND (`is_deleted`=0)",
"code": "42S22",
"type": "yii//db//Exception",
"file": "E://wwwroot//ccp_api_dev_new//vendor//yiisoft//yii2//db//Schema.php",
"line": 678,
"stack-trace": [
"#0 E://wwwroot//ccp_api_dev_new//vendor//yiisoft//yii2//db//Command.php(1304): yii//db//Schema->convertException(Object(PDOException), 'SELECT * FROM `...')",
"#1 E://wwwroot//ccp_api_dev_new//vendor//yiisoft//yii2//db//Command.php(1165): yii//db//Command->internalExecute('SELECT * FROM `...')",
"#2 E://wwwroot//ccp_api_dev_new//vendor//yiisoft//yii2//db//Command.php(407): yii//db//Command->queryInternal('fetchAll', NULL)",
"#3 E://wwwroot//ccp_api_dev_new//vendor//yiisoft//yii2//db//Query.php(249): yii//db//Command->queryAll()",
"#4 E://wwwroot//ccp_api_dev_new//vendor//yiisoft//yii2//db//ActiveQuery.php(133): yii//db//Query->all(NULL)",
"#5 E://wwwroot//ccp_api_dev_new//common//models//DouyinWebAppUserAccessTokenQuery.php(23): yii//db//ActiveQuery->all(NULL)",
"#6 E://wwwroot//ccp_api_dev_new//common//logics//DouyinWebAppUserAccessToken.php(107): common//models//DouyinWebAppUserAccessTokenQuery->all()",
"#7 E://wwwroot//ccp_api_dev_new//common//services//DouyinWebAppAccessTokenService.php(101): common//logics//DouyinWebAppUserAccessToken::findAllByChannelAppSourceUuids(Array)",
"#8 E://wwwroot//ccp_api_dev_new//douyin//rests//article//VideoCreateAction.php(235): common//services//DouyinWebAppAccessTokenService::findModelsValidByChannelAppSourceUuids(Array)",
"#9 [internal function]: douyin//rests//article//VideoCreateAction->run()",
"#10 E://wwwroot//ccp_api_dev_new//vendor//yiisoft//yii2//base//Action.php(94): call_user_func_array(Array, Array)",
"#11 E://wwwroot//ccp_api_dev_new//vendor//yiisoft//yii2//base//Controller.php(181): yii//base//Action->runWithParams(Array)",
"#12 E://wwwroot//ccp_api_dev_new//vendor//yiisoft//yii2//base//Module.php(534): yii//base//Controller->runAction('video-create', Array)",
"#13 E://wwwroot//ccp_api_dev_new//vendor//yiisoft//yii2//web//Application.php(104): yii//base//Module->runAction('v1/article/vide...', Array)",
"#14 E://wwwroot//ccp_api_dev_new//vendor//yiisoft//yii2//base//Application.php(392): yii//web//Application->handleRequest(Object(yii//web//Request))",
"#15 E://wwwroot//ccp_api_dev_new//douyin//web//index.php(17): yii//base//Application->run()",
"#16 {main}"
],
"error-info": [
"42S22",
1054,
"Unknown column 'channel_app_source_uuid' in 'where clause'"
],
"previous": {
"name": "Exception",
"message": "SQLSTATE[42S22]: Column not found: 1054 Unknown column 'channel_app_source_uuid' in 'where clause'",
"code": "42S22",
"type": "PDOException",
"file": "E://wwwroot//ccp_api_dev_new//vendor//yiisoft//yii2//db//Command.php",
"line": 1299,
"stack-trace": [
"#0 E://wwwroot//ccp_api_dev_new//vendor//yiisoft//yii2//db//Command.php(1299): PDOStatement->execute()",
"#1 E://wwwroot//ccp_api_dev_new//vendor//yiisoft//yii2//db//Command.php(1165): yii//db//Command->internalExecute('SELECT * FROM `...')",
"#2 E://wwwroot//ccp_api_dev_new//vendor//yiisoft//yii2//db//Command.php(407): yii//db//Command->queryInternal('fetchAll', NULL)",
"#3 E://wwwroot//ccp_api_dev_new//vendor//yiisoft//yii2//db//Query.php(249): yii//db//Command->queryAll()",
"#4 E://wwwroot//ccp_api_dev_new//vendor//yiisoft//yii2//db//ActiveQuery.php(133): yii//db//Query->all(NULL)",
"#5 E://wwwroot//ccp_api_dev_new//common//models//DouyinWebAppUserAccessTokenQuery.php(23): yii//db//ActiveQuery->all(NULL)",
"#6 E://wwwroot//ccp_api_dev_new//common//logics//DouyinWebAppUserAccessToken.php(107): common//models//DouyinWebAppUserAccessTokenQuery->all()",
"#7 E://wwwroot//ccp_api_dev_new//common//services//DouyinWebAppAccessTokenService.php(101): common//logics//DouyinWebAppUserAccessToken::findAllByChannelAppSourceUuids(Array)",
"#8 E://wwwroot//ccp_api_dev_new//douyin//rests//article//VideoCreateAction.php(235): common//services//DouyinWebAppAccessTokenService::findModelsValidByChannelAppSourceUuids(Array)",
"#9 [internal function]: douyin//rests//article//VideoCreateAction->run()",
"#10 E://wwwroot//ccp_api_dev_new//vendor//yiisoft//yii2//base//Action.php(94): call_user_func_array(Array, Array)",
"#11 E://wwwroot//ccp_api_dev_new//vendor//yiisoft//yii2//base//Controller.php(181): yii//base//Action->runWithParams(Array)",
"#12 E://wwwroot//ccp_api_dev_new//vendor//yiisoft//yii2//base//Module.php(534): yii//base//Controller->runAction('video-create', Array)",
"#13 E://wwwroot//ccp_api_dev_new//vendor//yiisoft//yii2//web//Application.php(104): yii//base//Module->runAction('v1/article/vide...', Array)",
"#14 E://wwwroot//ccp_api_dev_new//vendor//yiisoft//yii2//base//Application.php(392): yii//web//Application->handleRequest(Object(yii//web//Request))",
"#15 E://wwwroot//ccp_api_dev_new//douyin//web//index.php(17): yii//base//Application->run()",
"#16 {main}"
]
}
}

5、现阶段的解决方案，或者调整表：cpa_pre_pub_log 的字段：pub_log_code 的类型为：varchar(16)。或者将 $e->getCode() 的值类型转换为数字类型，暂时决定采用此方案，毕竟，响应 code 的值类型为字符串的概率极低。如图5

图5

try {
} catch(/Throwable $e) {
Yii::$app->response->statusCode = $e->statusCode ?? 200;
return ['code' => (int) $e->getCode(), 'message' => $e->getMessage()];
}

6、决定先从根本上解决抛出异常的问题，不再抛出异常。此为 程序 Bug。SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘channel_app_source_uuid’ in ‘where clause’。表：cpa_douyin_web_app_user_access_token 中已经不存在字段：channel_app_source_uuid。