Does it work with strict mode = off?

[aszenz]

If a mysql table doesn't use strict mode, has 0000-00-00 dates, doesn't use full group by, does the replication still work?

I'm wondering how the result type conversion handles edge cases like:

1. Mysql decimals
2. Mysql tinyints as booleans
3. mysql non-strict date time values

[baolinhuang]

DuckDB currently does not fully support non-strict modes in sql_mode.
For timestamps like 0000-00-00 00:00:00, DuckDB will refuse to write, may lead to replication errors.
Currently, we support configuring only_full_group_by in sql_mode, which is one of the few sql_mode options we support.

For the result type of select SQL, DuckDB will keep it consistent with MySQL.
Because the DuckDB SQL execution path goes through the MySQL prepare process, and the result set is transformed based on the field_list generated by MySQL prepare.

[aszenz]

DuckDB currently does not fully support non-strict modes in sql_mode. For timestamps like 0000-00-00 00:00:00, DuckDB will refuse to write, may lead to replication errors.

It would be great if the serer could support configurations for working with legacy data like useNullForInvalidDates allowing graceful handling of such values during replication.

Currently, we support configuring only_full_group_by in sql_mode, which is one of the few sql_mode options we support.

So that means queries without full group by's will work in duckdb mode.

For the result type of select SQL, DuckDB will keep it consistent with MySQL. Because the DuckDB SQL execution path goes through the MySQL prepare process, and the result set is transformed based on the field_list generated by MySQL prepare.

That's good to know, i'll try to test it soon, it would be easier if there was a docker setup similar to https://github.com/apecloud/myduckserver?tab=readme-ov-file#replicating-data