Default date error

[mxAsad]

In my source db the default date for columns with null date values is 0000-00-00 but the default date for clickhouse null date values is 1970-01-01 and that is causing this error. Is there any fix for it:

TextEdit

File

Edit

Format

View

Window

Help

message.txt

(db.ceel aftab.cu.hop2 2025-01-24 13:51:30,828

ERROR] Error serializing column 'receiver_id_issue into data type 'Nullable(Date32)

Traceback (most recent call last):

col type, write column(data, output, context)

File "/usr/local/lib/python3.10/dist-packeges/clickhovar.coonect/driver/transform.py", line 99, in chunk gen File/us/Local/lib/python3.10/diat Rackages/clickhovar.seongct/datatypes/bAAC.RY", line 214, in write_column

self.write_column_data(column, dest, stx)

File "/usr/local/lib/python3.10/dist-packages/clickhavar.coonect/datatypes/bAABARY", line 229, in write_column_data self. write column binary(column, dest, SX)

File "/usr/local/lib/python3.10/diat neckeges/slisinyar.coonect/datatypes/temporal.py", line 45, in_write_column_binary column [e if x is None else (xgsg).days for x in column)

File "/usr/local/lib/python3.10/dia-reckeges/clickboyar soonest/datatypes/temporal.py", line 45, in

column=[0 if x is None else (x-gsg).days for x in column)

TypeError: unsupported operand type(s) for: 'int' and 'datetime.datetime'

Ideceel aftabsu.hpp? 2825-01-24 13:51:30,854 ERROR) unhandled exception

Traceback (most recent call last):

File "/usr/local/lib/python3.10/dist-packages/mysql_ch_replicator/dh..realicetarany", line 196, in run

self.perform_initial replication()

File "/usr/local/lib/python3.10/diat neckeans/mysql_ch_replicator/dh_carlicetacary", line 240, in perform_initial_replication

self.perform_initial replication_table(table)

File "/usr/local/lib/python3.10/dlat.neckages/mysql_ch_replicator/db_realisatar.py", line 319, in perform initial_replication_table

self.clickhouse api insert(table_name, records, table structure-clickhovar.table structure) File "/usr/local/lib/python3.18/dist-packages/mysql_ch_replicator/clickhevas ARIARY", line 221, in insert

self.client.insert(table-full_table_name, data records_to_insert) File "/usr/local/lib/python3.10/dist-packages/clickhouse.connect/driver/client.py", line 635, in insert

return self.data_insert(context) File "/usr/local/lib/python3.18/dist-packages/clickhevas.coonect/driver/httpclient.py", line 280, in data_insert

response = self._raw_request(plock gen, parans, headers, error_handler error_handler, server_wait=False)

File "/usr/local/lib/python3.10/dist-packages/clickhouse.connect/driver/httpclient.RyY", Line 470, in raw_request

error handler(response)

File "/usr/local/lib/python3.18/dist-packages/clickhevas.coonect/driver/htteclientary", line 265, in error_handler

raise ex

File "/usr/local/lib/python3.18/dist-packeans/clickhevas.conect/driver/transfacm.ox", line 99, in shunkaen

col_type.write_column(data, output, context) File "/usr/local/lib/python3.10/dist-packages/clickhevas.coonect/datatypes/bAAGARY", line 214, in write_column

self.write_column_data(column, dest, stx)

File "/usr/local/lib/python3.18/dist-packages/clickhouse...connect/datatypes/bAAGARY", line 229, in write_column_data self. write_column_binary(column, dest, stx)

File "/usr/local/lib/python3.18/dist-packages/clickhouse connect/datatypes/temporal.py", line 45, in_write_column_binary

column 10 if x is Hone else (xesg) days for x in column)

File "/usr/local/lib/python3.10/dist neckeans/clickhausn.connect/datatypes/temporal.py", line 45, in «liatcamp

column 10 if x is None else (x-csg).days for x in column) TypeError: unsupported operand type(s) for 'int' and 'datetime.datetime

root@ip-20-8-28-159:~/ch/binlog/aftabsu_hop2

[bakwc]

I need a steps to reproduce it. I know there is some bug in handling dates, I spent several hours trying to reproduce it locally, but no chance.
Either provide all steps (eg. what database should I create, what records should I insert, etc.) or provide access to your machine where it's reproduced. Otherwise I would not be able to debug it.

Dates handling logic is inside clickhouse_api.py, line 186 - 207. There is attempt to fix wrong dates there, but probably something is missing.

[mxAsad]

Unfortunately even i only have read access to that database and i can't give them, not to mention that datbase is in a closed network so it can't be accesed from outside anyways but yeah i saw your logic there for handling faulty dates and added some more handlers and handled the year(4) error in converter.py so far i still have to figure out the problem maybe the problem is my database itself maybe there are some corrupt records there I would have to take a closer look on the data itself I will get back to you when i figure out the exact problem. Thank you for your cooperation.

[bakwc]

Please let me know when you find something, you are the second person already who report this issue. Even if data is corrupted / etc, we should handle it, so I would appreciate if you share your findings or any information on this issue.

[mxAsad]

Of course, I will get back to as soon as I start working on the database. Also, do you know of any way to enable replication from ms sql to clickhouse as well? If so please get back to me.

[bakwc]

Also, do you know of any way to enable replication from ms sql to clickhouse as well?

Unfortunately I don't

[mxAsad]

The problem was in the else statement where you were setting the value of e = 0 in clickhouse api file in the part where we had to handle corrupt dates. You should replace it with the default date value. Otherwise when a corrupt recordsl comes, an int is passed and that int is causing the data type mismatch error

[mxAsad]

May be make the except and else statements much more robust to make sure only dates pass through that part. I don't have the code where we handled it otherwise i would have uploaded it as well. So, I can only give you the directions

[bakwc]

Fixed in v0.0.64