# MaterializeMySQL Engine

Refer to the [official documentation](https://clickhouse.com/docs/en/engines/database-engines/materialized-mysql/)

**Syntax:**

Configure the database engine type as MaterializeMySQL and configure related parameters when creating a database.

```
CREATE DATABASE [IF NOT EXISTS] db_name [ON CLUSTER ck_cluster]
ENGINE = MaterializeMySQL('host:port', 'database', 'user', 'password') 
[SETTINGS...]

where SETTINGS are:
[ { include_tables | exclude_tables } ]
[ skip_error_count ]
[ skip_unsupported_tables ]
[ query_with_final] 
[ order_by_only_primary_key ]
[ enable_binlog_reserved ]
[ shard_model ]
[ rate_limiter_row_count_per_second ]
```

**Engine Parameters:**

<table>
    <thead>
        <tr>
            <th>Parameter</th>
            <th>Description</th>
        </tr>
    </thead>
    <tbody>
    <tr>
        <td>host:port</td>
        <td>URL and port number of the MySQL database.</td>
    </tr>
    <tr>
        <td>database</td>
        <td>Name of the MySQL database.</td>
    </tr>
    <tr>
        <td>user</td>
        <td>MySQL database account username.</td>
    </tr>
    <tr>
        <td>password</td>
        <td>Password for the MySQL database account.</td>
    </tr>
    </tbody>
</table>

**Engine Configuration Options:**

<table>
    <thead>
        <tr>
            <th>Configuration Option</th>
            <th>Description</th>
        </tr>
    </thead>
    <tbody>
    <tr>
        <td>max_rows_in_buffer</td>
        <td>
          Maximum number of rows allowed to cache data in memory (for a single table and unqueryable cached data). When the row count is exceeded, the data will be materialized. Default value: 65505.
        </td>
    </tr>
    <tr>
        <td>max_bytes_in_buffer</td>
        <td>
          Maximum number of bytes allowed to cache data in memory (for a single table and unqueryable cached data). When the byte count is exceeded, the data will be materialized. Default value: 1048576.
        </td>
    </tr>
    <tr>
        <td>max_rows_in_buffers</td>
        <td>
          Maximum number of rows allowed to cache data in memory (for a database and unqueryable cached data). When the row count is exceeded, the data will be materialized. Default value: 65505.
        </td>
    </tr>
    <tr>
        <td>max_bytes_in_buffers</td>
        <td>
          Maximum number of bytes allowed to cache data in memory (for a database and unqueryable cached data). When the byte count is exceeded, the data will be materialized. Default value: 1048576.
        </td>
    </tr>
    <tr>
        <td>max_flush_data_time</td>
        <td>
          Maximum number of milliseconds allowed to cache data in memory (for a database and unqueryable cached data). When this time is exceeded, the data will be materialized. Default value: 1000.
        </td>
    </tr>
    <tr>
        <td>max_wait_time_when_mysql_unavailable</td>
        <td>
           Retry interval (milliseconds) when MySQL is unavailable. Negative value disables retry. Default value: 1000.
        </td>
    </tr>
    <tr>
        <td>allows_query_when_mysql_lost</td>
        <td>
          Allows querying the materialized table when MySQL is lost. Default value: 0 (false).
        </td>
    </tr>
    </tbody>
</table>

**Example:**

```
CREATE DATABASE IF NOT EXISTS db_name ON CLUSTER cluster
ENGINE = MaterializeMySQL('<MySQL Address>:3306', '<Database>', '<User>', '<Password>') 
SETTINGS 
        allows_query_when_mysql_lost=true,
        max_wait_time_when_mysql_unavailable=10000;
```

## MySQL Server-Side Configuration

To ensure `MaterializeMySQL` works correctly, there are some mandatory `MySQL` side configuration settings:

- `default_authentication_plugin = mysql_native_password`, because `MaterializeMySQL` can only authorize using this method.
- `gtid_mode = on`, because GTID-based logging is mandatory to provide proper `MaterializeMySQL` replication. Note that when turning this mode `On`, you should also specify `enforce_gtid_consistency = on`.

## Usage Tips

**Virtual Fields**

When creating a new ReplacingMergeTree engine table with the MaterializeMySQL database engine on a cloud data warehouse UClickHouse cluster, two virtual fields will be added to the table by default.

<table>
    <thead>
        <tr>
            <th>Field</th>
            <th>Type</th>
            <th>Description</th>
        </tr>
    </thead>
    <tbody>
    <tr>
        <td>_version</td>
        <td>UInt64</td>
        <td>Transaction counter, recording data version information.</td>
    </tr>
    <tr>
        <td>_sign</td>
        <td>TypeInt8</td>
        <td>Deletion flag, indicating if the row is deleted. Value range as below:
            <ul>
                <li>1: The row is not deleted.</li>
                <li>
                    -1: The row is deleted.
                    <strong>Note</strong> Rows with <code>_sign=-1</code> are not physically deleted from the table.
                </li>
            </ul>
        </td>
    </tr>
    </tbody>
</table>

**DDL Statement Conversion**

MaterializeMySQL does not support direct insert, delete, and update queries. MySQL DDL statements will be converted to corresponding ClickHouse DDL statements:

- MySQL INSERT query is converted to `INSERT with _sign=1`.
- MySQL DELETE query is converted to `INSERT with _sign=-1`.
- MySQL UPDATE query is converted to `INSERT with _sign=1` and `INSERT with _sign=-1`.

**Note:**

- If ClickHouse cannot parse certain DDL statements, the statements will be ignored.
- The MaterializeMySQL engine does not support cascading UPDATE/DELETE queries.

**SELECT Queries**

- If _version is not specified in the SELECT query, the FINAL modifier is used, and the data corresponding to the maximum _version is returned, which is the latest version of the data.
- If _sign is not specified in the SELECT query, it defaults to `WHERE _sign=1`, which means it returns data that is not deleted `(_sign=1)`.

**Index Conversion**

- ClickHouse database tables automatically convert MySQL primary key and index clauses into `ORDER BY` tuples.
- ClickHouse has only one physical order determined by the `ORDER BY` clause. To create a new physical order, use materialized views.

## MySQL and Cloud Data Warehouse UClickHouse Field Type Correspondence

<table>
    <thead>
        <tr>
            <th>MySQL</th>
            <th>ClickHouse</th>
        </tr>
    </thead>
    <tbody>
    <tr>
        <td>TINY</td>
        <td>Int8</td>
    </tr>
    <tr>
        <td>SHORT</td>
        <td>Int16</td>
    </tr>
    <tr>
        <td>INT24</td>
        <td>Int32</td>
    </tr>
    <tr>
        <td>LONG</td>
        <td>UInt32</td>
    </tr>
    <tr>
        <td>LONG</td>
        <td>UInt64</td>
    </tr>
    <tr>
        <td>FLOAT</td>
        <td>Float32</td>
    </tr>
    <tr>
        <td>DOUBLE</td>
        <td>Float64</td>
    </tr>
    <tr>
        <td>DECIMAL, NEWDECIMAL</td>
        <td>Decimal</td>
    </tr>
    <tr>
        <td>DATE, NEWDATE</td>
        <td>Date</td>
    </tr>
    <tr>
        <td>DATETIME, TIMESTAMP</td>
        <td>DateTime</td>
    </tr>
    <tr>
        <td>DATETIME2, TIMESTAMP2</td>
        <td>DateTime64</td>
    </tr>
    <tr>
        <td>STRING</td>
        <td>String</td>
    </tr>
    <tr>
        <td>VARCHAR, VAR_STRING</td>
        <td>String</td>
    </tr>
    <tr>
        <td>BLOB</td>
        <td>String</td>
    </tr>
    <tr>
        <td>BIT</td>
        <td>UInt64</td>
    </tr>
    <tr>
        <td>SET</td>
        <td>UInt64</td>
    </tr>
    <tr>
        <td>ENUM</td>
        <td>Enum16</td>
    </tr>
    <tr>
        <td>JSON</td>
        <td>String</td>
    </tr>
    <tr>
        <td>YEAR</td>
        <td>String</td>
    </tr>
    <tr>
        <td>TIME</td>
        <td>String</td>
    </tr>
    <tr>
        <td>GEOMETRY</td>
        <td>String</td>
    </tr>
    </tbody>
</table>

Other types are not supported. If the MySQL table contains columns of such types, ClickHouse throws an exception "Unhandled data type" and stops replication.

[Nullable](https://clickhouse.com/docs/zh/sql-reference/data-types/nullable/) is already supported.

## Synchronize with UDB MySQL Cloud Database

**Create Database and Table**

- Create a database and table in UDB MySQL database

  ```
  CREATE DATABASE mysqltest;
  CREATE TABLE mysqltest.testtable (id INT PRIMARY KEY, name VARCHAR(10));
  ```

- Create a synchronized database in the cloud data warehouse UClickhouse.

  ```
  set allow_experimental_database_materialize_mysql=1;
  CREATE DATABASE mysqltest ENGINE = MaterializeMySQL('MySQL database address', 'Database name', 'Username', 'Password');
  ```

- Query tables in the synchronized library in the cloud data warehouse UClickHouse.

  ```
  SHOW TABLES FROM mysqltest
  ```

  Query result as follows:

  <!-- image-todo -->

**Insert Data and Query**

- Insert data in UDB MySQL database

  ```
  INSERT INTO mysqltest.testtable VALUES (1,'Zhang San'),(2,'Li Si'),(3,'Jin Gang');
  ```

- Query in the cloud data warehouse UClickhouse

  ```
  SELECT * FROM mysqltest.testtable;
  ```

  Result as follows:

  <!-- image-todo -->

**Delete Data, Add Column and Query**

- Delete data, add a column, and update data for querying in UDB MySQL database.

  ```
  DELETE FROM mysqltest.testtable WHERE id=1;
  ALTER TABLE mysqltest.testtable ADD COLUMN age INT;
  UPDATE mysqltest.testtable SET age=28 where id=3;
  SELECT * FROM testtable;
  ```

  Result as follows:

  <!-- image-todo -->

- Query in the cloud data warehouse UClickhouse

  ```
  SELECT * FROM mysqltest.testtable;
  ```

  Result as follows:

  <!-- image-todo -->