MySQL
The MySQL connector reads tables and stored procedure results, and writes rows to tables, in a MySQL database.
When to use it
- You read from or write to MySQL.
- You read a large table with a primary key, which is read by seeking the key.
- You need a staging table created and dropped around a load, with a DB Script node.
What you need
- The host name and port the runtime can reach.
- A database user and password. The user needs read access for Sources and write access for loads.
- For a verified TLS connection to a private or self-signed authority, its CA certificate in PEM form.
Connect
The connection form lists its steps on the left. Open any step from that list. The Environments step holds the values that differ per environment.
Test connection is in the form footer. Beside it, Test against picks Base, the connection as saved, or an environment, whose values are applied first.
| Field | Step | What to enter |
|---|---|---|
| Username | Authentication | The database user. |
| Password | Authentication | The user's password. Required to connect. |
| Host | Connection | Host name or IP address. |
| Port | Connection | Default 3306. |
| Database | Connection | The database name. |
| Schema (Optional) | Connection | Optional. Tables are read and written in Database unless an action names another one, as database.table. |
| SSL Mode | Connection | Disable, Require (encrypt, no verification), Verify CA or Verify full. Default Disable. |
| CA Certificate | Connection | A PEM certificate for Verify CA or Verify full with a private or self-signed authority. |
| Connect Timeout (ms) | Options | Default 30000. |
| Pool Max | Options | Maximum open connections. Default 10. |
| Note | Options | Free text for your team. |
Require encrypts the connection without checking the certificate. Verify CA checks that a trusted authority signed it. Verify full also checks that the host name matches.
Supported operations
| Action | Node | Operations |
|---|---|---|
| Table Query (Source) | Source | Query, in No-code or SQL mode |
| Table Load (Target) | Load | Insert, Update, Upsert, Delete |
| Stored Procedure (Source) | Source | Execute |
| Stored Procedure (Target) | Load | Execute |
| DDL Script | DB Script | Create, Alter, Drop, Truncate, Rename |
Limits
- A table with a single-column primary key is read by seeking that key. Other objects are read in a fixed order with an offset, which is slower on a large table.
- Upsert, Update and Delete loads stage rows in a temporary table and apply them in one transaction.
- Load batches are sized by statement bytes.
Troubleshooting
- Test connection fails with correct credentials. The database must accept connections from the runtime that runs the data pipeline, not from your computer. For a database on a private network, run a Private Agent inside that network or allow-list the runtime's address.
- The test passes but Configure Action lists no objects. The user can sign in but cannot see the catalog. Grant it read access on the schema, then press Get Metadata again.
- A column is missing from the field list. The list is read when metadata is loaded. Press Get Metadata again to read the current columns.
- A load fails on one batch. The run log names the statement and the batch. Check the rows in that batch against the column types and lengths.
- SSL errors with Verify CA or Verify full. The server certificate is signed by an authority the runtime does not trust. Paste that authority's PEM into CA Certificate.
Related
- Source node, Load node and DB Script node.
- Connections for environments and sharing.
- PostgreSQL, SQL Server, Oracle and Snowflake.