Symptom-to-Branch Map
An INSERT INTO Database2.TableName(Value1, Value2, Value3) SELECT FirstValue, SecondValue, ThirdValue FROM Database1.TableName; statement fails in Ignition for one of two reasons. The first is how Ignition hands the statement to JDBC. The second is where the two schemas physically live. The same statement can run cleanly in a MySQL console and still fail from a Named Query or script. The table below tells you which check to start with.
| What you see | Likely cause | Go to |
|---|---|---|
| Error says the statement does not return a result set, or the Named Query test fails on an otherwise valid INSERT | Statement sent as a query instead of an update | Check 3 |
| Works when both schemas are on the same MySQL host, fails once the source moves to a remote server | A single statement cannot span two JDBC connections | Check 1, then Check 4 |
| Error says the source schema or table does not exist, but it clearly exists on the remote server | Local MySQL server is resolving Database1 against its own catalog |
Check 1 |
| Access denied on one schema only | Gateway connection account lacks grants on the second schema | Check 2 |
| Cross-server copy works but takes minutes and blocks other gateway scripts | Row-by-row inserts, one network round trip per row | Check 4, then Procedure |
Check 1: Physical Location of Both Schemas
Before anything else, confirm whether Database1 and Database2 are served by the same MySQL instance. A named database connection in the Ignition Gateway is one JDBC pool pointed at one host and port. Ignition does no SQL parsing across connections. It passes the statement text to that one server, and that server has to resolve every table the statement names.
- In the Gateway web interface, open the database connections list. Record the host and port in the connect URL for every connection involved.
- Through the connection you plan to run the INSERT on, execute
SELECT @@hostname, @@port;. Then runSHOW DATABASES;and confirm both schema names appear in the result.
Both schemas listed on one instance: the schema-qualified INSERT INTO...SELECT is valid. In MySQL a "database" is a schema, and the connection's default schema is only a default. Qualified names reach any schema on that server the account can access. Go to Check 2.
Source schema missing from that instance: the source lives on another host. No single statement run through one Ignition connection can read it, so rewording the SQL will not help. Skip to Check 4.
Check 2: Grants for the Gateway Connection Account
A same-server cross-schema INSERT needs SELECT on the source schema and INSERT on the destination schema. Both grants must belong to the user configured in the Gateway connection, not the user you test with in a desktop client.
- Through the Ignition connection, run .
- Confirm the grants cover
Database1.*(or the specific table) for SELECT andDatabase2.*for INSERT. - If either grant is missing, add it on the MySQL server. Then re-run the query from the Designer, not from an external tool.
Do not move on until a plain SELECT COUNT(*) FROM Database1.TableName succeeds through the same Ignition connection that will run the INSERT.
Check 3: Statement Type Sent to JDBC
JDBC has two separate execution paths. Query execution expects a result set back. Update execution expects an affected-row count. INSERT INTO...SELECT contains a SELECT, but it is a data-modification statement and returns only a row count. If it goes down the query path, the driver throws an error even though the SQL is correct.
| Where you run it | Set / call | Confirmation |
|---|---|---|
| Named Query | Set the query type explicitly to Update Query. Do not trust automatic type detection for INSERT...SELECT; it can misread the embedded SELECT. | Testing the query returns an affected-row count, not a dataset |
| Script, raw SQL |
system.db.runUpdateQuery(updateQuery, dbName), or system.db.runPrepUpdate when parameters are involved |
Return value is an integer row count |
| Script, via Named Query |
system.db.runNamedQuery against a Named Query already set to Update Query |
Return value is an integer row count |
| Script, wrong call |
system.db.runQuery with an INSERT |
Fails: no result set returned |
If the type is set correctly and the statement still fails, move it into a stored procedure on the MySQL server. Call it with CALL from a Named Query, or with the stored-procedure scripting functions. The procedure body runs entirely inside MySQL, so the statement-type question disappears. This only helps when both schemas are on the same server. A procedure on the local server cannot reach a remote host any more than a plain statement can.
Same server, grants correct, Update type set: the query runs. You are done here.
Different servers: continue to Check 4.
Check 4: Cross-Server Transfer Method
Two hosts means two JDBC connections, and no single query can use two JDBC connections. The data must travel through the Ignition Gateway: read it with the source connection, then write it with the destination connection. What you choose is how the write is packaged.
| Method | Round trips | When to use | Limits |
|---|---|---|---|
Row-by-row runPrepUpdate
|
One per row | Small tables, occasional runs | Slow at volume; partial copy if it fails mid-loop without a transaction |
Batched multi-row INSERT ... VALUES (?,?,?),(?,?,?)...
|
One per batch | MySQL destination, any volume | MySQL caps a prepared statement at 65,535 placeholders; the statement must also fit within the server's max_allowed_packet
|
Array parameters with unnest() in the FROM clause |
One per batch | Destination that supports SQL UNNEST, such as PostgreSQL | MySQL has no UNNEST; not available for a MySQL destination |
| Database-side link (for example, MySQL's FEDERATED storage engine) or external replication | None through Ignition | Continuous sync owned by the DBA | Needs server-side configuration outside Ignition; confirm the engine is enabled on the local server |
For a PostgreSQL destination, the array method keeps the insert as one set-based statement. Pass one Java array per column as a parameter and expand them in the FROM clause:
INSERT INTO target_table (value1, value2, value3)
SELECT * FROM unnest(?::text[], ?::int[], ?::float8[]);The column casts above are examples. Match them to the destination column types. For a MySQL-to-MySQL copy, use the batched multi-row INSERT in the procedure below.
Procedure: Batched Scripted Copy Between Two Connections
Run this in Gateway scope, from a Gateway timer event or a message handler. A Vision client script also works, but the data is routed through the client on its way back to the Gateway, which adds a network leg for every row. The connection names below are placeholders. Replace them with the exact names shown on the Gateway's database connections page.
- Create or confirm two Gateway connections: one to the remote MySQL host (source) and one to the local MySQL host (destination). Check that both show a valid status on the Gateway before continuing.
- Decide how the job will avoid copying rows twice. A common approach is a high-water mark, such as the largest auto-increment ID or timestamp already present in the destination. Read it first and use it as a filter on the source SELECT.
- Deploy the script below. Set
BATCHso thatBATCH * column_countstays well under the 65,535-placeholder cap. With three columns, a few hundred to a few thousand rows per batch is a reasonable starting range. - Run it once manually from the Script Console against a test destination table. Check the logged row counts before scheduling it.
The SourceID column is an assumption. It gives the job an idempotent restart point. If the destination has no column that identifies the source row, add one, or apply a unique key on the destination and handle duplicates in the INSERT. The transaction means a failure partway through leaves no partial batch behind, and the next run resumes from the same high-water mark.
For very large source tables, do not pull everything into one result set. Add a LIMIT to the source SELECT and loop until it returns fewer rows than the limit. That keeps Gateway memory bounded.
Verification of the Resolving Branch
-
Row count: run
SELECT COUNT(*) FROM TableNameon the source through and on the destination through , using the same ID filter. Counts must match. The loggedwrittenvalue must equal the logged read count. -
Content check: compare an aggregate on each side, such as
SUMof a numeric column andMIN/MAXof the ID. Matching counts with mismatched sums point to a column-mapping or type-conversion error in the argument order. - Rollback test: make one value invalid for the destination column on a test table, run the job, and confirm the destination row count is unchanged. The Gateway log should show the rollback message.
- Idempotence test: run the job a second time with no new source rows. The logged read count must be zero and the destination count must be unchanged. Do not schedule the job until this run inserts nothing.
FAQ
What happens if I run INSERT INTO...SELECT with system.db.runQuery in Ignition?
The driver executes it on the query path, which expects a result set. An INSERT returns only a row count, so the call throws an error even though the SQL is valid. Use system.db.runUpdateQuery or system.db.runPrepUpdate, or a Named Query explicitly set to Update Query.
What happens if the source schema is on a remote MySQL server and the target is local?
The statement runs on the one server behind the chosen Ignition connection, and that server cannot see tables on another host, so it fails. Read from the source connection and write to the destination connection in a script, batching the inserts inside a transaction.
What happens if a batched multi-row insert has too many parameters in MySQL?
MySQL rejects prepared statements with more than 65,535 placeholders, and very large statements can also exceed max_allowed_packet. Keep rows-per-batch times columns-per-row well below the placeholder cap and loop over batches.