MySQL
Read MySQL tables and views into a Strata flow, and write results to InnoDB tables. Works with MySQL 8.0 and 8.4, and with MariaDB. MariaDB stores JSON columns as text, so they read as text and take text when you append to them. MariaDB-only types such as UUID and INET6 can't be read.
Before you start
- A host and port that accept connections from outside your network, a database name, a user and a password. The connection stays inside that one database.
- Connections come from Oplema's servers, not your browser. This also applies when the rest of your flow runs locally. A database behind a firewall or allowlist must accept them. Oplema doesn't promise a fixed IP address and there is no SSH tunnel or private link; contact us before you plan around an allowlist.
- A dedicated user for Oplema, with the permissions below. See MySQL's GRANT statement.
-- Read
GRANT SELECT ON mydb.* TO 'oplema'@'%';
-- Write also needs the Read grants
-- Write: Append rows, and Replace, which runs DELETE
GRANT INSERT, DELETE ON mydb.* TO 'oplema'@'%';
-- Write: Create new table
GRANT CREATE ON mydb.* TO 'oplema'@'%';Connect
- In Strata, select an Input block to read, or an Output block to write.
- Set Source to Connectors. On an Output block the same control is Destination.
- In the connector dropdown below it, choose MySQL.
- Open the Connection dropdown and choose Create new connection.
- Fill in Host, Port (default 3306), Database, User and Password. The password is shown as you type. Select Use SSL to encrypt the connection; the server's certificate and hostname must then verify. Without it, traffic is not encrypted.
- Set Name (optional). Without a name, the connection is labeled
host/database. - Select Save connection.
The new connection is selected. Saving only checks the format of what you typed. It doesn't sign in, so wrong details first show up as an error when the pickers below load.
A saved connection can't be edited. To change a password, token or any other field, open the Connection dropdown, choose Delete (the ×) on its row, and create the connection again. Blocks that used the deleted connection must then select the new one.
Read
| Setting | What it does |
|---|---|
| Table or view | From the connection's database. There's no schema picker. |
| Columns | All columns or Selected columns. |
| Date column, From and To | Optional. These are inclusive whole days. TIMESTAMP uses UTC; DATETIME keeps its stored time. |
Select Refresh data to read again. A read uses one read-only query with no row limit, and fails if the server waits more than 30 seconds for the next row.
Write
Pick a Table. Existing tables must use InnoDB.
- Create new table: enter a Table name, default
output. It's created in the connection's database with InnoDB. - An existing table: set Write to Replace or Append rows. Replace deletes all rows and inserts the new ones in one transaction, so the table keeps its columns and types.
Output writes when you run the flow. Choosing tables in the panel never writes. If you pick Create new table and a table with that name already exists, the run fails. Table creation commits separately, so a failed load can leave an empty new table.
Limits and billing
- Reads: the first 50 MB of each download is free. After that, each download costs $0.01 per GB from your usage. Failed reads aren't charged, and a read stops when your usage runs out.
- Writes: the first 50 MB of each write is free. After that, each write costs $0.01 per GB from your usage, and a write stops when your usage runs out.
- MySQL's own limit applies too: a row larger than the server's packet limit fails.
See Billing for who pays and how usage works.
Troubleshooting
| Message | What to do |
|---|---|
MySQL: select at least one column | Choose All columns, or select at least one column. |
MySQL: column <name> is unavailable; refresh available columns | The table changed. Refresh the list and select the columns again. |
MySQL: choose a date column before setting dates | Pick a Date column, or clear From and To. |
MySQL: From must not be after To | Fix the date range. |
MySQL: selected table is unavailable; refresh and check permissions | Check the table exists and the user has SELECT. |
MySQL: unsupported column type <type>; select supported columns | Use Selected columns and leave that column out. |
MySQL: read waited over 30 seconds for the next row | The server stalled. Check its load, then run again. |
MySQL: append and replace require an existing InnoDB table | Convert the table to InnoDB, or create a new one. |
MySQL: <name> is outside the TIMESTAMP range (1970–2038); convert it to a timezone-free DATETIME or text in Strata | Change the destination column type, or convert the value. |
MySQL: write produced warning <code>; input was not accepted without conversion | MySQL would have altered a value. Convert the column in Strata first. |
MySQL: a row exceeds the server packet or parameter limit; reduce the row size | Shorten the row, or raise max_allowed_packet. |
MySQL: scaled FLOAT/DOUBLE columns can round values silently; use an unscaled floating-point or DECIMAL destination | Change the destination column type. |
port must be a number | Fix the port, then save again. |
MySQL: empty replacement is blocked; existing data was preserved | The incoming table has no rows. Check the flow upstream. |
MySQL: write completion could not be confirmed. Check the destination before running again. | The connection dropped during the write. Look at the table first, because a retry could write the rows twice. |
This transfer exceeds the free 50 MB and the available usage credits | The read or write passed 50 MB and your usage ran out. Add usage, or move less data. |
Prepare your data. Build it in Strata.
Strata opens in your browser, with no account.