Trinium
Start free trial
Home Product Security Nodes & connectors Docs Use cases Pricing FAQ About us Start free trial
Browse the docs
Output node

MySQL output

Load rows into a MySQL table. Turn on All or nothing and a load that fails leaves the table as it was.

Category
Output
Connection
MySQL

What it does

Loads the incoming rows into a MySQL table through a saved Connection. Appends, truncates-then-loads, or upserts (INSERT … ON DUPLICATE KEY UPDATE). The table is auto-created from the incoming columns if it doesn't exist.

Inputs and outputs

InputsOne.
OutputsNone. This node ends a pipeline.

When to use it

  • Write pipeline output into a MySQL table
  • Refresh a table each run (truncate)
  • Idempotently merge by a key (upsert)

Examples

Load staging, then merge

Table: stg_orders · Pre-SQL: TRUNCATE TABLE stg_orders · Post-SQL: INSERT INTO orders SELECT * FROM stg_orders ON DUPLICATE KEY UPDATE total = VALUES(total)

Empty the staging table, load into it, then merge into the real one. The merge runs only if the load succeeded.

Append

Table: shop.orders, Mode: append

Replace all rows

Mode: truncate

Empties the table first, then loads.

Upsert

Mode: upsert, Key columns: id

Insert new, update existing by id.

Tips

  • Each pre- and post-SQL statement has its own ⤢, the SQL Studio, with the connection's tables and columns beside the statement. It has no Run button: a Test run executes none of these statements.
  • Pre-SQL and post-SQL run on this same connection, around the load. Pre-SQL runs and commits BEFORE the load starts; post-SQL runs only AFTER it has finished successfully, so a failed load never fires the merge.
  • One statement per row, run in order. If one fails the run stops there, and the message names which statement it was and what the database said.
  • A Test run (Run to here) executes none of them, so testing stays safe on a pipeline whose pre-SQL truncates a table.
  • Credentials live on the Connection, never in the pipeline.
  • Auto-created text key columns become VARCHAR(255) so they fit MySQL's unique index.
  • Dates, times and decimals load natively. Date-times are created as DATETIME(6), not TIMESTAMP (whose range stops in 2038), so sub-second values and far-future dates both survive.
  • All or nothing (off by default): on, every row of the load lands in ONE transaction, so a failure part-way leaves the table's rows exactly as they were; off, each statement commits as it runs and a failure leaves the rows written so far (and, with truncate, an emptied table). On, truncate empties with DELETE (slower on a very large table), and a table this node creates is created before the transaction, so a failed first load leaves it empty.
  • Turn All or nothing off for a replicated MySQL cluster with a transaction cap (Group Replication / InnoDB Cluster, Galera, TiDB) or a load too large to hold open; pair Off with Upsert so a re-run overwrites rather than duplicates.
MySQL inputDatabase Lookup