[Skip to main content](#vessel-content) # Error with Geolocation plugin ## - **[earmaster](/forum/user/35023-earmaster "View earmaster's Profile")** - [![earmaster's Avatar](https://www.hikashop.com/media/kunena/core/svg/person.svg)](/forum/user/35023-earmaster "View earmaster's Profile") Topic Author - [ Offline](/forum/user/35023-earmaster) Less More - Posts: 136 - Thank you received: 2 - - 3 hours 3 minutes ago [\#100000474](#100000474) by [earmaster](/forum/user/35023-earmaster "View earmaster's Profile") *[Error with Geolocation plugin](/forum/25-install-update/100000093-error-with-geolocation-plugin#100000474 "-- url of the page with the problem -- : www.earmaster.com -- HikaShop version -- : 6.6.0 -- Joomla version -- : 5.4.9 Stable -- PHP version -- : 8.5.12 -- Browser(s) name and version -- : Chrome 156.0 -- Error-message(debug-mod must be tuned on) -- : Lock wait timeout exceeded; try restarting transaction We regularly got this unhandled exception on ordinary front-end pages, often non-shop pages visited by new visitors: Each occurrence means a visitor waits about 50 seconds and then gets an error page. Cause Line 327 is the cleanup query at the end of getZone(): This runs for every visitor without an existing session: every new visitor, and every bot or client that doesn't keep cookies. The table only has an index on (geolocation_type, geolocation_ref_id). So the DELETE has to scan and lock every "visit" row, not just the expired ones. On our site that's about 1.87 million rows. The IP lookup in the same function has no index on geolocation_ip either. When several new visitors arrive at the same time, these queries queue behind each other until one times out. Workaround we applied Adding two indexes solved it for us: EXPLAIN confirms the DELETE now uses idx_type_created. Suggestions * Add these indexes (or equivalent ones) to the table definition and the update script, so they survive updates and other sites get them too. * Run the purge occasionally, for example once a day or through a scheduled task, instead of on every new visitor's first page view. * Rows with geolocation_type = 'bot' are never purged. We have about 245,000 of them after a year, and the number keeps growing. * Possible typo: a few lines above the DELETE in getZone(), if(empty($zones)) looks like it should be if(!empty($zones)). In practice the fallback query below it usually finds the zone anyway, but it's worth checking.")* was created by *[earmaster](/forum/user/35023-earmaster "View earmaster's Profile")* -- url of the page with the problem -- : [ www.earmaster.com](https://www.earmaster.com) -- HikaShop version -- : 6.6.0 -- Joomla version -- : 5.4.9 Stable -- PHP version -- : 8.5.12 -- Browser(s) name and version -- : Chrome 156.0 -- Error-message(debug-mod must be tuned on) -- : Lock wait timeout exceeded; try restarting transaction We regularly got this unhandled exception on ordinary front-end pages, often non-shop pages visited by new visitors: Code: Joomla\Database\Exception\ExecutionFailureException Lock wait timeout exceeded; try restarting transaction #2 plugins/system/hikashopgeolocation/hikashopgeolocation.php(327): JFalangDatabase->execute() #3 plugins/system/hikashopgeolocation/hikashopgeolocation.php(185): plgSystemHikashopgeolocation->getZone() #4 plugins/system/hikashopgeolocation/hikashopgeolocation.php(86): plgSystemHikashopgeolocation->process() Each occurrence means a visitor waits about 50 seconds and then gets an error page. **Cause** Line 327 is the cleanup query at the end of getZone(): Code: DELETE FROM #__hikashop_geolocation WHERE geolocation_type = 'visit' AND geolocation_created < (now - 1 year) This runs for every visitor without an existing session: every new visitor, and every bot or client that doesn't keep cookies. The table only has an index on (geolocation_type, geolocation_ref_id). So the DELETE has to scan and lock every "visit" row, not just the expired ones. On our site that's about 1.87 million rows. The IP lookup in the same function has no index on geolocation_ip either. When several new visitors arrive at the same time, these queries queue behind each other until one times out. **Workaround we applied** Adding two indexes solved it for us: Code: ALTER TABLE #__hikashop_geolocation   ADD INDEX idx_type_created (geolocation_type, geolocation_created),   ADD INDEX idx_ip (geolocation_ip(45)),   ALGORITHM=INPLACE, LOCK=NONE; EXPLAIN confirms the DELETE now uses idx_type_created. **Suggestions** 1. Add these indexes (or equivalent ones) to the table definition and the update script, so they survive updates and other sites get them too. 2. Run the purge occasionally, for example once a day or through a scheduled task, instead of on every new visitor's first page view. 3. Rows with geolocation\_type = 'bot' are never purged. We have about 245,000 of them after a year, and the number keeps growing. 4. Possible typo: a few lines above the DELETE in getZone(), *if(empty($zones))* looks like it should be *if(!empty($zones))*. In practice the fallback query below it usually finds the zone anyway, but it's worth checking. Please [Log in](/login?return=aHR0cHM6Ly93d3cuaGlrYXNob3AuY29tL2ZvcnVtLzI1LWluc3RhbGwtdXBkYXRlLzEwMDAwMDA5My1lcnJvci13aXRoLWdlb2xvY2F0aW9uLXBsdWdpbj90bXBsPW1hcmtkb3du) or [Create an account](/register) to join the conversation. Board Categories HikaShop Install & Update Product & Category Display Content Creation Sales and Orders Orders management Reports & Statistics Customers Checkout Payment Methods Shipping Methods Import & Export (CSV, XLS…) Specific Features Affilation Taxes Wishlist Vote Carousel & Slider Mass Actions Filters & Search Discount & Coupon Feature Request Integrations Showcase General Talk about HikaShop Development HikaShop 3.0 Beta Translation Commercial Jobs HikaShop Components HikaMarket Market: Feature requests Market: How to? Market: Bug Report HikaSerial General talk about HikaSerial Serial: Bug Report Serial: How to? Support en Français de HikaSerial (French Support) HikaSubscription HikaAuction Auction: Feature requests Auction: How to? Auction: Bug report Hikashop around the world Support en Français (French Support) Russian FAQ. Курс выживания с Hikashop. Частые вопросы и о Расширения Hikashop Наши сайты на Hikashop 日本語版サポート(Japanese Support) Thai German Archives Bug Report How to? [](/forum/topics/feed/mode-topics?format=feed&type=rss) Time to create page: 0.055 seconds [Powered by](/forum/credits) [Kunena Forum](https://www.kunena.org) [](#top)