GatewayException: You have an error in your SQL syntax;

[Tom_Cobb]

I have a transaction group connecting to a MySQL database. It is getting messages about syntax errors in the SQL queries, but I can only see a snippet of the query. Enabling query logging in the MySQL database does not capture the attempted updates. I have searched for options to log the queries for my transaction group but I see none.

The database connection status on the gateway UI shows green and says "valid"

The ignition platform version is 7.6.7. The gateway is running on a Windows Server 2016.

When I run the query "SELECT * from group_table limit 1" in the Database Query Browser (group_table exists), I get the error:

GatewayException: 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 'FETCH FIRST 1000 ROWS ONLY' at line 1
	caused by SQLSyntaxErrorException: 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 'FETCH FIRST 1000 ROWS ONLY' at line 1

Ignition v7.6.7 (b2014082615)
Java: Oracle Corporation 1.8.0_331

I get this same error no matter what query I run.

I have tried downloading and re-installing the JDBC driver and re-creating the transaction group. I put the required config entries into the JDBC driver:

Connect URL:

jdbc:mysql://<the lan ip>:3306/<the db name>

and "Extra Connection Properties":

zeroDateTimeBehavior=CONVERT_TO_NULL;connectTimeout=120000;socketTimeout=120000;useSSL=false;allowPublicKeyRetrieval=true;

But nothing helps. Can anyone point me at something else to try?

[flexware]

That is a strange error - it reminds me of a time I ran into something similar, and it ended up being an invalid Validation query in either my database connection or driver settings. I don't know if your issue has anything to do with this, but I'm just going to throw this out there in case it points you in the right direction.

First of all, I don't believe

FETCH FIRST 1000 ROWS ONLY

is valid MySQL syntax - that looks more like Oracle syntax. Here are the places in the gateway I would check - of course, you would need to look in the corresponding 7.6.7 versions of these configuration pages.

1. Config->Databases->Drivers->MySQL->Edit
   
   
2. Same page, but switch tabs from "JDBC Drivers" to "Translators"
   
   
3. Go to your database Connection's settings, then expand Advanced Properties, then check this Validation Query field:
   
   

[Tom_Cobb]

THANK YOU!!!! In checking all the areas you told me to check I noticed that under Advanced Properties, the Translator was set to "DB2" rather than 'MySQL'. Changed that and it started working.