Link check gets stuck

@wpmudev-support8

Although it is still running, the first error appears in the debug log and it seems related:

[redacted]

[09-Apr-2021 07:42:38 UTC] PHP Warning:  mysqli_query(): (HY001/1038): Out of sort memory, consider increasing server sort buffer size in /***/***/public/wp-includes/wp-db.php on line 2056
[09-Apr-2021 07:42:38 UTC] WordPress databasefout Out of sort memory, consider increasing server sort buffer size bij query SELECT DISTINCT links.*
FROM wp_blc_links AS links
				INNER JOIN wp_blc_instances AS instances USING(link_id)
				WHERE
					(
						( last_check_attempt < '2021-03-30 07:42:38' )
						OR
						(
							(broken = 1 OR being_checked = 1)
							AND may_recheck = 1
							AND check_count < 3
							AND last_check_attempt < '2021-04-09 07:12:38'
						)
					)

				AND
					( instances.container_type IN ('comment', 'post', 'page', 'dummy') )
					AND ( instances.parser_type IN ('link', 'image', 'metadata', 'url_field', 'acf') )
				
ORDER BY last_check_attempt ASC
LIMIT 30 gemaakt door include('phar:///***/***/wp/php/boot-phar.php'), include('phar:///***/***/wp/vendor/wp-cli/wp-cli/php/wp-cli.php'), WP_CLI\bootstrap, WP_CLI\Bootstrap\LaunchRunner->process, WP_CLI\Runner->start, WP_CLI\Runner->run_command_and_exit, WP_CLI\Runner->run_command, WP_CLI\Dispatcher\Subcommand->invoke, call_user_func, WP_CLI\Dispatcher\CommandFactory::WP_CLI\Dispatcher\{closure}, call_user_func, Cron_Event_Command->run, Cron_Event_Command::run_event, do_action_ref_array('blc_cron_check_links'), WP_Hook->do_action, WP_Hook->apply_filters, wsBrokenLinkChecker->cron_check_links, wsBrokenLinkChecker->work, wsBrokenLinkChecker->get_links_to_check

Hi @imc67

Thank you for the information.

Can you contact your hosting support and ask to increase the buffer size?

https://stackoverflow.com/a/29575947

Once done, try to run again and check if the issue is gone.

Best Regards
Patrick Freitas

Hi @wpmudevsupport12 I contacted the developers of the platform we use, this is their answer:

I haven’t seen this error or mysql variable before. Reading through some other project issues like https://github.com/mozilla/addons-server/issues/15970 , it seems this happens when one tries to order by a field which is not index. In the query above, I guess last_check_attempt is not indexed. Can you ask the dev to maybe make an index. I think otherwise, the fix is to order with the primary key or some other indexed key.

It seems your config of the query and table/index could fix it and then you’ll make more of your users/customers happy!

• This reply was modified 3 years, 7 months ago by MarcelC.

@wpmudevsupport12 and @wpmudev-support8 any update on this?

Hi @imc67

Thank you for the information.

I sent this to our developers to verify it,

We will reply once hearing back from the team.

Best Regards
Patrick Freitas

Hi @imc67

We tasked it to our developers to verify and improve this query if necessary.

I am afraid we can’t give an estimated time to release an improved version.

Had your hosting increased the buffer size for a test?

Best Regards
Patrick Freitas

@wpmudevsupport12 @wpmudev-support8

Had your hosting increased the buffer size for a test?

It’s self hosting, I added the buffer size and indeed it all works now. However the setting is not persistent (docker) and none of the many applications using this MySQL needed it before.

I guess the real solution is to optimize the query.

Hi @imc67

Thank you for the update.

Yes, it would be a temporary solution, we already reported to our developers to improve the query, I am afraid we can’t give an estimated time, but please, keep the plugin updated.

I am marking the ticket as resolved, but feel free to ping us any time you need.

Best Regards
Patrick Freitas

4 months later and still no optimized query?

Hi @imc67

I hope you are doing well

We are already working on the next plugin version but no ETA to release to production.

Best Regards
Patrick Freitas

Hello @imc67!

Hope you’re having a good day!

As there was no activity on this ticket for a bit, I’ll mark it as resolved for now.

If you need any further assistance, please feel free to reopen it and post here any time.

Kind regards,
Pawel

Don’t close the issue as your team is still working on it!

Hi @imc67,

Apologies for the confusion there.

Just a note, all of the reports (including this one) are tasked internally and tracked by the devs from there, actions taken on the forum tickets don’t affect them in any way so your ticket being closed didn’t actually made any changes to internal task itself.

Cheers,
Predrag