spiceai/docs

spiceai/docs/README.md

title: 'MySQL Data Connector' sidebar_label: 'MySQL Data Connector' description: 'MySQL Data Connector Documentation' tags:

data-connectors
mysql
relational
component-metrics

MySQL is an open-source relational database management system that uses structured query language (SQL) for managing and manipulating databases.

The MySQL Data Connector enables federated/accelerated SQL queries on data stored in MySQL databases.

datasets:
  - from: mysql:mytable
    name: my_dataset
    params:
      mysql_host: localhost
      mysql_tcp_port: 3306
      mysql_db: my_database
      mysql_user: my_user
      mysql_pass: ${secrets:mysql_pass}
      mysql_pool_min: 1
      mysql_pool_max: 5

datasets:
  - from: mysql:mytable
    name: my_dataset
    params:
      mysql_host: localhost
      mysql_tcp_port: 3306
      mysql_db: my_database
      mysql_user: my_user
      mysql_pass: ${secrets:mysql_pass}
      mysql_pool_min: 1
      mysql_pool_max: 5

Configuration

`from`

The from field takes the form mysql:database_name.table_name where database_name is the fully-qualified table name in the SQL server.

If the database_name is omitted in the from field, the connector will use the database specified in the mysql_db parameter. If the mysql_db parameter is not provided, it will default to the user's default database.

:::info Unquoted identifiers are normalized to lowercase. To reference a table or database with mixed-case characters, wrap each case-sensitive part in double quotes: mysql:my_database."MixedCaseTable". See Identifier Case Sensitivity (opens in a new tab). :::

These two examples are identical:

datasets:
  - from: mysql:mytable
    name: my_dataset
    params:
      mysql_db: my_database
      ...

datasets:
  - from: mysql:mytable
    name: my_dataset
    params:
      mysql_db: my_database
      ...

datasets:
  - from: mysql:my_database.mytable
    name: my_dataset
    params: ...

datasets:
  - from: mysql:my_database.mytable
    name: my_dataset
    params: ...

`name`

The dataset name. This will be used as the table name within Spice.

Example:

datasets:
  - from: mysql:path.to.my_dataset
    name: cool_dataset
    params: ...

datasets:
  - from: mysql:path.to.my_dataset
    name: cool_dataset
    params: ...

SELECT COUNT(*) FROM cool_dataset;

SELECT COUNT(*) FROM cool_dataset;

+----------+
| count(*) |
+----------+
| 6001215  |
+----------+

+----------+
| count(*) |
+----------+
| 6001215  |
+----------+

The dataset name cannot be a reserved keyword (opens in a new tab) or any of the following keywords that are reserved by MySQL:

PARTITION

`params`

The MySQL data connector can be configured by providing the following params. Use the secret replacement syntax (opens in a new tab) to load the secret from a secret store, e.g. ${secrets:my_mysql_conn_string}.

Parameter Name	Description
`mysql_connection_string`	The connection string to use to connect to the MySQL server. This can be used instead of providing individual connection parameters. Its `pool_min`, `pool_max`, `abs_conn_ttl`, and `abs_conn_ttl_jitter` query settings are used for any pool setting the matching Spicepod parameter does not set. Setting both to different values fails the dataset. See Connection Pool Sizing (opens in a new tab).
`mysql_host`	The hostname of the MySQL server.
`mysql_tcp_port`	The port of the MySQL server.
`mysql_db`	The name of the database to connect to.
`mysql_user`	The MySQL username.
`mysql_pass`	The password to connect with.
`mysql_sslmode`	Optional. Specifies the SSL/TLS behavior for the connection, supported values: `required`: (default) Require TLS and verify the server certificate against system root CAs and domain name (equivalent to `verify_identity`). Set `mysql_sslrootcert` to verify against a specific CA bundle instead of the system trust store.`preferred`: Attempt a TLS connection but skip certificate and hostname verification (invalid or self-signed certificates are accepted). The connection is still encrypted and does not fall back to plaintext — if the server does not support TLS, the connection fails. Not recommended for production, as it does not protect against man-in-the-middle attacks.`disabled`: Do not attempt to use an SSL connection, even if the server supports it.
`mysql_sslrootcert`	Optional parameter specifying the path to a custom PEM certificate that the connector will trust.
`mysql_time_zone`	Optional. Specifies connection time zone. Default is `+00:00` (UTC). Accepts: Fixed offsets (e.g., `+02:00`).IANA time zone names (e.g., `America/Los_Angeles`), if supported by the MySQL server.`system`: The MySQL server host’s OS time zone.`local_system`: The local runtime OS time zone.
`mysql_pool_min`	The minimum number of connections to keep open in the pool, lazily created when requested. Default: `1`
`mysql_pool_max`	The maximum number of connections created in the connection pool. Default: `5`
`mysql_connection_max_lifetime`	How long a pooled connection is used before it is closed and replaced, such as `10m`. Accepts a duration from `1s` to `365d`, or `0s` to keep connections open indefinitely. Each connection's lifetime is extended by a random amount of up to a fifth of it, or of up to `abs_conn_ttl_jitter` seconds when `mysql_connection_string` sets it, and a connection is not closed while a query is using it. Replacing connections resolves the host again, so the pool spreads across the servers behind a load-balanced hostname such as an Amazon Aurora reader endpoint. Default: `5m`.
`mysql_zero_date_behavior`	Optional. How to handle the MySQL `0000-00-00` / `0000-00-00 00:00:00` zero-date sentinel for DATE/DATETIME/TIMESTAMP columns. Supported values: `null`: (default) Coerces zero dates to NULL and reports such columns as nullable in the Arrow schema.`error`: Fails the scan when a zero date is encountered and honors the source NOT NULL constraint exactly.

Replication parameters

The following parameters configure MySQL binlog replication (opens in a new tab) (native CDC) when using refresh_mode: changes. primary_key is required on the accelerator (except on the append-only arrow engine), and on engines other than cayenne so is on_conflict: upsert on that key. See MySQL Binlog Replication (opens in a new tab) for source prerequisites, semantics, and metrics.

Parameter Name	Description
`mysql_replication_server_id`	Optional. The `server_id` this replica registers with. Must be unique among all replicas attached to the source. Default: derived from the dataset name and process.
`mysql_replication_initial_snapshot`	Optional. When existing rows load: `auto` (default) snapshots when no resumable position exists and resumes without a snapshot when one does; `disabled` streams changes only; `always` re-snapshots on every start.
`mysql_replication_checkpoint_interval`	Optional. How often the committed binlog position persists to the accelerator sidecar (e.g. `10s`). Default: `10s`.
`mysql_replication_bootstrap_batch_size`	Optional. Number of rows per emitted batch during the initial snapshot. Default: `8192`. Maximum: `1048576`.
`mysql_replication_invalid_checkpoint_behavior`	Optional. What to do when the persisted position cannot be resumed losslessly — either it was purged from the source, or the source table's column layout drifted incompatibly with the recorded position: `error` (default) or `restart` (drop the position and re-snapshot). Default: `error`.
`mysql_replication_ready_lag`	Optional. For `refresh_mode: changes`, the dataset is marked Ready once its replication lag (now minus the newest applied commit's binlog-header timestamp) falls below this. Default: `2s`.

`metrics`

The MySQL data connector supports the following optional component metrics (opens in a new tab):

Metric Name	Type	Description
`connection_count`	Gauge	Gauge of active connections to the database server
`connections_in_pool`	Gauge	Gauge of active connections that are idling in the pool
`active_wait_requests`	Gauge	Gauge of requests that are waiting for a connection to be returned to the pool
`create_failed`	Counter	Counter of connections that failed to be created
`discarded_superfluous_connection`	Counter	Counter of connections that were closed because there were already enough idle connections in the pool
`discarded_unestablished_connection`	Counter	Counter of connections that were closed because they could not be established
`dirty_connection_return`	Counter	Counter of connections that were returned to the pool but were dirty (ie. open transactions, pending queries, etc)
`discarded_expired_connection`	Counter	Counter of connections that were discarded because they were expired by the pool constraints (i.e. TTL expired)
`resetting_connection`	Counter	Counter of connections that were reset
`discarded_error_during_cleanup`	Counter	Counter of connections that were discarded because they returned an error during cleanup
`connection_returned_to_pool`	Counter	Counter of connections that were returned to the pool

These metrics are not enabled by default, enable them by setting the metrics parameter:

datasets:
  - from: mysql:mytable
    name: my_dataset
    metrics:
      - name: connection_count
      - name: connections_in_pool
      - name: active_wait_requests
      - name: create_failed
      - name: discarded_superfluous_connection
      - name: discarded_unestablished_connection
      - name: dirty_connection_return
      - name: discarded_expired_connection
      - name: resetting_connection
      - name: discarded_error_during_cleanup
      - name: connection_returned_to_pool
    params: &params
      mysql_host: localhost
      mysql_tcp_port: 3306
      mysql_user: my_user
      mysql_pass: ${secrets:mysql_pass}

datasets:
  - from: mysql:mytable
    name: my_dataset
    metrics:
      - name: connection_count
      - name: connections_in_pool
      - name: active_wait_requests
      - name: create_failed
      - name: discarded_superfluous_connection
      - name: discarded_unestablished_connection
      - name: dirty_connection_return
      - name: discarded_expired_connection
      - name: resetting_connection
      - name: discarded_error_during_cleanup
      - name: connection_returned_to_pool
    params: &params
      mysql_host: localhost
      mysql_tcp_port: 3306
      mysql_user: my_user
      mysql_pass: ${secrets:mysql_pass}

Types

The table below shows the MySQL data types supported, along with the type mapping to Apache Arrow types in Spice.

MySQL Type	Arrow Type
`TINYINT`	`Int8`
`SMALLINT`	`Int16`
`INT`	`Int32`
`MEDIUMINT`	`Int32`
`BIGINT`	`Int64`
`DECIMAL`	`Decimal128` / `Decimal256`
`FLOAT`	`Float32`
`DOUBLE`	`Float64`
`DATETIME`	`Timestamp(Microsecond, None)`
`TIMESTAMP`	`Timestamp(Microsecond, None)`
`YEAR`	`Int16`
`TIME`	`Time64(Nanosecond)`
`DATE`	`Date32`
`CHAR`	`Utf8`
`BINARY`	`Binary`
`VARCHAR`	`Utf8`
`VARBINARY`	`Binary`
`TINYBLOB`	`Binary`
`TINYTEXT`	`Utf8`
`BLOB`	`Binary`
`TEXT`	`Utf8`
`MEDIUMBLOB`	`Binary`
`MEDIUMTEXT`	`Utf8`
`LONGBLOB`	`LargeBinary`
`LONGTEXT`	`LargeUtf8`
`JSON`	`LargeUtf8`
`SET`	`Utf8`
`ENUM`	`Dictionary(UInt16, Utf8)`
`BIT`	`UInt64`

:::note

The MySQL TIMESTAMP value is retrieved as a UTC time value (opens in a new tab) by default. Use the mysql_time_zone configuration parameter to specify the desired time zone for interpreting TIMESTAMP values during data retrieval.

:::

Casts Evaluated in Spice

MySQL rounds a floating-point or decimal value to the nearest integer when it casts the value to an integer type, so CAST(1.5 AS SIGNED) returns 2. Spice truncates toward zero and returns 1. A CAST or TRY_CAST from a floating-point or decimal value into an integer type is therefore not sent to MySQL: Spice reads the referenced columns and evaluates the cast itself. This applies to filters too, so WHERE CAST(price AS INT) = 1 selects the rows whose price truncates to 1. The other conditions in the same WHERE clause are still sent to MySQL.

DataFusion's cast and type functions, such as arrow_cast (opens in a new tab), cast_to_type, and arrow_typeof (opens in a new tab), are also evaluated in Spice. Run EXPLAIN (opens in a new tab) to see which parts of a query are sent to MySQL.

Limitations

MySQL has no native nested or array column types (see the MySQL data types reference (opens in a new tab)), so columns containing Arrow Struct, List, or LargeList values are not supported by this connector.
MySQL spatial types (opens in a new tab) (GEOMETRY, POINT, LINESTRING, POLYGON, MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, GEOMETRYCOLLECTION) are not currently supported — only the types listed in the Types (opens in a new tab) table are mapped to Arrow.
Nested or array-valued data should be stored in a JSON column, which is read as LargeUtf8 and can be decoded with SQL JSON functions.

Examples

Connecting using username and password

datasets:
  - from: mysql:path.to.my_dataset
    name: my_dataset
    params:
      mysql_host: localhost
      mysql_tcp_port: 3306
      mysql_db: my_database
      mysql_user: my_user
      mysql_pass: ${secrets:mysql_pass}

datasets:
  - from: mysql:path.to.my_dataset
    name: my_dataset
    params:
      mysql_host: localhost
      mysql_tcp_port: 3306
      mysql_db: my_database
      mysql_user: my_user
      mysql_pass: ${secrets:mysql_pass}

Connecting using SSL

datasets:
  - from: mysql:path.to.my_dataset
    name: my_dataset
    params:
      mysql_host: localhost
      mysql_tcp_port: 3306
      mysql_db: my_database
      mysql_user: my_user
      mysql_pass: ${secrets:mysql_pass}
      mysql_sslmode: preferred
      mysql_sslrootcert: ./custom_cert.pem

datasets:
  - from: mysql:path.to.my_dataset
    name: my_dataset
    params:
      mysql_host: localhost
      mysql_tcp_port: 3306
      mysql_db: my_database
      mysql_user: my_user
      mysql_pass: ${secrets:mysql_pass}
      mysql_sslmode: preferred
      mysql_sslrootcert: ./custom_cert.pem

Connecting using a Connection String

datasets:
  - from: mysql:path.to.my_dataset
    name: my_dataset
    params:
      mysql_connection_string: mysql://${secrets:my_user}:${secrets:my_password}@localhost:3306/my_db

datasets:
  - from: mysql:path.to.my_dataset
    name: my_dataset
    params:
      mysql_connection_string: mysql://${secrets:my_user}:${secrets:my_password}@localhost:3306/my_db

Connecting to the default database

datasets:
  - from: mysql:mytable
    name: my_dataset
    params:
      mysql_host: localhost
      mysql_tcp_port: 3306
      mysql_user: my_user
      mysql_pass: ${secrets:mysql_pass}

datasets:
  - from: mysql:mytable
    name: my_dataset
    params:
      mysql_host: localhost
      mysql_tcp_port: 3306
      mysql_user: my_user
      mysql_pass: ${secrets:mysql_pass}

With custom connection pool settings

datasets:
  - from: mysql:path.to.my_dataset
    name: my_dataset
    params:
      mysql_host: localhost
      mysql_tcp_port: 3306
      mysql_db: my_database
      mysql_user: my_user
      mysql_pass: ${secrets:mysql_pass}
      mysql_pool_min: 5
      mysql_pool_max: 10
      mysql_connection_max_lifetime: 10m

datasets:
  - from: mysql:path.to.my_dataset
    name: my_dataset
    params:
      mysql_host: localhost
      mysql_tcp_port: 3306
      mysql_db: my_database
      mysql_user: my_user
      mysql_pass: ${secrets:mysql_pass}
      mysql_pool_min: 5
      mysql_pool_max: 10
      mysql_connection_max_lifetime: 10m

Secrets

Spice integrates with multiple secret stores to help manage sensitive data securely. For detailed information on supported secret stores, refer to the secret stores documentation (opens in a new tab). Additionally, learn how to use referenced secrets in component parameters by visiting the using referenced secrets guide (opens in a new tab).

Cookbook

A cookbook recipe to configure MySQL as a data connector in Spice. MySQL Data Connector (opens in a new tab)
A cookbook recipe to configure AWS RDS Aurora (MySQL Compatible) as a data connector in Spice. AWS RDS Aurora (MySQL Data Connector) (opens in a new tab)
A cookbook recipe to configure Planetscale as a data connector in Spice. Planetscale (MySQL Data Connector) (opens in a new tab)
A cookbook recipe to stream changes from MySQL into Spice over the binary log. MySQL CDC (Binlog Replication) (opens in a new tab)
A cookbook recipe to stream changes from an AWS-hosted Aurora MySQL cluster into Spice over the binary log. Aurora MySQL CDC (Binlog Replication, AWS Hosted) (opens in a new tab)