You have an error in your SQL syntax

[_AnonymousUser]

I setup the mysql sample database tbi in order to run the tutorial examples. When I attempt to run a select against the Customer table (SELECT * FROM `tbi`.`customer` WHERE (`gender` = '2')) I get an error "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=100' at line 1". I tried removing the limit feature and received a similar error. Since this is related to the online tutorial are you aware of a MySQL patch that I need to apply?
Thank you

[Sebastiao_Qlik]

Hi jclose,
which version of MySQL are you using?

[_AnonymousUser]

The MySQL version jclose and I are using is 5.2.47.

[_AnonymousUser]

Confirmed 5.2.47 - latest version - installed Monday from the website.

[Anonymous]

Did you run an anlaysis, or you were runing the sql query directly in sql explorer query editor?
What is your the sql query string looks like?

[_AnonymousUser]

I ran an analysis per the tutorial then right clicked on one of the results and selected 'View Rows'. The SQL editor window popped up with the generated SQL statement, I ran the query and then the error appeared. SELECT * FROM `tbi`.`customer` WHERE (`gender` = '2').
I tried it again with a simple select * and got the same error. I tried removing the select Max records at top and got a different but very similar error.
I'm running Tutorial 58 and the GenderAna analysis.

[Anonymous]

Lets try to separate potential database setup and access issues from product issues. Please try running the query:
SELECT * FROM `tbi`.`customer` WHERE (`gender` = '2');
from the MySQL workbench - outside of the DQ studio and see if you get results.

[Anonymous]

This turns out to be a MySQL version issue. If use are using MySQL v5.6, then when you right click -> Preview Table in the DB metadata for any MySQL table, you will see an error.
When using the MySQL database, the command passed to MySQL from Talend profiling queries includes a row limit option with the following syntax:
set OPTION SQL_SELECT_LIMIT = 100; -- (note: doesn?t have to be 100, can be any number)
This command works in MySQL versions up through MySQL v5.5.
In MySQL v5.6, the OPTION keyword is removed, and the correct command is:
set SQL_SELECT_LIMIT = 100;
So the Talend profiling query gives a MySQL database error due to the inclusion of the 'OPTION' keyword when run against v5.6.
The workaround is to uncheck the Limit Rows checkbox in the generated query. The other workaround is to use MySQL v5.5.
The JIRA for this: https://jira.talendforge.org/browse/TDQ-7171