pingcap
make task config loader.SQL_MODE configurable
Feature Request
Is your feature request related to a problem? Please describe:
If MySQL has some data dumped in session sql_mode, using current global sql_mode to adjust loader's SQL_MODE might be ineffective.
Describe the feature you'd like:
make task config loader.SQL_MODE configurable
Describe alternatives you've considered:
Change downstream TiDB's global sql_mode.
Teachability, Documentation, Adoption, Migration Strategy:
In what case there's "MySQL has some data dumped in session sql_mode", does dumpling have this configuration 🤔
In what case there's "MySQL has some data dumped in session sql_mode", does dumpling have this configuration 🤔
For example, I load some data without "NO_ZERO_IN_DATE" in sql_mode. But the global sql_mode might be "NO_ZERO_IN_DATE". Loader will fail because of this.
related to https://github.com/pingcap/dm/issues/1735
I think we could always mask some SQL mode, to provide better compatibility.
related to #1735
I think we could always mask some SQL mode, to provide better compatibility.
SGTM
@lance6716 what do you mean by "always mask some SQL mode"?
A rough thought, we should remove some SQL mode such as NO_ZERO_IN_DATE when synchronize to downstream, and keep rest of them to handle changes of content made by ANSI_QUOTES, for example.
I disagree. I think the behavior of having the DM worker match the upstream SQL mode is 100% correct, but there should be an option to override that if the data requires it.
I disagree. I think the behavior of having the DM worker match the upstream SQL mode is 100% correct, but there should be an option to override that if the data requires it.
ok we shall left an option to deal with automatic logic going wrong. in the meantime, we'll try to improve automatic logic to reduce manual intervention.
The error number returned by TiDB Server can be interpreted by DM to give better information about the resolution.
Another improvement to DM could be to simply display error output in a more friendly manner. Instead of showing raw JSON with embedded newlines, maybe we can show it in a more friendly, human-readable way?
Something I'm a little confused about here is that the SQL mode needed to be able to execute a statement should be included in the MySQL binary log, so maybe the problem here really is that DM isn't correctly reading that data from the binlog and setting it for downstream?
Something I'm a little confused about here is that the SQL mode needed to be able to execute a statement should be included in the MySQL binary log, so maybe the problem here really is that DM isn't correctly reading that data from the binlog and setting it for downstream?
DM will not read binlog in this phase. DM will load sqls into downstream in this phase. But the sql_mode in upstream can't represent the status when we write these data.