MySQL

1. Overview

MySQL is a widely-used open-source relational database management system that stores and manages structured data using SQL (Structured Query Language). It is known for its reliability, scalability, and performance in handling database operations.

Through GoInsight's MySQL node, you can seamlessly integrate MySQL database operations into your automation workflows. You gain full lifecycle management capabilities for your MySQL data, including:

• Query and retrieve data from tables with advanced filtering, sorting, and pagination.
• Insert, update, and upsert records with support for batch operations and transaction modes.
• Delete specific rows based on conditions, or clear and drop entire tables.
• Execute custom SQL statements with parameterized queries and multiple execution modes.
• Inspect table structures to retrieve detailed schema information.

2. Prerequisites

Before using this node, you need to meet the following requirements:

• Database Access: You need access to a MySQL database instance with appropriate connection credentials (host, port, username, password, and database name).
• Permissions: Your MySQL user account must have the necessary privileges to perform the intended operations (SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, etc.).
• Network Connectivity: Ensure that your GoInsight instance can establish network connectivity to your MySQL server.

3. Credentials

For detailed guidance on how to obtain and configure credentials for connecting to your MySQL database, please refer to our official documentation: Credentials Configuration Guide.

4. Supported Operations

Summary

This MySQL node provides comprehensive data management capabilities centered around table records, table structures, and custom SQL execution. The following operations are available:

Operation Details

Select

Queries data from a MySQL table with support for conditional filtering, pagination, sorting, and distinct results. Returns paginated query results with metadata including total count, page information, and whether more data is available.

Input Parameters:

• Table: The name of the table to query data from (required).

Options:

• Where: WHERE clause for filtering. Use ? as placeholders for values. Example: age > ? AND (name = ? OR email = ?).
• WhereValues: Array of values for WHERE clause placeholders. Example: ["18", "Melody", "hello@gmail.com"]
• Page: Page number for pagination. Default: 1.
• PageSize: Number of records per page. Default: 50.
• Sort: Array of sort objects. Example: [{"id":"asc"},{"updated_at":"desc"}]
• Distinct: Whether to return distinct results. Default: false.
• OutputColumns: Array of column names to return. Default: [] (all columns). Example: ["id","name","email"]
• ReplaceEmptyStrings: Whether to replace empty strings with NULL in WHERE values. Default: false.
• ConnectionTimeout: Database connection timeout in milliseconds. Default: 5000.
• Debug: Whether to enable debug mode. When true, response includes RawSql field. Default: false.

Output:

• Success (bool): Whether the operation was successful.
• Result (object-array): Query result array containing data rows that match the query conditions.
• QueryCount (number): The number of rows returned in the current page.
• TotalCount (number): The total number of rows that match the query conditions across all pages.
• TotalPage (number): The total number of pages based on the PageSize.
• CurrentPage (number): The current page number.
• HasMore (bool): Whether there are more pages of data available.
• RawSql (string): The raw SQL statement executed (only returned when debug mode is enabled).
• StatusCode (number): Operation status code: 200 (success), 500 (database error), -1 (parameter error).
• ErrorMessage (string): Error description if operation failed, empty on success.

Insert

Inserts one or more rows into a MySQL table. Supports single or batch insert operations with different batch processing modes (single, independently, transaction). Allows skipping on conflict and replacing empty strings with NULL.

Input Parameters:

• Table: The name of the table to insert data into (required).

Options:

• DataMode: Data mapping mode: defineMap (manually define data mapping), autoMap (automatically use previous node data, not yet supported). Default: defineMap.
• Values: Array of objects to insert. Example: [{"name":"Melody","age":25}] or [{"name":"Melody","age":25},{"name":"Alice","age":30}]
• QueryBatching: Batch processing mode: single (single query mode), independently (each statement executed independently), transaction (all statements in a transaction). Default: single.
• SkipOnConflict: Whether to skip on conflict (uses INSERT IGNORE). Default: false.
• ReplaceEmptyStrings: Whether to replace empty strings with NULL. Default: false.
• ConnectionTimeout: Database connection timeout in milliseconds. Default: 5000.
• Debug: Whether to enable debug mode. When true, response includes RawSql field. Default: false.

Output:

• Success (bool): Whether the operation was successful.
• Result (object-array): Query result array, typically empty for insert operations.
• AffectedRows (number): The number of rows affected (inserted).
• InsertId (number): The auto-increment ID of the first inserted row.
• RawSql (string): The raw SQL statement executed (only returned when debug mode is enabled).
• StatusCode (number): Operation status code: 200 (success), 500 (database error), -1 (parameter error).
• ErrorMessage (string): Error description if operation failed, empty on success.

Update

Updates data in a MySQL table by locating records based on matching columns and updating specified fields. Only updates fields that are not in the matching columns list.

Input Parameters:

• Table: The name of the table to update data in (required).

Options:

• DataMode: Data mapping mode: defineMap (manually define data mapping), autoMap (automatically use previous node data, not yet supported). Default: defineMap.
• ColumnsToMatchOn: Array of column names used to locate the record. Example: ["id"] or ["email","status"]
• ValuesToSend: Object with data to update. Must include columns in ColumnsToMatchOn. Example: {"id":123,"name":"Melody","age":25}
• ReplaceEmptyStrings: Whether to replace empty strings with NULL. Default: false.
• ConnectionTimeout: Database connection timeout in milliseconds. Default: 5000.
• Debug: Whether to enable debug mode. When true, response includes RawSql field. Default: false.

Output:

• Success (bool): Whether the operation was successful.
• AffectedRows (number): The number of rows affected by the update.
• RawSql (string): The raw SQL statement executed (only returned when debug mode is enabled).
• StatusCode (number): Operation status code: 200 (success), 500 (database error), -1 (parameter error).
• ErrorMessage (string): Error description if operation failed, empty on success.

Upsert

Inserts or updates data in a MySQL table based on unique key matching. Uses INSERT ... ON DUPLICATE KEY UPDATE syntax to perform upsert operations, supporting single or batch data processing with three execution modes.

Input Parameters:

• Table: The name of the table to perform upsert operation (required).

Options:

• DataMode: Data mapping mode: defineMap (manual data mapping), autoMap (automatic data mapping, not supported yet). Default: defineMap.
• ColumnsToMatchOn: Array of column names for matching. Example: ["email","status"]
• ValuesToSend: Array of objects to insert or update. Example: [{"name":"Melody","age":25,"email":"hello@gmail.com","status":1}]
• QueryBatching: Batch processing mode: single (single query mode), independently (independent query mode), transaction (transaction mode). Default: single.
• ReplaceEmptyStrings: Whether to replace empty strings with NULL. Default: false.
• ConnectionTimeout: Database connection timeout in milliseconds. Default: 5000.
• Debug: Whether to enable debug mode. When true, response includes RawSql field. Default: false.

Output:

• Success (bool): Whether the operation was successful.
• Result (object-array): Query result array, typically empty for upsert operations.
• AffectedRows (number): The number of rows affected by the operation. 1 indicates a new row was inserted, 2 indicates an existing row was updated.
• InsertId (number): The auto-increment ID of the first inserted record. Returns 0 if only updates were performed.
• RawSql (string): The raw SQL statement executed (only returned when debug mode is enabled).
• StatusCode (number): Operation status code: 200 (success), 500 (database error), -1 (parameter error).
• ErrorMessage (string): Error description if operation failed, empty on success.

Delete

Deletes rows from a MySQL table based on specified conditions, or clears/drops the entire table. Supports three deletion modes: delete (conditional row deletion), truncate (clear all data while preserving table structure), and drop (remove the entire table).

Input Parameters:

• Table: The name of the table to delete from (required).

Options:

• Command: Deletion command type: delete (conditional row deletion), truncate (clear all data), drop (remove table). Default: delete.
• Where: WHERE condition statement for DELETE command (required for delete mode). Supports AND/OR operators and ? placeholders.
• WhereValues: Array of parameter values for WHERE clause. Example: ["18", "Melody", "hello@gmail.com"]
• ConnectionTimeout: Database connection timeout in milliseconds. Default: 5000.
• Debug: Whether to enable debug mode. When true, response includes rawsql field. Default: false.

Output:

• Success (bool): Whether the operation was successful.
• AffectedRows (number): The number of rows affected by the operation.
• Result (object-array): Query result array, typically empty for delete operations.
• RawSql (string): The raw SQL statement executed (only returned when debug mode is enabled).
• StatusCode (number): Operation status code: 200 (success), 500 (database error), -1 (parameter error).
• ErrorMessage (string): Error description if operation failed, empty on success.

Describe

Retrieves the structure of a MySQL table, including detailed information about columns such as field names, data types, collation, null constraints, keys, default values, extra attributes, privileges, and comments.

Input Parameters:

• Table: The name of the table to describe (required).

Options:

• ConnectionTimeout: Database connection timeout in milliseconds. Default: 5000.
• Debug: Whether to enable debug mode. When true, response includes RawSql field. Default: false.

Output:

• Success (bool): Whether the operation was successful.
• Result (object-array): Array of table structure information. Each object contains detailed field information including Field, Type, Collation, Null, Key, Default, Extra, Privileges, and Comment.
• RawSql (string): The raw SQL statement executed (only returned when debug mode is enabled).
• StatusCode (number): Operation status code: 200 (success), 500 (database error), -1 (parameter error).
• ErrorMessage (string): Error description if operation failed, empty on success.

Execute

Executes one or more MySQL SQL statements with parameterized queries. Supports multiple SQL statements separated by semicolons, with three execution modes: single (process all statements at once), independently (execute each statement separately), and transaction (execute all statements within a transaction with rollback on failure).

Input Parameters:

• Query: SQL query statement(s). Multiple statements can be separated by semicolons (required).

Options:

• QueryValues: Array of parameter values for SQL statement(s). Values replace ? placeholders. Example: ["18", "1", "hello@gmail.com", "Melody", "25", "1"]
• ReplaceEmptyStrings: Whether to replace empty strings with NULL. Valid values: true, false. Default: false.
• QueryBatching: Batch processing mode: single (process all at once, fastest), independently (execute each separately), transaction (all in transaction, rollback on failure). Default: single.
• ConnectionTimeout: Database connection timeout in milliseconds. Default: 5000.
• Debug: Whether to enable debug mode. When true, response includes RawSql field. Default: false.

Output:

• Success (bool): Whether the operation was successful.
• Result (object-array): Query result array. For SELECT queries, contains data rows. For other operations, typically empty.
• AffectedRows (number): The number of rows affected by INSERT/UPDATE/DELETE operations.
• InsertId (number): The auto-increment ID of the last inserted row (for INSERT operations).
• RawSql (string): The raw SQL statement executed (only returned when debug mode is enabled).
• StatusCode (number): Operation status code: 200 (success), 500 (database error), -1 (parameter error).
• ErrorMessage (string): Error description if operation failed, empty on success.

5. Example Usage

This section will guide you through creating a simple workflow that queries user data from a MySQL database table. This is one of the most common operations when working with databases.

Scenario: You want to retrieve a list of users from your users table, filtering by age and sorting the results by name.

Workflow Overview: The workflow consists of three nodes: Start → MySQL (Select) → Answer

Step-by-Step Guide

Step 1: Add the MySQL Tool Node

1. In the workflow canvas, click the "+" button to add a new node.
2. In the panel that appears, select the "Tools" tab.
3. Find and select Mysql from the tools list.
4. In the list of operations supported by Mysql, click Select. This will add a corresponding node to your canvas.

Step 2: Configure the Node

Click on the newly added Select node to open its configuration panel on the right side.

Credential Configuration:

1. At the top of the panel, locate the credential field.
2. Click the dropdown menu and select your pre-configured MySQL credentials (which should include your database host, port, username, password, and database name).

Parameter Configuration:

Fill in the following parameters to configure your query:

• Table: Enter the name of the table you want to query. For this example, enter users.
• Where (Optional): Enter a WHERE clause to filter results. For example, to find users older than 18, enter: age > ?
• WhereValues (Optional): Provide an array of values for the placeholders in your WHERE clause. For the example above, enter: ["18"]
• Page (Optional): Specify which page of results to return. Leave as 1 for the first page.
• PageSize (Optional): Specify how many records to return per page. For example, enter 10 to get 10 results.
• Sort (Optional): Define how to sort the results. To sort by name in ascending order, enter: [{"name":"asc"}]
• OutputColumns (Optional): Specify which columns to return. To get only id, name, and email, enter: ["id","name","email"]. Leave empty to return all columns.

Step 3: Run and Verify

1. Once all required parameters are correctly filled in, any error indicators in the top-right corner of the canvas should disappear.
2. Click the "Test Run" button in the top-right corner to execute the workflow.
3. After successful execution, click the log icon in the top-right corner to view the detailed input and output of the node.
4. In the output, you'll see:
   • Result: An array containing the user records that matched your query
   • QueryCount: The number of records returned in this page
   • TotalCount: The total number of records matching your criteria
   • HasMore: Whether there are additional pages available

Final Workflow: After completing these steps, your entire workflow is configured. Click "Test Run" to execute it, and the MySQL Select operation will query your database table and return the filtered, paginated results.

6. FAQs

Q: I'm getting a "Connection timeout" error. What should I do?

A: Connection timeout errors typically indicate network connectivity issues. Please check the following:

• Verify that your MySQL server is running and accessible.
• Ensure your firewall allows connections from your GoInsight instance to the MySQL server.
• Check that the host, port, and database name in your credentials are correct.
• Try increasing the ConnectionTimeout parameter value (default is 5000 milliseconds).

Q: Why am I getting a "StatusCode: -1" with a parameter error?

A: A status code of -1 indicates a parameter validation error. Common causes include:

• Missing required parameters (e.g., Table name for Select operation).
• Invalid data format (e.g., malformed JSON in Values or Sort parameters).
• Mismatched number of placeholders (?) in WHERE clause and values in WhereValues array.
• Empty ColumnsToMatchOn when using Update or Upsert operations.

Q: What's the difference between the three QueryBatching modes?

A: The three modes affect how multiple operations are processed:

• single: All SQL statements are processed together in a single query. This is the fastest option but treats all operations as one unit.
• independently: Each SQL statement is executed separately. If one fails, others can still succeed.
• transaction: All statements are executed within a database transaction. If any statement fails, all changes are rolled back to maintain data consistency.

Q: When should I use Upsert instead of Insert or Update?

A: Use Upsert when you want to insert a record if it doesn't exist, or update it if it does, based on a unique key or combination of columns. This is particularly useful when:

• You're not sure whether a record already exists in the database.
• You want to avoid the overhead of checking existence before inserting or updating.
• You need to maintain data consistency with a single atomic operation.

Q: Can I see the actual SQL queries being executed?

A: Yes! Set the Debug parameter to true in any operation. When debug mode is enabled, the response will include a RawSql field containing the actual SQL statement that was executed. This is helpful for troubleshooting and understanding how your parameters are being translated into SQL.

7. Official Documentation

For more information about MySQL and its SQL syntax, please refer to:

MySQL Official Documentation