[BUG] TypeError: unsupported operand type(s) for |: 'type' and 'type'

[kopyl]

Bug Description

After running the tool I immediately got this error.

Steps to Reproduce

1. Install with python3.9 -m pip install mysql_ch_replicator
2. Run with python3.9 -m mysql_ch_replicator --config mysql_to_clickhouse_config.yaml db_replicator --db ridesharing --initial_only=True

Expected Behavior

A replication to happen, lol

Actual Behavior

$ python3.9 -m mysql_ch_replicator --config mysql_to_clickhouse_config.yaml db_replicator --db ridesharing --initial_only=True
Traceback (most recent call last):
  File "/opt/homebrew/Cellar/python@3.9/3.9.23/Frameworks/Python.framework/Versions/3.9/lib/python3.9/runpy.py", line 188, in _run_module_as_main
    mod_name, mod_spec, code = _get_module_details(mod_name, _Error)
  File "/opt/homebrew/Cellar/python@3.9/3.9.23/Frameworks/Python.framework/Versions/3.9/lib/python3.9/runpy.py", line 147, in _get_module_details
    return _get_module_details(pkg_main_name, error)
  File "/opt/homebrew/Cellar/python@3.9/3.9.23/Frameworks/Python.framework/Versions/3.9/lib/python3.9/runpy.py", line 111, in _get_module_details
    __import__(pkg_name)
  File "/opt/homebrew/lib/python3.9/site-packages/mysql_ch_replicator/__init__.py", line 1, in <module>
    from .main import main
  File "/opt/homebrew/lib/python3.9/site-packages/mysql_ch_replicator/main.py", line 9, in <module>
    from .config import Settings
  File "/opt/homebrew/lib/python3.9/site-packages/mysql_ch_replicator/config.py", line 34, in <module>
    class Index:
  File "/opt/homebrew/lib/python3.9/site-packages/mysql_ch_replicator/config.py", line 35, in Index
    databases: str | list = '*'
TypeError: unsupported operand type(s) for |: 'type' and 'type'

Environment

• mysql_ch_replicator version:
• Operating System: macOS
• Python version: 3.9 as in the requirements

MySQL Configuration

# Default Homebrew MySQL server config
[mysqld]
# Only allow connections from localhost
bind-address = 127.0.0.1
mysqlx-bind-address = 127.0.0.1

Replicator Configuration

mysql:
  host: 'localhost'
  port: 3306
  user: 'root'
  password: ''

clickhouse:
  host: 'localhost'
  port: 8323
  user: 'default'
  password: 'default'

binlog_replicator:
  data_dir: '/Users/olehkopyl/Dropbox/Development/Veido/City-G/Projects/Jurny/Tasks/optimize-fare/clickhouse/clickhouse_data_dir'
  records_per_file: 100000

databases: 'ridesharing'

[bakwc]

Could you please try on a newer python version, eg. 3.10?

[kopyl]

@bakwc sure, thank you very much. Will do and then update you :)

[kopyl]

@bakwc

/opt/homebrew/opt/python@3.10/bin/python3.10: No module named mysql_ch_replicator.__main__; 'mysql_ch_replicator' is a package and cannot be directly executed

[kopyl]

@bakwc now suddenly running mysql_ch_replicator like this

mysql_ch_replicator --config mysql_to_clickhouse_config.yaml db_replicator --db ridesharing --initial_only=True

became possible.

But now I'm getting this error:

[Errno 61] Connection refused')) executing HTTP request attempt 1 (http://localhost:8123)

When I type clickhouse, it enters the CLI.
But for some reason it does not connect with your tool to default port 8123 :(

Am I missing something?

[kopyl]

Seems like I had to run the ch server:

clickhouse server --daemon

But then I got another issue: the default password has to be '' (empty string).

After setting it to '' i got another error:

$ mysql_ch_replicator --config mysql_to_clickhouse_config.yaml db_replicator --db ridesharing --initial_only=True
[dbrepl ridesharing 2025-09-26 00:55:27,379  WARNING] local timezone EEST may return unexpected times due to Daylight Savings Time/Summer Time differences
[dbrepl ridesharing 2025-09-26 00:55:27,389     INFO] launched db_replicator
[dbrepl ridesharing 2025-09-26 00:55:27,389     INFO] recreating database
[dbrepl ridesharing 2025-09-26 00:55:27,416     INFO] last known transaction None
[dbrepl ridesharing 2025-09-26 00:55:27,450    ERROR] unhandled exception
Traceback (most recent call last):
  File "/opt/homebrew/lib/python3.10/site-packages/mysql_ch_replicator/db_replicator.py", line 232, in run
    self.initial_replicator.create_initial_structure()
  File "/opt/homebrew/lib/python3.10/site-packages/mysql_ch_replicator/db_replicator_initial.py", line 34, in create_initial_structure
    self.create_initial_structure_table(table)
  File "/opt/homebrew/lib/python3.10/site-packages/mysql_ch_replicator/db_replicator_initial.py", line 45, in create_initial_structure_table
    mysql_structure = self.replicator.converter.parse_mysql_table_structure(
  File "/opt/homebrew/lib/python3.10/site-packages/mysql_ch_replicator/converter.py", line 1120, in parse_mysql_table_structure
    raise Exception(f'No primary key for table {structure.table_name}, {create_statement}')
Exception: No primary key for table current_location, CREATE TABLE `current_location` (
  `entity_type` varchar(64) DEFAULT NULL,
  `entity_id` int DEFAULT NULL,
  `lat` double DEFAULT NULL,
  `lon` double DEFAULT NULL,
  `heading` float DEFAULT NULL,
  `speed` float DEFAULT '0',
  `accuracy` float DEFAULT '0',
  `zone_id` int DEFAULT '0',
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `entity` (`entity_id`,`entity_type`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3
Traceback (most recent call last):
  File "/opt/homebrew/bin/mysql_ch_replicator", line 8, in <module>
    sys.exit(main())
  File "/opt/homebrew/lib/python3.10/site-packages/mysql_ch_replicator/main.py", line 184, in main
    run_db_replicator(args, config)
  File "/opt/homebrew/lib/python3.10/site-packages/mysql_ch_replicator/main.py", line 114, in run_db_replicator
    db_replicator.run()
  File "/opt/homebrew/lib/python3.10/site-packages/mysql_ch_replicator/db_replicator.py", line 232, in run
    self.initial_replicator.create_initial_structure()
  File "/opt/homebrew/lib/python3.10/site-packages/mysql_ch_replicator/db_replicator_initial.py", line 34, in create_initial_structure
    self.create_initial_structure_table(table)
  File "/opt/homebrew/lib/python3.10/site-packages/mysql_ch_replicator/db_replicator_initial.py", line 45, in create_initial_structure_table
    mysql_structure = self.replicator.converter.parse_mysql_table_structure(
  File "/opt/homebrew/lib/python3.10/site-packages/mysql_ch_replicator/converter.py", line 1120, in parse_mysql_table_structure
    raise Exception(f'No primary key for table {structure.table_name}, {create_statement}')
Exception: No primary key for table current_location, CREATE TABLE `current_location` (
  `entity_type` varchar(64) DEFAULT NULL,
  `entity_id` int DEFAULT NULL,
  `lat` double DEFAULT NULL,
  `lon` double DEFAULT NULL,
  `heading` float DEFAULT NULL,
  `speed` float DEFAULT '0',
  `accuracy` float DEFAULT '0',
  `zone_id` int DEFAULT '0',
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `entity` (`entity_id`,`entity_type`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3

So your tool does not allow replicating tables with no primary key? :(

[kopyl]

I added a column to my table, but got another error:

[dbrepl ridesharing 2025-09-26 01:10:17,648  WARNING] local timezone EEST may return unexpected times due to Daylight Savings Time/Summer Time differences
[dbrepl ridesharing 2025-09-26 01:10:17,675     INFO] launched db_replicator
[dbrepl ridesharing 2025-09-26 01:10:17,675     INFO] recreating database
[dbrepl ridesharing 2025-09-26 01:10:17,697     INFO] last known transaction None
[dbrepl ridesharing 2025-09-26 01:10:17,748    ERROR] unhandled exception
Traceback (most recent call last):
  File "/opt/homebrew/lib/python3.10/site-packages/mysql_ch_replicator/db_replicator.py", line 232, in run
    self.initial_replicator.create_initial_structure()
  File "/opt/homebrew/lib/python3.10/site-packages/mysql_ch_replicator/db_replicator_initial.py", line 34, in create_initial_structure
    self.create_initial_structure_table(table)
  File "/opt/homebrew/lib/python3.10/site-packages/mysql_ch_replicator/db_replicator_initial.py", line 49, in create_initial_structure_table
    clickhouse_structure = self.replicator.converter.convert_table_structure(mysql_structure)
  File "/opt/homebrew/lib/python3.10/site-packages/mysql_ch_replicator/converter.py", line 415, in convert_table_structure
    clickhouse_field_type = self.convert_field_type(field.field_type, field.parameters)
  File "/opt/homebrew/lib/python3.10/site-packages/mysql_ch_replicator/converter.py", line 403, in convert_field_type
    clickhouse_type = self.convert_type(mysql_type, mysql_parameters)
  File "/opt/homebrew/lib/python3.10/site-packages/mysql_ch_replicator/converter.py", line 397, in convert_type
    raise Exception(f'unknown mysql type "{mysql_type}"')
Exception: unknown mysql type "multipolygon"
Traceback (most recent call last):
  File "/opt/homebrew/bin/mysql_ch_replicator", line 8, in <module>
    sys.exit(main())
  File "/opt/homebrew/lib/python3.10/site-packages/mysql_ch_replicator/main.py", line 184, in main
    run_db_replicator(args, config)
  File "/opt/homebrew/lib/python3.10/site-packages/mysql_ch_replicator/main.py", line 114, in run_db_replicator
    db_replicator.run()
  File "/opt/homebrew/lib/python3.10/site-packages/mysql_ch_replicator/db_replicator.py", line 232, in run
    self.initial_replicator.create_initial_structure()
  File "/opt/homebrew/lib/python3.10/site-packages/mysql_ch_replicator/db_replicator_initial.py", line 34, in create_initial_structure
    self.create_initial_structure_table(table)
  File "/opt/homebrew/lib/python3.10/site-packages/mysql_ch_replicator/db_replicator_initial.py", line 49, in create_initial_structure_table
    clickhouse_structure = self.replicator.converter.convert_table_structure(mysql_structure)
  File "/opt/homebrew/lib/python3.10/site-packages/mysql_ch_replicator/converter.py", line 415, in convert_table_structure
    clickhouse_field_type = self.convert_field_type(field.field_type, field.parameters)
  File "/opt/homebrew/lib/python3.10/site-packages/mysql_ch_replicator/converter.py", line 403, in convert_field_type
    clickhouse_type = self.convert_type(mysql_type, mysql_parameters)
  File "/opt/homebrew/lib/python3.10/site-packages/mysql_ch_replicator/converter.py", line 397, in convert_type
    raise Exception(f'unknown mysql type "{mysql_type}"')
Exception: unknown mysql type "multipolygon"

[bakwc]

So your tool does not allow replicating tables with no primary key? :(

Unfortunately this is ClickHouse limitations, not a tool. For ClickHouse every table must have primary key.

Exception: unknown mysql type "multipolygon"

This is on our end. multipolygon is not very popular type, I will add support of it in a next few days.

[kopyl]

@bakwc thank you very much :)

Could you please ping me once you do it?

[bakwc]

Added multipolygon support, @kopyl please check the recent release v0.0.94

[kopyl]

@bakwc thank you very much :)

Here is what I've done:

1. Created a new environment for just this package.
2. pip install git+https://github.com/bakwc/mysql_ch_replicator.git (to install the latest version)

And it worked. It replicated the database

Now I just have a few questions:

1. How to add a table to the existing database?
2. Where can I find the database storage file? I thought that it's supposed to be data_dir from the yaml config file, but it only has the directory with the same name as the replicated database, which has only 2 files: state.pckl and db_replicator.log totalling of 56KB (I was expected to have 5-10GB at least)

[bakwc]

1. Just add it to mysql, and it should be created in CH automatically. (Better test on dev environment with similar DB to avoid issues, eg when adding some unsupported type etc).
2. mysql_ch_replicator does not store DB content itself. It only store a binary log (what was changed), and only for some limited time (eg. 8 hours, configurable). So untill you start heavy writes you would not see any major data there. The final data is stored inside ClickHouse, you should check where clickhouse storage configured.

Also keep in mind that if you run with --initial_only=True it will not track any updates. It will run only once and exit. To copy all changes you should run it without --initial_only=True, and don't turn off (you could turn off but for some small period of times, eg restarts, reboots etc).

[kopyl]

@bakwc thank you :)

But do you know where are the database files are written to on macOS?
On Linux it's usually under /var/lib/clickhouse/.