I am attempting to configure tag history and database connection after doing a fair amount of development and it seems as though the database tables are not getting created despite having a valid database connection and many tags configured to use the database for history.
Using: Windows Server 2016, SQL Server 2017 Express, Ignition 7.9.7
Looking at the status for the database on the Ignition gateway everything appears to be in order (valid connection) but the total throughput only shows as 0.1 queries/sec.
Looking at the store and forward status on the Ignition gateway I see a regularly increasing quarantine count with reason āInvalid object name āsqlth_drvāā
The error above seems to indicate that it canāt find that database table and looking in SQL management studio it appears as though no tables have been added to the database by ignition.
Any suggestions to resolve this?
I am seeing these errors repeating in the diagnostics log:
E
MemoryForwardTransaction Time:
11May2018 10:06:39
Message:
Error forwarding data
ļ
Logger:
W
TagHistoryDatasourceSink Time:
11May2018 10:06:32
Message:
There is a problem checking the tag history database tables during initialization of the store and forward engine which could prevent tag history data from being forwarded properly. Trying again in 60 seconds.
ļ
Logger:
W
TagHistoryDatasourceSink Time:
11May2018 10:06:29
Message:
There is a problem checking the tag history database tables during initialization of the store and forward engine which could prevent tag history data from being forwarded properly. Trying again in 60 seconds.
ļ
Logger:
E
DatasourceForwardTransaction Time:
11May2018 10:06:21
Message:
Error forwarding data
ļ
Logger:
E
SocketIODelegate Time:
11May2018 10:06:20
Message:
Socket connection closed, DriverState was Connected.
I see there is a Create table permission denied message logged in there. Iāll confirm but had thought the user used for the connection had ownership of the DB.
Thanks for pointing me to the wrapper.log file (very helpful). I was able to identify and correct a permissions issue with the user account used for the database connection. The database tables were then created automatically.
Dear All,
Iām working with version 8.0.3 right now, I think I have face by the same problem.
My database connection is valid, and Iām already enable the tag history in the tag pane, but there is no automatic table is created in SQL.
Please inform what is the root cause , and how are you solving this issue.
I am facing this issue on Ignition 8.0.3. I deleted the sqlt_data_x_x_x tables by accident while clearing out some old historical tag data that was no longer required.
I have tag historian enabled for a few tags but I get the error
"Exception: Error running query:
TagHistory(paths=[prov:default:/tag:propulsion (port)/portmelopressure], start=Wed Dec 18 16:18:15 SGT 2019, end=Wed Dec 18 16:19:15 SGT 2019, flags=0)@0ms
On: Propulsion Overview.Root Container.Group.PME_Speed 1.Chart Control Command 3.data
caused by GatewayException: Table āsmav.sqlt_data_3_2019_12ā doesnāt exist
caused by SQLSyntaxErrorException: Table āsmav.sqlt_data_3_2019_12ā doesnāt exist
"
when I try to display tag history. I found that Ignition has not recreated the sqlt_data_x_x_x tables in mySQL.
I think there is an issue with Ignition auto creating tables in my database. How can I fix this? I have tried linking my gateway to a fresh mySQL schema but no tables were created either.
Based on my testing, it appears that Ignition does not properly handle Postgres treating CREATE TABLE as transnational. Iāve been able to create tables in user: Postgres, schema: public, but not able to create them with a user and schema that I created. I tried SET SCHEMA for the pre-connect script, but that didnāt help.
In creating tables from SQL Workbench, Iāve consistently found that COMMIT is required after CREATE TABLE. Iāmnot sure if this is a user issue, or a schema issue or?, but IMO, itās a bug.
Did you give that user ownership of or equivalent permissions in the DB containing the schema?
FWIW, I generally use the postgres account to create an empty DB owned by the desired ignition user. Then the ignition user "just works".
I've never run into that, and I use PostgreSQL all the time. Did you have a trailing semicolon on the CREATE statement? Have you searched your workbench preferences for an automatic transaction mode setting? (I usually use psql or the designer's own Query Browser, and have never had this problem.)
What workbench, BTW? The typical PostgreSQL user will be using pgAdmin4.
I ran into the same issue:
I have different schemas in my database and datasources.
Ignition created historian tables only for the first datasource and not for the others. And historian is throwing errors because the tables are not there.
I worked around it by manually adding the tables in the schemas and adding a trigger that would add the tables partitions whenever ignition adds a line in the āsqlth_partitionsā tableā¦
CREATE OR ALTER TRIGGER tai_qlt_sqlth_partitions ON SCADA.qlt.sqlth_partitions
AFTER INSERT
AS
BEGIN
DECLARE @tablename NVARCHAR(100)
SET @tablename = (SELECT TOP 1 pname FROM INSERTED )
DECLARE @insert_query NVARCHAR(MAX);
SET @insert_query =CONCAT(N'CREATE TABLE SCADA.qlt.', @tablename,' (
tagid int NOT NULL,
intvalue bigint NULL,
floatvalue float NULL,
stringvalue nvarchar(255) COLLATE Latin1_General_CI_AS NULL,
datevalue datetime NULL,
dataintegrity int NULL,
t_stamp bigint NOT NULL,
CONSTRAINT PK__sqlt_dat__', @tablename,' PRIMARY KEY (tagid,t_stamp)
)
CREATE NONCLUSTERED INDEX ', @tablename,'t_stampndx ON qlt.', @tablename,' ( t_stamp ASC )
WITH ( PAD_INDEX = OFF ,FILLFACTOR = 100 ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON )
ON [PRIMARY ] ')
EXEC sp_executesql @insert_query
END```