Symptom
When running a freehand SQL query containing "OPTION SQL_SELECT_LIMIT=DEFAULT" to MySQL database from SAC, the below error message appears:
"Error occurred in the SAP Analytics Cloud agent Service: "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 'OPTION SQL_SELECT_LIMIT=DEFAULT' at line 1"."
Environment
SAP Analytics Cloud
Reproducing the Issue
1- Login to SAC.
2- Go to connections.
3- Create a new SQL database connection. (MySQL or MariaDB).
4- Check the "Use freehand SQL" checkbox.
5- Go to Modeler.
7- Choose import data from datasource.
8- Choose the" use freehand SQL" option and run a query that contains OPTION SQL_SELECT_LIMIT=DEFAULT.
>>> Notice the error: "Error occurred in the SAP Analytics Cloud agent Service: "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 'OPTION SQL_SELECT_LIMIT=DEFAULT' at line 1""
Cause
This is a known bug in MySQL, please refer to this link for more information: https://bugs.mysql.com/bug.php?id=66659
Resolution
Replace OPTION SQL_SELECT_LIMIT=DEFAULT with LIMIT number_of_rows as such:
SELECT column1, column2, ... FROM table_name LIMIT number_of_rows;
Keywords
Cloud for Analytics, Cloud4Analytics, CloudforAnalytics, SAP AC, Cloud-Analytics, CloudAnalytics, SAPCloudAnalytics, Issue, sapanalyticscloud, sap analytical cloud, sap analytical cloud, limit rows, cannot, execute , KBA , LOD-ANA-AQU , Import Data Connections (Acquiring Data) , Known Error