Registry / database / node-red-contrib-stackhero-mysql

node-red-contrib-stackhero-mysql

library1.0.6jsnpmunverified

node-red-contrib-stackhero-mysql is a Node-RED contribution that provides a robust node for interacting with MySQL and MariaDB databases. Currently at version 1.0.6, this package maintains a stable release cadence, focusing on reliability and compatibility. Its primary differentiator is native support for TLS (SSL) encryption for secure connections, along with compatibility for the "Caching SHA2 password" authentication method, which is crucial for modern MySQL 8.x environments. This addresses a common pain point for users connecting to newer MySQL instances. The node simplifies database operations within Node-RED flows by allowing SQL queries in `msg.topic` and parameterized arguments in `msg.payload`, supporting various parameter styles including object-based, named, positional, and bulk insertions. It integrates seamlessly into Node-RED, extending its capabilities for data persistence and retrieval without requiring custom code for connection handling or authentication specifics.

database web-framework

npm install node-red-contrib-stackhero-mysql

github ↗package ↗homepage ↗

INSTALL

IMPORT

SIG · NODE-RED-CONTRIB-S

node-red-contrib-stackhero-mysql

databasejavascriptv1.0.6

harness data pending

Install & Compatibility

Where this runs

No compatibility data collected yet for this library.

Code

Verified usage

Demonstrates preparing `msg.topic` and `msg.payload` within a Node-RED function node for database interaction, showcasing various parameter passing methods.

/* This code would be placed in a Node-RED 'function' node. It prepares the 'msg.topic' with an SQL query and 'msg.payload' with arguments. Connect this 'function' node's output to an 'Stackhero MySQL' node in your flow. */ // Example: Object-based insertion msg.topic = 'INSERT INTO `users` SET ?'; msg.payload = { name: 'Adrien', age: 42 }; // Example: Named parameters (uncomment to use) // msg.topic = 'INSERT INTO `users` (`name`, `age`) VALUES (:name, :age)'; // msg.payload = { name: 'Romain', age: 12 }; // Example: Positional parameters (uncomment to use) // msg.topic = 'INSERT INTO `users` (`name`, `age`) VALUES (?, ?)'; // msg.payload = [ 'Eva', 28 ]; // Example: Bulk (multi-row) insertion (uncomment to use) // msg.topic = 'INSERT INTO `users` (`name`, `age`) VALUES ?'; // msg.payload = [[ // [ 'Alice', 30 ], // [ 'Bob', 25 ] // ]]; return msg;

Debug

Known issues

gotchaDirectly embedding variables into `msg.topic` for SQL queries can lead to severe SQL injection vulnerabilities. Always use `msg.payload` to pass arguments to your queries.

fix

Use parameterized queries. Place placeholders (like `?`, `:name`, `SET ?`, or `VALUES ?`) in `msg.topic` and provide the corresponding values in `msg.payload`.

affects: >=1.0.0

gotchaWhen connecting to a MySQL server configured with `--require_secure_transport=ON`, connections using insecure transport will be rejected with an 'Connections using insecure transport are prohibited' error.

fix

Ensure that the 'Use TLS (SSL) encryption' option is enabled in the Node-RED node's configuration for your database connection.

affects: >=1.0.0

gotchaConnecting to MySQL 8+ servers using older, incompatible MySQL clients or Node-RED nodes may result in 'ER_NOT_SUPPORTED_AUTH_MODE: Client does not support authentication protocol requested by server' errors, particularly with the 'Caching SHA2 password' authentication method.

fix

Ensure you are using `node-red-contrib-stackhero-mysql` as it explicitly supports 'Caching SHA2 password' authentication. Verify your database user is configured with the correct authentication plugin if issues persist.

affects: >=1.0.0

Errors

Common errors & fixes

Connections using insecure transport are prohibited while --require_secure_transport=ON

The MySQL/MariaDB server is configured to only accept secure (TLS/SSL) connections, but the Node-RED node is attempting an insecure connection.

fix

In the Node-RED node's configuration, navigate to the database connection settings and check the 'Use TLS (SSL) encryption' box.

ER_NOT_SUPPORTED_AUTH_MODE: Client does not support authentication protocol requested by server; consider upgrading MySQL client

The MySQL server (typically MySQL 8 or newer) is using an authentication method ('Caching SHA2 password') that the connecting client or Node-RED node does not support.

fix

Confirm you are using the `node-red-contrib-stackhero-mysql` package, as it specifically includes support for 'Caching SHA2 password'. If the error persists, ensure your database user's authentication plugin is set correctly on the server.

Upgrade

Version history

1.0.6latest on npm

Audit

Dependencies

mysql2requiredRuntime dependency for MySQL/MariaDB database connectivity, providing TLS and Caching SHA2 password support.

node-redrequiredPeer dependency, as this is a Node-RED contribution node.

Agent activity

6 hits · last 30 days

node

Resources

githubhttps://github.com/stackhero-io/node-red-contrib-stackhero-mysql ↗

packagehttps://www.npmjs.com/package/node-red-contrib-stackhero-mysql ↗

homepagehttps://stackhero.io ↗