I am transferring my site from one development site to another.
Everything is working except the following SQL
(SELECT "1" AS type, `m`.`id`, `m`.`package_name`, `m`.`ending_city`, `m`.`discount_type`, `m`.`discount_amount`, (SELECT image_name FROM `package_images` WHERE package_type="1" AND package_id=m.id AND is_temp="0" ORDER BY RAND() LIMIT 1) AS package_image, (SELECT adult_price FROM `tour_package_price` WHERE package_id=m.id AND package_type="1" AND price_type="1") AS package_price, `c`.`city_name` FROM (`tour_package_master` m) JOIN `city` c ON `c`.`id`=`m`.`ending_city` LEFT JOIN (`tour_package_date_range` d) ON m.id=d.tour_package_id WHERE `d`.`tour_start_date`>=NOW() AND `m`.`active_status` = "1" GROUP BY `m`.`id`) UNION ALL (SELECT "2" AS type, `m`.`id`, `m`.`package_name`, `m`.`ending_city`, `m`.`discount_type`, `m`.`discount_amount`, (SELECT image_name FROM `package_images` WHERE package_type="2" AND package_id=m.id AND is_temp="0" ORDER BY RAND() LIMIT 1) AS package_image, (SELECT adult_price FROM `tour_package_price` WHERE package_id=m.id AND package_type="2" AND price_type="1") AS package_price, `c`.`city_name` FROM (`weekend_tour_package_master` m) JOIN `city` c ON `c`.`id`=`m`.`ending_city` WHERE `m`.`tour_start_date`>=NOW() AND `m`.`active_status` = "1" GROUP BY `m`.`id`)
You can check the running SQL at this url
http://travelindia.india-web-design.com/welcome/test_query
when you click on the you will see the sql executing correctly.
But the same query is not working on the new server. You can check at
http://travelindia.staging-websites.com/welcome/test_query
Its getting a syntax error:
Error Number: 1064
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 'JOIN `city` c ON `c`.`id`=`m`.`ending_city` WHERE `m`.`tour_start_date`>=NOW() A' at line 1
You can check by clicking on the button.
Please find a comparison of the mysql server versions
Current Server Configurations:
Server: Localhost via UNIX socket Server type: MySQL Server version: 5.5.42-cll - MySQL Community Server (GPL)
New Server Configurations:
Server: Localhost via UNIX socket Server type: Percona Server Server version: 5.5.31-30.3-log - Percona Server (GPL), Release rel30.3, Revision 520 Protocol version: 10
Thanks in advance
I guess MySQL supports FROM (tour_package_master m) but may be Percona Server doesn't support like (FROM tour_package_master m) change it to FROM tour_package_master m
Note: Remove ( and ).
Change following lines in your query.
FROM (`tour_package_master` m)
FROM (`weekend_tour_package_master` m)
To
FROM `tour_package_master` m
FROM `weekend_tour_package_master` m