Repository navigation
MySQL json columns round JSON doubles in history; store JSON losslessly (3.2.0) #1016
Description
Target: 3.2.0 (General, lobby 7825). Listed as a known limitation in the 3.1.0 notes.
Problem
On MySQL, the history payload column, and other columns declared json, use MySQL's native JSON type. MySQL parses a double itself when it stores the document and keeps its own binary double, which can land one ULP from what the client sent:
SELECT JSON_EXTRACT('{"x":1.4820142857142857}', '$.x'); -- MySQL 8.0.46: 1.4820142857142855
SELECT CAST('{"x":124435.71428571429}' AS JSON); -- {"x": 124435.71428571428}So on MySQL, native's history loses float precision even after #1010 (float_roundtrip):
WorkflowStarted's memo and search attributes;- heartbeat details, read back through activity metadata;
- every other JSON value read from a
jsoncolumn.
Published PHP 2.11.8 on MySQL has the same loss; it's the same schema. MariaDB declares these columns longtext with a json_valid check and keeps every digit, as SQLite and PostgreSQL do.
Evidence: lab2, published PHP 2.11.8 and native at #1011, each on MySQL 8.0.46, gave identical rounded history values. The memo projection, typed search attributes and the visibility list stay exact on native. The json-doubles fixture records this as the reviewed mysql_json_doubles exception.
Options (to decide in the design)
- Store JSON as text on MySQL, as MariaDB does:
longtextwith aJSON_VALIDcheck, for the affected columns. That needs a schema step and migration for existing MySQL deployments, and any SQL that uses JSON functions on those columns must keep working. - Keep the
jsoncolumn and add a raw text copy that readers prefer, written in the same transaction. That's less disruptive to existing queries, at the cost of extra storage.
Either way:
- an upgrade step for existing MySQL databases (native schema version bump), verified like the other schema upgrades;
- rows written before the upgrade keep MySQL's rounded doubles, and the upgrade notes must say so;
- the
json-doublesfixture'smysql_json_doublesexception is removed for native once fixed. It stays for published PHP.
Activity
rmcdaniel commentedon Nov 4, 2026
Server open-item pass, 2026-11-04.
Owner: Server. I reviewed the two candidate storage approaches against the recorded MySQL-only loss: existing rounded rows cannot be reconstructed, and a driver/parser fix alone cannot recover digits already lost in JSON storage. The next source artifact is an affected-column/JSON-query inventory plus a migration/read-compatibility comparison of LONGTEXT+JSON_VALID versus an atomic raw-text shadow. It must specify old-reader behavior, rollback and schema-version handling before selecting a migration.
General: after that bounded design, choose the compatibility/rollback requirement for the future 3.2 slice. No DDL or data rewrite is proposed now. The native mysql_json_doubles exception can be removed only after exact new-write/export/cold-replay qualification; the published PHP exception and the warning about legacy rows remain.
rmcdaniel commentedon Nov 4, 2026
Bounded 3.2 design comparison at accepted source
d5c5750478993b69d3ed7e0ec447fb44bbdfc1d3. This is static source/catalog review only: no database, DDL, image, experiment or frozen-artifact change was made.Physical inventory. The current MySQL bootstrap and version-8 captured catalog agree on 75 native JSON columns across 36 tables. They are the following candidate storage surface; this inventory does not claim that every column currently receives doubles. Existing LONGTEXT Avro/blob columns and typed
value_floatprojections are separate.activity_executionsretry_policy,parallel_group_path,activity_optionsruntime_credentialsroles,claimsruntime_payload_completion_budgetscontext,slots,objectsworkflow_child_callsretry_policy,timeout_policy,arguments,metadataworkflow_commandscontextworkflow_durable_stream_itemspayloadworkflow_durable_streamsmetadataworkflow_history_eventspayloadworkflow_instancesvisibility_labels,memoworkflow_linksparallel_group_pathworkflow_memosvalue,portable_valueworkflow_messagesmetadataworkflow_namespacesexternal_payload_storageworkflow_run_lineage_entriespayloadworkflow_run_summariesvisibility_labelsworkflow_run_timeline_entriespayloadworkflow_run_timer_entriespayloadworkflow_run_waitspayloadworkflow_runsvisibility_labelsworkflow_schedule_history_eventspayloadworkflow_schedulesspec,action,memo,search_attributes,visibility_labels,recent_actions,buffered_actionsworkflow_search_attributesvalue_keyword_listworkflow_service_callsdeadline_policy,idempotency_policy,cancellation_policy,retry_policy,boundary_policy,metadata,outcome_metadata,caller_principal_roles,caller_principal_claimsworkflow_service_endpointsboundary_policy,metadataworkflow_service_operationshandler_binding,deadline_policy,idempotency_policy,cancellation_policy,retry_policy,boundary_policy,metadataworkflow_servicesboundary_policy,metadataworkflow_signal_recordsvalidation_errorsworkflow_taskspayloadworkflow_update_validation_taskscommand_context,validation_errorsworkflow_updatesvalidation_errorsworkflow_worker_build_id_rolloutsworkflow_typesworkflow_worker_compatibility_heartbeatssupportedworkflow_worker_registrationssupported_workflow_types,workflow_definition_fingerprints,workflow_command_contracts,supported_activity_types,capabilities,capability_manifest,process_metricsworkflow_worker_sessionsrequirementsdw_task_completionsreceiptdw_poll_receiptsresponseThe list was independently extracted from 0003_full_schema.sql and checked exactly against 0008_catalog.json. Later current MySQL step files add no further JSON columns. Existing MariaDB bootstrap uses LONGTEXT/JSON_VALID for this document surface. No reviewed historical migration or catalog byte would be rewritten.
Query and adapter impact. MySQL backend templates use JSON_EXTRACT/JSON_UNQUOTE to select failure/activity/timer/signal/selection IDs, bridge metadata, replay-blocked flags, local-execution mode and worker-session IDs. Schedule-list queries extract workflow type/task queue. These functions accept JSON documents supplied as text, so they remain candidate query paths after a text conversion; they still need actual backend qualification. Operator metrics uses JSON_TABLE over a bound workflow-type list, not a precision-authoritative durable numeric document. History/metadata readers also use raw payload casts and byte ceilings; new physical representation must preserve those bounds and index-led page shapes. The native source scan found no production JSON_SET/REPLACE/MERGE mutation that could be assumed safe for arbitrary exact doubles.
Crucially, using a MySQL JSON function to return a nested numeric JSON value can parse/normalize that value again. Exact document reads must select the preserved text and use the existing float-roundtrip application decoder; changing the column type alone is not a blanket precision guarantee for arbitrary SQL projections. MySQL JSON documentation describes parsed document arguments and normalized JSON-valued results. The vendored SQLx MySQL Json adapter already encodes documents as strings and accepts JSON/string/byte-compatible columns, then decodes the returned string. That is source compatibility evidence, not a completed type-migration test.
Recommended decision: prefer one authoritative LONGTEXT document if General accepts a forward native schema upgrade with all writers fenced and backup/restore as the rollback boundary. Keep shadows only if there is a concrete required legacy physical-JSON consumer to preserve, and specify that consumer and its write authority; do not select shadows merely on an assumed old-binary rollback benefit. No PHP release/mixed writer is proposed.
Upgrade and rollback contract before implementation. Use the next available MySQL native schema revision, fresh bootstrap/catalog and explicit earlier-version recognition; current ownership is version 8. Reuse the existing named initialization lock and
rust-upgradingfence. MySQL DDL implicitly commits despite statement-level atomic DDL, so this cannot be represented as one transaction across 36 tables. Group each table's changed columns/checks into a recognized atomic table step, validate that a resumed table is exactly old or exactly new, skip completed table steps, and update the final ledger/marker only after the full target catalog verifies. The current replay path mainly makes CREATE TABLE idempotent; blindly replaying ADD CHECK/ALTER statements is not an acceptable resumability plan. MySQL atomic-DDL rules support that distinction.An enforced CHECK requires an engine version that actually enforces it; MySQL before 8.0.16 parsed and ignored CHECK syntax. The currently inspected source profiles are newer, but the declared 8.0 baseline must not silently become a new patch floor. General must explicitly settle that compatibility boundary if older 8.0 patches remain in scope. See ALTER TABLE / CHECK behavior. MariaDB already keeps text and must not be retyped by a MySQL-only fix. Preserve SQL NULL versus JSON null, all IDs/FKs, task/receipt authority and bounded-reader behavior.
Legacy JSON documents already rounded by MySQL cannot recover lost digits. Backfilling text/shadows can preserve only what remains; migration must not claim reconstructed original values. A rollback to the older schema/binary is a separately reviewed restore/recovery operation, not a down migration that reparses exact new text as JSON. Its handling of post-cutover writes must be explicit before operational qualification.
Named next actions. General chooses the forward-only/restore rollback requirement versus a named legacy physical-JSON consumer, and the enforced-CHECK compatibility boundary. Server then owns the narrow native 3.2 migration/query source plan. Required later qualification covers fresh schema and upgrade, recognized partial DDL/resume, foreign/drifted schema refusal, legacy rounded rows, all new-write readers/export/cold replay, SQL-null/JSON-null, invalid JSON refusal, atomic receipts and exact payload byte ceilings on the selected MySQL lines. Only that exact successful native qualification can retire its
mysql_json_doublesexception; published PHP retains its exception. No database run or source migration is admitted by this design comment.rmcdaniel commentedon Nov 4, 2026
General selects one authoritative LONGTEXT document with enforced JSON_VALID for the native3.2 storage design, rather than an unbounded raw-shadow/dual-write fallback. The inspected SQLx adapter/text query compatibility and exact catalog fencing in 6644479449 provide no concrete legacy physical-JSON consumer that a shadow would preserve. A later genuine consumer requirement needs its named read/write authority and explicit compatibility review, not an assumed old-binary rollback benefit.
Adopt a fenced forward upgrade: all writers stopped/fenced under the existing named initialization ownership lock, recognized atomic per-table DDL steps, exact old/new catalog validation on resume, and final ledger/ownership advance only after the complete target verifies. Do not rewrite historical migration/catalog bytes or call36 tables one transaction. Preserve SQL NULL versus JSON null, identifiers/FKs, authority receipts, collation/defaults and bounded/index-led readers. Precision-authoritative reads select preserved text and decode in the application; SQL JSON projections are not allowed to silently re-normalize those durable numbers. Existing rounded rows remain honestly rounded unless a separately verified authoritative source can rebuild them.
Rollback is explicit: before cutover/new writes, a verified quiescent pre-upgrade restore is possible. After new text-authoritative writes, ordinary recovery stays on the new schema with a qualified compatible binary or forward repair. A pre-upgrade restore is disaster recovery requiring a separately reviewed post-cutover-write reconciliation/RPO decision, not automatic code-only rollback or a down conversion through native JSON that rounds new values.
For this new versioned native schema, General chooses an explicit MySQL8.0.16-or-newer/enforced-CHECK precondition, verified before DDL. Older patches must receive a typed unsupported-schema-upgrade refusal; parsed-but-ignored CHECK is never accepted. Document this candidate schema prerequisite and upgrade impact before any native publication. It does not retroactively change the current published stable declaration or invent old-patch qualification; MariaDB's existing text representation remains separate and unchanged. Final release/version/support documentation and actual selected endpoint qualification are still required before rollout.
Server owns the concrete migration/query/bootstrap/catalog/resume source plan and failure vectors in its assigned3.2 slot. Include partial DDL, drift/foreign schema, invalid JSON, exact float/int64 text, SQL-null/JSON-null, rounded legacy data, all readers/export/cold replay and receipt/byte-bound cases. This is design/source planning only: no DDL, database, migration execution, artifact, compatibility retirement or frozen tuple change is admitted.
rmcdaniel commentedon Nov 5, 2026
Concrete native 3.2 source plan following General's storage decision 6644575285. Source inventory remains exact main
d5c5750478993b69d3ed7e0ec447fb44bbdfc1d3: 75 MySQL JSON columns in 36 tables. This is a source/query/catalog/resumption plan. No SQL, DDL, database, native build, artifact or runtime has run.The target stays one authoritative
LONGTEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_bincolumn with a named enforcedJSON_VALIDconstraint, preserving each existing column's position, NULL/NOT NULL and default contract. SQL NULL and JSONnullremain distinct. No shadow column or dual writer is added. Existing binary-JSON values migrate as the representation MySQL can currently return; digits already normalized by that representation cannot be reconstructed. Future writes/readback must preserve application-produced numeric text and decode through the existing precision-preserving JSON path.The implementation can now be divided without another storage-policy decision:
mysql.rs::Flavorcurrently sharesVERSION=8between MySQL and MariaDB. The JSON precision step must select target version/checksum/catalog by actual flavor; do not silently convert MariaDB or impose the new MySQL patch prerequisite on it. Carry that target throughschema_upgradereporting, takeover journal stamping andbackend.rs::beginownership-row locking. Those are the three externalmysql::VERSIONuses found in the source audit. Assign the actual new version in the implementation slot, without preempting another schema change.GET_LOCKplusrust-upgradingfence. Add a compiled deterministic table/column plan covering all 75 columns. For each affected table, issue one atomicALTER TABLEcontaining every required MODIFY and named CHECK, with stable bounded identifiers derived from compiled names. On entry/resume accept only an exact old or exact new complete definition for each table: old means execute that table's ALTER, new means skip, any third/partially altered definition means typed catalog refusal. A lost connection after successful DDL is resolved by fresh catalog inspection, never by assuming rollback or rerunning ADD CHECK. Unaffected tables must match exactly. Do not widen the existing CREATE-IF-NOT-EXISTS replay loop into an unchecked ALTER loop.rust-development. A failure before that point remains fenced/incomplete. A restart after the final commit uses ordinary target verification; it does not reapply DDL. Older binaries continue to refuse the newer marker before writes.Query/driver work: retain the current SQLx MySQL JSON decoder's accepted text/bytes behavior unless a focused regression proves a driver change necessary. Bind application JSON as text without a MySQL JSON cast; select the authoritative text for payload/receipt/replay reads. Existing
JSON_EXTRACT/JSON_UNQUOTEstring predicates over schedule fields, namespace/query state and registration metadata remain predicates, not a source for recovered numeric payloads. Review every inventoried JSON-function site, raw historyCAST(... AS CHAR), byte bound, generated/index dependency and query-plan assumption against the new type. Keep payload bytes, signed zero, exponent boundaries, nested arrays/objects and exact typed Avro roundtrip as assertions. This does not promise that JSON function numeric results preserve the original token spelling.Required source controls are concrete: fresh target bootstrap; exact v8 upgrade; interrupted-before-fence and after-fence refusal; interruption before/after each of 36 table ALTERs; exact complete-old/new mixed catalog resumption; subset-column/check mutation refusal; missing/extra/NOT ENFORCED constraint refusal; lost DDL connection resolved by catalog; lost final-commit connection resolved by marker/ledger; duplicate upgrader lock contention; older binary refusal; pre-8.0.16 typed refusal with zero DDL; unchanged MariaDB catalog/behavior; NULL/default/column order/index/foreign-key preservation; malformed JSON rejection by the database; and application write/read/HTTP/export/replay precision on the 75-column inventory. Tests must distinguish source failure from setup/unsupported-engine refusal and keep the existing frozen float-history/untouched-file proof as its historical scope.
Recovery is already decided: ordinary post-cutover recovery uses compatible new-schema binaries or forward repair. Restoring a pre-upgrade backup is a separately reviewed disaster-recovery operation with post-cutover writes and RPO reconciled; there is no automatic lossy down conversion. The source/upgrade documentation must name stopped/fenced writers, the prerequisite, backup/restore boundary, interruption/resume and older-binary refusal without implying that an old JSON backup recovers lost digits.
Server owns the implementation and failure vectors in the assigned native 3.2 slot, followed by normal exact-head backend/source qualification and independent Security review. The immediate allowed advance is the compiled table/target-catalog and query delta, not executing an upgrade. No schema version, release tag, existing image, stable support floor or operator database is changed by this plan.
rmcdaniel commentedon Nov 5, 2026
The selected immediate source-planning step is now reviewable in draft #1035, exact head
4bdb83835d2cf984608d2e6f894791c099d4e8b5, treeb1ee3a55a8761a8d85c84f14f139f5c71c1b56f3, base accepted mainc9a75838f0d70fd5237f4cbcf70f9f046f1e5d42. This adds only the compiled plan, required-observation inventory and README; no native code, old SQL/catalog or schema version changes.tests/Fixtures/contracts/mysql-json-text/upgrade-plan.v1.json: 202,592 bytes, SHA-2565c76fbd8d1340e2339f045c75e1c63957dc9fd00f4175c4c5c7db2ec5e644ca5. Exact source pins, 75 columns / 36 table steps, 75 unique enforced-check names, 67 nullable/eight NOT NULL columns, and 24 unaffected relations. Applying the declared replacements to the source gives the 60-relation candidate catalog hash4396886a2273c22d37aee3754a0398b4104021a2a8b5d83c7517c950794fc902.upgrade-vectors.v1.json: 24,672 bytes, SHA-256775070a419c8822c41dae019e7d85ad3450107019041812b86a7487a7b439a5c. Twenty-two required-observation families, all 72 before/after table interruption cells, per-column check/null coverage and seven raw-text binary64/int64 carriers. The existing float authority is hash-pinned and unchanged.Independent source checks compare every bootstrap/catalog column pair, all source hashes, ordinals/attributes/checks, exact reversal of every changed CREATE back to its original bytes, full candidate hash, vector matrices and raw numeric identities. The 24 production query sites exclude test modules and retain their exact source-line hashes. Whitespace/public-boundary/credential-pattern checks pass. Fresh ordinary draft CI is pending.
The candidate CREATE strings are derived source expectations, not captured or accepted MySQL catalogs. Target version and target migration checksum remain null. Actual CREATE presentation/enforcement, valid FK creation order, flavor-specific target identity, bootstrap/upgrade/resume/query/reader source, typed refusal bindings, real endpoint/backend tests and release support docs remain required. The inventory cannot retire
mysql_json_doubles; legacy rounded digits and published PHP's exception remain honest. No SQL, database, DDL, native build, image, migration or runtime ran.Next owners: Security/General review the exact planning artifact in its existing 3.2 scope; Server owns the actual source and future qualification packet. This draft does not enter the #1031/#1033 integration order or claim #1016 complete. It makes the already chosen single-authoritative-text, enforced-CHECK, per-table-resume and forward-recovery decisions concrete without changing the operational gates.
rmcdaniel commentedon Nov 5, 2026
I reconciled the historical planning inputs against actual accepted main
b3388130without changing this PR or starting another CI train.Of the 17 source pins, only
rust/src/runtime/mysql.rsandrust/src/runtime/schedule_list.rsdiffer after the accepted schedule-summary slice. All migrations/catalogs, upgrade authority, other classified query sources, JSON binder and numeric fixture remain exact. The two changed production JSON fragments only qualifyactionass.actionin workflow-type/task-queue string extraction. The static inventory remains 24 sites with the same classifications; this is not a backend-plan result for the newly joined summary query.A private source-only dry recompilation at
b3388130preserves the exact 60-relation catalog, 36 affected tables, 75 JSON columns/checks, complete derived DDL, candidate catalog hash and cumulative migration checksum. All 22 future observation families, seven numeric carriers and 72 interruption cells remain unchanged. Target version/checksum remain null and execution remains disabled. No SQL/DDL, database, image/tool build or runtime operation occurred.The dry plan is 202,596 bytes, SHA-256
f4057de8a5b690d57de5445949000dff6761b219bd13db18d2f253997c86ec69. It is preparation evidence, not a committed successor or a transfer of Security's historical4bdb8383review.Next action: Server owns the later normally refreshed planning successor, repinning the two inputs and aliased query fragments plus the vector's derived plan identity, with fresh ordinary CI and exact Security review when General releases that source slot. Current #1033 integration and ordered #1036 refresh remain first. Actual target assignment/bootstrap/upgrade/resume/text-column/query/backend/runtime qualification stays separately held.
rmcdaniel commentedon Nov 5, 2026
Server has completed the existing planning artifact's current-source reconciliation: #1035 preparation, preserved
007c801fon owned WIP ref. All seventeen input identities now match inspected accepted native source 223; #1031's two changed source files/query inventory are included. The 60/36/75 catalog/table/column plan, target candidate hash, 72 interruption cells and seven numeric carriers are unchanged. Target version/checksum remain null and execution/runtime remain false.Next owner/action remains Server: actual-main refresh and normal source checks after the current #1038 → #1036 → #1039 train, then the still-required versioned bootstrap/catalog/resume/query/reader implementation and precise typed refusal bindings. This update performs no migration, SQL, backend or runtime operation and introduces no blanket Security source-merge gate; privileged upgrade effects retain their separate review/admission.