MySQLExtend

Upsert, get, add and update data in MySQL

Actions4

Overview

The MySQLExtend node's "Insert" operation allows you to insert one or more rows into a specified MySQL table. You can define which columns to populate and provide additional options to control the behavior of the SQL INSERT statement, such as ignoring errors or setting priority. This node is useful for automating data ingestion into MySQL databases from various sources within n8n workflows.

Common scenarios:

Importing new records from external APIs or files into a MySQL database.
Automating batch inserts as part of ETL (Extract, Transform, Load) processes.
Logging events or transactions in real-time to a MySQL table.

Example:
You have a list of user data coming from an API, and you want to insert each user as a new row in your users table, specifying only certain columns.

Properties

Name	Type	Meaning
Table	String	Name of the table in which to insert data. Required.
Columns	String	Comma-separated list of property names to use as columns for the new rows.
Options	Collection	Modifiers for the INSERT statement. Includes: • Ignore (Boolean): Whether to ignore ignorable errors. • Priority (Options): Set to "Low Priority" or "High Priority" to control execution order.

Output

The output is a JSON array containing the result of the MySQL INSERT operation.
For each input item, the output includes the response from MySQL, typically with fields like:
- affectedRows: Number of rows inserted.
- insertId: The ID of the first inserted row (if applicable).
- Other metadata returned by the MySQL driver.
If an error occurs and "Continue On Fail" is enabled, the output will include an object with an error field containing the error message.

Example output:

[
  {
    "fieldCount": 0,
    "affectedRows": 1,
    "insertId": 42,
    "info": "",
    "serverStatus": 2,
    "warningStatus": 0
  }
]

Or, on error (with "Continue On Fail"):

[
  {
    "error": "Unknown column 'foo' in 'field list'"
  }
]

Dependencies

MySQL Database: Requires access to a running MySQL server.
Credentials: You must configure MySQL credentials in n8n under the name mySqlExtend.
SSL Support: Optional SSL parameters (caCertificate, clientCertificate, clientPrivateKey) can be provided for secure connections.

Troubleshooting

Common issues:

Invalid Table or Column Names: If the specified table or columns do not exist, you will receive an error like "Unknown column 'foo' in 'field list'".
Duplicate Entries: If inserting duplicate primary keys without proper handling, you may get a duplicate entry error unless "Ignore" is enabled.
Connection Errors: Incorrect credentials or unreachable database will result in connection errors.
Malformed Input Data: If the input items do not contain all required properties matching the specified columns, the insert may fail.

Error messages and resolutions:

"Unknown column ...": Check that your "Columns" property matches actual table columns.
"Table '...' doesn't exist": Verify the "Table" property is correct and the table exists.
"ER_DUP_ENTRY": Enable the "Ignore" option if you want to skip duplicate entries, or ensure unique values are provided.
Connection errors: Double-check your credentials and network/firewall settings.