Queued Tracking enabled coming along with very rare "Mysqli statement execute error : Deadlock found when trying to get lock; try restarting transaction"

Recently we enabled queued tracking (settings below).

Now between once per day or once per week “cron tab” console core:archive throw the following error:

ERROR [2019-12-01 12:00:09] 30416 Got invalid response from API request: ?module=API&method=API.get&idSite=1&period=day&date=last52&format=php&trigger=archivephp. Response was ‘a:2:{s:6:“result”;s:5:“error”;s:7:“message”;s:99:“Mysqli statement execute error : Deadlock found when trying to get lock; try restarting transaction”;}’
ERROR [2019-12-01 12:00:09] 30416 Empty or invalid response ‘’ for website id 1, Time elapsed: 1.635s, skipping
ERROR [2019-12-01 12:00:29] 30416 2 total errors during this script execution, please investigate and try and fix these errors.

Any idea?

Queued Tracking settings:

Settings that will be used:
Backend: mysql
NumQueueWorkers: 8
NumRequestsToProcess: 150
ProcessDuringTrackingRequest: 0
QueueEnabled: 1

Redis backend only settings (does not apply when using MySQL backend):
Host: 127.0.0.1
Port: 6379
Timeout: 0
Password:
Database: 0
UseSentinelBackend: 0
SentinelMasterName: mymaster

Version / stats:
PHP version: 7.3.12
Uname: Linux 3.12.18-clouder0 #1 SMP Tue Oct 9 18:14:47 EEST 2018 x86_64
Backend version: 5.6.36-82.1-log
Memory: array (
‘used_memory_human’ => ‘disabled’,
‘used_memory_peak_human’ => ‘disabled’,
)

Performing some tests:
Connection works in general
Initial expire seems to be set correctly
setIfNotExists works fine
expireIfKeyHasValue seems to work fine
Extending expire seems to be set correctly
expireIfKeyHasValue correctly expires only when the value is correct
Expire is still set which is correct
deleteIfKeyHasValue seems to work fine
List feature seems to work fine

Done

we’d like to suggest to apply 2 sets of changes, then run the queued tracking process with a new parameter: -vvv
and let us know what is the output then?

So the two patches to apply are:
https://patch-diff.githubusercontent.com/raw/matomo-org/plugin-QueuedTracking/pull/117.patch
and
https://patch-diff.githubusercontent.com/raw/matomo-org/matomo/pull/15197.patch

Are you familiar with applying patches?

We’re hoping the new output will help us figure out what’s going on.

Thanks,

Hi and thank you for your reply. I’ll post more details about the output of “-vvv” as soon as I have it available. But maybe not anymore this year :slight_smile:

For the moment all queued tracking is disabled

Can I assume that this will be part of the next version? If so, I would wait until the next release before patching the current installation.

Hi,

This patch (Set tracker debug flag before tracker environment is created. by diosmosis · Pull Request #117 · matomo-org/plugin-QueuedTracking · GitHub) was merged on 27 Nov 2019, but there hasn’t been a release yet. I’ll ask @thomas_matomo about it.

New version of QueuedTracking was released, does it help?

Thank you for your response. I’ll enable it again over the weekend report back to you.

It’s on air now with the following settings:

Crontab:

*	*	*	*	*	cd /home/asi/public_html/matomo/ && /usr/local/php73/bin/php ./console queuedtracking:process > /dev/null

Any advise welcome.

thank you

Can you also please:

  1. upgrade matomo to the latest beta: I would like to test early beta and RC releases, how do I enable automatic updates to use these development versions? FAQ - Analytics Platform - Matomo
  2. then run the queued tracking process with a new parameter: -vvv and let us know what is the output then?

Hello, I’m still on version 3.13.0, and since this version I get this error message every now and then (once a day / week):

Any idea or advise?

1)
Cron <asi@asi> /usr/local/php73/bin/php /home/asi/public_html/matomo/console core:archive --url=https://matomo.asi.asia/ > /dev/null

ERROR [2020-01-24 03:05:03] 15690  Got invalid response from API request: ?module=API&method=API.get&idSite=23&period=day&date=last2&format=php&trigger=archivephp. Response was 'a:2:{s:6:"result";s:5:"error";s:7:"message";s:99:"Mysqli statement execute error : Deadlock found when trying to get lock; try restarting transaction";}'
ERROR [2020-01-24 03:05:03] 15690  Empty or invalid response '' for website id 23, Time elapsed: 0.718s, skipping
ERROR [2020-01-24 03:05:17] 15690  2 total errors during this script execution, please investigate and try and fix these errors.
                                                                                                 
  [Exception]                                                                                    
  2 total errors during this script execution, please investigate and try and fix these errors.  

core:archive [--url="..."] [--force-all-websites] [--force-all-periods[="..."]] [--force-timeout-for-periods[="..."]] [--skip-idsites[="..."]] [--skip-all-segments] [--force-idsites[="..."]] [--skip-segments-today] [--force-periods[="..."]] [--force-date-last-n="..."] [--force-date-range[="..."]] [--force-idsegments="..."] [--concurrent-requests-per-website[="..."]] [--concurrent-archivers[="..."]] [--disable-scheduled-tasks] [--accept-invalid-ssl-certificate] [--php-cli-options[="..."]]




2)
Cron <asi@asi> /usr/local/php73/bin/php /home/asi/public_html/matomo/console core:archive --url=https://matomo.asi.asia/ > /dev/null

ERROR [2020-01-23 00:35:03] 21953  Got invalid response from API request: ?module=API&method=API.get&idSite=10&period=day&date=last2&format=php&trigger=archivephp. Response was 'a:2:{s:6:"result";s:5:"error";s:7:"message";s:99:"Mysqli statement execute error : Deadlock found when trying to get lock; try restarting transaction";}'
ERROR [2020-01-23 00:35:03] 21953  Empty or invalid response '' for website id 10, Time elapsed: 1.057s, skipping
ERROR [2020-01-23 00:35:11] 21953  2 total errors during this script execution, please investigate and try and fix these errors.
                                                                                                 
  [Exception]                                                                                    
  2 total errors during this script execution, please investigate and try and fix these errors.  

core:archive [--url="..."] [--force-all-websites] [--force-all-periods[="..."]] [--force-timeout-for-periods[="..."]] [--skip-idsites[="..."]] [--skip-all-segments] [--force-idsites[="..."]] [--skip-segments-today] [--force-periods[="..."]] [--force-date-last-n="..."] [--force-date-range[="..."]] [--force-idsegments="..."] [--concurrent-requests-per-website[="..."]] [--concurrent-archivers[="..."]] [--disable-scheduled-tasks] [--accept-invalid-ssl-certificate] [--php-cli-options[="..."]]