Ignition sqlt_data Errors: Historian, Not a Timer Script

Which process wrote the log line?

Follow the packet backwards. Every gateway log entry carries a logger name in the left column, and that name is the return address of the code that emitted it. Two families of names show up there, and they route the investigation in opposite directions.

That distinction settles the question before any searching starts. Store-and-forward operations against sqlt_data_* tables are internal to the tag historian write path; no project script sits in that loop. A timer script only becomes a suspect when the logger name is one a person chose, because Jython has no other way to produce an arbitrary label in that column.

How does a missing sqlt_data_ partition break the historian?

The write path runs tag → history storage provider → store-and-forward engine (memory buffer, then disk cache) → database connection → INSERT into a partition table named on the pattern sqlt_data_<providerId>_<YYYY>_<MM>, with the period set by the provider's partition settings. Metadata lives beside it in sqlth_te, sqlth_drv, sqlth_scinfo and sqlth_partitions.

The read path is where a dropped table bites hardest. A tag history query does not guess table names; it reads sqlth_partitions, builds the list of partitions covering the requested time range, and issues a SELECT against each one. Drop the partition tables with a database maintenance job or a manual purge and the rows in sqlth_partitions survive, so the historian keeps querying tables that no longer exist. The write path is more forgiving: it creates the next partition on demand, provided the connection user holds DDL rights on the correct schema.

Which search method actually finds the caller?

Three tools cover this ground, and none of them covers all of it. Pick by what the logger name told you.

Run grep first. It answers the whole-gateway question in one command instead of N Designer sessions, and script resources are stored as readable Python on disk, which is exactly what a hard-coded table name or a custom logger name looks like. Fall back to Designer search only for the projects grep could not clear, because that is where binary resources hide string literals. For anything about which tags feed which history provider, the tag report tool is the only instrument that reads the live tag configuration rather than files.

How do I run the search?

1. Layer one first: on the gateway status pages, confirm the database connection is Valid and pointed at the schema you believe holds the historian tables. A connection that silently resolved to the wrong catalog produces exactly the same missing-table text as a dropped partition.
2. Sweep the project tree from a shell on the gateway host:

   cd <ignition-install>/data
   grep -rn "sqlt_data" projects/
   grep -rn "testdtqLibrary" projects/
   grep -rln --binary-files=text "IgnitionHud" .

   The returned path names both the project and the resource. A gateway timer script resolves to its project's gateway-event-script resource folder, so the directory path alone identifies the timer.
3. For each project grep did not hit, open it in the Designer and run find/replace with binary resource searching enabled. The Designer searches one project at a time; that is the reason it runs second, not first.
4. Open the tag report tool and filter on the history storage provider. Export the result. Any tag returned with the provider set to the retired name is still driving writes into that provider's partition tables.
5. Cross-check the metadata against reality:

   SELECT * FROM sqlth_partitions ORDER BY start_time DESC;
   
   SELECT table_name FROM information_schema.tables
    WHERE table_name LIKE 'sqlt\_data%';

   Every partition row with no matching table is a guaranteed query failure.

How do I clear the errors and stop them recurring?

1. Back up the historian database before touching metadata.
2. Delete the orphan partition rows — match on the column holding the table name, and verify that column name against your schema before running it:

   DELETE FROM sqlth_partitions WHERE pname = '<missing_table_name>';

3. On the gateway store-and-forward status page, inspect the quarantine. Records addressed to a partition that no longer exists will retry forever; retry them once after the metadata is clean, then delete whatever still fails.
4. Grant the connection user CREATE, INSERT and SELECT on the historian schema so the engine can build the next partition without help.
5. If the logger name pointed at a script rather than the historian, edit the resource grep found: replace the hard-coded table name with a parameter, a project property, or a named query, so the next schema change fails at one location instead of inside a timer.
6. Retire the dead provider properly. Reassign or clear history on every tag from the tag report export, then remove the provider — deleting the provider while tags still reference it recreates this fault.
7. Make the next occurrence a 30-second job by putting the project and module into the logger name:

   LOGGER = system.util.getLogger(
       "%s.%s" % (system.util.getProjectName(), system.reflect.getModulePath())
   )

   The log column then names the project directly, and in library scripts the module path names the resource, so no search is needed at all.

How do I verify the fix?

1. Filter the gateway log viewer on the exact logger name from the original entry and watch it for a full partition period. No new entries means the emitter is genuinely gone, not just quieter.
2. Confirm the store-and-forward quarantine count is zero and stays zero, with the forward rate tracking the sink rate.
3. Query information_schema again and confirm the current-period sqlt_data_ table exists and its row count increments between two reads a minute apart — that proves the engine holds DDL rights and the write path is intact.
4. Re-run the grep sweep for the dropped table name and the retired provider name. Zero hits across projects/ and the gateway config confirms nothing else still references them.
5. Run a tag history query that spans the boundary between the deleted range and the live partition. It must return current data and complete without a missing-table exception for the historical portion; a data gap there is expected, an error is not.

FAQ