Part of the Pins Not Showing Up for uws_at issue.

Rank Field Conflict With MySQL 8

While changed settings is an issue and still needs to be investigated, the main culprit here is the upgrade from MySQL 5 to MySQL 8. Servers were upgraded to MySQL 8 as the prior release we no longer supported. Amazon RDS services dropped support for the older MySQL version with a hard cut off in the fall of 2024. As such we were forced to upgrade MySQL; A good thing overall but it involved a lot of data query and code updates and testing.

MySQL added the work “rank” as a reserved word. This conflicts with our Professional an Enterprise feature that has used rank as a standard field name for a decade. Turns out we missed on location where we need to specially mark this field in the data query.

Any sites that have, or had, a location with the rank field set may experience an issue with initial location results not appearing.

Resolved in the 2502.24.01 update to the Experience module.
Testing underway 25/02/24 13:00 EST.
On production as of 25/02/24 14:45 EST.

Dev notes

MySQL 8 Data Query Investigation

\SLP_AJAX::find_locations()

query_slug: 'standard_location_load'
query_statements:
Array
(
    [0] => selectall_initial_distance
    [1] => where_default_validlatlong
    [2] => where_initial_distance
)

Query sent to DB:
SELECT *, sl_initial_distance AS sl_distance FROM wp_718_store_locator  
LEFT JOIN wp_718_slp_extendo USING(sl_id)  

WHERE   
sl_latitude REGEXP '^[0-9]|-' 
AND sl_longitude REGEXP '^[0-9]|-'  
AND  ( NOT sl_private OR sl_private IS NULL)   
AND  ( (sl_initial_distance < 1500) OR (sl_initial_distance IS NULL) )      

ORDER BY coalesce(featured,0) desc,case when rank is null then 99999 when rank = 0 then 99999 when rank > 0 then rank end asc,sl_distance asc,sl_initial_distance asc LIMIT 70

query params for DB select:
Array
(
    [0] => 1500
    [1] => 70
)
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'is null then 99999 when rank = 0 then 99999 when rank > 0 then rank end asc,sl_d' at line 1

Could be from \SLP_Experience_AJAX::create_string_CustomSQLOrder()