> ## Documentation Index
> Fetch the complete documentation index at: https://docs.hotglue.com/llms.txt
> Use this file to discover all available pages before exploring further.
# MySQL
# Connector Details
| Name | Value |
| :------------- | :---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Platform | [MySQL](https://mysql.com) |
| Auth Type | API Keys |
| Direction | Bidirectional |
| Tap Repo | [https://github.com/transferwise/pipelinewise-tap-mysql](https://github.com/transferwise/pipelinewise-tap-mysql) |
| Target Repo | [https://github.com/hotgluexyz/target-mysql-v2](https://github.com/hotgluexyz/target-mysql-v2) |
| Tap Metrics | Usage:
|
| Target Metrics | Usage:
|
# Target MySQL
## Config
`target-mysql` requires the standard 5 connection parameters to be specified in the config:
```json theme={null}
{
"host": "https://...",
"port": "3306",
"user": "...",
"password": "...",
"database": "...",
}
```
## Example ETL Script
```python theme={null}
import gluestick as gs
import os
# Define standard Hotglue directories
ROOT_DIR = os.environ.get("ROOT_DIR", ".")
INPUT_DIR = f"{ROOT_DIR}/sync-output"
OUTPUT_DIR = f"{ROOT_DIR}/etl-output"
# Read sync output
input = gs.Reader()
# Get tenant id
tenant_id = os.environ.get('USER_ID', os.environ.get('TENANT', 'default'))
# Iterate through the different streams in the sync output
for key in eval(str(input)):
input_df = input.get(key)
"""
Here we get the metadata for extracting the key properties, such as primary and replication keys.
The database export targets will utilize these primary keys when upserting data.
If you wish to hardcode your choice of primary keys, you can do so here.
"""
metadata = input.get_metadata(key)
if metadata.get("key_properties"):
# Use the key_properties (e.g. primary and replication keys) from the catalog
key_properties = eval(metadata["key_properties"])
else:
key_properties = []
# Include tenant_id as a field if desired
input_df["tenant"] = tenant_id
# Write this stream to the OUTPUT directory with the specified key_properties
gs.to_singer(input_df, key, OUTPUT_DIR, keys=key_properties)
```
## Optional config flags
| Property | Description | Default |
| ------------------------- | -------------------------------------------------------- | ------------------ |
| table\_name\_pattern | MySQL table name pattern to use when creating tables | "\$\{TABLE\_NAME}" |
| lower\_case\_table\_names | Use lowercase for table names or not | true |
| allow\_column\_alter | Allow column alterations or not | false |
| replace\_null | Replace null values with others or not | false |
| table\_config | Dictionary with specifications on insert/upsert/truncate | {} |
### The `replace_null` Option (Experimental)
By enabling the `replace_null` option, null values are replaced with 'empty' equivalents based on their data type. Use with caution as it may alter data semantics.
When `replace_null` is `true`, null values are replaced as follows:
| JSON Schema Data Type | Null Value Replacement |
| --------------------- | ---------------------- |
| string | Empty string(`""`) |
| number | `0` |
| object | Empty object(`{}`) |
| array | Empty array(`[]`) |
| boolean | `false` |
| null | null |
### The `table_config` option
By default, target-mysql will upsert your payload into each table. This means it will insert all new data into your table, but perform an update on primary key collision.
The `table_config` allows you to override this behavior with one of two insertion methods:
* `truncate`: Delete the existing table and replace it with the new data
* `insert`: Attempt to insert all records as new rows, throwing an error on primary key collision
If you want to use the `truncate` or `insert` methods, you can specify which tables should use the method using the `table_config` flag:
```json theme={null}
{
"host": "..."
....
"table_config": {
"accounts": "truncate",
"vendors": "insert",
}
}
```
# Tap Changelog
| Version | Notes |
| :------------------------------------------------------------------------------------------------------------------------------- | :----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| [1.6.0 (2024-05-09)](https://github.com/transferwise/pipelinewise-tap-mysql/releases/tag/v1.6.0) | ## What's Changed
\* Prefer SSL connection to source by @louis-pie in [https://github.com/transferwise/pipelinewise-tap-mysql/pull/181](https://github.com/transferwise/pipelinewise-tap-mysql/pull/181)
**Full Changelog**: [https://github.com/transferwise/pipelinewise-tap-mysql/compare/v1.5.6...v1.6.0](https://github.com/transferwise/pipelinewise-tap-mysql/compare/v1.5.6...v1.6.0) |
| [1.5.6 (2023-08-10)](https://github.com/transferwise/pipelinewise-tap-mysql/releases/tag/v1.5.6) | ## What's Changed
\* AP-1531 Zero-pad fixed-length binary fields by @Samira-El in [https://github.com/transferwise/pipelinewise-tap-mysql/pull/162](https://github.com/transferwise/pipelinewise-tap-mysql/pull/162)
**Full Changelog**: [https://github.com/transferwise/pipelinewise-tap-mysql/compare/v1.5.5...v1.5.6](https://github.com/transferwise/pipelinewise-tap-mysql/compare/v1.5.5...v1.5.6) |
| [1.5.5 (2023-07-05)](https://github.com/transferwise/pipelinewise-tap-mysql/releases/tag/v1.5.5) | ## What's Changed
\* LookupError: unknown encoding: utf8mb3 by @Samira-El in [https://github.com/transferwise/pipelinewise-tap-mysql/pull/163](https://github.com/transferwise/pipelinewise-tap-mysql/pull/163)
\* Bump plpygis from 0.2.0 to 0.2.1 by @dependabot in [https://github.com/transferwise/pipelinewise-tap-mysql/pull/142](https://github.com/transferwise/pipelinewise-tap-mysql/pull/142)
**Full Changelog**: [https://github.com/transferwise/pipelinewise-tap-mysql/compare/v1.5.4...v1.5.5](https://github.com/transferwise/pipelinewise-tap-mysql/compare/v1.5.4...v1.5.5) |
| [1.5.4 (2023-05-22)](https://github.com/transferwise/pipelinewise-tap-mysql/releases/tag/v1.5.4) | ## What's Changed
\* AP-1477: Bump python-mysql-replication by @Samira-El in [https://github.com/transferwise/pipelinewise-tap-mysql/pull/156](https://github.com/transferwise/pipelinewise-tap-mysql/pull/156)
**Full Changelog**: [https://github.com/transferwise/pipelinewise-tap-mysql/compare/v1.5.3...v1.5.4](https://github.com/transferwise/pipelinewise-tap-mysql/compare/v1.5.3...v1.5.4) |
| [1.5.3 (2023-04-25)](https://github.com/transferwise/pipelinewise-tap-mysql/releases/tag/v1.5.3) | \* LOG\_BASED: Set mariadb slave capability to 4 to mitigate bug in Mariadb 10.6.12 ([https://github.com/transferwise/pipelinewise-tap-mysql/pull/149](https://github.com/transferwise/pipelinewise-tap-mysql/pull/149))
|
| [1.5.2 (2022-08-12)](https://github.com/transferwise/pipelinewise-tap-mysql/releases/tag/v1.5.2) | \* Bump mysql-replication to 0.30 |
| [1.5.1 (2022-04-05)](https://github.com/transferwise/pipelinewise-tap-mysql/releases/tag/v1.5.1) | Fix: Handle case when BINLOG\_GTID\_POS returns multiple comma separated GTIDs |
| [1.5.0 (2022-03-11)](https://github.com/transferwise/pipelinewise-tap-mysql/releases/tag/v1.5.0) | - Support logical replication using GTID, for both Mariadb & MySql
- Log error message when session sqls fail
- Bump depenedencies to support Mysql 8
- Migrate CI to Github Actions.
|
| [1.4.3 (2021-04-09)](https://github.com/transferwise/pipelinewise-tap-mysql/releases/tag/v1.4.3) | \* Fix in LOG\_BASED method: re-discovery constantly running when table has unsupported column type.
\* Add support for tinytext column type
\* Bump pendulum to 1.5.1
\* Add unit tests and re-arrange tests folder
|
| [1.4.2 (2021-03-15)](https://github.com/transferwise/pipelinewise-tap-mysql/releases/tag/v1.4.2) | Fixing typo introduced by a previous change. |
| [1.4.1 (2021-03-12)](https://github.com/transferwise/pipelinewise-tap-mysql/releases/tag/v1.4.1) | Fix data loss during log\_based replication. |
| [1.4.0 (2020-11-09)](https://github.com/transferwise/pipelinewise-tap-mysql/releases/tag/v1.4.0) | Support MySQL spatial types |
| [1.3.8 (2020-10-16)](https://github.com/transferwise/pipelinewise-tap-mysql/releases/tag/v1.3.8) | Fix mapping bit to boolean values |
| [1.3.7 (2020-09-04)](https://github.com/transferwise/pipelinewise-tap-mysql/releases/tag/v1.3.7) | Fix an issue with converting row to singer record where encountering a `time` type column causes the override of the whole row. |
| [1.3.6 (2020-09-02)](https://github.com/transferwise/pipelinewise-tap-mysql/releases/tag/v1.3.6) | Fixed an issue when every extracted row was logged at `INFO` level and produced huge log files |
| [1.3.5 (2020-08-27)](https://github.com/transferwise/pipelinewise-tap-mysql/releases/tag/v1.3.5) | Fix issue with `time` sql type not properly processed.
Previously, it used to be turned into a datetime in the form of `1970-01-01 HH:MM:SS`, and now it's formatted to only be `HH:MM:SS` |
| [1.3.4 (2020-08-05)](https://github.com/transferwise/pipelinewise-tap-mysql/releases/tag/v1.3.4) | Fix few issues with new discovered schema after changes are detected during LOG\_BASED runtime. |
| [1.3.3 (2020-07-23)](https://github.com/transferwise/pipelinewise-tap-mysql/releases/tag/v1.3.3) | During LOG\_BASED runtime, detect new columns, incl renamed ones, by comparing the columns in the binlog event to the stream schema, and if there are any additional columns, run discovery and send a new SCHEMA message to target. This helps avoid data loss. |
| [1.3.2 (2020-06-15)](https://github.com/transferwise/pipelinewise-tap-mysql/releases/tag/v1.3.2) | - Revert `pymysql` back to `0.7.11`.
`pymysql >= 0.8.1` introducing some not expected and not backward compatible changes how it's dealing with
invalid datetime columns. |
| [1.3.1 (2020-06-15)](https://github.com/transferwise/pipelinewise-tap-mysql/releases/tag/v1.3.1) | - Fix dependency issue by removing `attrs` from `setup.py`
- Bump `pymysql` to `0.9.3` |
| [1.3.0 (2020-05-18)](https://github.com/transferwise/pipelinewise-tap-mysql/releases/tag/v1.3.0) | - Add optional `session_sqls` connection parameter
- Support `JSON` column types |
| [Make logging customizable](https://github.com/transferwise/pipelinewise-tap-mysql/releases/tag/v1.2.0) | |
| [Update bookmark only if binlog position is valid](https://github.com/transferwise/pipelinewise-tap-mysql/releases/tag/v1.1.5) | |
| [Update bookmark when reading bookmark finished](https://github.com/transferwise/pipelinewise-tap-mysql/releases/tag/v1.1.4) | |
| [Update bookmark only before writing state message](https://github.com/transferwise/pipelinewise-tap-mysql/releases/tag/v1.1.3) | |
| [Handle null bytes in Binary type columns using SQL](https://github.com/transferwise/pipelinewise-tap-mysql/releases/tag/v1.1.2) | |
| [Handle padding 0s in Binary type columns](https://github.com/transferwise/pipelinewise-tap-mysql/releases/tag/v1.1.1) | |
| [ Support binary and varbinary columns](https://github.com/transferwise/pipelinewise-tap-mysql/releases/tag/v1.1.0) | |
| [Upgrade mysql-replication package](https://github.com/transferwise/pipelinewise-tap-mysql/releases/tag/v1.0.7) | |
| [Add license classifier](https://github.com/transferwise/pipelinewise-tap-mysql/releases/tag/v1.0.6) | |
| [Remove BINARY and VARBINARY support](https://github.com/transferwise/pipelinewise-tap-mysql/releases/tag/v1.0.5) | |