Database Tables not Created

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.

Another post suggest that tables are automatically created when an attempt is made to push history to the database but that does not seem to be the case here.
http://forum.inductiveautomation.com/t/when-are-db-tables-automatically-created/15089/3

Can you upload the actual wrapper.log or system_logs.idb file (or PM it to me if you’re not comfortable uploading it?)
These files are in the ignition install directory, in the /logs folder; see https://support.inductiveautomation.com/index.php?/Knowledgebase/Article/View/119/0/obtaining-ignition-logs for more info.

I have PM’d you the wrapper.log file. The site would not allow me to attache the system_logs.idb file due to file size.

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.

1 Like

Yep, that’s exactly what the issue is. The system is trying to create the requisite tables but isn’t able to.

1 Like

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.

3 Likes

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.

Best Regard,
Manutchai S.

Does the wrapper log have anything to say about it?

Hi all,

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```