Updated from MySQL 5.1 to 5.5 - all good. I was aware of the differences and sites accepted the upgrade except one.
But the query is too complex for me to see what is wrong...
Below is the working in 5.1
SELECT DISTINCT SQL_CACHE *
FROM Regions
WHERE (region_id IN
(SELECT SQL_CACHE included_region_id AS region_id
FROM Contains_areas
WHERE region_id =
(SELECT toplevel_region_id AS region_id
FROM Sites
WHERE site_url = 'http://www.domainname.com/' LIMIT 1))
OR (region_id =
(SELECT toplevel_region_id AS region_id
FROM Sites
WHERE site_url = 'http://www.domainname.com/' LIMIT 1))
AND user_id='1')
ORDER BY region ASC
But in 5.5, the error is
"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 'AS region_id FROM Contains_areas WHERE region_id IN ( SELE' at line 26"
I would appreciate if the error could be identified as I have searched for the answer, but this query is beyond me.
Thanks in advance
SQL_CACHE and SQL_NO_CACHE in subquery are no longer permitted in MySQL 5.5.3:
The SQL_CACHE and SQL_NO_CACHE options affect caching of query results in the query cache (see Section 8.9.3, “The MySQL Query Cache”). SQL_CACHE tells MySQL to store the result in the query cache if it is cacheable and the value of the query_cache_type system variable is 2 or DEMAND. With SQL_NO_CACHE, the server does not use the query cache. It neither checks the query cache to see whether the result is already cached, nor does it cache the query result. (Due to a limitation in the parser, a space character must precede and follow the SQL_NO_CACHE keyword; a nonspace such as a newline causes the server to check the query cache to see whether the result is already cached.)
For views, SQL_NO_CACHE applies if it appears in any SELECT in the query. For a cacheable query, SQL_CACHE applies if it appears in the first SELECT of a view referred to by the query.
As of MySQL 5.5.3, these two options are mutually exclusive and an error occurs if they are both specified. Also, these options are not permitted in subqueries (including subqueries in the FROM clause), and SELECT statements in unions other than the first SELECT.