You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by "PDGGK (via GitHub)" <gi...@apache.org> on 2026/09/01 06:38:41 UTC
[PR] Decode MySQL binlog dates that have a zero month or a zero day [shardingsphere]
PDGGK opened a new pull request, #39676:
URL: https://github.com/apache/shardingsphere/pull/39676
Changes proposed in this pull request:
- Return MySQL text form from the `DATE`, `DATETIME` and `DATETIME2` binlog decoders when the date has a zero month or a zero day, instead of handing it to `LocalDate`.
MySQL stores a date with a zero month or a zero day whenever `NO_ZERO_IN_DATE` is not in `sql_mode`. Verified against a real server:
```
$ docker run -d mysql:8.0 --log-bin=binlog --binlog-format=ROW --sql-mode=''
$ mysql> INSERT INTO t_date VALUES (1, '2023-00-15', '2023-00-15 10:00:00'),
(2, '2023-05-00', '2023-05-00 10:00:00'),
(3, '2023-05-15', '2023-05-15 10:00:00');
$ mysql> SELECT id, d, dt FROM t_date;
+----+------------+---------------------+
| id | d | dt |
+----+------------+---------------------+
| 1 | 2023-00-15 | 2023-00-15 10:00:00 |
| 2 | 2023-05-00 | 2023-05-00 10:00:00 |
| 3 | 2023-05-15 | 2023-05-15 10:00:00 |
+----+------------+---------------------+
```
Reading the resulting `binlog.000001` back, the row events carry those dates in the ordinary packed encodings — `0f ce 0f` and `a0 ce 0f` for the two `DATE` values, `99 ae de a0 00` and `99 b0 00 a0 00` for the two `DATETIME2` values. Running the decoders' own arithmetic over exactly those bytes:
| column value | payload | current result |
|---|---|---|
| `DATE` `2023-05-15` | `af ce 0f` | `java.sql.Date` 2023-05-15 |
| `DATE` `2023-00-15` | `0f ce 0f` | `DateTimeException: Invalid value for MonthOfYear (valid values 1 - 12): 0` |
| `DATE` `2023-05-00` | `a0 ce 0f` | `DateTimeException: Invalid value for DayOfMonth (valid values 1 - 28/31): 0` |
| `DATETIME2` `2023-00-15 10:00:00` | `99 ae de a0 00` | `DateTimeException: ... MonthOfYear ...: 0` |
| `DATETIME2` `2023-05-00 10:00:00` | `99 b0 00 a0 00` | `DateTimeException: ... DayOfMonth ...: 0` |
The exception propagates out of `MySQLBinlogRowsEventPacket`, so one such row stops the binlog stream for the whole migration or CDC job rather than affecting one column.
Each of these decoders already returns text for the *all*-zero date — `MySQLTimeValueUtils.ZERO_OF_DATE` and `DATETIME_OF_ZERO` — which needs exactly the same relaxed `sql_mode` to exist in a table at all. So the partially zero case is returned the same way, and the `java.sql` types are kept for every date they can hold, leaving anything that works today unchanged. On the CDC path a `String` reaches `ColumnValueConvertUtils` and comes out a `StringValue`, as the zero value already does.
The pre-5.6.4 `MySQLDatetimeBinlogProtocolValue` unpacks a plain decimal `YYYYMMDDHHMMSS`, so its zero-month and zero-day paths reach `LocalDateTime.of` the same way; it is fixed alongside, although a modern server writes `DATETIME2` and I could not produce a live payload for it.
### Tests
Six cases added, two per decoder, using the payloads above:
- `MySQLDateBinlogProtocolValueTest` — `assertReadDateWithZeroMonth`, `assertReadDateWithZeroDay`
- `MySQLDatetimeBinlogProtocolValueTest` — `assertReadDatetimeWithZeroMonth`, `assertReadDatetimeWithZeroDay`
- `MySQLDatetime2BinlogProtocolValueTest` — `assertReadWithZeroMonth`, `assertReadWithZeroDay`
On `master` all six fail with the `DateTimeException` above; the existing cases in those three classes pass unchanged there and here, which pins that complete dates are untouched.
`mvn test -pl database/protocol/dialect/mysql` — 448 tests, and `-pl kernel/data-pipeline/dialect/mysql` — 97 tests, all passing. `checkstyle:check -Pcheck` clean.
Related to #39674, which fixes the same shape of failure in the `TIME2` decoder. The two do not overlap and can be reviewed in either order.
---
Before committing this PR, I'm sure that I have checked the following options:
- [x] My code follows the [code of conduct](https://shardingsphere.apache.org/community/en/involved/conduct/code/) of this project.
- [x] I have self-reviewed the commit code.
- [ ] I have (or in comment I request) added corresponding labels for the pull request.
- [x] I have passed maven check locally : `./mvnw clean install -B -T1C -Dmaven.javadoc.skip -Dmaven.jacoco.skip -e`.
- [ ] I have made corresponding changes to the documentation.
- [x] I have added corresponding unit tests for my changes.
- [ ] I have updated the Release Notes of the current development version. For more details, see [Update Release Note](https://shardingsphere.apache.org/community/en/involved/contribute/contributor/)
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] Decode MySQL binlog dates that have a zero month or a zero day [shardingsphere]
Posted by "terrymanu (via GitHub)" <gi...@apache.org>.
terrymanu merged PR #39676:
URL: https://github.com/apache/shardingsphere/pull/39676
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] Decode MySQL binlog dates that have a zero month or a zero day [shardingsphere]
Posted by "PDGGK (via GitHub)" <gi...@apache.org>.
PDGGK commented on PR #39676:
URL: https://github.com/apache/shardingsphere/pull/39676#issuecomment-5789223097
@terrymanu thanks for the review, and sorry for the delay.
- Locale: `formatIncompleteDate` and `formatIncompleteDatetime` now format with `Locale.ROOT`. `DATE`, `DATETIME` and `DATETIME2` each have a case under a `th-TH-u-nu-thai` default format locale that restores the original afterwards. Without the fix, all three return Thai digits.
- Fractional `DATETIME2`: added partial-zero cases with a non-zero fraction at precision 2, 4 and 6. They read real payload bytes and assert both the full string (e.g. `2023-00-15 10:00:00.123456`) and that the fraction bytes are consumed. I checked that they fail if the suffix is dropped or the fraction is not read on this path.
Description updated.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] Decode MySQL binlog dates that have a zero month or a zero day [shardingsphere]
Posted by "PDGGK (via GitHub)" <gi...@apache.org>.
PDGGK commented on PR #39676:
URL: https://github.com/apache/shardingsphere/pull/39676#issuecomment-5843943395
@terrymanu thanks. The three `MySQLTimeValueUtils` helpers are now package-private, and the `DATE` return expression is on one line. I checked the other wrapped expressions this PR adds against the same rule: the `DATETIME` and `DATETIME2` ternaries are 230 and 287 characters joined, so they stay wrapped before `?` and `:`.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org