Ignition 8.1.0 - default Error executing annotation query with powerChart component

Ignition 8.1.0
MYSQL 8.0.20
JDBC Driver: mysql-connector-java-8.0.20.jar

When I use the perspective powerChart component, every seconds I have the following error in the gateway logs.

DB	10Nov2020 17:30:16	default Error executing annotation query.
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 ')' 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.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953)

at com.mysql.cj.jdbc.ClientPreparedStatement.executeQuery(ClientPreparedStatement.java:1003)

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:1008)

at com.inductiveautomation.gateway.tags.history.query.DatasourceAnnotationQuery.execute(DatasourceAnnotationQuery.java:119)

at com.inductiveautomation.gateway.tags.history.module.TagHistoryProvider.queryAnnotations(TagHistoryProvider.java:275)

at com.inductiveautomation.ignition.gateway.sqltags.history.TagHistoryManagerImpl.queryAnnotations(TagHistoryManagerImpl.java:665)

at com.inductiveautomation.ignition.gateway.tags.model.ProjectDefaultTagManagerFacade.queryAnnotations(ProjectDefaultTagManagerFacade.java:441)

at com.inductiveautomation.perspective.gateway.components.PowerChartModelDelegate$AnnotationFetch.fetch(PowerChartModelDelegate.java:229)

at com.inductiveautomation.perspective.gateway.comm.FetchableCacheImpl.fetch(FetchableCacheImpl.java:31)

at com.inductiveautomation.perspective.gateway.comm.Routes.handleFetch(Routes.java:1510)

at com.inductiveautomation.ignition.gateway.dataroutes.Route.service(Route.java:252)

at com.inductiveautomation.ignition.gateway.dataroutes.RouteGroupImpl.service(RouteGroupImpl.java:61)

at com.inductiveautomation.ignition.gateway.dataroutes.RouteGroupCollectionServlet.serviceInternal(RouteGroupCollectionServlet.java:54)

at com.inductiveautomation.ignition.gateway.dataroutes.AbstractRouteGroupServlet.service(AbstractRouteGroupServlet.java:38)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)

at org.eclipse.jetty.servlet.ServletHolder$NotAsyncServlet.service(ServletHolder.java:1391)

at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:760)

at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:547)

at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:143)

at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:590)

at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:127)

at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:235)

at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:1607)

at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:233)

at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1297)

at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:188)

at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:485)

at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:1577)

at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:186)

at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1212)

at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141)

at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:127)

at org.eclipse.jetty.rewrite.handler.RewriteHandler.handle(RewriteHandler.java:322)

at org.eclipse.jetty.server.handler.HandlerList.handle(HandlerList.java:59)

at org.eclipse.jetty.server.handler.HandlerCollection.handle(HandlerCollection.java:146)

at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:127)

at org.eclipse.jetty.server.Server.handle(Server.java:500)

at org.eclipse.jetty.server.HttpChannel.lambda$handle$1(HttpChannel.java:383)

at org.eclipse.jetty.server.HttpChannel.dispatch(HttpChannel.java:547)

at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:375)

at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:270)

at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:311)

at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:103)

at org.eclipse.jetty.io.ChannelEndPoint$2.run(ChannelEndPoint.java:117)

at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.runTask(EatWhatYouKill.java:336)

at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.doProduce(EatWhatYouKill.java:313)

at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.tryProduce(EatWhatYouKill.java:171)

at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.run(EatWhatYouKill.java:129)

at org.eclipse.jetty.util.thread.ReservedThreadExecutor$ReservedThread.run(ReservedThreadExecutor.java:388)

at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:806)

at org.eclipse.jetty.util.thread.QueuedThreadPool$Runner.run(QueuedThreadPool.java:938)

at java.base/java.lang.Thread.run(Unknown Source)



I have the same issue but only when using the power chart with a filter path, if I remove the filter path there’s no error. (troubleshooting only, that won’t be an acceptable solution)

Is that the full text of the error message? Can you try setting the gateway.database.selects logger to TRACE (briefly, it’ll throw a lot of messages into the logs) and capturing a log export to upload or send in to support.

Yes, that’s the full text.
Yes I can put it into trace and see what we get.
I am not sure if I can get the logs out of the client machine, but I’ll ask if we’ve got a reasonable method to get files out and see what I can do.
Is there a ticket or attention line you want me to mention on submission? I don’t have a ticket started on the subject, just this forum stuff.

Ideally get in contact with support with the logs - you can mention this forum thread and they’ll chase me down.

I’m having the same issue, was this ever resolved?

We have similar errors being logged, we have a 8.1.4 Perspective licensed server that doesn’t have the Tag History module licensed, but it’s connected to a 7.9.17 gateway that does, and I suspect this is an issue with the front end 8.1.4 server not being able to perform annotation queries against the DB that another gateway’s historian is linked to (either intentionally or not).

Same issue anyone find a solution?

Are all you reporting issues querying data from older (pre 8.1) historians?
If so, then this is probably an easy enough fix on our end, to just check the version of the target historian. If not, then there might be multiple fundamental issues here, so we’ll need more data - which brings me back to “please contact support”.

Paul, At first I thought that checking version would work. It will certainly get rid of the error filling my log. However, if we try to use any portion of the Power Chart that leverages annotations we are still going to break. I believe the only solution no matter how ugly it is, is to add that table to all our historians.

Care to share a MySQL Create statement (not sure about data types).

Something like this should work:

CREATE TABLE `sqlth_annotations` (
	`annotationid` VARCHAR,
	`tagid` INT,
	`start_time` BIGINT,
	`end_time` BIGINT,
	`type` VARCHAR,
	`datavalue` VARCHAR,

Thank you.

@PGriffith I have a couple issues regarding the “Create” statement. According to the SDK it should be “datavalue” not “data_value”. Which is correct?

Also since this field will carry the actual annotations, should it be a TEXT type to allow for very large entries?

datavalue is correct; that was just a mistake on my part translating from Java code to actual SQL syntax. TEXT might be more suitable, but our actual internal translator will just create a VARCHAR column.

Just to clarify, is it meant to be possible for a 8.1.x server (without tag history license) to execute annotation queries against a remote tag history provider? And if so, should it work when that remote gateway is running 7.9.17? This is what our setup would require. Thanks!

No, you can’t issue any kind of historian query without a tag history license (as of 8.1, where we tightened up some restrictions, iirc).
The remote gateway running 7.9.17 will not have the ability to query or store new annotations - the manual table creation described here is just to suppress an error until we change things first-party.

1 Like

Ok thanks for the clarification.

@PGriffith Does it matter whether the datatype is VARCHAR or NVARCHAR?
This is what our annotation table looks like and we’re getting heavily hit with that message every few second.

CREATE TABLE [dbo].[sqlth_annotations](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[tagid] [int] NULL,
	[start_time] [bigint] NULL,
	[end_time] [bigint] NULL,
	[type] [nvarchar](255) NULL,
	[datavalue] [nvarchar](255) NULL,
	[annotationid] [nvarchar](255) NULL,
	[id] ASC

It shouldn’t matter, but if you upgrade the 8.X gateway originating queries past 8.1.10 this won’t happen anymore. I’d recommend that instead.