---
metadata:
  - name: generator
    content: Diplodoc Platform v5.52.0
alternate:
  - https://ydb.tech/docs/en/concepts/query_execution/federated_query/mysql.md?version=v25.3
  - https://ydb.tech/docs/ru/concepts/query_execution/federated_query/mysql.md?version=v25.3
  - href: en/concepts/query_execution/federated_query/mysql.md
    type: text/markdown
    title: Markdown version
  - href: ../../../llms.txt
    type: text/markdown
    title: llms.txt
sourcePath: en/core/concepts/query_execution/federated_query/mysql.md
---
> **Documentation Index:** Fetch the complete configuration index at https://ydb.tech/docs/en/llms.txt

# Working with MySQL Databases

This section provides basic information about working with external [MySQL](https://www.mysql.com/) databases.

To work with an external MySQL database, you need to follow these steps:

1. Create a [secret](https://ydb.tech/docs/en/concepts/datamodel/secrets.md?version=v25.3) containing the password for connecting to the database.

    ```yql
    CREATE OBJECT mysql_datasource_user_password (TYPE SECRET) WITH (value = "<password>");
    ```

2. Create an [external data source](https://ydb.tech/docs/en/concepts/datamodel/external_data_source.md?version=v25.3) that describes a specific MySQL database. The `LOCATION` parameter contains the network address of the MySQL instance to connect to. The `DATABASE_NAME` specifies the database name (for example, `mysql`). The `LOGIN` and `PASSWORD_SECRET_NAME` parameters are used for authentication to the external database. You can enable encryption for connections to the external database using the `USE_TLS="TRUE"` parameter.

    ```yql
    CREATE EXTERNAL DATA SOURCE mysql_datasource WITH (
        SOURCE_TYPE="MySQL",
        LOCATION="<host>:<port>",
        DATABASE_NAME="<database>",
        AUTH_METHOD="BASIC",
        LOGIN="user",
        PASSWORD_SECRET_NAME="mysql_datasource_user_password",
        USE_TLS="TRUE"
    );
    ```

3. <!-- source: en/concepts/query_execution/federated_query/_includes/connector_deployment.md -->
   Deploy the [connector](https://ydb.tech/docs/en/concepts/query_execution/federated_query/architecture.md?version=v25.3#connectors)  and [configure](https://ydb.tech/docs/en/devops/deployment-options/manual/federated-queries/index.md?version=v25.3)  the YDB dynamic nodes to interact with it. Additionally, ensure network access from the YDB dynamic nodes to the external data source (at the address specified in the `LOCATION` parameter of the `CREATE EXTERNAL DATA SOURCE` request). If network connection encryption to the external source was enabled in the previous step, the connector will use the system's root certificates. More details on TLS configuration can be found in the [guide](https://ydb.tech/docs/en/devops/deployment-options/manual/federated-queries/connector-deployment.md?version=v25.3) on deploying the connector.
   <!-- endsource: en/concepts/query_execution/federated_query/_includes/connector_deployment.md -->
4. [Execute a query](#query) to the database.

## Query Syntax {#query}

The following SQL query format is used to work with MySQL:

```yql
SELECT * FROM mysql_datasource.<table_name>
```

where:

- `mysql_datasource` - the external data source identifier;
- `<table_name>` - the table name within the external data source.

## Limitations {#limitations}

When working with MySQL clusters, there are a number of limitations:

1. <!-- source: en/concepts/query_execution/federated_query/_includes/supported_requests.md -->
   External sources are available only for reading data through `SELECT` queries. The federated query processing engine currently does not support queries that modify tables in external sources.
   <!-- endsource: en/concepts/query_execution/federated_query/_includes/supported_requests.md -->
2. <!-- source: en/concepts/query_execution/federated_query/_includes/datetime_limits.md -->
   If the date value stored in the external data source is outside the allowed range for YDB (all dates used must be later than 1970-01-01 but earlier than 2105-12-31), such a value in YDB will be converted to `NULL`.
   <!-- endsource: en/concepts/query_execution/federated_query/_includes/datetime_limits.md -->
3. <!-- source: en/concepts/query_execution/federated_query/_includes/predicate_pushdown.md -->
   The YDB federated query processing system is capable of delegating the execution of certain parts of a query to the system acting as the data source. Query fragments are passed through YDB directly to the external system and processed within it. This optimization, known as "predicate pushdown", significantly reduces the volume of data transferred from the source to the federated query processing engine. This reduces network load and saves computational resources for YDB.

   A specific case of predicate pushdown, where filtering expressions specified after the `WHERE` keyword are passed down, is called "filter pushdown". Filter pushdown is possible when using:

   |Description|Example|
   |---|---|
   |Filters like `IS NULL`/`IS NOT NULL`|`WHERE column1 IS NULL` or `WHERE column1 IS NOT NULL`|
   |Logical conditions `OR`, `NOT`, `AND`|`WHERE column IS NULL OR column2 IS NOT NULL`|
   |Comparison conditions `=`, `<>`, `<`, `<=`, `>`, `>=` with other columns or constants|`WHERE column3 > column4 OR column5 <= 10`|

   Supported data types for filter pushdown:
   <!-- endsource: en/concepts/query_execution/federated_query/_includes/predicate_pushdown.md -->

    |YDB Data Type|
    |----|
    |`Bool`|
    |`Int8`|
    |`Uint8`|
    |`Int16`|
    |`Uint16`|
    |`Int32`|
    |`Uint32`|
    |`Int64`|
    |`Uint64`|
    |`Float`|
    |`Double`|

## Supported Data Types

In the MySQL database, the optionality of column values (whether the column can contain `NULL` values or not) is not a part of the data type system. The `NOT NULL` constraint for any column of any table is stored within the `IS_NULLABLE` column in the [INFORMATION_SCHEMA.COLUMNS](https://dev.mysql.com/doc/refman/8.4/en/information-schema-columns-table.html) system table, i.e., at the table metadata level. Therefore, all basic MySQL types can contain `NULL` values by default, and in the YDB type system they should be mapped to [optional](https://ydb.tech/docs/en/yql/reference/types/optional.md?version=v25.3).

Below is a correspondence table between MySQL types and YDB types. All other data types, except those listed, are not supported.

| MySQL Data Type | YDB Data Type | Notes |
|---|----|------|
|`bool`|`Optional<Bool>`||
|`tinyint`|`Optional<Int8>`||
|`tinyint unsigned`|`Optional<Uint8>`||
|`smallint`|`Optional<Int16>`||
|`smallint unsigned`|`Optional<Uint16>`||
|`mediumint`|`Optional<Int32>`||
|`mediumint unsigned`|`Optional<Uint32>`||
|`int`|`Optional<Int32>`||
|`int unsigned`|`Optional<Uint32>`||
|`bigint`|`Optional<Int64>`||
|`bigint unsigned`|`Optional<Uint64>`||
|`float`|`Optional<Float>`||
|`real`|`Optional<Float>`||
|`double`|`Optional<Double>`||
|`date`|`Optional<Date>`|Valid date range from 1970-01-01 to 2105-12-31. Values outside this range return `NULL`.|
|`datetime`| `Optional<Timestamp>` | Valid time range from 1970-01-01 00:00:00 to 2105-12-31 23:59:59. Values outside this range return `NULL`.|
|`timestamp`| `Optional<Timestamp>` | Valid time range from 1970-01-01 00:00:00 to 2105-12-31 23:59:59. Values outside this range return `NULL`.|
|`tinyblob`|`Optional<String>`||
|`blob`|`Optional<String>`||
|`mediumblob`|`Optional<String>`||
|`longblob`|`Optional<String>`||
|`tinytext`|`Optional<String>`||
|`text`|`Optional<String>`||
|`mediumtext`|`Optional<String>`||
|`longtext`|`Optional<String>`||
|`char`|`Optional<Utf8>`||
|`varchar`|`Optional<Utf8>`||
|`binary`|`Optional<String>`||
|`varbinary`|`Optional<String>`||
|`json`|`Optional<Json>`||