# Jync 捷同-异构数据库实时同步工具 **Repository Path**: mgb/jync ## Basic Information - **Project Name**: Jync 捷同-异构数据库实时同步工具 - **Description**: Jync 捷同 - 极简、极速的异构数据库实时同步工具。单 JAR 部署，无需 Kafka/Zookeeper，零外部依赖。支持 MySQL、Oracle、PostgreSQL 及众多信创数据库。内置 DDL 自动同步、幂等 upsert、断点续传，内网离线可用。沐编程 (QQMu) 出品 | 官网: https://jync.qqmu.com - **Primary Language**: Java - **License**: MIT - **Default Branch**: main - **Homepage**: https://jync.qqmu.com - **GVP Project**: No ## Statistics - **Stars**: 0 - **Forks**: 18 - **Created**: 2026-10-13 - **Last Updated**: 2026-10-13 ## Categories & Tags **Categories**: Uncategorized **Tags**: None ## README # Jync · Real-Time Heterogeneous Database Sync > **Jync**, pronounced exactly like "sync" — the **J** stands for Java. > A minimal four-letter coinage and a member of the J-family (sibling project: Jargus, a Java code-review platform). > One JAR, ready to run — no Kafka, no ZooKeeper, not a single line of code. [简体中文](README.md) | **English** [![License: MIT](https://img.shields.io/badge/License-MIT-blue.svg)](LICENSE) [![Java 17+](https://img.shields.io/badge/Java-17%2B-orange.svg)](https://openjdk.org/) [![Spring Boot 2.7](https://img.shields.io/badge/Spring%20Boot-2.7-brightgreen.svg)](https://spring.io/projects/spring-boot) ![Jync · Heterogeneous Database Real-time Synchronization Tool](docs/jync.png) A ready-to-run web application that keeps **schema and data** in sync **across heterogeneous databases** in real time. Start a single jar, click a few times in your browser, and **any two of the 17 built-in database types (plus a custom type)** form a continuous sync link — Oracle → PostgreSQL and MySQL → Dameng are just two examples among them — **no Kafka, no ZooKeeper, not a single line of code.** - Repository: - China mirror: - Demo video: (Bilibili, ~10 min; narration in Chinese) Stack: Spring Boot 2.7 monolith + Thymeleaf server-side rendering + Quartz scheduling + embedded H2 metadata store. **Zero external dependencies, fully usable on an air-gapped intranet.** --- ## Table of Contents - [Screenshots](#screenshots) - [Features](#features) - [Comparison With Existing Tools](#comparison-with-existing-tools) - [Supported Databases](#supported-databases) - [Deployment](#deployment) - [Login & Roles](#login--roles) - [Workflow](#workflow) - [Concurrency & Consistency Design](#concurrency--consistency-design) - [Incremental Detection Strategies](#incremental-detection-strategies) - [Binlog CDC (MySQL)](#binlog-cdc-mysql) - [Log CDC (Oracle LogMiner)](#log-cdc-oracle-logminer) - [Log CDC (PostgreSQL logical decoding)](#log-cdc-postgresql-logical-decoding) - [Log CDC (SQL Server CDC)](#log-cdc-sql-server-cdc) - [Configuration](#configuration) - [AI-Assisted Conversion (optional)](#ai-assisted-conversion-optional) - [Architecture](#architecture) - [Tests](#tests) - [Known Limitations](#known-limitations) - [License](#license) --- ## Screenshots ### Sign-in — one password stands between the jar and your connections Username and password, with theme and language switchable from the top-right corner at any time. Until the default password is changed, every page carries a red warning at the top — dismissable for the session, but back as soon as the browser reopens, and gone for good only once the password actually changes. ![Sign-in](docs/jync_login.png) ### Dashboard — the whole sync posture on one screen Project count, connection count, synced tables, 24-hour change volume, and a recent activity feed. The project overview and the activity feed show **at most five rows each**, so the dashboard never grows with the log; the card headers link through to the full project list and change log. ![Dashboard](docs/jync_kanban.png) ### Dark theme — the whole palette moves, not just the background One toggle in the top-right corner. Dashboard, cards, tables, and icons all follow, instead of a black background left studded with glaring light-mode controls. The toggle's moon / sun icon is fixed golden yellow, legible on either palette. ![Dashboard in dark theme](docs/jync_kanban_anye.png) ### Bilingual UI — one click switches the whole interface The "中 / EN" button in the navbar flips every menu, form, status, and error message between Chinese and English; the choice is remembered in the browser. Until the user picks one explicitly, the default language is inferred from the browser timezone. ![English interface](docs/jync_kanban_en.png) ### Database connections — sources and targets in separate lists, test before you save Connections are split by purpose into a Source table and a Target table, each paginated on its own; the project form's source and target selectors only offer connections from the matching list, so the two sides can no longer be mixed up. Pick a database type and the JDBC URL is generated for you — preview it, test it. Nearly every database driver ships inside the distribution; for GBase, Oscar, and custom drivers, just point at a jar and it loads dynamically. ![Database connections](docs/jync_db.png) ### Adding a connection — pick the purpose first; the URL writes itself Choose Source (data is read from it during sync) or Target (data is written into it), then fill in host, port, and database name; the JDBC URL appears as you type, so there is no need to remember each vendor's connection-string shape. The password is encrypted on the way into storage, and the connection can be tested before you commit it. ![Adding a connection](docs/jync_db_add.png) ### Projects — many pipelines in parallel, start and pause at will Each project is one source → target pair, independently startable and pausable, with status and last-sync time at a glance. The list is paginated — twenty rows per page by default, with 15 / 20 / 30 / 50 / 100 selectable in the footer plus a jump-to-page box; long page ranges collapse into ellipses, and clicking an ellipsis widens that side by five pages in place instead of laying out every number at once. ![Projects](docs/jync_xiangmu.png) ### New project — one tested connection per side Fill in the name and description, pick one tested connection from each of the source and target lists; saving lands you on the project detail page to choose sync objects. ![New project](docs/jync_xiangmu_add.png) ### Project detail — per-table selection with visible cursor strategy Tables / views / stored procedures grouped for selection, with search and bulk actions. Every table's incremental detection strategy is labeled inline — `IDENTITY` and `NONE` are called out prominently, because they mean updates may not propagate. Clicking "Sync now" while a cycle is in flight is not discarded: one extra round runs automatically once the current round releases the lock, and any number of clicks coalesce into exactly one such rerun. ![Project detail](docs/jync_xiangmu_xiangqing.png) ### Change log — every change is traceable Object name, change type, affected row count, elapsed time, and full error detail. The same pager as the project list; from page two on, auto-refresh is disabled with the reason in its tooltip — the log is newest-first, so an arriving entry would otherwise shuffle the rows you are reading downwards. ![Change log](docs/jync_log.png) ### AI providers — several configured, exactly one active Enabling another switches the current one off. The list shows the protocol, the model, and the last probe result; rendering it never reaches out to the network. ![AI providers](docs/jync_ai.png) ### Adding a provider — probe the endpoint before saving The key is encrypted like a database password. The probe sends a real request rather than a TCP check — a wrong key, a misspelled model, and a base URL that is one path segment off are invisible to anything less. ![Adding a provider](docs/jync_ai_add.png) ### AI drafting — the candidate streams into the editor `Draft with AI` on the review page sends the source procedure body to the active provider and the candidate streams into the editor as it is produced, instead of arriving in one blob when the timeout is nearly up. When the draft finishes the page shows a single reminder: the output is a candidate only — run it against the target database yourself before saving it as an override. The editor renders SQL with DBeaver-style highlighting (keywords, strings, numbers and comments in separate colours); the green banner in the shot is the target accepting the statement from the temporary-object syntax check. ![AI drafting](docs/jync_xiangmu_suoyin_ai.png) ### System information — runtime settings and version on one page Instance ID, Java version, scheduled project count, sync defaults, and the concurrency/recovery safeguards are gathered as read-only info. The version card shows the running version, the latest GitHub release, and this version's release notes. The update check runs in the background and never slows the page down — on an air-gapped intranet, the local version number and bundled notes still render normally. ![System information](docs/jync_system.png) --- ## Features | Area | Capability | |---|---| | **Project management** | Configure source/target databases; run many projects in parallel without interference | | **Connection management** | Connections are classified as source or target, shown in separate paginated lists; existing connections are categorized automatically on upgrade | | **Connection testing** | Verify connectivity before saving; preview the auto-assembled JDBC URL | | **Object selection** | Tables / views / stored procedures, all selected by default, with search and bulk checkboxes | | **Sync scope** | Table schema, table data, indexes, views, stored procedures and functions | | **Auto object creation** | Missing objects are created in the target using the target's dialect | | **SQL dialect adaptation** | Type mapping, function-name conversion, identifier quoting, pagination syntax, stored-procedure wrapping | | **Real-time sync** | Polling (2s default), or precise orchestration via Cron expressions; MySQL sources can switch per project to Binlog CDC — streamed binlog replay with second-level latency that captures DELETEs too | | **Crash recovery** | Resumes from the last cursor after a restart; changes made while down are backfilled | | **Chunked initial load** | The full load commits and checkpoints per primary-key keyset chunk; an interruption resumes at the last chunk instead of re-reading the whole table | | **Parallel full load** | Single-column integer-PK tables split into disjoint MIN/MAX ranges copied in parallel (`sync.full-load-parallelism`, capped at 8) | | **Start/stop control** | Start or pause anytime; manual "Sync Now" supported | | **Sync-now rerun** | A click during a run is not discarded: one extra round follows automatically, and repeated clicks coalesce into one | | **Change records** | Object, change type, row count, duration, and error detail for every run | | **Dashboard** | Projects, connections, synced tables, 24-hour change volume, recent activity; overview and feed capped at five rows each | | **Pagination** | Project and change-log lists paginate at 20 rows/page by default, with 15/20/30/50/100 selectable, jump-to-page, and in-place ellipsis expansion | | **i18n** | Chinese / English switch; default language inferred from browser timezone | | **Theming** | Light/dark toggle; follows the OS until the user picks one | | **Fully local frontend** | No CDN, no webfont, zero outbound requests at runtime — works offline | ### Frontend Design Indigo-to-pink gradient accent (`#4f46e5` → `#ec4899`), soft shadows, glassmorphic navbar. Styles are hand-written CSS plus design tokens (CSS variables) — **no framework, no build step.** **Theme switching** - The theme lives on `` and only overrides CSS variables, so components need no second set of dark rules - Storage key `jync-theme` (localStorage) - **Follows the OS** `prefers-color-scheme` until the user has chosen explicitly, and reacts live to OS theme changes; once the toggle is clicked, the user's choice wins permanently - An inline `` script applies the theme before first paint, so dark-mode users never see a white flash **Default language resolution** (highest priority first) | Priority | Source | Notes | |---|---|---| | 1 | Language cookie | An explicit click on the language button always wins | | 2 | **Browser timezone** | `Asia/Shanghai`, `Asia/Hong_Kong`, `Asia/Taipei`, `Asia/Macau`, … → Chinese; everything else → English | | 3 | `Accept-Language` | Fallback for the very first request, before the timezone is reported | | 4 | Simplified Chinese | Final default | **Why timezone outranks `Accept-Language`:** overseas Chinese users often run an English-language browser while sitting in a Chinese timezone. Timezone is the better signal for "which language do you actually want to read." Because rendering is server-side, the language must be decided before render, so a frontend script probes the timezone and writes the `JYNC_TZ` cookie; the server's `TimezoneAwareLocaleResolver` reads it to pick the Locale. On a first visit, if the rendered language disagrees with the timezone inference, the page refreshes once; after an explicit language choice it never refreshes again. **Offline / intranet ready** — every frontend asset lives in the repo, and **no external request is made at runtime**: | Asset | Notes | |---|---| | `css/app.css` | Hand-written styles, including design tokens and the dark theme | | `js/theme.js`, `js/app.js` | Vanilla JS — no jQuery, no framework | | `vendor/css/bootstrap-icons.min.css` + `vendor/fonts/*.woff2` | Icon font, self-hosted | | `vendor/favicon.svg` | Inline gradient SVG | Typography uses the system font stack (`PingFang SC` / `Microsoft YaHei` / …) — **no webfont**. Small graphics like dropdown arrows are inline `data:` URIs. Total static payload is roughly 440 KB. To verify: fetch any page's HTML and count external `http(s)` URLs in `src`/`href` — the answer is **0**. --- ## Comparison With Existing Tools ### At a glance > Basis of comparison: the table below reflects each tool's official documentation / open-source edition in its default deployment (as of 2026-09), and covers only the "out-of-the-box continuous sync across heterogeneous databases" scenario. Every tool has its own positioning and strengths; for scenarios where Jync is the wrong fit, see "When **not** to use Jync" below. | Dimension | **Jync** | Debezium + Kafka | Canal | Flink CDC | DataX | Kettle | SymmetricDS | Navicat/DBeaver transfer | |---|---|---|---|---|---|---|---|---| | **Deployment** | **One jar** | Kafka + Connect + ZK/KRaft | Canal Server (+MQ) | Flink cluster (JM/TM) | CLI scripts | Desktop + repository | Engine on every node | Desktop client | | **External deps** | **None** | Kafka, ZooKeeper | ZooKeeper (cluster) | Flink, checkpoint store | None (but JSON jobs) | JVM + plugins | Triggers in source DB | None | | **How you configure it** | **Click in a web UI** | YAML/REST + consumer code | Config file + client code | SQL/DataStream code | JSON job files | Drag-and-drop ETL | properties + create triggers | Wizard | | **Continuous incremental sync** | Supported: polling/Cron & log CDC (MySQL binlog, Oracle LogMiner) | Supported: log-based | Supported: binlog | Supported: log-based | N/A (one-shot batch by design) | Partial (roll your own scheduling & delta logic) | Supported: trigger-based | N/A (one-shot transfer by design) | | **Schema (DDL) sync** | Supported: **auto-creates tables/indexes/views/procs** | Partial (emits DDL events; applying them is your job) | Partial (events only) | Partial (custom code) | Not supported (tables must pre-exist) | Partial (manual mapping) | Partial | Supported (one-shot only) | | **Heterogeneous dialect conversion** | Supported: types/functions/quoting/paging/procs | Not supported (positioned as event output; conversion is the consumer's job) | Not supported (positioned as binlog subscription; no dialect conversion) | Partial | Partial (limited type mapping) | Partial (manual) | Partial | Partial (one-shot mapping) | | **Chinese domestic databases** | Supported: **DM / KingBase / OceanBase / TiDB / HighGo / Vastbase / YashanDB / GBase / Oscar / OpenGauss** | Partial (no official domestic-DB connector; community or DIY) | Not supported (MySQL family only, binlog-based by design) | Partial (a few) | Partial (needs custom plugins) | Partial (generic JDBC only) | Partial | Partial | | **Intrusiveness to source** | **Default polling mode: read-only queries, zero intrusion**; Binlog CDC mode adds replication grants (still read-only binlog parsing) | binlog/WAL + replication privileges | binlog required | binlog/WAL required | read-only | read-only | **must create triggers** | read-only | | **Idempotency / resume** | Supported: PK upsert + persisted cursor | Supported: offsets | Supported | Supported: checkpoints | N/A (one-shot batch) | Partial (roll your own delta logic) | Supported | N/A (one-shot transfer) | | **Built-in monitoring** | Supported: dashboard + change log | Prometheus/Grafana required | DIY | Flink UI (job-level) | logs | limited | Web console | N/A (one-shot transfer, no continuous monitoring) | | **Air-gapped** | Supported: no outbound calls | Supported | Supported | Supported | Supported | Supported | Supported | Supported | | **Best fit** | Small/medium, departmental, domestic-DB migration | Large-scale streaming | MySQL ecosystem | Large-scale streaming | Bulk offline loads | Complex ETL | Multi-master replication | Ad-hoc data moves | > Legend: Supported = capability provided out of the box　Partial = partial support or extra work required　Not supported = capability not provided　N/A = dimension outside that tool's positioning ### Five differences that actually matter **1. "One jar" versus "a whole platform"** Debezium and Flink CDC are excellent streaming frameworks, but standing up a single MySQL → PostgreSQL pipeline means Kafka, Kafka Connect, a coordination service, and then a consumer you write yourself to translate events into target-side DML. The Jync equivalent is: `java -jar jync.jar`, open a browser, create two connections, create a project, click Start. **When the size of the sync need doesn't justify the operational cost of a streaming platform, this gap is decisive.** **2. Schema sync is a first-class feature, not homework left for you** Most CDC tools solve only the data stream; the target tables are yours to create. DataX explicitly requires them to pre-exist. Jync reads source metadata and creates tables, indexes, views, and stored procedures **in the target's dialect**, then propagates the diff after source-side DDL changes. In a heterogeneous migration, DDL translation and type mapping is usually more work than moving the rows. **3. Built for Chinese domestic databases and localization migrations** Dameng (DM), KingBase, OceanBase, TiDB, HighGo, Vastbase, YashanDB, GBase, Oscar, and OpenGauss are preset first-class options — not "you can probably reach it over generic JDBC," but dedicated dialect implementations: the `MERGE INTO ... FROM DUAL` upsert form, type ceilings (Oracle `VARCHAR2` 4000), function-name differences, and identifier quoting rules are all handled. Oracle/SQL Server → domestic-DB replacement is this tool's home turf, and it happens to be exactly the scenario the Debezium and Canal ecosystems were not designed for — their official domestic-DB support is relatively limited. **4. Zero intrusion into the source database (default polling mode)** SymmetricDS requires triggers in the source. Debezium, Canal, and Flink CDC require binlog / WAL logical replication to be enabled plus replication privileges — on many production databases that is a change request with an approval workflow attached. Jync's **default polling mode** needs one **read-only account** and derives deltas from cursor-column queries — the source's schema and configuration are untouched. With [Binlog CDC](#binlog-cdc-mysql) enabled, the sync account additionally needs REPLICATION SLAVE / REPLICATION CLIENT grants — still strictly read-only (binlog parsing only: no triggers, no writes to the source), but no longer the bare minimum of a single read-only account. **5. One-shot data movement versus staying in sync** Navicat's and DBeaver's "data transfer", and DataX, solve "copy this data across, once." Jync solves "keep both sides consistent, indefinitely": resume from the cursor after a restart, backfill changes that happened while down, and make every row write idempotent. Those are two different problems. ### When *not* to use Jync Being honest about the boundaries: - **You need millisecond latency** → use Debezium / Flink CDC. [Binlog CDC](#binlog-cdc-mysql) mode (MySQL sources only) delivers second-level latency and replays changes in strict binlog order; a polling design's floor on latency is the poll interval. Binlog CDC does not capture DDL, and non-MySQL sources remain polling-only. - **You need physical-delete capture on large tables** → with Binlog CDC (MySQL sources), DELETEs are natively visible and replicated at any table size; other sources keep the polling limitation — without a source-side audit table, delete detection requires comparing full primary-key sets, so it's only enabled below `full-compare-max-rows`. - **One-time initial load of hundreds of millions of rows** → tools built for bulk throughput, like DataX, will be faster. - **You need complex ETL transformation (cleansing, aggregation, multi-stream joins)** → use Kettle / Flink. Jync does **synchronization**, not **transformation**. - **Bidirectional multi-master replication** → use SymmetricDS. This tool assumes it is the sole writer to the target. --- ## Supported Databases MySQL, MariaDB, Oracle, SQL Server, DB2, PostgreSQL, OpenGauss, **Dameng (DM)**, **KingBase**, **OceanBase**, **TiDB**, **HighGo**, **Vastbase**, **YashanDB**, **GBase**, **Oscar**, H2, plus **custom databases** (supply a JDBC URL, driver class name, and driver jar path — loaded dynamically at runtime). Every driver above except **GBase** and **Oscar** ships inside the distribution, so those types work out of the box with no extra jar. GBase and Oscar publish no official artifact on Maven Central and are not redistributed here: obtain the vendor jar yourself and fill in its path; the same applies to custom types. External jars are loaded through a dedicated `URLClassLoader` and registered with `DriverManager` via a `DriverShim`, isolated from the application classloader so driver version conflicts can't pollute the main app. See [THIRD_PARTY_LICENSES.md](THIRD_PARTY_LICENSES.md) for the bundled drivers' versions and licenses. PolarDB publishes no official JDBC artifact on Maven Central — pick the MySQL or PostgreSQL type according to the edition's protocol; GBase 8s likewise has none, so use the custom type. --- ## Deployment ### Requirements | Item | Requirement | |---|---| | JDK | **17 or newer** | | Maven | 3.6+ (build time only) | | Memory | ≥ 512 MB heap recommended | | Port | `8080` by default | | Disk | Metadata store + logs; reserve ~1 GB | ### 1. Build ```bash git clone https://github.com/vfaner/jync.git # In mainland China, use the mirror: # git clone https://gitee.com/super_rgh/jync.git cd jync mvn clean package -DskipTests ``` Artifact: `target/jync.jar` (executable fat jar). ### 2. Run ```bash java -jar target/jync.jar ``` Open . The first launch creates the following under the **current working directory**: | Directory | Contents | |---|---| | `./data` | The tool's own metadata (H2 file DB: connections, projects, cursors, locks, change log, metadata snapshots) | | `./logs` | Runtime logs | > ⚠️ These are **relative paths**. Always start from the same directory, or override them with absolute paths — otherwise a restart won't find your existing data. ### 3. Production configuration (important) Create `application.yml` next to the jar: ```yaml server: port: 8080 spring: datasource: url: jdbc:h2:file:/opt/jync/data/jync;MODE=MySQL;AUTO_SERVER=TRUE sync: poll-interval: 2000 # polling interval in ms batch-size: 500 fetch-size: 1000 chunk-size: 100000 # rows per full-load chunk; each commits + checkpoints; 0 = no chunking full-load-parallelism: 1 # parallel workers (single-column integer-PK tables only, capped at 8) safety-lag-ms: 1000 row-count-audit-interval-ms: 60000 full-compare-max-rows: 20000 lock-ttl-ms: 300000 crypto-password: replace-with-your-own-strong-secret # ← MUST change crypto-salt: replace-with-your-own-hex-salt # ← MUST change logging: file: path: /opt/jync/logs ``` Start with: ```bash java -jar jync.jar --spring.config.additional-location=file:./application.yml ``` > ⚠️ **Use `additional-location` (additive overlay), never `spring.config.location`**: the latter *replaces* all default config locations, so the jar-internal `application.yml` is no longer read — built-in keys such as i18n (`spring.messages.basename`), schema auto-creation (`spring.jpa.hibernate.ddl-auto`) and `app.github-url` are all lost. The symptoms are markers like `??login.title_zh_CN??` on the login page, no tables on a fresh install, or outright startup failure. The external file only needs the keys you want to **override**. > 🔐 **Security note:** `sync.crypto-password` and `sync.crypto-salt` encrypt the stored database passwords. **The distribution ships with defaults; you must change them in production.** After changing them, previously stored passwords can no longer be decrypted and must be re-entered in the UI. `crypto-salt` must be a valid hexadecimal string. ### 4. Loading external driver jars (GBase / Oscar / custom) Drivers for MySQL, MariaDB, Oracle, SQL Server, DB2, PostgreSQL, OpenGauss, Dameng, KingBase, OceanBase, HighGo, Vastbase, YashanDB, and H2 ship inside the distribution (TiDB speaks the MySQL protocol and reuses the MySQL driver), so **this step is not needed for them**. Only **GBase**, **Oscar**, and **custom databases** require an external jar. Place the vendor jar on the server, for example: ```bash mkdir -p /opt/jync/drivers cp gbase-jdbc.jar oscar.jar /opt/jync/drivers/ ``` When one of these types is selected, the form automatically shows a **Driver jar** card. Just fill in the **driver jar path** — either a single jar file or a directory of jars, e.g. `/opt/jync/drivers`. Custom types also need a JDBC URL and a driver class; the **Detect driver classes** button can read candidate class names straight from the jar. Click **Test Connection** to confirm it loads, then save. ### 5. Running as a service **Option A: systemd (recommended)** `/etc/systemd/system/jync.service`: ```ini [Unit] Description=Jync Database Sync After=network.target [Service] Type=simple User=jync WorkingDirectory=/opt/jync ExecStart=/usr/bin/java -Xms512m -Xmx1g -jar /opt/jync/jync.jar \ --spring.config.additional-location=file:/opt/jync/application.yml Restart=always RestartSec=10 [Install] WantedBy=multi-user.target ``` ```bash sudo systemctl daemon-reload sudo systemctl enable --now jync sudo systemctl status jync ``` **Option B: nohup (quick trial)** ```bash cd /opt/jync nohup java -jar jync.jar > /dev/null 2>&1 & ``` ### 6. Upgrading ```bash sudo systemctl stop jync cp target/jync.jar /opt/jync/jync.jar sudo systemctl start jync ``` The metadata store uses `ddl-auto: update`, so its schema evolves automatically. **Back up `./data` before upgrading.** Source-side changes that occur while the tool is down are backfilled by the cursor mechanism on restart — nothing is lost. --- ## Login & Roles Every page and every endpoint requires a signed-in session. There is no anonymous entry point. On first start two accounts are seeded into the `app_user` table of the metadata store (skipped entirely if the table already has rows — nobody's password is ever reset): | Username | Role | Initial password | Permissions | |---|---|---|---| | `admin` | Administrator | `123456` | Everything | | `view` | Viewer | `123456` | Read-only | > ⚠️ **Change both passwords immediately after your first sign-in.** The jar is publicly downloadable, so the initial password is not a secret. Any account still on it sees a persistent yellow banner at the top of every page; clicking it jumps to the change-password form. ### What the two roles differ on Authorization is not a list of paths — it is decided **by HTTP method**. Every write in this tool is a POST and no GET mutates state, so there is exactly one rule: **a POST requires the administrator role.** New endpoints therefore cannot be forgotten. - **Administrator**: create/edit/delete database connections, projects and AI providers; select sync objects, start/pause sync, sync now, reset progress; draft and save procedure conversions; clear the change log. - **Viewer**: sees every page and all of the data (dashboard, connection list, the checked state on project detail, the change log, and the SQL on the conversion review page — all viewable, selectable and copyable), but no write control is rendered anywhere, and hand-crafting the request to hit the endpoint directly is refused too. The config block on project detail is kept visible and natively greyed out rather than hidden, because the selection state is itself useful read-only information. Both roles can change their own password. ### Changing your own password Click your username in the top-right → **Change password**, or go straight to `/account/password`. The current password is required; the new one must be at least 6 characters and must differ from the current one. The change signs you out immediately — sign back in with the new password. Login passwords are stored as one-way BCrypt hashes, unlike the database and AI credentials, which must be recoverable in cleartext to hand to a driver. **A forgotten password cannot be recovered.** If it really is lost, delete that row from `app_user`; the initial password is re-seeded on the next restart. --- ## Workflow 1. **Database Connections** → create a source and a target connection → click **Test Connection** 2. **Projects** → create a project → pick the source and target 3. Open **project detail** → check the tables/views/procedures to sync → configure sync options → save 4. Click **Sync Now** for a one-off verification (a click during a run queues exactly one extra round once the current one finishes), or **Start Sync** to begin continuous polling 5. Inspect each run in the **Change Log** --- ## Concurrency & Consistency Design This is the heart of the tool and deserves its own section. ### Initial full load: chunked, resumable, parallel The initial full load is no longer "one big query that reads the whole table". Tables with a primary key are paged by keyset (`WHERE pk > ? ORDER BY pk` — no OFFSET, so deep pagination does not decay); every `sync.chunk-size` rows (100,000 by default) are committed and the page boundary is written to the `chunk_checkpoint` table: - **Crash / killed process** → on restart, committed chunks are skipped and the load resumes at the checkpoint boundary; the small overlap at the boundary converges through the idempotent upsert - **A completed full load** → the table's checkpoints are deleted; checkpoint rows exist only while a load is in flight - `sync.chunk-size: 0` disables chunking and restores the classic single streamed read - **Keyless tables** cannot chunk (no key to page on, no idempotency): they are still streamed in one read and an interruption restarts the table from the beginning — the project detail page badges them "no checkpointed resume" On top of chunking, tables with a **single-column integer primary key** (including Oracle `NUMBER(p≤18,0)` zero-scale keys) can also load in parallel: `SELECT MIN(pk), MAX(pk)` bounds the key space, which is split into disjoint ranges that up to `sync.full-load-parallelism` workers (capped at 8) page through independently. Workers borrow and return their connection per chunk, so parallelism beyond `sync.max-pool-size` (5 by default) simply queues on the pool. A key span wider than a long can safely express, or bounds that cannot be read, fall back to sequential; composite-key and keyless tables stay sequential — parallelism is enabled only where splitting is provably safe. While a load runs, the project detail page shows "x of y ranges done, n chunks committed" live. ### The incremental window is a closed interval Each cycle, when syncing table data: 1. **First**, read `MAX(cursor column)` from the source as this window's upper bound (the high watermark) 2. Query rows where `cursor > last cursor AND cursor <= this watermark` 3. Write to the target and commit 4. **Only after a successful commit**, advance the cursor to the watermark The upper bound in step 2 is the crux. Without it, a long-running read could advance the cursor past data it never actually read — those rows would be skipped forever. With the bound in place, rows written to the source during the sync fall naturally outside the window and get picked up next cycle. ### The cursor advances only after data is committed Source and target are two separate heterogeneous databases; there is no cross-database transaction. So the chosen order is: **commit target data first, then persist the cursor.** - Crash between the two → the same window is replayed next time - Crash before the target commit → the transaction rolls back, and it is likewise replayed Neither case loses data. The cost is that a row may be delivered twice, which the next point absorbs. ### Every write is idempotent Every row is written via a primary-key-based upsert, so repeated execution converges to the same state: | Database | Statement | |---|---| | MySQL / MariaDB | `INSERT ... ON DUPLICATE KEY UPDATE` | | PostgreSQL family | `INSERT ... ON CONFLICT (pk) DO UPDATE` | | Oracle / DM | `MERGE INTO ... USING (SELECT ? FROM DUAL)` | | SQL Server | `MERGE ... WITH (HOLDLOCK)` | | DB2 | `MERGE INTO ... USING (VALUES (?))` | | Generic / custom | UPDATE-then-INSERT (fallback where no native upsert exists, with unique-conflict retry) | This turns "at-least-once delivery" into "effectively exactly-once." Deletes are likewise primary-key-conditional, so replaying them has no side effects. > **Tables without a primary key:** existing rows cannot be identified, so idempotency cannot be guaranteed. The tool warns explicitly and recommends adding a primary key. ### Timestamp safety lag A timestamp is fixed when the statement executes, but a row only becomes visible to us when its transaction commits. A source transaction that **starts early and commits late** may carry a timestamp below a watermark we have already passed — and would then be skipped forever. So when persisting the watermark, we roll it back by `sync.safety-lag-ms` (1 second by default). The cost is that a few already-synced rows inside that window are re-delivered, which idempotent writes absorb; the benefit is that late-committing transactions are never lost. ### Single-instance execution: three layers of locking | Layer | Coverage | Gap | |---|---|---| | `@DisallowConcurrentExecution` | The same job never fires concurrently within one scheduler | Quartz only, not manual runs; lost on process restart | | JVM `ReentrantLock` (per project) | Manual "Sync Now" and scheduled runs are mutually exclusive in-process | Useless across processes | | Database lock row (with expiry) | Cross-process, cross-node | — | The database lock is the layer that survives restarts: in-memory locks die with the process, and with only in-memory locking a new instance could not tell whether a hard-killed predecessor is still running. The lock row carries a lease (`sync.lock-ttl-ms`, 5 minutes by default) so a crashed instance's lock can be taken over instead of blocking the project forever; on startup, an instance also proactively releases locks it left behind. Acquisition uses a conditional UPDATE (`WHERE lock_owner IS NULL OR lock_owner = ? OR lock_expires_at < ?`), so when two instances race, only one UPDATE can match — they can never both hold the lock. ### Clicks during a run: coalesced into one rerun "Sync Now" and the scheduled trigger share the three locks above. A click that finds the lock held no longer ends at a curt "already running" — the request registers a **rerun**: once the current round finishes and releases the lock, a background thread runs one more round. N clicks during a run coalesce into **one** rerun, never a queue of N; and if the rerun itself finds the lock taken again (a scheduled trigger got there first, say), it re-registers until it is consumed. A scheduled fire that lands mid-run takes the same path, so "the round I missed" is made up as well. ### Recovering from schema changes Metadata snapshots are persisted in the `metadata_snapshot` table, and each object updates its own snapshot immediately after its DDL is applied successfully. Therefore: - DDL changes that happened on the source while the tool was down are detected on restart via snapshot comparison - If a run crashes partway, already-applied objects keep their snapshots and the rest are re-detected next cycle - `CREATE TABLE` tolerates "already exists" errors, making schema sync replayable too ### Row-count auditing A cursor can prove "how far I read," but not "the target still holds those rows." So every `sync.row-count-audit-interval-ms` (60 seconds by default), a row-count audit reconciles the target's actual row count against what the cursor claims was delivered, recording any drift in the change log. --- ## Incremental Detection Strategies ### What actually triggers a sync Incremental sync is one query: ```sql SELECT * FROM table WHERE cursor_col > last_cursor AND cursor_col <= watermark ORDER BY cursor_col ``` **Whether a row gets synced depends on one thing only: whether its cursor column value has risen above the last recorded value.** Everything below follows from that: - Cursor on a **single numeric primary key** (`id`): an UPDATE does not change `id`, so the row's cursor value stays put → **updates never propagate**. Only inserts get through. - Cursor on a **creation timestamp** (`create_time`): same story — a creation time does not move when the row is modified → **updates never propagate**. - Cursor on a **last-modified timestamp** (`update_time`): updates are detected, but **only if that column is actually changed**. If the DDL has no `ON UPDATE CURRENT_TIMESTAMP` and your statement is `UPDATE t SET name = 'x' WHERE id = 1` (never setting `update_time`), the column does not move and the row is never picked up. In other words, **the cursor column must be one that increases whenever a row is touched.** Otherwise modifications are invisible to the sync. This is not a defect; it is the unavoidable price of doing incremental replication with ordinary queries. Nothing in the source tells us a row was modified, so the column value has to say it. The default polling mode never reads the source's binlog and never installs triggers; with [Binlog CDC](#binlog-cdc-mysql) enabled, Jync parses the source's binlog read-only (REPLICATION SLAVE / REPLICATION CLIENT grants required) — still no triggers and no writes to the source, while UPDATEs and DELETEs become directly visible and the cursor-column caveats above no longer apply. ### Auto-detection order Chosen in descending order of reliability: | Strategy | Trigger | Inserts | Updates | |---|---|:---:|---| | `TIMESTAMP` | A temporal-typed column matching the **last-modified** naming convention | ✅ | ✅ provided that column really is updated | | `ROW_SCN` | Oracle source, and the table was created `WITH ROWDEPENDENCIES` (row-level `ORA_ROWSCN`) | ✅ | ✅ deletes are not detected; each cycle reads `MAX(ORA_ROWSCN)`, a full scan | | `IDENTITY` | A column matching the **creation-time** convention, or a single numeric primary key | ✅ | ❌ never detected | | `FULL_COMPARE` | No usable cursor column, and row count ≤ `sync.full-compare-max-rows` (default 20000) | ✅ | ✅ full-table upsert every cycle | | `NONE` | No usable cursor column and the table is too large | ❌ | ❌ initial full load only, then skipped with a stated reason | `ROW_SCN` is the Oracle-only fallback: a table with no timestamp columns at all can still use per-row commit SCNs as its cursor when it was created `WITH ROWDEPENDENCIES` — no DBA table change required. A real last-modified column still wins (portable, index-friendly). Block-level SCNs (tables built without `ROWDEPENDENCIES`) are too coarse and are **never** offered; DM and YashanDB reuse the Oracle metadata reader, but the probe is whitelisted by product name to real Oracle only. Names accepted as **last-modified** (16, resolve to `TIMESTAMP`): `UPDATE_TIME` `UPDATED_AT` `UPDATETIME` `UPDATED_TIME` `LAST_MODIFIED` `LASTMODIFIED` `LAST_UPDATE` `LAST_UPDATED` `MODIFY_TIME` `MODIFIED_AT` `MODIFIED_TIME` `GMT_MODIFIED` `ROW_VERSION` `ROWVERSION` `SYS_UPDATE_TIME` `DATA_CHANGE_TIME` Names accepted as **creation-time** (7, resolve to `IDENTITY`, updates undetectable): `CREATE_TIME` `CREATED_AT` `CREATETIME` `CREATED_TIME` `GMT_CREATE` `INSERT_TIME` `ADD_TIME` Matching rules: case-insensitive, `-` treated as `_`, matched as a **substring** (`biz_update_time_utc` counts as a hit), and the lists are tried in the order above so the most specific convention wins. **The column's type must also genuinely be temporal** — a `VARCHAR` named `update_time` is not used as a cursor, because string comparison ordering is unreliable. By the same rule `ROW_VERSION` / `ROWVERSION` only matches when its type really is temporal; SQL Server's `rowversion` is a binary type and does not qualify. ### Setting the cursor column manually **You can set a cursor column per table** on the project detail page; it takes precedence over auto-detection. But what you pick is a **column**, not a strategy — the column's type decides which strategy you end up with: | Type of the chosen column | Resulting strategy | Note | |---|---|---| | Temporal (`DATETIME` / `TIMESTAMP` / `DATE` / `TIME`) | `TIMESTAMP` | Detects updates | | Numeric (`INT` / `BIGINT` / `DECIMAL`, …) | `IDENTITY` | **Still cannot detect updates** | | Anything else, or the column does not exist | Ignored, falls back to auto-detection | Logged as a WARN | So there is **no way** to force a table into `FULL_COMPARE` from the UI: it is selected automatically only when no usable cursor column exists at all and the table is small enough. Tables resolving to `IDENTITY` or `NONE` are flagged on the project detail page along with the reason, because it means updates may not propagate. ### When updates are not propagating **The durable fix** — give the source table a last-modified column that genuinely moves: ```sql -- MySQL ALTER TABLE your_table ADD COLUMN update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP; ``` With that name you do not need to configure anything; the next cycle resolves it to `TIMESTAMP` automatically. On databases without an equivalent "rewrite on update" clause, the writer (application code or a trigger) has to maintain the column. **Catching up on updates already missed** — **stop the sync first**, then hit **Reset progress** on the project detail page (resetting is refused while the project is enabled, with a message telling you to stop it). This deletes all cursor progress and structure snapshots for the project, so the next cycle performs a full load: the table is re-read and every row upserted, previously missed updates go across, and the structure is re-baselined. It is a one-time catch-up though: unless the cursor column itself is fixed, the next UPDATE will be missed again. --- ## Binlog CDC (MySQL) Polling derives increments from a cursor column: it inherently cannot see DELETEs and depends on a column that grows whenever a row is touched (previous chapter). Since v1.5.0, MySQL sources can switch per project to the **Binlog CDC** capture strategy: Jync poses as a read-only replica, parses the source's binlog and streams the changes into the target — second-level latency, capturing INSERT / UPDATE / DELETE alike. It **installs no triggers and writes nothing to the source**; the binlog is read only. Switch strategies on the capture-strategy card of the project detail page. Before switching, the tool runs a **readiness probe** that itemizes which prerequisites pass or fail. The detail page also carries a CDC status panel: position, lag, throughput, rows applied, skip counters and the last error. ### How it works 1. **Record the position** — on start, the current binlog position (file + offset, plus GTID set (when GTID mode is on)) is persisted to the `cdc_position` table *before* the full load begins, so changes made during the baseline are already covered by the resume point. 2. **Chunked full baseline** — existing rows are loaded into the target in primary-key keyset chunks (reusing the polling mode's chunk/checkpoint machinery). A crash mid-baseline reruns the whole baseline; no half-seeded state survives. 3. **Streamed replay** — once the baseline completes, the binlog is consumed from the recorded position and replayed transaction by transaction. The consistency contract is **at-least-once** with a data-first position discipline: each binlog transaction is committed to the target first, the daemon lease is re-checked, and only then does the persisted position advance. A crash anywhere in between replays the overlap window after restart, and the idempotent upserts (the same write engine polling uses) absorb the replay — nothing is lost and nothing duplicates. ### Source prerequisites The probe's seven checks; any failure is itemized with its reason on the strategy card: | # | Check | Requirement | |---|---|---| | 1 | Version | MySQL ≥ 5.7 (8.0+ recommended); **MariaDB is rejected outright** (its binlog dialect differs) | | 2 | `log_bin` | `ON` — binary logging must be enabled on the source | | 3 | `binlog_format` | `ROW` — row events are what make per-row replay possible | | 4 | `binlog_row_image` | `FULL` — replay applies full after-images | | 5 | `server_id` | Non-zero, and not colliding with the fake id Jync will present — Jync uses `sync.cdc.server-id-offset + project id` (default offset `612000`) as its replica id, so parallel projects never collide | | 6 | Replication grants | The sync account needs `REPLICATION SLAVE` + `REPLICATION CLIENT` | | 7 | Binlog retention | `binlog_expire_logs_seconds` (MySQL 8) / `expire_logs_days` (5.7) must keep at least 24 hours, or the probe fails the check — retention shorter than an outage window lets the position be purged away; `0` (never auto-purge) passes the probe but is flagged with a disk-usage warning | The probe runs both on the connection-test page and on the project's strategy card; when every check passes, the card marks CDC as recommended. ### Scope and limitations - **MySQL only**: MariaDB / TiDB / OceanBase / GBase are family members, but their binlog dialects differ — **none are supported**; later releases will evaluate them. - **DDL is not captured**: DDL statements in the stream are logged as WARN and skipped (the detail page shows a "DDL skipped" count), and cached table metadata is invalidated. After a source-side schema change, align the target schema manually; when the drift is too large to align, use **Rebuild baseline** to reseed. - **Tables without a primary key** are seeded once during the baseline, then skipped in the streaming phase, and flagged per table in the CDC status panel ("No-PK tables, seeded once only") — without a key there is no idempotent replay. ### Failure handling | Failure | Behavior | Remedy | |---|---|---| | Binlog purged (position no longer available) | The project is parked in **ERROR** immediately; no endless retry | Click **Rebuild baseline** on the detail page: stops the consumer, clears the position, reloads in chunks — one-click recovery | | `server_id` collides with Jync's fake id | The project is parked in **ERROR** | Raise `sync.cdc.server-id-offset`, or change the source's `server_id` | | Network drop / source restart | Exponential reconnect backoff: starts at 1s, doubles per attempt, capped at 60s; the backoff resets only after a connection has demonstrably stayed up | Nothing to do — the position does not move while disconnected, and the gap is replayed on reconnect | ERROR is a "wait for a human" hat: fatal failures never hot-loop against the source or the logs. After fixing the cause, recover via **Rebuild baseline** or a plain restart. ### Multi-instance deployment CDC consumers are mutually exclusive by **lease**: at most one node consumes a project's binlog stream at any moment (lease TTL `sync.cdc.lock-ttl-ms`, default 30s). When the holder dies, its lease expires within ≤ 30s and the other nodes' supervise tick (`sync.cdc.supervise-interval-ms`, default 10s) grabs it and resumes from the persisted `cdc_position` — total takeover **≤ 60s**, with at-least-once replay covering the seam, so no event is lost. ### Switch semantics Three things to know about switching: - **CDC replicates DELETEs** — rows deleted at the source are deleted at the target; polling never sees deletes, and this is the biggest behavioral difference between the strategies. - **Switching triggers a full re-sync** — switching to binlog CDC invalidates the old position and reruns the chunked baseline; the target may briefly lag during the reload. - **Switching back to polling keeps the position** — the `cdc_position` row is retained (switching to CDC again still re-baselines), while the polling cursors are cleared and re-parsed: the next polling round realigns with a full load before resuming increments. --- ## Log CDC (Oracle LogMiner) Since v1.6.0, Oracle sources can switch per project to the **LogMiner CDC** capture strategy: Jync uses Oracle's built-in LogMiner engine to mine redo logs and stream INSERT / UPDATE / DELETE changes into the target — second-level latency. It installs no triggers and writes nothing to the source's business tables; it relies solely on Oracle's official log-mining interface. The switch entry is the same as for MySQL binlog CDC: the capture-strategy card on the project detail page. A readiness probe runs before switching, listing each prerequisite's status item by item. ### How it works 1. **Record position** — at startup, read the source's current SCN (`V$DATABASE.CURRENT_SCN`) and persist it to the `cdc_position` table. The position is written **before** the full load, so changes that occurred during baseline are covered by the subsequent mining pass. 2. **Chunked full baseline** — load existing data in primary-key keyset chunks, reusing the same chunking / checkpoint machinery as MySQL binlog CDC. 3. **Stream replay** — after baseline completes, mine `V$LOGMNR_CONTENTS` from the recorded SCN and replay each transaction (ordered by commit SCN) into the target. The consistency contract is the same **at-least-once**: each transaction commits to the target, the lease is re-checked, and only then is the SCN position advanced (**data before position**). After a crash, replay resumes from the previous SCN; the overlap window is absorbed by idempotent upserts. ### Source prerequisites The probe's seven checks correspond one-to-one: | # | Check | Requirement | |---|---|---| | 1 | Source reachable | Oracle connection OK, version ≥ 11g | | 2 | `ARCHIVELOG` mode | `LOG_MODE = ARCHIVELOG` — LogMiner needs archived redo | | 3 | Database-level supplemental logging | `ALTER DATABASE ADD SUPPLEMENTAL LOG DATA` — minimal supplemental (PK + ROWID) | | 4 | Table-level supplemental logging | `ALTER TABLE ... ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS` — UPDATE must carry the full after-image of every column | | 5 | Mining privileges | Sync account needs `SELECT ANY TRANSACTION` + `EXECUTE ON SYS.DBMS_LOGMNR` + `SELECT ON V_$DATABASE / V_$LOG / V_$LOGFILE / V_$ARCHIVED_LOG / V_$LOGMNR_CONTENTS` | | 6 | No LONG columns | Selected tables must not contain LONG columns (unsupported by LogMiner) | | 7 | Archive retention | Archived logs retained ≥ 24h (recommended), otherwise the position can be purged | **CDB-mode note:** in a CDB, `ADD_LOGFILE` / `START_LOGMNR` may only run in CDB$ROOT as a common user (with the `C##` prefix). The sync account must be that common user with mining privileges; a PDB-local account receives `ORA-65040`. ### Scope and limitations - **Oracle only:** 11g and above (11g uses `EXECUTE_CATALOG_ROLE` / `SELECT_CATALOG_ROLE`; 12c+ uses the `LOGMINING` role plus explicit view grants). - **DDL not captured:** DDL rows from LogMiner are logged as WARN and skipped; the cached table metadata is invalidated. - **No-PK tables:** seeded once at baseline; row events are skipped during streaming. - **LONG columns / oversized LOBs:** LogMiner's support for LONG types and very large LOBs is limited; such tables are skipped and counted. - **Archive retention:** archived logs must be retained long enough (≥ 24h recommended), otherwise the position is purged and the project parks in ERROR. ### Failure handling | Failure | Behavior | Action | |---|---|---| | Archived log deleted (position lost) | Project parks in **ERROR** immediately | Click **Rebuild baseline** on the detail page: stop consumer, clear position, re-run full load | | Network drop / source restart | Exponential backoff reconnect | No manual action — mining resumes from the last SCN | | Parse failure > 5 times | Project parks in **ERROR** (defensive circuit breaker) | Investigate the SQL_REDO dialect, then rebuild baseline | ### Multi-instance deployment Same lease-mutual-exclusion semantics as MySQL binlog CDC: at most one node consumes a given project's LogMiner stream. When the holder crashes, its lease expires, another node supervises and takes over, resuming from the persisted SCN in `cdc_position`. ### Switch semantics Identical to MySQL binlog CDC: switching triggers a full reload, CDC replicates DELETEs, and switching back to polling retains the position. --- ## Log CDC (PostgreSQL logical decoding) Since v1.7.0, PostgreSQL sources can switch per project to a **logical-decoding CDC** capture strategy: Jync consumes the `test_decoding` text stream through PostgreSQL's built-in logical-decoding machinery and replays INSERT / UPDATE / DELETE events into the target — second-level latency. It **installs no triggers and writes nothing to the source's business tables**; it relies solely on PostgreSQL's official logical-decoding interface. The entry point is the same strategy card as MySQL binlog / Oracle LogMiner. A **readiness probe** enumerates each prerequisite before switching. ### How it works 1. **Record the position** — on start, the current LSN (`pg_current_wal_lsn()` as text, e.g. `0/167D3F8`) is persisted to `cdc_position.lsn` *before* the full load, so changes made during the baseline are already covered by the slot's later reads. 2. **Chunked full-load baseline** — keyset chunked load of existing rows, reusing the same chunk/checkpoint machinery as MySQL binlog and Oracle LogMiner. 3. **Streamed replay** — from the recorded LSN, cycle `pg_logical_slot_get_changes('jync_', NULL, , 'include-xids', '1')` and parse each row against the `test_decoding` text protocol (`BEGIN/COMMIT` boundaries, single-quoted strings with `''` escapes, `NULL` columns, UPDATE `old key-` image skipped), replaying events transaction by transaction into the target. The same **at-least-once** contract: commit to target → re-check lease → advance LSN. Crash replay absorbs the overlap window with idempotent upserts. ### Source-side prerequisites | # | Check | Requirement | |---|---|---| | 1 | Source reachable | PostgreSQL connection healthy, version ≥ 9.4 | | 2 | `wal_level` | `logical` — set in `postgresql.conf` and restart | | 3 | `REPLICATION` role | Sync account needs `REPLICATION` privilege (or superuser) | | 4 | `test_decoding` plugin | Probe creates and drops a throwaway slot to confirm plugin availability | | 5 | Table `REPLICA IDENTITY` | Selected tables must not be `NOTHING` (UPDATE/DELETE would lose the old image); default (PK) suffices when a PK exists, otherwise `ALTER TABLE ... REPLICA IDENTITY FULL` | ### Support and limits - **PostgreSQL only**: 9.4+ natively supports logical decoding; 12+ recommended. OpenGauss / HighGo / Vastbase PG-compatible variants are not yet validated. - **Slot lifecycle**: consumer auto-creates `jync_` on first connect and **never drops it on stop** (dropping would lose the watermark and WAL retention becomes unbounded). Long-idle slots should be cleaned up by DBA: `SELECT pg_drop_replication_slot('jync_')`. - **DDL not captured**: DDL rows degrade to an unsupported-table counter; cached metadata is invalidated. - **Keyless tables**: seeded once during baseline; UPDATE/DELETE still locate rows when `REPLICA IDENTITY = FULL`. ### Failure handling | Failure | Behavior | Action | |---|---|---| | WAL recycled (LSN out of range) | Project parks in **ERROR** | Click **Rebuild baseline** on the detail page | | Network drop / source restart | Exponential backoff reconnect | Auto-resumes from the last LSN | | Table `REPLICA IDENTITY` changed | Related row events degrade to a counter, other tables unaffected | Fix the identity or rebuild baseline | ### Multi-instance deployment Same lease-mutual-exclusion semantics as MySQL / Oracle CDC: at most one node consumes a given project's logical slot stream. `pg_logical_slot_get_changes` also advances the slot's consume-side position, naturally excluding concurrent pulls. ### Switch semantics Same as MySQL / Oracle: switching triggers a full reload, CDC replicates DELETEs, and switching back to polling retains the position. --- ## Log CDC (SQL Server CDC) Since v1.7.0, SQL Server sources can switch per project to a **SQL Server CDC** capture strategy: Jync reads the change tables (`cdc.__CT`) through `cdc.fn_cdc_get_all_changes_`, filtered by LSN range, and replays row-level changes into the target — second-level latency. It **installs no triggers and writes nothing to the source's business tables**; it relies solely on SQL Server's official CDC interface. ### How it works 1. **Record the position** — on start, `sys.fn_cdc_get_max_lsn()` is persisted to `cdc_position.lsn` as a 20-character hex string (`CONVERT(varchar(32), lsn, 1)`) *before* the full load. 2. **Chunked full-load baseline** — keyset chunked load of existing rows. 3. **Streamed replay** — cycle `cdc.fn_cdc_get_all_changes_(@from, @to, 'all update old')` and dispatch by `__$operation`: `1` = DELETE, `2` = INSERT, `4` = UPDATE-after (upsert), `3` = UPDATE-before (skipped — upsert semantics do not need it). Rows sharing `__$start_lsn` are flushed as one transaction. The same **at-least-once** contract applies. ### Source-side prerequisites | # | Check | Requirement | |---|---|---| | 1 | Source reachable | SQL Server connection healthy, version ≥ 2012 | | 2 | Database-level CDC enabled | `sys.databases.is_cdc_enabled = 1` — requires `EXEC sys.sp_cdc_enable_db` | | 3 | SQL Server Agent running | Capture / cleanup jobs depend on Agent; without it the change tables stay empty | | 4 | CDC role | Sync account must be in `cdc_Admin` or `cdc_reader` (or sysadmin) | | 5 | Per-table capture instance | Selected table must exist in `cdc.change_tables` — otherwise the probe shows the DDL to run: `EXEC sys.sp_cdc_enable_table ...` | ### Support and limits - **SQL Server only**: 2012+ natively supports CDC; 2017+ recommended (Agent on Linux is officially supported from this release). - **Agent dependency**: when Agent stops, the change tables do not grow and the consumer idles. The project does not park in ERROR, but latency grows unbounded. - **DDL not captured**: DDL causing capture-instance structure changes degrades related row events to a counter. - **Keyless tables**: seeded once during baseline; UPDATE/DELETE still locate rows because the change table carries the full image. ### Failure handling | Failure | Behavior | Action | |---|---|---| | CDC retention window expired (LSN purged) | Project parks in **ERROR** | Click **Rebuild baseline** | | Agent stopped | Consumer idles, change tables frozen | Start SQL Server Agent; no restart needed | | Network drop / source restart | Exponential backoff reconnect | Auto-resumes from the last LSN | ### Multi-instance deployment Same lease-mutual-exclusion semantics as MySQL / Oracle / PostgreSQL CDC. ### Switch semantics Same as the other three CDC strategies: switching triggers a full reload, CDC replicates DELETEs, and switching back to polling retains the position. --- ## Configuration Under `sync.*` in `application.yml`: | Key | Default | Description | |---|---|---| | `poll-interval` | `2000` | Polling interval (ms) | | `batch-size` | `500` | Rows per JDBC batch | | `fetch-size` | `1000` | Source result-set fetch size | | `chunk-size` | `100000` | Rows per chunk of the initial full load; each chunk commits and checkpoints on its own; `0` disables chunking (classic streamed read) | | `full-load-parallelism` | `1` | Parallel workers per full load; single-column integer-PK tables only, capped at 8; values above `max-pool-size` queue on the connection pool | | `max-pool-size` | `5` | Maximum connections per database connection pool | | `max-retries` | `3` | Consecutive failures before a task is marked ERROR | | `safety-lag-ms` | `1000` | Timestamp watermark rollback; see above | | `row-count-audit-interval-ms` | `60000` | Row-count audit interval (ms) | | `full-compare-max-rows` | `20000` | Row ceiling for full-table comparison | | `lock-ttl-ms` | `300000` | Sync lock lease duration (ms) | | `sync.cdc.reconnect-backoff-ms` | `1000` | Initial reconnect backoff (ms) after a dropped binlog stream; doubles per attempt | | `sync.cdc.reconnect-backoff-max-ms` | `60000` | Cap for the exponential reconnect backoff (ms) | | `sync.cdc.server-id-offset` | `612000` | Base of the fake replica server id: effective id = offset + project id; raise it when the source's `server_id` collides | | `sync.cdc.lock-ttl-ms` | `30000` | CDC consumer lease TTL (ms); a crashed holder's lease expires within this window so another node can take over | | `sync.cdc.supervise-interval-ms` | `10000` | How often each node supervises and grabs wanted CDC leases (ms) | | `sync.cdc.connect-timeout-ms` | `10000` | BinaryLogClient handshake timeout (ms) | | `sync.cdc.logminer.batch-size` | `10000` | V$LOGMNR_CONTENTS batch fetch size for LogMiner | | `sync.cdc.logminer.idle-poll-ms` | `1000` | Idle polling interval (ms) when LogMiner has no new data | | `crypto-password` | *(default)* | Password encryption key — **must be changed in production** | | `crypto-salt` | *(default)* | Encryption salt (hex) — **must be changed in production** | | `ai.enabled` | `true` | Whether AI-assisted conversion may be configured. `false` removes the menu and its endpoints | Connection passwords are stored encrypted with AES-256 via Spring Security Crypto, marked with an `enc:` prefix to avoid double encryption, and remain compatible with plaintext written before encryption was enabled. --- ## AI-Assisted Conversion (optional) Textual substitution only goes so far with incompatible stored-procedure syntax. `SqlBodyConverter` deliberately refuses to translate procedural control flow, because getting it wrong yields SQL that is valid but returns different results — a silent wrong answer, which is far worse than a failure that names the object. The `AI` menu offers another route: let a model draft a candidate that you review before it is stored. **The boundaries here are hard:** - AI **only produces candidates**. It never participates in a sync; `StructureSyncService` calls no AI code at all. - A candidate is written to the project's `ddlOverrides` only after you confirm it, using the manual override path that was already supported. - With no provider enabled, the tool makes **no outbound request whatsoever**, exactly as before. - Setting `sync.ai.enabled=false` removes the feature entirely — menu and REST endpoints alike. ### Configuration The `AI` page accepts several providers, of which **exactly one is active at a time**. Enabling another switches the current one off; the exclusivity is enforced server-side inside a transaction, so two open browser tabs cannot leave two providers enabled. | Field | Notes | |---|---| | Protocol | `OpenAI-compatible` or `Anthropic`. Nearly every self-hosted endpoint is the former | | Base URL | Endpoint root. Vendors disagree about whether `/v1` belongs here, so the form shows the request path that will actually be used | | Model | Model name, e.g. `deepseek-chat` | | API key | Encrypted with AES-256 like a database password; leave blank when editing to keep the stored value | | Max tokens / timeout | A long procedure needs room for the whole rewritten body | Every provider can be **probed**. The probe sends a real request with `max_tokens: 1`: a TCP or `HEAD` check would report success for a wrong key, a misspelled model, or a base URL that is one path segment off — the three things that actually go wrong. The outcome is stored and shown as a status badge, so rendering the list itself never reaches out to the network. Enabling a provider makes AI-assisted conversion available. A failed probe does **not** block enabling — a transient network problem should not make the setting unsavable — and the list shows a red badge instead. ### Conversion review The `Conversion review` entry on the project detail page (it appears once both the source and the target are configured) lists every view and stored procedure the project has selected, marking which ones already carry a manual override. Opening one object leads to the review page: | Area | Contents | |---|---| | Side-by-side | The source's original definition on the left, `SqlBodyConverter`'s mechanical conversion on the right — that is, **the statement the sync would actually execute if you did nothing** | | Uncertainties | The model's own list of what it cannot guarantee is equivalent, placed above the SQL. This is the first thing to read | | Editor | What gets stored. If an override already exists it opens with that content, which a fresh mechanical conversion will not overwrite | | Syntax check | Takes the editor's current content and really creates it on the **target**, then drops it | The mechanical column calls the same `StructureSyncService` methods the sync itself uses, not a second simplified implementation — the moment the two drift, this page is lying, and "the SQL shown to the reviewer differs from the SQL the sync runs" is worse than showing nothing. Two things need saying plainly: **The uncertainty list is worth more than the SQL.** If a two-hundred-line procedure comes back with no uncertainties at all, that is a signal the model did not look carefully — not a signal the conversion is good. Read the list before reading the SQL. **The syntax check writes to the target.** It really creates an object there named `JYNC_AI_CHECK_`, then drops it in a `finally`. This is the only place any AI-related code writes to the target, so it runs only when you click the button, and only after a confirmation dialog; the sync path never calls it. Why create it for real: no database offers a portable "parse but do not execute" call, and Oracle-family products create PL/SQL that fails to compile as an `INVALID` object rather than raising an error — hence the `USER_ERRORS` readback for those products, without which every check would report a false success. Passing the check **only proves the target accepts this DDL**, not that it behaves the same. A recursive object is verified only as a renamed copy, whose self-call resolves to whatever already exists on the target; that is reported explicitly as a caveat. ### Before you use it Probing and conversion both send requests to the endpoint you configure, and a conversion includes **the procedure's source code**, which often encodes business rules and even the full table structure. On an intranet or a regulated deployment, prefer a local or in-house inference endpoint, and confirm you are permitted to send this code off-site. Also note that **no tool can guarantee the converted procedure behaves identically**, and an LLM is no exception. Procedure differences are usually semantic rather than syntactic: cursor behaviour, implicit transaction boundaries, `NO_DATA_FOUND`-style exception control flow, the non-determinism of pagination without an explicit sort, or `NULL` in string concatenation — treated as an empty string by Oracle but poisoning the whole expression in MySQL. So the role here is **drafting**, not **guaranteeing**: every candidate needs human review, and critical procedures should be tested against the target for real. --- ## Architecture ``` com.qqmu.jync ├── config Configuration: i18n, Quartz, Jackson, SecurityConfig, SyncProperties ├── controller MVC controllers; controller/api holds the REST endpoints ├── service │ ├── connection DataSourceManager, DriverLoader, DriverShim, connection testing │ ├── metadata MetadataReader dialect implementations + snapshot service │ ├── monitor ChangeDetector (schema diffing), CursorStrategyResolver │ ├── converter SqlDialect implementations, type mapping, SQL body conversion │ ├── sync SyncEngine, DataSyncService, StructureSyncService, DdlExecutor │ ├── ai Provider config, shared HTTP layer, candidate drafting, candidate validation, review orchestration │ ├── task Quartz scheduling, three-layer locking, context assembly, startup recovery │ └── auth Account seeding, BCrypt password change, UserDetailsService ├── model JPA entities and enums ├── repository Spring Data JPA ├── dto SyncConfig, ChangeEvent, SyncResult, meta/* metadata models └── util CryptoUtil ``` ### One deliberate `@Transactional` decision `SyncStateWriter`, `SyncLockStore`, and `SyncTaskStore` are separate beans rather than methods on `SyncEngine` / `SyncLockService`. The reason is that Spring's `@Transactional` is proxy-based: self-invocation within the same class bypasses the proxy, and `REQUIRES_NEW` and `@Modifying` queries lose their transactional semantics. Only a cross-bean call makes those semantics real — and cursor advancement and lock acquisition depend on exactly that. --- ## Tests ```bash mvn test ``` 847 unit tests, covering: - **Dialect invariants** — every dialect produces a conflict-handling idempotent upsert; bind order matches placeholder count; type mapping never exceeds per-product ceilings (Oracle `VARCHAR2` 4000, SQL Server 4000, DB2 DECIMAL 31, precision-less `NUMBER` never yields `DECIMAL(0,0)`); declared precision is clamped to the ceiling without losing fractional digits; non-portable defaults are dropped rather than emitted as invalid DDL - **SQL body rewriting** — string literals, quoted identifiers, line comments, and block comments are never rewritten; escaped quotes inside a literal do not end it early; unterminated literals are preserved verbatim; `SUBSTR` → `SUBSTRING` does not double-hit itself - **Cursor strategies** — resolution priority; `IDENTITY` correctly flagged as "may miss updates"; graceful downgrade rather than an error when a configured column becomes invalid; a `VARCHAR` `update_time` is never misused; Oracle row-level SCN probing (`ROWDEPENDENCIES` whitelist, block-level never offered, DM/Yashan not probed) - **Cursor serialization** — timestamps round-trip as UTC ISO-8601 without losing millisecond precision; oversized numbers downgrade to `BigDecimal`; corrupt values are treated as "not yet synced" instead of throwing - **Password encryption** — round-trip, no double encryption, backward compatibility with legacy plaintext, distinct ciphertexts for identical passwords - **AI configuration** — enabling one provider necessarily disables every other (the enabled set is asserted to be exactly one); the global switch masks even an enabled provider; keys are stored encrypted, a blank field on edit keeps the stored one, and the probe receives the plaintext rather than the ciphertext; changing the endpoint clears a stale "reachable" badge; the key appears neither in the probe result nor in the edit page source; OpenAI sends `Authorization: Bearer` while Anthropic sends `x-api-key` and no `Authorization`; a trailing slash or an already-complete endpoint path never produces a doubled path - **AI reply parsing** — SQL is recovered from bare JSON, from a ```` ```json ```` fence, and from half a fence truncated by the token limit; a reply that ignores the output format entirely still has its SQL used, but gains an extra uncertainty entry (a model that would not follow the format probably did not follow "do not invent columns" either); when the model declines the conversion outright its stated reasons are kept rather than discarded as a network error; an empty reply never silently clears the editor; an unconfigured provider throws rather than masquerading as "conversion failed" - **Candidate validation** (against real H2) — a same-named object is **byte-identical before and after** a check, including the two forms easiest to get wrong (`CREATE OR REPLACE` and a schema-qualified name); no object is left behind afterwards, and a failed `CREATE` is cleaned up just the same; a statement whose `CREATE` header cannot be parsed is refused rather than forwarded verbatim to the target; the temporary name stays within Oracle's 30-byte limit and two consecutive checks never collide; "the database is unreachable" and "the statement was rejected" do not share one message - **Override storage** — the key matches the one `StructureSyncService` actually looks up (`FUNCTION` folds onto `PROCEDURE`); a blank override is refused (an empty-string override is worse than none — the sync would execute it and the object would silently disappear); deleting a key that does not exist writes nothing; overrides whose object was dropped at the source or deselected in the project are flagged as orphans and can be cleared - **Page rendering** — every conditional branch of both review pages (override present/absent, model available/not, same dialect family/not, orphans/none) is actually rendered, asserting that no `??` appears in the output — a message key added to only one bundle shows up here as `??key_en_US??`; an object name containing a dot is not truncated as a file extension by Spring - **Login & roles** — the initial accounts are seeded only into an empty table, so a restart does not re-seed and a table that already has rows has nobody's password reset; the stored hash starts with `$2a$` and does not contain the cleartext; the five rejection reasons for a password change (wrong current password, too short, mismatch, same as the current one, no such account) each return their own message key; the "using the initial password" flag flips to false after a successful change - **Authorization rules** — an unauthenticated page request redirects to `/login`, while an unauthenticated `/api/**` request answers 401 JSON rather than handing a login page to `fetch()`; every POST by a viewer is refused; `/account/password` is permitted for both roles, and that rule *must* precede "a POST requires admin" or a viewer would be shown a password form they are forbidden to submit; a POST missing its CSRF token is refused even for an administrator - **JSON endpoints** — both a rejected request and a server-side exception return 200 with `success:false`, because the caller is `fetch()` and a 500 carrying an HTML error page reaches the user as nothing but a blank toast - **Pager window** — an unusable page size falls back to the default and an out-of-range page clamps to the last; an ellipsis widens only its own side by five pages; the chosen page size really reaches the repository query; a single page renders just that one number - **Sync-now rerun** — repeated clicks during a run produce exactly one extra round; a click with nothing in flight executes immediately and queues nothing; a request for a missing project runs nothing - **Dialect defaults** — a MySQL temporal column's default must carry the column's fractional precision (`CURRENT_TIMESTAMP(6)`), or MySQL rejects the CREATE TABLE with error 1067 - **Chunked full load & resume** (real H2) — keyset paging SQL shapes (composite-PK OR chains, watermark upper bound, the `ORA_ROWSCN` pseudo-column emitted unquoted and without the IS NULL rescue, Oracle offset-0 pagination never leaking the `RNUM_` helper column); checkpoint lifecycle (per-chunk commits, completed ranges/chunks skipped, cleared on success); parallel range splits disjoint and contiguous, with a long-overflow-safe fallback; the parallelism gate (zero-scale `NUMERIC(18,0)` admitted, fractional keys refused, composite keys stay sequential); keyless tables keep the streamed full pull and get the "no checkpointed resume" badge; `chunk-size: 0` disables chunking entirely; the row-count audit bows out for pseudo-column cursors - **Real-Oracle integration test** — `OracleMigrationHarnessIT` (testcontainers + an Oracle 23ai Free container; named `*IT` so the default suite skips it, and it disables itself without Docker) proves against a live database: parallel ranged full load of a `NUMBER(18)` key, resume from a checkpoint after an interruption, and the complete `ORA_ROWSCN` chain on a `ROWDEPENDENCIES` table (probe → strategy resolution → an incremental cycle delivering an UPDATE to an already-synced row); run explicitly with `mvn test -Dtest=OracleMigrationHarnessIT` (add `-Dapi.version=1.44` on Docker Engine ≥ 29) - **Binlog CDC** — the readiness probe's seven verdicts (version, `log_bin` / `binlog_format` / `binlog_row_image`, `server_id` collision, replication grants, retention, MariaDB rejected); fake server id = offset + project id; row events mapped to idempotent upserts with transaction buffering (DDL invalidates the cached table metadata and drops the buffer, keyless tables skipped, a moved PK upserts the new key *and* deletes the old one, unmappable values skipped pairwise and counted); position discipline (data committed before the position advances; a lost lease never advances it); failure classification (binlog purge / `server_id` collision / auth failure park the project in ERROR until a human acts, transient faults back off exponentially 1s→60s and reset only after a demonstrably live connection); lease mutual exclusion with ≤ 60s takeover; the strategy-switch sequences (to CDC: position cleared, re-baseline; back to polling: position kept, cursors cleared); the README documentation guards - **Real-MySQL container integration tests (binlog CDC)** — `CdcBaselineStreamingIT` / `CdcVolumeAndTypesIT` / `CdcResilienceIT` (testcontainers + a real MySQL 8 binlog source; named `*IT` so the default suite skips them, and they **require Docker**, disabling themselves without it) prove against a live database: baseline → streamed continuation, a mid-baseline crash rerunning the whole baseline, 100k-row consistency, the full type-matrix round trip, and the binlog-purge / disconnect failure paths; run explicitly with `mvn test -Dtest=CdcBaselineStreamingIT -Dapi.version=1.44` There is also an end-to-end script (H2 source and target, 20 assertions) covering initial full load, incremental inserts, incremental updates, idempotency across repeated syncs, DDL column-addition propagation, **matching row counts with no duplicates under concurrent writes**, concurrent invocations rejected by the lock, **writes made during downtime backfilled after restart**, automatic polling, and change-log / cursor-strategy reporting. --- ## Known Limitations - **Stored procedure conversion** — mechanical differences (function names, identifier quoting, `FROM DUAL`, pagination syntax) are converted automatically, but PL/SQL, T-SQL, and PL/pgSQL have different procedural control-flow constructs, so complex procedures cannot be translated reliably. Such objects are attempted with their original source; on failure the specific error is reported. You can review them one by one on the `Conversion review` page and save a manual override there (with a provider configured, a model can draft the candidate for you) — but **a candidate still needs human confirmation**, and passing the syntax check does not mean semantic equivalence. - **What AI drafting is and is not** — the model only produces candidates and never participates in a sync; `StructureSyncService` calls no AI code. Semantic equivalence cannot be guaranteed by any tool — cursor behaviour, implicit transaction boundaries, exception control flow, and `NULL` concatenation semantics do not show up in the syntax, so critical procedures must be tested against the target for real. - **What the syntax check costs** — it really creates a temporary object on the target and then drops it. It runs only on a manual click (behind a confirmation), but killing the process between those two steps leaves a `JYNC_AI_CHECK_*` object behind, and the list page will not discover it for you. Upgrading from before v1.3.0: orphans left by older releases use the prefix `SYNCTOOL_AI_CHECK_*` — look for the old prefix when cleaning up. - **Row-delete detection** — without a source-side audit table this requires comparing the full primary-key sets on both sides, so it is only enabled for tables below `full-compare-max-rows`. - **Tables without a primary key** — idempotency cannot be guaranteed and replay may produce duplicate rows; the tool warns. - **Target writers** — the target database is assumed to be written only by this tool. - **Latency** — a polling design's latency floor is the poll interval. MySQL sources that need lower latency can switch to the built-in [Binlog CDC](#binlog-cdc-mysql) strategy (streamed binlog replay, second-level latency); for other databases the design can be extended with LogMiner-style log parsing. --- ## Contributing Questions, feature requests and feedback are all welcome: | Channel | Account | |---------|---------| | QQ | 817094 | | QQ | 2912167928 | | QQ Group | 426669837 | | WeChat | qqmu66 | Issues and pull requests are welcome: - GitHub: - Gitee: If this project helps you, a Star ⭐ is appreciated. --- ## License Released under the [MIT License](LICENSE) — free for commercial and non-commercial use. The distribution bundles fourteen third-party JDBC drivers (Oracle, DB2, Dameng, KingBase, OceanBase, HighGo, Vastbase, YashanDB, and others), each under its vendor license — see [THIRD_PARTY_LICENSES.md](THIRD_PARTY_LICENSES.md) for versions, links, and the Oracle/IBM redistribution terms. **GBase** and **Oscar** drivers are not distributed with this project; obtain them from the vendors and confirm your own usage rights.