Huge quantity of database queries related to geokrety

[andrixnet]

On OCRO I experienced several server outages due to exhaustion of disk space.
Upon detailed investigation I discovered the following:

• MariaDB is configured to write binary logs;
• these logs got tens of GB only in a few days.
• these logs store all modifications for replication.
• the server is configured to purge binary logs after 7 days, so the problem happens quite faster.

Apache log extract:

213.28.230.62 - - [19/Sep/2023:21:15:50 +0300] "GET /okapi/services/caches/shortcuts/search_and_retrieve?langpref=en&oauth_consumer_key=E5vTL4Tg7RTsVbShVYcm&oauth_nonce=6ad4f0accb70afe14c71eb494874b2a5&oauth_signature_method=HMAC-SHA1&oauth_timestamp=1695147347&oauth_token=VqrL7TxYKtpcKy2cke95&oauth_version=1.0&retr_method=services%2Fcaches%2Fgeocaches&retr_params=%7B%22fields%22%3A%20%22code%7Cname%7Clocation%7Ctype%7Cstatus%7Cdifficulty%7Cterrain%7Csize%7Csize2%7Cdate_hidden%7Ctrackables_count%7Cowner%7Cfounds%7Cnotfounds%7Crating%7Crating_votes%7Crecommendations%7Cregion%7Ccountry2%7Cattr_acodes%7Cattrnames%7Cis_found%7Cis_recommended%7Cis_watched%22%7D&search_method=services%2Fcaches%2Fsearch%2Fbbox&search_params=%7B%22bbox%22%3A%2261.897881%7C25.471025%7C62.045661%7C25.656419%22%2C%22status%22%3A%22Available%7CTemporarily%20unavailable%22%2C%22ignored_status%22%3A%22notignored_only%22%7D&wrap=true&oauth_signature=gIlOaWqkQ6Cow%2FwmgHZATylYGe4%3D HTTP/1.1" 200 47 "-" "Mozilla/5.0 (X11; Linux x86_64; rv:9.0.1) Gecko/20100101 Firefox/9.0.1 cgeo/2023.08.24"
195.201.174.92 - - [19/Sep/2023:21:15:54 +0300] "GET /viewcache.php?cacheid=355&desclang=en&lang=en HTTP/1.1" 200 30185 "-" "serpstatbot/2.1 (advanced backlink tracking bot; https://serpstatbot.com/; [email protected])"
149.102.239.80 - - [19/Sep/2023:21:16:05 +0300] "GET /admin/usage_mudquest/usage_201409.html HTTP/1.0" 301 273 "https://promagnit.ru/" "Mozilla/5.0 (Windows NT 6.1) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/103.0.5060.114 Safari/537.36"
172.58.38.144 - - [19/Sep/2023:21:16:06 +0300] "GET /okapi/services/caches/shortcuts/search_and_retrieve?search_method=services%2Fcaches%2Fsearch%2Fbbox&search_params=%7B%22bbox%22%3A%2237.597561%7C-122.006033%7C37.802926%7C-121.820639%22%2C%22status%22%3A%22Available%7CTemporarily%20unavailable%22%7D&retr_method=services%2Fcaches%2Fgeocaches&retr_params=%7B%22fields%22%3A%20%22code%7Cname%7Clocation%7Ctype%7Cstatus%7Cdifficulty%7Cterrain%7Csize%7Csize2%7Cdate_hidden%7Ctrackables_count%7Cowner%7Cfounds%7Cnotfounds%7Crating%7Crating_votes%7Crecommendations%7Cregion%7Ccountry2%7Cattr_acodes%7Cattrnames%22%7D&wrap=true&langpref=en&consumer_key=E5vTL4Tg7RTsVbShVYcm HTTP/1.1" 200 47 "-" "Mozilla/5.0 (X11; Linux x86_64; rv:9.0.1) Gecko/20100101 Firefox/9.0.1 cgeo/2023.08.24"
172.58.38.144 - - [19/Sep/2023:21:16:11 +0300] "GET /okapi/services/caches/shortcuts/search_and_retrieve?search_method=services%2Fcaches%2Fsearch%2Fnearest&search_params=%7B%22limit%22%3A%22200%22%2C%22offset%22%3A%220%22%2C%22center%22%3A%2237.700948%7C-121.911492%22%2C%22radius%22%3A%22200%22%2C%22status%22%3A%22Available%22%2C%22found_status%22%3A%22notfound_only%22%2C%22exclude_my_own%22%3A%22true%22%7D&retr_method=services%2Fcaches%2Fgeocaches&retr_params=%7B%22fields%22%3A%20%22code%7Cname%7Clocation%7Ctype%7Cstatus%7Cdifficulty%7Cterrain%7Csize%7Csize2%7Cdate_hidden%7Ctrackables_count%7Cowner%7Cfounds%7Cnotfounds%7Crating%7Crating_votes%7Crecommendations%7Cregion%7Ccountry2%7Cattr_acodes%7Cattrnames%22%7D&wrap=true&langpref=en&consumer_key=E5vTL4Tg7RTsVbShVYcm HTTP/1.1" 400 597 "-" "Mozilla/5.0 (X11; Linux x86_64; rv:9.0.1) Gecko/20100101 Firefox/9.0.1 cgeo/2023.08.24"
195.201.174.92 - - [19/Sep/2023:21:16:15 +0300] "GET /viewcache.php?cacheid=355&desclang=en&lang=nl HTTP/1.1" 200 30328 "-" "serpstatbot/2.1 (advanced backlink tracking bot; https://serpstatbot.com/; [email protected])"

MySQL query log extract:

            set okapi_syncbase = now()
            where wp_oc in ('GC2P9MN')
                 5417 Query     DELETE FROM gk_item_waypoint WHERE id= '8059'
                 5417 Query     INSERT INTO gk_item_waypoint (id, wp)
                        VALUES ('8059', 'GC2P9MN')
                        ON DUPLICATE KEY UPDATE wp='GC2P9MN'
                 5417 Query     INSERT INTO gk_item (`id`, `name`, `distancetravelled`, `latitude`, `longitude`, `stateid`)
                VALUES ('8060', 'HARP_018 - woj szablik', '81', '', '','0')
                ON DUPLICATE KEY UPDATE `name`='HARP_018 - woj szablik', `distancetravelled`='81',
                                        `latitude`='', `longitude`='',
                                        `stateid`='0'
                 5417 Query     SELECT distinct wp FROM gk_item_waypoint
                WHERE id='8060'



            set okapi_syncbase = now()
            where wp_oc in ('')
                 5417 Query     DELETE FROM gk_item_waypoint WHERE id= '8063'
                 5417 Query     INSERT INTO gk_item (`id`, `name`, `distancetravelled`, `latitude`, `longitude`, `stateid`)
                VALUES ('8064', 'HARP_022 - woj kicarz', '4190', '', '','0')
                ON DUPLICATE KEY UPDATE `name`='HARP_022 - woj kicarz', `distancetravelled`='4190',
                                        `latitude`='', `longitude`='',
                                        `stateid`='0'
                 5417 Query     SELECT distinct wp FROM gk_item_waypoint
                WHERE id='8064'
                 5416 Query     update caches
            set okapi_syncbase = now()
            where wp_oc in ('')
                 5417 Query     DELETE FROM gk_item_waypoint WHERE id= '8064'
                 5417 Query     INSERT INTO gk_item (`id`, `name`, `distancetravelled`, `latitude`, `longitude`, `stateid`)
                VALUES ('8065', 'HARP_023 - woj koguciarz', '2094', '50.33282', '18.92832','1')
                ON DUPLICATE KEY UPDATE `name`='HARP_023 - woj koguciarz', `distancetravelled`='2094',
                                        `latitude`='50.33282', `longitude`='18.92832',
                                        `stateid`='1'
                 5417 Query     SELECT distinct wp FROM gk_item_waypoint
                WHERE id='8065'
                 5416 Query     update caches
            set okapi_syncbase = now()
            where wp_oc in ('OP273B')
                 5417 Query     DELETE FROM gk_item_waypoint WHERE id= '8065'
                 5417 Query     INSERT INTO gk_item_waypoint (id, wp)
                        VALUES ('8065', 'OP273B')
                        ON DUPLICATE KEY UPDATE wp='OP273B'
                 5417 Query     INSERT INTO gk_item (`id`, `name`, `distancetravelled`, `latitude`, `longitude`, `stateid`)
                VALUES ('8066', 'HARP_024 - woj lukacz', '147', '', '','0')
                ON DUPLICATE KEY UPDATE `name`='HARP_024 - woj lukacz', `distancetravelled`='147',
                                        `latitude`='', `longitude`='',
                                        `stateid`='0'
                 5417 Query     SELECT distinct wp FROM gk_item_waypoint
                WHERE id='8066'

---

The behaviour indicates a relation with a cron job, since these query storms seem to happen periodically.
These look like queries from updates from code and/or additional updates due to SQL triggers.
Anyway, they generate huge amounts of database modifications, hence the huge binary logs.

Also, looking at these queries, looks like data about geokrets unrelated to OCRO, which begs the question why are they processed on OCRO?

IMO OCRO should process OC <=> GeoKrety data only for geokrets located in OR caches, or at most geokrets located in Romania (geographically).

For now I disabled replication and binary logs, but it's only a temporary workaround and I am seeking a fix.
Thank you.