I'm using Kettle 4.1 with MySql 5.6 and am trying to run the chapter 4 demo from the Pentaho Kettle Solutions: Building Open Source ETL Solutions with Pentaho Data Integration and am having the "Dimension Lookup / Update" step fails with the 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=1' at line 1
How to resolve this issue?
You need to upgrade the version MySql Connector that Kettle is using. Go to your Kettle installation's libext\JDBC folder and replace mysql-connector-java-3.1.14-bin.jar with a newer version of Connector/J downloaded from MySql's site. Just extract the newer version from the zip and copy it over and delete the older version. Then rerun and it should work.
Personally, it failed with version 5.1.6 of the Connector, but it worked with 5.1.23 (the currently lastest version) YMMV.
