When operating a JSON field in MySQL 5.7, the problem of data loss is processed when concurrent execution (query, modification, reassignment) is processed (use a function JSON_ARRAY_APPEND that modifies the JSON value)

Post Views: 23

1. The json field is stored in the array format, the value example:[249, 247, 250, 244, 243, 255, 246, 245, 257, 248, 259, 252, 256]. as shown in Figure 1

json 字段中存储的是数组格式，值示例：[249, 247, 250, 244, 243, 255, 246, 245, 257, 248, 259, 252, 256] — Figure 1

2. The existing code running in the queue job is implemented as follows, the generated SQL is as follows


$themeSaasTask = ThemeSaasTask::whereJsonContains('theme_task_ids', $this->themeInstallationTask->id)->first();
$fromThemeInstallationIds = json_decode($themeSaasTask->from_theme_installation_ids, true);
$fromThemeInstallationIds[] = $this->themeInstallation->originalThemeInstallation->id;
$themeSaasTask->from_theme_installation_ids = json_encode($fromThemeInstallationIds);
$themeSaasTask->updated_at = now()->utc()->toDateTimeString();
$themeSaasTask->save();


select * from `theme_saas_task` where json_contains(`theme_task_ids`, '393') limit 1

update `theme_saas_task` set `from_theme_installation_ids` = '[380]', `theme_saas_task`.`updated_at` = '2023-08-21 03:10:57' where `id` = 23

3. Since the queue tasks can be run at the same time, when concurrent execution, the value that needs to be added to the json field will be lost. lost[251, 253, 254, 258]. Since there are a large number of queries, modification and save operations, which are triggered at the same time point 2023-06-20 06:36:27, data is lost. as shown in Figure 2

由于队列任务可以同时运行，在并发执行时，会导致需要添加至 json 字段中的值丢失。丢失了 [251, 253, 254, 258]。由于有大量的查询修改保存操作皆在同一时间点 2023-06-20 06:36:27 触发，进而导致了数据丢失 — Figure 2

4. Reference:12.17.4 Functions to modify JSON values JSON_ARRAY_APPEND The code is implemented as follows, and the generated SQL is as follows. as shown in Figure 3

参考：12.17.4 修改 JSON 值的函数 JSON_ARRAY_APPEND — Figure 3


$themeSaasTask->from_theme_installation_ids = DB::raw('JSON_ARRAY_APPEND(from_theme_installation_ids, "$", ' . $this->themeInstallation->originalThemeInstallation->id . ')');
$themeSaasTask->updated_at = now()->utc()->toDateTimeString();
$themeSaasTask->save();


update
  `theme_saas_task`
set
  `from_theme_installation_ids` = JSON_ARRAY_APPEND(from_theme_installation_ids, "$", 403),
  `theme_saas_task`.`updated_at` = '2023-08-22 02:39:12'
where
  `id` = 30

In addition to adding values to the json array, there are also cases of deleting values to the json array. The existing implementation is as follows


$themeSaasTask = ThemeSaasTask::whereJsonContains('theme_task_ids', $this->themeInstallationTask->id)->first();
if ($this->themeInstallation->type == ThemeInstallation::TYPE_UPDATE && $themeSaasTask) {
	$themeInstallationTaskIds = json_decode($themeSaasTask->theme_task_ids, true);
	$themeInstallationTaskIds = array_values(array_diff($themeInstallationTaskIds, [$this->themeInstallationTask->id]));
	if (empty($themeInstallationTaskIds)) {
		ThemeSaasTask::where('id', $themeSaasTask->id)->delete();
	} else {
		$themeSaasTask->theme_task_ids = json_encode($themeInstallationTaskIds);
		$themeSaasTask->save();
	}
}

6. When running concurrently in the queue, there are a total of 17 values in the field Theme_Task_ids. In theory, after 17 executions, `id` = 22 should be deleted. But, in fact, there is still left in the field Theme_Task_ids[380, 381, 382, 383, 385, 386, 389, 394]. The SQL executed is as follows: As shown in Figure 4

当在队列中并发运行时，字段 theme_task_ids 中总计有 17 个值，理论上来说，执行了 17 次以后，`id` = 22 应该会被删除掉。但是，实际上，字段 theme_task_ids 中还剩下 [380, 381, 382, 383, 385, 386, 389, 394] — Figure 4


update `theme_saas_task` set `theme_task_ids` = '[379,380,381,382,383,384,385,386,387,388,389,390,391,392,393,394,395]', `theme_saas_task`.`updated_at` = '2023-08-23 01:54:15' where `id` = 22;

/* 删除掉 379 */
select * from `theme_saas_task` where json_contains(`theme_task_ids`, '379') limit 1;
update `theme_saas_task` set `theme_task_ids` = '[380,381,382,383,384,385,386,387,388,389,390,394]', `theme_saas_task`.`updated_at` = '2023-08-23 02:01:39' where `id` = 22; 

/* 只需要删除掉 380，但是 379 又被添加 */
select * from `theme_saas_task` where json_contains(`theme_task_ids`, '380') limit 1;
update `theme_saas_task` set `theme_task_ids` = '[379,381,382,383,384,385,386,387,388,389,390,394]', `theme_saas_task`.`updated_at` = '2023-08-23 02:01:39' where `id` = 22;

/* 只需要删除掉 381，但是 379,380 又被添加 */
select * from `theme_saas_task` where json_contains(`theme_task_ids`, '381') limit 1;
update `theme_saas_task` set `theme_task_ids` = '[379,380,382,383,384,385,386,387,388,389,390,394]', `theme_saas_task`.`updated_at` = '2023-08-23 02:01:39' where `id` = 22;

/* 只需要删除掉 384，但是 380,381 又被添加 */
select * from `theme_saas_task` where json_contains(`theme_task_ids`, '384') limit 1;
update `theme_saas_task` set `theme_task_ids` = '[380,381,382,383,385,386,387,388,389,390,394]', `theme_saas_task`.`updated_at` = '2023-08-23 02:01:40' where `id` = 22;

7. Reference:mysql json array delete data . It is found that the parameter search_str of json_search is currently not supported by numbers. as shown in Figure 5

发现 JSON_SEARCH 的参数 search_str 暂不支持数字 — Figure 5


SELECT
	JSON_SEARCH( theme_task_ids, 'all', '375' ) 
FROM
	theme_saas_task 
WHERE
	id = 21;

8. It is finally decided to convert the value into the field Theme_Task_ids first, and convert it to a string first. The code adjustment running in the queue job is implemented as follows


$themeInstallationTaskId = '' . $this->themeInstallationTask->id . '';
$themeSaasTask = ThemeSaasTask::whereJsonContains('theme_task_ids', $themeInstallationTaskId)->first();

$themeSaasTask->theme_task_ids = DB::raw('JSON_REMOVE(theme_task_ids,	JSON_UNQUOTE(JSON_SEARCH(theme_task_ids, "one", ' . $themeInstallationTaskId . ')))');
$themeSaasTask->save();


update
  `theme_saas_task`
set
  `theme_task_ids` = '[\"516\",\"517\",\"518\",\"519\",\"520\",\"521\",\"522\",\"523\",\"524\",\"525\",\"526\",\"527\",\"528\",\"529\",\"530\",\"531\",\"532\"]',
  `theme_saas_task`.`updated_at` = '2023-08-23 08:33:57'
where
  `id` = 30;

"JSON_REMOVE(theme_task_ids, JSON_UNQUOTE(JSON_SEARCH(theme_task_ids, \"one\", 519)))";

9. However, in the case of concurrency, there are still some values that have not been deleted, a total of 17, 12 have been deleted, and there are still 5 left. as shown in Figure 6

但是在并发情况下，仍然存在一些值未被删除掉，总计 17 个，删除了 12 个，还剩下 5 个 — Figure 6

10. Due to the low frequency of this code running, I finally decided to add a lock to achieve


        $themeSaasTask = ThemeSaasTask::whereJsonContains('theme_task_ids', $this->themeInstallationTask->id)->first();

        if ($this->themeInstallation->type == ThemeInstallation::TYPE_UPDATE && $themeSaasTask) {

            app(ThemeManager::class)->cleanTheme($this->themeInstallation, $this->themeInstallation->theme);

            DB::beginTransaction();
            $themeSaasTask = ThemeSaasTask::lockForUpdate()->find($themeSaasTask->id);
            $themeInstallationTaskIds = json_decode($themeSaasTask->theme_task_ids, true);
            Log::info(
                '$themeInstallationTaskIds3-'.$this->themeInstallationTask->id,
                [$themeInstallationTaskIds]
            );
            $themeInstallationTaskIds = array_values(array_diff($themeInstallationTaskIds, [$this->themeInstallationTask->id]));
            if (empty($themeInstallationTaskIds)) {
                ThemeSaasTask::where('id', $themeSaasTask->id)->delete();
            } else {
                $themeSaasTask->theme_task_ids = json_encode($themeInstallationTaskIds);
                Log::info(
                    '$themeSaasTask->theme_task_ids3-'.$this->themeInstallationTask->id,
                    [$themeSaasTask->theme_task_ids]
                );
                $themeSaasTask->save();
            }
            DB::commit();
        }

11. Finally confirm that there is a problem with this plan, and finally leave it[567, 568, 569, 570, 571, 572, 573, 574, 575, 576, 577, 578, 579, 580, 581]. Expect an empty array. The logging records found that when the query, the queried data did not meet the expectations, the number was reduced, and the first and the 17th logs were printed out respectively.


[2023-08-24 10:10:06] local.INFO: $themeInstallationTaskIds3-567 [
    [
        567,
        568,
        569,
        570,
        571
    ]
] 
[2023-08-24 10:10:06] local.INFO: $themeSaasTask->theme_task_ids3-567 [
    "[568,569,570,571]"
] 

[2023-08-24 10:10:09] local.INFO: $themeInstallationTaskIds3-583 [
    [
        567,
        568,
        569,
        570,
        571,
        572,
        573,
        574,
        575,
        576,
        577,
        578,
        579,
        580,
        581,
        583
    ]
] 
[2023-08-24 10:10:09] local.INFO: $themeSaasTask->theme_task_ids3-583 [
    "[567,568,569,570,571,572,573,574,575,576,577,578,579,580,581]"
]

12. Finally, the root cause is found in the same record. In another place, the value is constantly added to the field Theme_Task_ids. Delete value. After deciding to add a value to the field Theme_Task_ids, then enter the queue in a unified batch.

Implementation before adjustment:


foreach ($list as $theme) {
	$themeTaskIds = json_decode($themeSaasTask->theme_task_ids, true);
	$themeTaskIds[] = $themeInstallationTask->id;
	$themeSaasTask->theme_task_ids = json_encode($themeTaskIds);
	$themeSaasTask->updated_at = now()->utc()->toDateTimeString();
	$themeSaasTask->save();
	// 推送任务到队列
	InstallThemeToDb::dispatch($themeInstallationTask)->onQueue('longtime');
}

Adjusted implementation:


$themeInstallationTasks = [];
foreach ($list as $theme) {
	$themeTaskIds = json_decode($themeSaasTask->theme_task_ids, true);
	$themeTaskIds[] = $themeInstallationTask->id;
	$themeSaasTask->theme_task_ids = json_encode($themeTaskIds);
	$themeSaasTask->updated_at = now()->utc()->toDateTimeString();
	$themeSaasTask->save();


	$themeInstallationTasks[] = $themeInstallationTask;
}

foreach ($themeInstallationTasks as $themeInstallationTask) {
	// 推送任务到队列
	InstallThemeToDb::dispatch($themeInstallationTask)->onQueue('longtime');
}

13. After confirming the adjustment, the scheme of step 12 is feasible, and finally the remaining[]. Expected to be an empty array, in line with expectations. Prints the first and 16th and 17th logs respectively.


[2023-08-24 13:42:24] local.INFO: $themeInstallationTaskIds6-635 [
    [
        635,
        636,
        637,
        638,
        639,
        640,
        641,
        642,
        643,
        644,
        645,
        646,
        647,
        648,
        649,
        650,
        651
    ]
] 
[2023-08-24 13:42:24] local.INFO: $themeSaasTask->theme_task_ids6-635 [
    "[636,637,638,639,640,641,642,643,644,645,646,647,648,649,650,651]"
]  

[2023-08-24 13:42:25] local.INFO: $themeInstallationTaskIds6-650 [
    [
        650,
        651
    ]
] 
[2023-08-24 13:42:25] local.INFO: $themeSaasTask->theme_task_ids6-650 [
    "[651]"
] 

[2023-08-24 13:42:26] local.INFO: $themeInstallationTaskIds6-651 [
    [
        651
    ]
]

Need long-term technical maintenance or remote troubleshooting?

I am a PHP / Go backend engineer with 15+ years of experience, focused on existing system maintenance, bug fixing, performance optimization, server troubleshooting, WordPress maintenance, and small feature iterations.

If your project is facing any of the following issues, we can start with a small troubleshooting task first:

✅ PHP / Laravel / Yii2 legacy systems without active maintenance
✅ Go / Gin backend APIs that need troubleshooting or optimization
✅ Slow, broken, or unstable WordPress websites
✅ Nginx / MySQL / Redis / Linux server issues
✅ CDN / Cloudflare / DNS / HTTPS configuration problems
✅ Long-term remote technical support or part-time maintenance

More details: About Me & Collaboration

WeChat: 13980074657
Email: shuijingwanwq@gmail.com
Telegram: @shuijingwan
GitHub: https://github.com/shuijingwan

When operating a JSON field in MySQL 5.7, the problem of data loss is processed when concurrent execution (query, modification, reassignment) is processed (use a function JSON_ARRAY_APPEND that modifies the JSON value)

评论

Leave a Reply Cancel reply

更多文章

Cloudflare Cache Rules Adjustment After Migrating WordPress Media to a CDN

From Local Folders to a Self-Hosted Online Spreadsheet: How I Manage Full-Time, Part-Time, and Project Opportunities

Troubleshooting Persistent EdgeOne 301 Requests After Moving WordPress Images to a Media Subdomain

Reinstalling Yoast SEO Broke an Old Post: Debugging a WordPress Fatal Error Caused by a Duplicate WPCode Snippet Function