Skip to content

Commit 4aa768b

Browse files
TheLastCicadaTheLastCicada
committed
fix: parse SQLite DATE strings before forwarding to MariaDB mirror
The prior revision patched Sequelize's base DATE._stringify to force a MariaDB-safe format, on the hypothesis that some steady-state write paths bypassed the mysql.DATE subclass. Empirical tracing (all three write paths - single INSERT, bulk INSERT, INSERT ... ON DUPLICATE KEY UPDATE - instrumented against the query generator) shows that is NOT the case: every MySQL write dispatches through mysql.DATE.stringify, which already emits "YYYY-MM-DD HH:mm:ss". The actual source of the residual "Incorrect datetime value" CI errors was the reconnect-backfill path. `source.findAll({ raw: true })` returns SQLite DATE columns as raw strings ("YYYY-MM-DD HH:mm:ss.SSS +00:00") without running sqlite.DATE.parse. The strings were forwarded verbatim into `mirror.bulkCreate`, which MariaDB strict mode rejected. Fix: drop `raw: true` from backfill's findAll calls. Model instances run sqlite.DATE.parse during construction, so DATE columns become Date objects on dataValues; mysql.DATE then serialises them safely. Use `.get({ plain: true, raw: true })` to extract the plain payload - plain:true alone runs attribute-level `get()` accessors, which would corrupt columns like ProjectV2.projectSector (stored as JSON, returned as Array by the getter) when bulkCreate'd into the mirror's STRING/TEXT column. The sqlite PK-only orphan sweep keeps `raw: true` - its projection excludes DATE and custom-getter columns, and a comment now documents that invariant. Revert the previous base-class `_stringify` monkey-patch and the no-op `timezone: "+00:00"` constructor options (that value is already Sequelize's default). Simplify the datetime regression test to pin only the mysql subclass's output contract - the one Sequelize API we actually depend on.
1 parent 5d23732 commit 4aa768b

4 files changed

Lines changed: 76 additions & 103 deletions

File tree

‎src/config/config.js‎

Lines changed: 0 additions & 62 deletions
Original file line numberDiff line numberDiff line change
@@ -1,55 +1,8 @@
1-
import Sequelize from 'sequelize';
2-
import moment from 'moment';
31
import { getConfig, getConfigV2 } from '../utils/config-loader';
42
import { getChiaRoot } from '../utils/chia-root.js';
53
import { logger } from './logger.js';
64
import { createHash } from 'crypto';
75

8-
// Force MariaDB-compatible datetime serialisation across all code paths.
9-
//
10-
// Sequelize 6's default BaseTypes.DATE._stringify formats dates as
11-
// "YYYY-MM-DD HH:mm:ss.SSS Z" (e.g. "2026-04-17 17:07:13.399 +00:00")
12-
// which recent MariaDB strict mode rejects:
13-
// Incorrect datetime value: '2026-04-17 17:07:13.399 +00:00'
14-
// for column `cadt_mirror_test`.`audit`.`createdAt` at row 1
15-
//
16-
// The mysql-specific subclass emits a safe format ("YYYY-MM-DD HH:mm:ss"),
17-
// but some runtime paths in Sequelize end up calling the BASE class's
18-
// _stringify even when the column belongs to a MySQL dialect. Observed
19-
// in CI: V2 mirror produced ~60 "Incorrect datetime value" errors per
20-
// live-api run even with `timezone: "+00:00"` set at the Sequelize
21-
// constructor level.
22-
//
23-
// Fix: patch BASE DATE._stringify to emit the MariaDB-safe format, and
24-
// explicitly restore the original "YYYY-MM-DD HH:mm:ss.SSS Z" format on
25-
// the SQLite subclass (whose `parse()` relies on the trailing offset
26-
// for round-trip). This leaves MySQL/MariaDB writes safe regardless of
27-
// which dispatch path Sequelize chooses, without breaking SQLite.
28-
const _origBaseStringify = Sequelize.DataTypes.DATE.prototype._stringify;
29-
Sequelize.DataTypes.DATE.prototype._stringify = function _stringify(
30-
date,
31-
options,
32-
) {
33-
if (!moment.isMoment(date)) {
34-
date = this._applyTimezone(date, options);
35-
}
36-
// Drop fractional seconds and the " Z" offset. MariaDB strict mode
37-
// rejects " +00:00". CADT doesn't use sub-second resolution on any
38-
// DATE column.
39-
return date.format('YYYY-MM-DD HH:mm:ss');
40-
};
41-
// SQLite's DATE class only overrides `parse`, not `_stringify`, so the
42-
// base-class patch above would otherwise silently change the format of
43-
// SQLite writes. SQLite's `parse()` appends options.timezone when the
44-
// string lacks a "+"/"-" offset - if we stripped the offset on write,
45-
// parse() would concatenate the offset on read, producing a value
46-
// different from what was written. Shadow an own-property `_stringify`
47-
// on `sqlite.DATE.prototype` that points to the original base
48-
// serialiser, so SQLite retains the "YYYY-MM-DD HH:mm:ss.SSS Z" format.
49-
if (Sequelize.DataTypes.sqlite && Sequelize.DataTypes.sqlite.DATE) {
50-
Sequelize.DataTypes.sqlite.DATE.prototype._stringify = _origBaseStringify;
51-
}
52-
536
const chiaRoot = getChiaRoot();
547
const persistanceFolder = `${chiaRoot}/cadt/v1`;
558
const v2PersistanceFolder = `${chiaRoot}/cadt/v2`;
@@ -108,18 +61,6 @@ export default {
10861
host: getConfig().MIRROR_DB.DB_HOST || '',
10962
dialect: 'mysql',
11063
logging: mirrorLogging,
111-
// Tell Sequelize to serialise Date values as UTC without a trailing
112-
// "+00:00" offset. Recent MariaDB strict mode rejects the
113-
// "YYYY-MM-DD HH:MM:SS.SSS +00:00" format Sequelize emits by default,
114-
// with errors like:
115-
// Incorrect datetime value: '2026-04-16 22:41:27.490 +00:00'
116-
// for column `cadt_mirror_test`.`audit`.`createdAt` at row 1
117-
// Setting timezone at the Sequelize constructor level (as opposed to
118-
// Model.init, where it is silently ignored) produces the MariaDB-safe
119-
// "YYYY-MM-DD HH:MM:SS" format. Without this, every mirror write that
120-
// touches a DATE/DATETIME column is silently dropped by the
121-
// fire-and-forget safeMirrorDbHandler.
122-
timezone: '+00:00',
12364
},
12465
// V2 Database Configurations
12566
v2Local: {
@@ -155,8 +96,5 @@ export default {
15596
host: getConfigV2().MIRROR_DB?.DB_HOST || '',
15697
dialect: 'mysql',
15798
logging: mirrorLogging,
158-
// See comment on `mirror` above. Same MariaDB strict-mode datetime
159-
// issue applies to V2.
160-
timezone: '+00:00',
16199
},
162100
};

‎src/database/index.js‎

Lines changed: 24 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -332,6 +332,11 @@ const sweepMirrorOrphans = async (source, mirror, name) => {
332332
}
333333
const pkAttr = pkAttrs[0];
334334

335+
// `raw: true` is safe here - the projection is PK-only, so there are
336+
// no DATE or custom-getter columns whose raw SQLite representation
337+
// could leak into a bulk write (which is the hazard backfillMirror
338+
// avoids). If a future change adds more projected attributes, revisit
339+
// the raw:true pattern (see backfillMirror for the safe variant).
335340
const mirrorPkRows = await mirror.findAll({
336341
attributes: [pkAttr],
337342
raw: true,
@@ -475,13 +480,29 @@ export const backfillMirror = async () => {
475480
while (true) {
476481
const where =
477482
lastPk === null ? undefined : { [pk]: { [Sequelize.Op.gt]: lastPk } };
478-
const rows = await source.findAll({
479-
raw: true,
483+
// NOTE: Do NOT pass `raw: true` to findAll. With raw:true
484+
// Sequelize returns SQLite values as-stored (strings),
485+
// including DATE columns as "YYYY-MM-DD HH:mm:ss.SSS +00:00".
486+
// Forwarding those strings verbatim to MariaDB's bulkCreate
487+
// triggers strict-mode "Incorrect datetime value" rejections.
488+
// Building model instances runs sqlite.DATE.parse so DATE
489+
// columns become real Date objects; mysql.DATE then serialises
490+
// them in the MariaDB-safe "YYYY-MM-DD HH:mm:ss" format.
491+
//
492+
// `.get({ plain: true, raw: true })` extracts dataValues
493+
// directly (bypassing any attribute-level `get()` accessors
494+
// a source model may define). Dates remain Date objects on
495+
// dataValues because the sqlite parser runs during instance
496+
// construction, not at get() time.
497+
const instances = await source.findAll({
480498
where,
481499
limit: BACKFILL_BATCH_SIZE,
482500
order: [[pk, 'ASC']],
483501
});
484-
if (rows.length === 0) break;
502+
if (instances.length === 0) break;
503+
const rows = instances.map((r) =>
504+
r.get({ plain: true, raw: true }),
505+
);
485506
await mirror.bulkCreate(rows, { updateOnDuplicate: updateFields });
486507
synced += rows.length;
487508
lastPk = rows[rows.length - 1][pk];

‎src/database/v2/index.js‎

Lines changed: 26 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -496,13 +496,31 @@ export const backfillMirrorV2 = async () => {
496496
while (true) {
497497
const where =
498498
lastPk === null ? undefined : { [pk]: { [Sequelize.Op.gt]: lastPk } };
499-
const rows = await source.findAll({
500-
raw: true,
499+
// NOTE: Do NOT pass `raw: true` to findAll. With raw:true
500+
// Sequelize returns SQLite values as-stored (strings),
501+
// including DATE columns as "YYYY-MM-DD HH:mm:ss.SSS +00:00".
502+
// Forwarding those strings verbatim to MariaDB's bulkCreate
503+
// triggers strict-mode "Incorrect datetime value" rejections.
504+
// Building model instances runs sqlite.DATE.parse so DATE
505+
// columns become real Date objects; mysql.DATE then serialises
506+
// them in the MariaDB-safe "YYYY-MM-DD HH:mm:ss" format.
507+
//
508+
// `.get({ plain: true, raw: true })` extracts dataValues
509+
// directly (bypassing any attribute-level `get()` accessors -
510+
// e.g. ProjectV2.projectSector returns a parsed Array via its
511+
// getter, but the mirror column stores the raw JSON string).
512+
// Dates are still Date objects on dataValues because the
513+
// sqlite parser runs during instance construction, not at
514+
// get() time.
515+
const instances = await source.findAll({
501516
where,
502517
limit: BACKFILL_BATCH_SIZE,
503518
order: [[pk, 'ASC']],
504519
});
505-
if (rows.length === 0) break;
520+
if (instances.length === 0) break;
521+
const rows = instances.map((r) =>
522+
r.get({ plain: true, raw: true }),
523+
);
506524
await mirror.bulkCreate(rows, { updateOnDuplicate: updateFields });
507525
synced += rows.length;
508526
lastPk = rows[rows.length - 1][pk];
@@ -581,6 +599,11 @@ const sweepMirrorOrphansV2 = async (source, mirror, name) => {
581599
}
582600
const pkAttr = pkAttrs[0];
583601

602+
// `raw: true` is safe here - the projection is PK-only, so there are
603+
// no DATE or custom-getter columns whose raw SQLite representation
604+
// could leak into a bulk write (which is the hazard backfillMirrorV2
605+
// avoids). If a future change adds more projected attributes, revisit
606+
// the raw:true pattern (see backfillMirrorV2 for the safe variant).
584607
const mirrorPkRows = await mirror.findAll({
585608
attributes: [pkAttr],
586609
raw: true,

‎tests/v2/integration/mirror-datetime-format.spec.js‎

Lines changed: 26 additions & 35 deletions
Original file line numberDiff line numberDiff line change
@@ -1,54 +1,45 @@
11
import { expect } from 'chai';
22
import Sequelize from 'sequelize';
33

4-
// Import config.js (via the barrel) to install the DATE._stringify patch.
5-
// The patch is applied once at module load; this file only exercises it.
6-
// eslint-disable-next-line no-unused-vars
7-
import _config from '../../../src/config/config.js';
8-
94
/**
105
* Regression test for the Sequelize mirror datetime format.
116
*
12-
* Recent MariaDB strict mode rejects Sequelize's default DATE serialization
13-
* of "YYYY-MM-DD HH:mm:ss.SSS Z" (e.g. "2026-04-17 17:07:13.399 +00:00")
14-
* with "Incorrect datetime value". `src/config/config.js` patches the base
15-
* DATE._stringify to emit "YYYY-MM-DD HH:mm:ss" for MySQL/MariaDB while
16-
* retaining the original format for SQLite (whose parse() depends on the
17-
* trailing offset for round-trip).
7+
* Recent MariaDB strict mode rejects Sequelize's base DATE serialization
8+
* ("YYYY-MM-DD HH:mm:ss.SSS Z", e.g. "2026-04-17 17:07:13.399 +00:00")
9+
* with "Incorrect datetime value".
10+
*
11+
* Normal mirror writes are safe because Sequelize dispatches to the
12+
* dialect-specific mysql.DATE class, which emits "YYYY-MM-DD HH:mm:ss"
13+
* (no offset, no fractional seconds). Empirically verified: single
14+
* INSERT, bulk INSERT, and INSERT ... ON DUPLICATE KEY UPDATE all route
15+
* through mysql.DATE._stringify and never touch the base-class method.
16+
*
17+
* This test pins that contract so a future Sequelize upgrade that changes
18+
* the mysql subclass serialiser (e.g. to emit an ISO "T" separator, or
19+
* to re-introduce a trailing offset) is caught here rather than silently
20+
* in a live-api run.
1821
*
19-
* If this test fails, CADT's MySQL mirror writes will silently drop every
20-
* insert that touches a DATE/DATETIME column in strict-mode MariaDB.
22+
* Separately, backfill's findAll({ raw: true }) path would have returned
23+
* SQLite DATE values as offset-bearing strings and forwarded them to
24+
* MariaDB verbatim - that class of bug is prevented by the
25+
* `.get({ plain: true, raw: true })` call in backfillMirror[V2], which
26+
* has its own coverage in the V1/V2 reconnect specs.
2127
*/
22-
describe('Mirror datetime serialization', function () {
28+
describe('Mirror datetime serialization (mysql subclass contract)', function () {
2329
const sampleDate = new Date('2026-04-17T17:07:13.399Z');
2430

25-
it('BASE DATE._stringify must emit the MariaDB-safe format (no offset, no fractional seconds)', function () {
26-
const out = Sequelize.DataTypes.DATE.prototype.stringify(sampleDate, {
31+
it('mysql DATE._stringify must emit "YYYY-MM-DD HH:mm:ss" (no offset, no fractional seconds)', function () {
32+
const out = Sequelize.DataTypes.mysql.DATE.prototype.stringify(sampleDate, {
2733
timezone: '+00:00',
2834
});
2935
expect(out).to.equal('2026-04-17 17:07:13');
30-
expect(out).to.not.include('+');
31-
expect(out).to.not.include('Z');
32-
expect(out).to.not.include('.');
33-
});
34-
35-
it('SQLite DATE._stringify must retain the original format (with trailing offset) for round-trip', function () {
36-
const out = Sequelize.DataTypes.sqlite.DATE.prototype.stringify(
37-
sampleDate,
38-
{ timezone: '+00:00' },
39-
);
40-
// SQLite's own parse() needs this offset suffix to reconstruct a Date
41-
expect(out).to.include('+00:00');
42-
const parsed = Sequelize.DataTypes.sqlite.DATE.parse(out, {
43-
timezone: '+00:00',
44-
});
45-
expect(parsed.getTime()).to.equal(sampleDate.getTime());
4636
});
4737

48-
it('mysql DATE._stringify must continue to emit the MariaDB-safe format', function () {
38+
it('mysql DATE._stringify must honour the constructor-level timezone option', function () {
39+
// Non-UTC timezone must shift the formatted wall-clock time.
4940
const out = Sequelize.DataTypes.mysql.DATE.prototype.stringify(sampleDate, {
50-
timezone: '+00:00',
41+
timezone: '+05:30',
5142
});
52-
expect(out).to.equal('2026-04-17 17:07:13');
43+
expect(out).to.equal('2026-04-17 22:37:13');
5344
});
5445
});

0 commit comments

Comments
 (0)