Documentation

    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

    1. In Strata, select an Input block to read, or an Output block to write.
    2. Set Source to Connectors. On an Output block the same control is Destination.
    3. In the connector dropdown below it, choose MySQL.
    4. Open the Connection dropdown and choose Create new connection.
    5. 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.
    6. Set Name (optional). Without a name, the connection is labeled host/database.
    7. 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

    SettingWhat it does
    Table or viewFrom the connection's database. There's no schema picker.
    ColumnsAll columns or Selected columns.
    Date column, From and ToOptional. 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

    MessageWhat to do
    MySQL: select at least one columnChoose All columns, or select at least one column.
    MySQL: column <name> is unavailable; refresh available columnsThe table changed. Refresh the list and select the columns again.
    MySQL: choose a date column before setting datesPick a Date column, or clear From and To.
    MySQL: From must not be after ToFix the date range.
    MySQL: selected table is unavailable; refresh and check permissionsCheck the table exists and the user has SELECT.
    MySQL: unsupported column type <type>; select supported columnsUse Selected columns and leave that column out.
    MySQL: read waited over 30 seconds for the next rowThe server stalled. Check its load, then run again.
    MySQL: append and replace require an existing InnoDB tableConvert 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 StrataChange the destination column type, or convert the value.
    MySQL: write produced warning <code>; input was not accepted without conversionMySQL would have altered a value. Convert the column in Strata first.
    MySQL: a row exceeds the server packet or parameter limit; reduce the row sizeShorten the row, or raise max_allowed_packet.
    MySQL: scaled FLOAT/DOUBLE columns can round values silently; use an unscaled floating-point or DECIMAL destinationChange the destination column type.
    port must be a numberFix the port, then save again.
    MySQL: empty replacement is blocked; existing data was preservedThe 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 creditsThe 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.