Mysql history tag

General Discussion
1

Hi,
I configured the replication function for mysql server, using
the MASTER - MASTER function and everything works correctly
in the project and in the database replication:

# for ID 1
server-id = 1
auto-increment-increment = 2
auto-increment-offset = 1

# for ID 2
server-id = 2
auto-increment-increment = 2
auto-increment-offset = 2

Unfortunately I noticed that the creation of the tables
related to the tag history many errors:

Caused by: java.sql.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 '"id" int IDENTITY(1,1),"name" nvarchar(255),"provider" nvarchar(255),PRIMARY KEY' at line 1
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
at com.mysql.cj.jdbc.StatementImpl.executeUpdateInternal(StatementImpl.java:1355)
at com.mysql.cj.jdbc.StatementImpl.executeLargeUpdate(StatementImpl.java:2128)
at com.mysql.cj.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1264)
at org.apache.commons.dbcp2.DelegatingStatement.executeUpdate(DelegatingStatement.java:322)
at org.apache.commons.dbcp2.DelegatingStatement.executeUpdate(DelegatingStatement.java:322)
at om.inductiveautomation.ignition.gateway.datasource.DelegatingStatement.executeUpdate(DelegatingStatement.java:80)
at com.inductiveautomation.ignition.gateway.datasource.SRConnectionWrapper$SRStatement.executeUpdate(SRConnectionWrapper.java:857)
at com.inductiveautomation.ignition.gateway.db.schema.DBTableSchema.executeCommandSet(DBTableSchema.java:138)

The are a solution ?
thank you

Ignition 8
Windows server 2016
MySql server 5.7.17

2

That’s not the full body of the error message - can you copy the entire thing, or upload a wrapper.log directly here?

3

1:

java.sql.SQLSyntaxErrorException: Table ‘automazione.sqlth_drv’ doesn’t exist
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:955)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeQuery(ClientPreparedStatement.java:1005)
at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:122)
at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:122)
at com.inductiveautomation.ignition.gateway.datasource.SRConnectionWrapper$SRPreparedStatement.executeQuery(SRConnectionWrapper.java:948)
at com.inductiveautomation.ignition.gateway.datasource.SRConnectionWrapper.runPrepQuery(SRConnectionWrapper.java:166)
at com.inductiveautomation.gateway.tags.history.storage.TagHistoryDatasourceSink.loadOrCreateSystemDetails(TagHistoryDatasourceSink.java:402)
at com.inductiveautomation.gateway.tags.history.storage.TagHistoryDatasourceSink.getSystemDetails(TagHistoryDatasourceSink.java:364)
at com.inductiveautomation.gateway.tags.history.storage.TagHistoryDatasourceSink.getProviderId(TagHistoryDatasourceSink.java:339)
at com.inductiveautomation.gateway.tags.history.storage.TagHistoryDatasourceSink.storeScanClassSet(TagHistoryDatasourceSink.java:471)
at com.inductiveautomation.gateway.tags.history.storage.TagHistoryDatasourceSink.storeDataToDatasource(TagHistoryDatasourceSink.java:457)
at com.inductiveautomation.ignition.gateway.history.sf.sinks.AbstractDatasourceSink.storeToDatasource(AbstractDatasourceSink.java:137)
at com.inductiveautomation.ignition.gateway.history.sf.sinks.AbstractDatasourceSink.storeData(AbstractDatasourceSink.java:115)
at com.inductiveautomation.ignition.gateway.history.sf.sinks.AggregateSink.storeData(AggregateSink.java:170)
at com.inductiveautomation.ignition.gateway.history.forwarders.ForwarderThread.run(ForwarderThread.java:149)

2:

java.lang.Exception: Error running query: CREATE TABLE sqlth_drv (“id” int IDENTITY(1,1),“name” nvarchar(255),“provider” nvarchar(255),PRIMARY KEY CLUSTERED (“id”))
at com.inductiveautomation.ignition.gateway.db.schema.DBTableSchema.executeCommandSet(DBTableSchema.java:140)
at com.inductiveautomation.ignition.gateway.db.schema.DBTableSchema.createTable(DBTableSchema.java:109)
at com.inductiveautomation.ignition.gateway.db.schema.DBTableSchema.verifyAndUpdate(DBTableSchema.java:91)
at com.inductiveautomation.gateway.tags.history.storage.TagHistoryDatasourceSink.checkTables(TagHistoryDatasourceSink.java:1110)
at com.inductiveautomation.gateway.tags.history.storage.TagHistoryDatasourceSink.initialize(TagHistoryDatasourceSink.java:237)
at com.inductiveautomation.gateway.tags.history.storage.TagHistoryDatasourceSink.lambda$initialize$0(TagHistoryDatasourceSink.java:249)
at com.inductiveautomation.ignition.common.execution.impl.BasicExecutionEngine$ThrowableCatchingRunnable.run(BasicExecutionEngine.java:518)
at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)
at java.base/java.util.concurrent.FutureTask.run(Unknown Source)
at java.base/java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(Unknown Source)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at java.base/java.lang.Thread.run(Unknown Source)
Caused by: java.sql.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 ‘“id” int IDENTITY(1,1),“name” nvarchar(255),“provider” nvarchar(255),PRIMARY KEY’ at line 1
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
at com.mysql.cj.jdbc.StatementImpl.executeUpdateInternal(StatementImpl.java:1355)
at com.mysql.cj.jdbc.StatementImpl.executeLargeUpdate(StatementImpl.java:2128)
at com.mysql.cj.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1264)
at org.apache.commons.dbcp2.DelegatingStatement.executeUpdate(DelegatingStatement.java:322)
at org.apache.commons.dbcp2.DelegatingStatement.executeUpdate(DelegatingStatement.java:322)
at com.inductiveautomation.ignition.gateway.datasource.DelegatingStatement.executeUpdate(DelegatingStatement.java:80)
at com.inductiveautomation.ignition.gateway.datasource.SRConnectionWrapper$SRStatement.executeUpdate(SRConnectionWrapper.java:857)
at com.inductiveautomation.ignition.gateway.db.schema.DBTableSchema.executeCommandSet(DBTableSchema.java:138)

4

Hi,
I had already inserted the last connector for java mysql-connector-java-8.0.16.jar
and in any case report the solution:

  1. Stop all database services, Journal, History etc.
  2. Delete the existing connection to the database
  3. Delete the installed drivers Config> Database> Database Drivers & Settings which refer to mysql
  4. Reinstall the mysql-connector-java-8.0.16.jar java connector
  5. Create a new connection with MariaDB > MySQL and NOT MySQL > Generic
  6. Enable Journal, History, etc. services
  7. Restart the Ignition service
1 Like