MySQL

Database bridge powering cross-server Fact, Asset, and player data synchronization with Redis/Valkey pub/sub.

The MySQL Extension replaces the default local file storage with a robust remote database system supporting MySQL, PostgreSQL, Redis, and Valkey. It enables real-time synchronization of Facts, Assets, and player data across multi-server networks via dual-mechanism sync (polling + optional Redis/Valkey pub/sub).

Dual Database Support — MySQL and PostgreSQL with automatic dialect switching.
- Redis & Valkey — Instant cross-server data sync and cache invalidation.

Architecture

Storage Tables

Type	Table	Description
Fact	`{prefix}facts`	Key-value storage for game state (quest progress, player stats, flags)
Asset	`{prefix}assets`	Binary/serialized data (configs, skins, schematics)
Servers	`servers`	Server registry with heartbeat, TPS, player counts, memory
Extensions	`{prefix}game_mode_extensions`	Per-extension enabled/disabled, network mode, profile mode
Game Modes	`{prefix}game_modes`	Game mode definitions with extension bindings

Features

Database Agnostic

Supports MySQL 8+ and PostgreSQL 14+ with automatic dialect detection. SQL syntax adapts automatically — ON DUPLICATE KEY UPDATE for MySQL, ON CONFLICT DO UPDATE for PostgreSQL. JDBC URL is constructed dynamically.

# config.yml
mysql:
  database-type: MYSQL    # or POSTGRESQL
  host: localhost
  port: 3306              # 5432 for PostgreSQL
  database: typewriter_data
  username: user
  password: pass
  use-ssl: false
  allow-public-key-retrieval: true
  table-prefix: "ty_"

Cross-Server Data Sync (Dual Mechanism)

The engine's FactDatabase now includes native cross-server sync via loadFactsSince() and syncFactsFromStorage() called every 5 seconds. The MySQL Extension complements this with:

Mechanism	Trigger	Latency	Fallback
Engine sync (always active)	Every 5 seconds, via `FactDatabase.syncFactsFromStorage()`	5s	N/A — always on
Extension polling (always active)	Every `sync-interval-seconds` (default 30s)	30s	N/A — always on
Redis/Valkey Pub/Sub (optional)	Instant on write	\< 500ms	Falls back to polling if Redis down

Race condition protection: Both the engine's syncFactsFromStorage() and the extension's updateFactCache() use lastUpdate timestamp comparison — a server never overwrites its local cache with stale data. Dirty facts (modified but not yet flushed) are also protected from being overwritten.

Engine-Level Dirty Tracking

The FactDatabase now includes full dirty tracking with debounced flush:

dirtyFacts — tracks facts modified since last persistence
deletedFacts — tracks facts that need to be removed from storage
Debounce 50ms — rapid sequential writes are batched into a single flush
flushDirtyFacts() — calls upsertFacts() / deleteFacts() on the storage

This ensures that every write to a fact is eventually flushed to MySQL without the 3-minute wait.

Non-Destructive Store & UPSERT

The FactStorage interface now exposes three distinct operations instead of one:

Method	Behavior	Destructive?
`storeFacts()`	Full sync — upserts all given facts (no DELETE)	❌ No
`upsertFacts()`	Inserts or updates — only writes given facts	❌ No
`deleteFacts()`	Deletes specific facts by (entry_id, group_id, game_mode)	✅ Yes (intentional)

The old storeFacts() pre-deleted facts not present in the input, causing data loss in cross-server scenarios. It now behaves as a non-destructive upsert. deleteFacts() is reserved for explicit deletions (expired facts, cache cleanup).

Deadlock Protection (3 Layers)

MySqlFactStorage uses three-layer protection against MySQL deadlocks:

SELECT ... FOR UPDATE — acquires exclusive row locks immediately, preventing conflicting transactions
withRetryOnDeadlock() — up to 3 retries with exponential backoff (100ms → 200ms → 400ms)
Batch within a transaction — all writes committed atomically

private suspend fun <T> withRetryOnDeadlock(operation: String, block: () -> T): T {
    for (attempt in 1..MAX_RETRIES) {
        try {
            return block()
        } catch (e: SQLException) {
            if (isDeadlock(e) && attempt < MAX_RETRIES) {
                delay(RETRY_BASE_DELAY_MS * (1L shl (attempt - 1)))
            } else {
                throw e
            }
        }
    }
}

Player Data Flush

The engine only persists fact caches to storage every 3 minutes (FACT_STORAGE_DELAY=180). Between cycles, player data lives only in memory. The MySQL Extension guarantees no data loss on player disconnect:

PlayerQuitEvent and PlayerKickEvent trigger an immediate, async flush via PlayerDataFlushListener
The flush runs on a Dispatchers.IO coroutine — never blocks the main thread
Data written at T0 is in MySQL by T+200ms (not T+180s)
Uses factDatabase.getCacheSnapshot() (public API) — no reflection

Player writes fact → cache updated → Player quits
                                        ↓
                              PlayerDataFlushListener
                              (fire-and-forget coroutine)
                                        ↓
                              DataSyncService.forceFlushToMySql()
                              (uses getCacheSnapshot() + upsertFacts)
                                        ↓
                              MySQL ← data persisted immediately

Shutdown Safety

During server reload or plugin disable, the extension follows a strict shutdown order to prevent HikariDataSource has been closed errors:

Set isShuttingDown = true — immediately, before any cleanup
Unregister PlayerDataFlushListener — via its shutdown() callback
Stop DataSyncService — cancels polling coroutine
Stop discovery services — removes heartbeat
Unload Koin module — releases DI bindings
Close DataSource — last step, no one is using it anymore

All database operations (forceFlushToMySql, syncFactsFromMySql, flushBeforeDisconnect) check isShuttingDown before proceeding. If the flag is set, they return immediately with a FINE-level log message instead of crashing.

[18:42:13 WARN]: DataSync: forceFlush failed: HikariDataSource has been closed.  ← ÉLIMINÉ
[18:42:13 ERROR]: Could not pass event PlayerQuitEvent to Typewriter            ← ÉLIMINÉ

Redis & Valkey Pub/Sub

Both Redis 7+ and Valkey 8+ are supported. Valkey uses the identical RESP protocol — the Jedis client works transparently with either backend.

redis:
  enabled: true
  type: redis               # redis or valkey
  host: localhost
  port: 6379
  username: ""              # Optional (Valkey ACL)
  password: ""
  channel: "typewriter:events"
  server-name: "survival"   # Unique per server

Pub/Sub channels:

Channel	Direction	Content
`typewriter:events/data_update`	Server → All	Fact/asset change notification (`"fact:quest_score"`)
`typewriter:events/config_update`	Admin → All	Hot-reload config trigger
`typewriter:events/server_update`	All → All	Player count + slots broadcast (5s interval)
`typewriter:events/player_tracking`	Server ↔ Server	`where_is`, `player_location`, `summon`

Auto-reconnect: If Redis disconnects, the subscriber thread retries every 5 seconds. The polling loop continues unaffected.

Server Discovery

Each server sends a heartbeat every 30 seconds to the servers table. Stale servers (60s without heartbeat) are automatically cleaned up. On JVM shutdown, the server deregisters itself.

-- Auto-created table
CREATE TABLE IF NOT EXISTS servers (
    server_id   VARCHAR(191) PRIMARY KEY,
    ip          VARCHAR(255) NOT NULL,
    port        INT NOT NULL,
    game_mode   VARCHAR(64) NOT NULL,
    last_heartbeat DATETIME(6) NOT NULL,
    tps         DOUBLE,
    version     VARCHAR(64),
    player_count INT,
    max_players  INT,
    free_memory  BIGINT,
    max_memory   BIGINT,
    extensions   TEXT
);

Extension Auto-Discovery

On startup, the extension scans all loaded TypeWriter extensions via the engine's ExtensionLoader and registers them in the database. Each extension has configurable:

Setting	Options	Description
enabled	`true` / `false`	Whether this extension uses MySQL storage
profileMode	`SHARED` / `PER_PROFILE`	Data shared or per-profile?
networkMode	`SHARED` / `ISOLATED`	Data shared across game modes or isolated?

Key normalization handles all variants: TypeWriter-ProfilesExtension → profiles, RPGCore → rpgcore, friends → party (alias).

Cross-Server Player Redirection

RedirectionManager enables cross-server player teleport via Redis pub/sub:

/locate <player> — finds which server a player is on, then teleports
/summon <player> — pulls a player from another server
Load balancing — redirectPlayer() picks the server with the lowest fill percentage

Config Hot-Reload

Sending a config_update message via Redis triggers an automatic config reload on all servers:

Redis: PUBLISH typewriter:events "config_update:profiles:enabled:true"
       → All servers reload MySqlExtensionConfig
       → ExtensionDiscoveryService re-registers extensions

Configuration Reference

Full mysql/config.yml:

mysql:
  enabled: true
  database-type: MYSQL                    # MYSQL or POSTGRESQL
  host: localhost
  port: 3306
  database: typewriter_data
  username: user
  password: pass
  use-ssl: false
  allow-public-key-retrieval: true
  table-prefix: "ty_"
  migrate-from-files: true               # Import local data on first start
  sync-interval-seconds: 30              # Polling interval for cross-server sync

  pool:
    maximum-pool-size: 10
    minimum-idle: 2
    connection-timeout: 30000
    max-lifetime: 1800000
    validation-timeout: 5000
    keepalive-time: 0

  game-mode:
    current: "default"                   # This server's game mode

  extensions:
    profiles:
      enabled: true
      profile-mode: per-profile          # shared or per-profile
      network-mode: shared               # shared or isolated
    cosmetics:
      enabled: true
      profile-mode: shared
      network-mode: shared
    rpgcore:
      enabled: true
      profile-mode: per-profile
      network-mode: isolated
    dungeon:
      enabled: true
      profile-mode: per-profile
      network-mode: isolated
    # ... all extensions configurable

redis:
  enabled: false
  type: redis                            # redis or valkey
  host: localhost
  port: 6379
  username: ""
  password: ""
  channel: "typewriter:events"
  server-name: "survival"

profiles:
  enabled: true
  max-profiles: 5
  default-profile-name: "Principal"
  save-statistics: true
  save-inventory: true
  save-ender-chest: true
  require-permission: false
  auto-create-default: true

Key Configuration Options

Option	Default	Description
`sync-interval-seconds`	30	Seconds between MySQL polling cycles
`redis.type`	redis	`redis` or `valkey`
`redis.server-name`	survival	Unique per-server identifier
`extensions.<key>.network-mode`	isolated	`shared` = cross-game-mode, `isolated` = per-mode
`extensions.<key>.profile-mode`	shared	`shared` = all profiles, `per-profile` = separated
`pool.maximum-pool-size`	10	HikariCP max connections
`pool.keepalive-time`	0	Keepalive in ms (0 = disabled)

Web UI

Access the MySQL administration page via the Server Management sidebar tab in the Typewriter web editor.

Tab Overview

Tab	Purpose
Servers	View registered servers, TPS, player counts, online/offline status
Game Modes	Create/edit/delete game modes, enable/disable extensions, configure network/profile modes
Health & Ops	Redis latency, database connection pool stats, MySQL logs
Topology	Visual graph of connected servers and their relationships
Entry Storage	Browse fact/asset tables, view individual entries, table schemas

Socket.IO Events

Event	Direction	Description
`fetchServerList`	Client → Server	Get all registered servers with health data
`fetchGameModes`	Client → Server	Get game mode definitions
`createGameMode` / `deleteGameMode`	Client → Server	Manage game modes
`updateGameModeExtension`	Client → Server	Toggle extension enabled/network/profile mode
`setExtensionEnabled`	Client → Server	Quick enable/disable toggle
`fetchEntryFilters`	Client → Server	Get per-extension entry filters
`fetchStorageOverview`	Client → Server	Get all fact/asset tables
`fetchTableEntries`	Client → Server	Browse entries in a table
`fetchTableSchema`	Client → Server	View table column definitions
`serverHealthCheck`	Client → Server	Check Redis + DB connectivity
`fetchMySqlLogs`	Client → Server	Recent MySQL operation logs

Permissions

Permission	Description
`typewriter.admin.server.list`	View servers, game modes, storage, logs
`typewriter.admin.server.health`	View health status and logs
`typewriter.admin.server.deregister`	Remove servers from registry
`typewriter.admin.gamemode.create`	Create or delete game modes
`typewriter.admin.gamemode.configure`	Configure extensions, entry filters, storage overrides

Dependencies

TypeWriter Engine (required)
MySQL 8+ or PostgreSQL 14+ (required)
Redis 7+ or Valkey 8+ (recommended for cross-server sync)
HikariCP 6.x (connection pooling, bundled)
Jedis 5.x (Redis/Valkey client, bundled)
JDBC drivers — MySQL Connector/J + PostgreSQL JDBC (bundled)

Testing

The extension has a comprehensive test suite split into two pillars:

Pillar 1 — Unit Tests (71 tests)

Located in TypeWriter-MySqlExtension/src/test/kotlin/. No infrastructure needed.

Test class	Coverage
`SqlDialectTest`	UPSERT generation (MySQL + PostgreSQL), quoting, type mappings
`ConfigParsingTest`	ProfileMode, NetworkMode, CacheBackend, DatabaseType parsing
`MessageParsingTest`	Redis wire format: data_update, server_update, player_tracking
`ExtensionKeyTest`	Key normalization, alias resolution, fallback chain, game mode isolation

Run: .\gradlew.bat :TypeWriter-MySqlExtension:test --no-daemon

Pillar 2 — Integration Tests (16 scenarios)

Located in mysql-integration-tests/. Uses TestContainers (real MySQL 8.4, PostgreSQL 16, Redis 7, Valkey 8) and simulated servers — no Minecraft required.

Scenario file	What it validates
`CrossServerSyncTest`	Polling sync, game mode isolation, global visibility, race condition guard
`RedisPubSubTest`	Redis PING, Valkey PING, pub/sub delivery, instant cross-server sync, polling fallback
`DeadlockRetryTest`	4-thread concurrent writes, FOR UPDATE + retry, data integrity
`PlayerFlushTest`	Quit flush completeness, async non-blocking behavior, sub-200ms persistence

Run: .\gradlew.bat :mysql-integration-tests:test --no-daemon (requires Docker)

Architecture​

Storage Tables​

Features​

Database Agnostic​

Cross-Server Data Sync (Dual Mechanism)​

Engine-Level Dirty Tracking​

Non-Destructive Store & UPSERT​

Deadlock Protection (3 Layers)​

Player Data Flush​

Shutdown Safety​

Redis & Valkey Pub/Sub​

Server Discovery​

Extension Auto-Discovery​

Cross-Server Player Redirection​

Config Hot-Reload​

Configuration Reference​

Key Configuration Options​

Web UI​

Tab Overview​

Socket.IO Events​

Permissions​

Dependencies​

Testing​

Pillar 1 — Unit Tests (71 tests)​

Pillar 2 — Integration Tests (16 scenarios)​