Ignition 8.1.0 - default Error executing annotation query with powerChart component

mazeyrat · November 10, 2020, 4:36pm

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)

Ticket
#134078

matt.eaton · January 8, 2021, 5:18pm

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)

PGriffith · January 8, 2021, 6:16pm

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.

matt.eaton · January 8, 2021, 6:38pm

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.

PGriffith · January 8, 2021, 10:15pm

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

isabellapitco · April 26, 2021, 9:12pm

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

c-w-t · April 27, 2021, 4:11pm

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).

justin.casleton · May 5, 2021, 3:02pm

Same issue anyone find a solution?

PGriffith · May 10, 2021, 11:34pm

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”.

weertske · May 11, 2021, 5:05pm

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).

PGriffith · May 11, 2021, 5:22pm

Something like this should work:

CREATE TABLE `sqlth_annotations` (
	`id` INT NOT NULL AUTO_INCREMENT,
	`annotationid` VARCHAR,
	`tagid` INT,
	`start_time` BIGINT,
	`end_time` BIGINT,
	`type` VARCHAR,
	`datavalue` VARCHAR,
	PRIMARY KEY (`id`)
);

weertske · May 11, 2021, 5:31pm

Thank you.

weertske · May 11, 2021, 5:46pm

@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?

PGriffith · May 11, 2021, 5:50pm

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.

c-w-t · June 11, 2021, 3:09pm

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!

PGriffith · June 11, 2021, 3:31pm

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.

c-w-t · June 11, 2021, 4:32pm

Ok thanks for the clarification.

son122 · February 3, 2022, 6:31pm

@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,
PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

PGriffith · February 3, 2022, 11:04pm

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.