Ignition 7.9.9, Mysql 8.0.12 (Group Replication InnoDB Cluster) - error for storing data in sqlt_data_ table

Ignition
1

Ignition 7.9.9, Mysql 8.0.12

For tag history, the store and froward engine report the following error :

The table does not comply with the requirements by an external plugin.
All history table (sqlth_xxx) are fine, but the sqlt_data_1_2018_10 is empty…

the logger reprot the follwing error :

DatasourceForwardTransaction	03Oct2018 17:57:41	Error forwarding data
java.sql.SQLException: The table does not comply with the requirements by an external plugin.
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3976)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3912)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2530)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2683)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2486)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1858)
at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2079)
at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2013)
at com.mysql.jdbc.PreparedStatement.executeLargeUpdate(PreparedStatement.java:5104)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1998)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:102)
at com.inductiveautomation.ignition.gateway.datasource.SRConnectionWrapper$SRPreparedStatement.executeUpdate(SRConnectionWrapper.java:955)
at com.inductiveautomation.ignition.gateway.datasource.SRConnectionWrapper.runPrepUpdate(SRConnectionWrapper.java:180)
at com.inductiveautomation.gateway.tags.history.storage.TagHistoryDatasourceSink.updateSCExecRecord(TagHistoryDatasourceSink.java:560)
at com.inductiveautomation.gateway.tags.history.storage.TagHistoryDatasourceSink.storeScanClassSet(TagHistoryDatasourceSink.java:476)
at com.inductiveautomation.gateway.tags.history.storage.TagHistoryDatasourceSink.storeDataToDatasource(TagHistoryDatasourceSink.java:458)
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

Looks like it might be related to replication requiring a primary key?
https://bugs.mysql.com/bug.php?id=83165

3

@PGriffith, I use mysql 8.0.12 with the built in Group Replication InnoDB cluster.
My Ignition database connection go through a local mysql router connected to the master database for RW.

The issue is that sqlth_xxx don’t have a primary key ? :cry:
Is there a workaround to add a primary key to that table (like an Auto Incremental key) ???
or is there a solution in Ignition 8.0 ???

4

This sounds very familiar - I definitely feel like this has been encountered before. Unfortunately, I can’t find whatever it is I’m remembering.

There are currently no changes to this aspect of the historian in 8.0. Adding an auto-incrementing PK would probably work - however, adding that column to each monthly partition will be tricky…

1 Like
5

Hi @PGriffith,

I’ve taken a closer look at the compatibility issue.
sqlt_data_xxxx table has a Primary Key, but there are 2 tables in the history system with no Primary Key :
sqlth_sce and sqlth_partitions.
So, the trick is not to cumbursome as the partition data table is compatible with mysql group replication requirement.

ALTER TABLE `histo`.`sqlth_partitions` 
ADD COLUMN `pk_ai` BIGINT(20) NOT NULL AUTO_INCREMENT AFTER `flags`,
ADD PRIMARY KEY (`pk_ai`);

ALTER TABLE `histo`.`sqlth_sce` 
ADD COLUMN `pk_ai` BIGINT(20) NOT NULL AUTO_INCREMENT AFTER `rate`,
ADD PRIMARY KEY (`pk_ai`);

seem to solve the issue.

Audit tables are OK

For alarme tables

ALTER TABLE `histo`.`alarm_event_data` ADD COLUMN `pk_ai` BIGINT(20) NOT NULL AUTO_INCREMENT AFTER `strvalue`, ADD PRIMARY KEY (`pk_ai`);

2 Likes