Resolving runUpdateQuery CREATE TABLE Errors in Ignition 7.8

Execution Scope and the Failure Boundary

A common provisioning pattern runs a gateway event script at startup. The script checks whether its tables exist in MySQL and creates any that are missing. It calls system.db.runUpdateQuery against the datasource System_Data with a statement of this form:

system.db.runUpdateQuery("CREATE TABLE IF NOT EXISTS email (EMAIL_ID int(11) NOT NULL, EmailAddress varchar(255), PRIMARY KEY (EMAIL_ID))", "System_Data")

This statement runs cleanly on Ignition 7.7.5. On 7.8.0 the same script throws an error from gateway scripts and from the Designer script editor. The gateway error text names no SQL cause. When a client button runs the identical statement, it completes with no error.

The term scope here means the runtime that executes the script. A client button runs in client scope, and the client forwards the query to the gateway, which runs it on the named datasource. Gateway event scripts (startup, timer, tag change) run inside the gateway runtime and call the database layer directly. Both paths use the same datasource and the same MySQL login. If one scope succeeds and the other fails, the SQL syntax and the MySQL server are not at fault. The fault is in the scripting function path for that scope and version.

Check 1: Run the identical statement from a client button, from the Designer script console, and from a gateway timer script. Expect the button to succeed and the table to appear in MySQL. If the button also fails, the problem is the SQL or the database login. Work through the next section before you treat it as a version defect.

Datasource Connection and MySQL Privileges

In gateway scope, a script has no default project datasource to fall back on. Pass the connection name explicitly on every call. The name is case-sensitive and must match the gateway's database connection name exactly.

Gateway startup scripts can fire before the datasource finishes connecting. A DDL call in that window fails even when the statement is correct. This fault can look the same as a version defect, so rule it out first.

DDL also needs the MySQL CREATE privilege on the target schema for the login used by the gateway connection. A login granted only SELECT/INSERT/UPDATE/DELETE passes every normal query and fails on CREATE TABLE.

1. Open the gateway web page and go to the database connections status. Confirm that System_Data reports a valid, connected state.
2. Log in to MySQL as the connection's user and run SHOW GRANTS;.
3. Confirm that the grants for the target schema include CREATE.

Check 2: Expect a valid connection status and a CREATE grant on the schema. If either one is missing, fix it and repeat Check 1 before you continue.

Full Exception Chain in Gateway Scope

The gateway log records the top-level scripting exception. The SQLException that carries the MySQL error text sits deeper in the cause chain. Wrap the call so that it catches both Java and Python exceptions, then walk getCause() down to the root. Ignition 7.x runs Jython 2.5, so use the comma form of except.

import java.lang

logger = system.util.getLogger("DBProvision")
db = "System_Data"
ddl = "CREATE TABLE IF NOT EXISTS email (EMAIL_ID int(11) NOT NULL, EmailAddress varchar(255), PRIMARY KEY (EMAIL_ID))"

try:
    system.db.runUpdateQuery(ddl, db)
    logger.info("DDL executed: email")
except java.lang.Exception, e:
    cause = e
    while cause is not None:
        logger.error("DDL failed: %s: %s" % (cause.getClass().getName(), cause.getMessage()))
        cause = cause.getCause()
except Exception, e:
    logger.error("DDL failed (Python): %s" % str(e))

Check 3: Trigger the gateway script and filter the gateway console log on DBProvision. Expect one line per level of the cause chain.

• If the lowest line is a MySQL SQLException with a server message, the database rejected the statement. Act on that message.
• If the chain ends in a scripting or framework class with no SQL cause, the failure occurred before MySQL received the statement. Record the full chain for the bug report.

Existence Check Without IF NOT EXISTS

The workaround does not depend on how the update-query path handles conditional DDL. Split the operation into two parts:

1. Run a plain read query against information_schema to test whether the table exists.
2. Issue CREATE TABLE without the IF NOT EXISTS clause, and only when the table is absent.

This adds one read per table and keeps the creation step idempotent at the script level. It also removes the ambiguous clause from the failing path.

import java.lang

logger = system.util.getLogger("DBProvision")
db = "System_Data"

tables = {
    "email": "CREATE TABLE email (EMAIL_ID int(11) NOT NULL, EmailAddress varchar(255), PRIMARY KEY (EMAIL_ID))"
}

sql = "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = ?"

for name in tables.keys():
    try:
        result = system.db.runPrepQuery(sql, [name], db)
        if result[0][0] == 0:
            system.db.runUpdateQuery(tables[name], db)
            logger.info("Created table %s" % name)
        else:
            logger.debug("Table %s exists" % name)
    except java.lang.Exception, e:
        cause = e
        while cause is not None:
            logger.error("%s: %s: %s" % (name, cause.getClass().getName(), cause.getMessage()))
            cause = cause.getCause()

DATABASE() returns the schema set as the default on the System_Data connection. If the connection URL has no default schema, replace it with the literal schema name as a second bound parameter.

Table and column names cannot be bound as ? parameters. Keep the DDL strings as fixed literals in the script, and never build them from tag values or user input.

Check 4: Drop a test table and run the gateway script. Expect a Created table log line. Run the script again and expect no create attempt and no error.

Script Placement and Version Handling

Where the DDL runs decides how often it fires and what happens when it fails.

Copying a script from a document or web page can bring in typographic quotes (“ ” ‘ ’) in place of straight ASCII quotes. Jython rejects these with a parse error before the database is ever reached. Retype every quote in the editor.

The client/gateway split on the same statement points to a regression in 7.8.0. Handle it as follows:

1. Open a support ticket with Inductive Automation. Attach the full exception chain from Check 3, the exact statement, and the scope matrix from Check 1.
2. Before you upgrade production, read the release notes of later 7.8 maintenance releases for scripting or database fixes.
3. Reproduce Check 1 on a test gateway running the candidate release.

Check 5: On the test gateway, run the original IF NOT EXISTS statement from a gateway script. If it succeeds, the upgrade clears the defect. If it still fails, keep the information_schema workaround in place.

End-to-End Verification

1. Check A: Confirm that the gateway database connection status for System_Data is valid.
2. Check B: Confirm that SHOW GRANTS for the connection user includes CREATE on the target schema.
3. Check C: Drop every managed table on a test schema and restart the project. Expect one Created table log line per table under DBProvision, with no error lines.
4. Check D: Run SHOW CREATE TABLE email; in MySQL. Expect EMAIL_ID int(11) NOT NULL, EmailAddress varchar(255), and PRIMARY KEY (EMAIL_ID).
5. Check E: Restart the project a second time. Expect only existence-check debug lines, no create attempts, and no exceptions.
6. Check F: Insert and select one row in email from a gateway script with system.db.runPrepUpdate and system.db.runPrepQuery. Expect an update count of 1 and the row returned on the select.

FAQ