MySQLExtend

Upsert, get, add and update data in MySQL

Actions4

Overview

The Upsert operation of the "MySQLExtend" n8n node allows you to insert new rows or update existing rows in a specified MySQL table based on a unique key. This is particularly useful for synchronizing data where you want to ensure that records are either created if they do not exist, or updated if they already exist, all in a single operation.

Common scenarios:

Synchronizing user or product data from another system into a MySQL database.
Importing CSV or API data and ensuring no duplicates based on a unique identifier.
Automating ETL (Extract, Transform, Load) workflows where upserts are required.

Practical example:
You have a list of products with unique SKUs. When importing this list, you want to add new products and update details of existing ones based on the SKU.

Properties

Name	Type	Meaning
Table	String	Name of the table in which to upsert data.
unique Key	String	Name of the property (column) used as the unique key to determine if a row should be upserted. Typically an ID or other unique field.
Columns	String	Comma-separated list of properties (columns) to use for the upsert operation.

Output

The output is a JSON array containing the result of each upsert operation. Each item typically includes metadata about the executed query, such as:

affectedRows: Number of rows affected by the upsert.
insertId: The ID of the inserted row (if applicable).
Other MySQL response fields depending on the driver.

Example output:

[
  {
    "fieldCount": 0,
    "affectedRows": 1,
    "insertId": 123,
    "info": "Rows matched: 1  Changed: 1  Warnings: 0",
    "serverStatus": 2,
    "warningStatus": 0
  }
]

If an error occurs and "Continue On Fail" is enabled, the output will include an object like:

[
  {
    "error": "Error message here"
  }
]

Dependencies

MySQL Database: You must have access to a MySQL server.
Credentials: The node requires valid MySQL credentials configured in n8n under the name mySqlExtend.
SSL Support: Optional SSL parameters can be provided (CA certificate, client certificate, private key) for secure connections.

Troubleshooting

Common issues:

Invalid Table or Column Names: Ensure the table and column names match those in your MySQL database.
Unique Key Not Indexed: For best performance and correct behavior, the unique key should be indexed and ideally set as UNIQUE in the database schema.
Data Type Mismatches: Make sure the input data types match the column definitions in your table.
Connection Errors: Incorrect credentials or network/firewall issues may prevent connecting to the MySQL server.
Malformed Columns List: The columns property must be a comma-separated string without extra spaces or invalid characters.

Common error messages:

"Unknown column 'xyz' in 'field list'" — Check that all specified columns exist in the target table.
"ER_DUP_ENTRY" — The unique key constraint is violated; check your input data.
"The operation \"upsert\" is not supported!" — This should not occur if using the node correctly, but indicates an unsupported operation value.

How to resolve:

Double-check property values and ensure the database schema matches your configuration.
If errors persist, enable "Continue On Fail" to capture error messages in the output for debugging.