topic Re: You have an error in your SQL syntax in Talend Studio

You have an error in your SQL syntax

_AnonymousUser — Tue, 09 Apr 2013 18:38:43 GMT

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

Re: You have an error in your SQL syntax

Sebastiao_Qlik — Wed, 10 Apr 2013 09:49:46 GMT

Hi jclose,
which version of MySQL are you using?

Re: You have an error in your SQL syntax

_AnonymousUser — Wed, 10 Apr 2013 20:42:51 GMT

The MySQL version jclose and I are using is 5.2.47.

Re: You have an error in your SQL syntax

_AnonymousUser — Thu, 11 Apr 2013 17:29:24 GMT

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

Re: You have an error in your SQL syntax

Anonymous — Fri, 12 Apr 2013 10:58:08 GMT

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?

Re: You have an error in your SQL syntax

_AnonymousUser — Fri, 12 Apr 2013 14:35:51 GMT

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.

Re: You have an error in your SQL syntax

Anonymous — Tue, 16 Apr 2013 14:58:59 GMT

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.

Re: You have an error in your SQL syntax

Anonymous — Thu, 02 May 2013 15:28:04 GMT

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