Tell us what you are trying to do?
We have three instances of the same site.
test.sitename.com - Test Site
staging.sitename.com - Staging Site for a New Version
sitename.com (live.sitename.com) - Live Site
All three have the same map views and their settings which haven't been touched in 4 months.
All three have the same same versions of WordPress and Toolset plugins (up-to-date).
Two have the same Google Maps API key, but all Google Maps API settings are basically the same.
On the live site (and only the live site) searching certain locations results in different (and very broken) results.
Searching the same location on Staging Site or Test Site and you get the correct results.
These sites are virtually identical - especially Live and Staging which have all the same plugins, plugin versions, settings, and API keys. The biggest difference is that the Map View on the Live site has been getting a great deal more use in the last month.
Is there any documentation that you are following?
This problem seems unique.
Is there a similar example that we can see?
No, but in my next private post I can share specific instructions to recreate the results on each.
What is the link to your site?
I'll need to whitelist your IP address to share all three sites.
Please set my next post to private.
Okay I can take a look at the three sites. Please let me know the URLs where I can find the maps on the sites. I will activate private reply fields here, then share my IP in another private message.
Oh and please let me know the specific search criteria that are showing irregular results on the live site. Thanks!
I forgot to add "Pages" to the Access Control Level for Toolset Supporter. Please try again.
Okay I'm able to see now, thanks. I'll continue investigating tomorrow and give you an update, as my shift is ending soon. I can see the test site has appx. 250k entries in the maps location cache, which makes maps cache management in wp-admin > Toolset > Settings > Maps tab basically unusable. I assume this is coming from User location-based distance searches. The live site has considerably more, appx. 395k entries in the maps location cache. My first guess is this could be part of the problem, but I'll need to continue my investigation tomorrow.
After a bit more investigation, I can't find anything obviously wrong in the live site that would be causing this problem. I'd like to run a few more tests but I don't want to impact the live site, and the problem isn't currently reproducible in either of the development environments you've provided. Is it possible to get one of the development environments updated with the same database as the live site? That would include the larger maps cache, which I suspect could be related to the issue here.
Thanks for recognizing the nature of the live site - not being a place to try things out. 🙂 We're in the process of syncing the Test Site up with the current Live Site. Please refrain from using either while this process is running. We'll let you know as soon as it's ready.
This of course means that on the Test Site you'll stop returning the correct results, but I can grant you access to the Staging Site which is also getting correct results. I'll set it up with the same login information and have it ready when Test has been updated.
Please standby.
Understood, I'm standing by for your updates.
You're all good to go.
Test is now in sync with Live, and I confirm the Lola, KS search is returning the incorrect results on Test, post-update.
You also now have access to Staging with the same credentials so you can check out the correct results.
Okay thanks, I ran some additional tests in the test environment but wasn't able to pin anything down as the cause of the problem. So I've made a clone of that site and installed it on my local environment to confirm the problem. I'm in the process of escalating this issue to my 2nd tier support team for additional investigation, and I'll update you when I have some feedback from the team.
Okay I have some feedback from my 2nd tier support team and our developers. The issue does seem to be related to the size of the maps address cache table. Since we cache the source location for all distance-based queries but do not provide an automated process for removing stale cache items, the cache table is becoming overloaded and several distance-based searches are therefore failing. A manual database intervention is required here, since the maps cache table isn't very useful in wp-admin in this case. A custom SQL query to delete some of those stale cache items in our local tests produced the desired search results, including the search for Iola, KS USA. My 2nd tier support team shared this SQL query you can use:
DELETE FROM `wp_toolset_maps_address_cache` WHERE `address_passed` NOT REGEXP '[a-z]'
You must modify the database table prefix to match the environment where you run the query ( I think the prefixes are different in each environment but you'll need to verify that ). That will drop about 380k stale items from the maps address location cache table in this environment. We suspect you'll need to run a similar query periodically as site traffic continues in the near future.
Would you like to run this query in the Test environment to verify it's working as expected? I don't have direct DB access so that's something you would need to handle in phpMyAdmin or another MySQL admin application.
Our developer provided a refined SQL snippet that will remove cached items more completely, though it is a more "expensive" query to run:
DELETE wp_toolset_maps_address_cache
FROM wp_toolset_maps_address_cache
LEFT JOIN wp_postmeta
ON wp_toolset_maps_address_cache.address_passed = wp_postmeta.meta_value
AND wp_postmeta.meta_key IN ('wpcf-dealer-address','wpcf-dealer-address-contact','wpcf-address-contact-crm-bdt','wpcf-address-contact-crm-ss','wpcf-address-contact-hd-bs','wpcf-address-contact-hd-rc','wpcf-address-contact-hd-ss')
WHERE wp_postmeta.meta_value IS NULL
Again, you need to update the wp_ table prefixes per environment DB.
If the first query has not yet been run, this query will drop all the stale cached User locations, so the first query is not necessary. If the first query has already been run on the Test environment, that's no problem. You can run this query as a follow-up to drop more items from the cache that were not already dropped by the first query.
What this query does is it drops any cached items that were not directly created from address custom field values. In essence, it will drop the cached items that represent stored User locations and stored search locations, not any addresses that were entered in custom fields.
We carried out the purge on Test and it was successful. We'd like to keep this ticket open until we have successfully completed the operation on Live.
Is this something we're going to have to run on phpmyadmin as need (probably twice yearly)? Is Toolset planning to improve on this in the settings menu with automation?
We carried out the purge on Test and it was successful. We'd like to keep this ticket open until we have successfully completed the operation on Live.
Okay glad to hear that was successful. I'll stand by here for your update.
Is this something we're going to have to run on phpmyadmin as need (probably twice yearly)?
Yes the recommended approach, for now, is to clear out those stale cache items by running this script periodically in the live environment. I can't really speak to the schedule - it's totally up to you and what you estimate based on site traffic patterns. I don't think the benefit of holding on to those cached locations outweighs the potential negative impacts of broken search results, so I would aim for a low cache size and more frequent purges. Please also note that each custom address field in your site is included explicitly in this script, so if you add more address fields to the site in the future you should update the custom code to prevent those items from being dropped inadvertently from the cache. Add those field slugs here, using the wpcf- prefix, in a comma-separated list:
...
AND wp_postmeta.meta_key IN ('wpcf-dealer-address','wpcf-dealer-address-contact','wpcf-address-contact-crm-bdt','wpcf-address-contact-crm-ss','wpcf-address-contact-hd-bs','wpcf-address-contact-hd-rc','wpcf-address-contact-hd-ss')
...
Is Toolset planning to improve on this in the settings menu with automation?
We already have plans to improve automation of address caching to help solve problems with imported addresses that are not automatically cached, and now that you've reported another issue related to this caching table we have tentatively linked those two tickets in our queue. It makes sense to try to tackle those issues together since they're closely related and live in the same cache table of the database. I can update the ticket here down the road when we have changes ready to go out, at which time you could discontinue running the script manually. I can't say for sure exactly what the solution will be yet, but I'd assume both those tasks would have WP cron components and some level of GUI update in Toolset > Settings > Maps.
Sorry I meant to set the ticket to stay open pending your update. I'm updating the status here now to reflect that.
