MySQL

Packages: dev.anchorlight.stonelib.storage.sql, dev.anchorlight.stonelib.storage

When several servers behind a proxy share state, it has to live in a database they all reach. This module is that stack: settings read from config, a connection pool, ordered schema migrations, and a repository that writes only what changed, so one server's save never discards another's.

:::danger Everything here blocks Every method that touches the database waits on it. Call them off the main thread, typically through SchedulerService.supplyAsync. See Threading Rules. :::

Driver

StoneLib does not bundle a MySQL driver. List it under libraries in plugin.yml, so Paper downloads it once and shares it:

libraries:
  - com.mysql:mysql-connector-j:9.1.0

DatabaseConfig

A record of connection settings, normally read from a database section of config.yml:

database:
  host: 127.0.0.1
  port: 3306
  name: myplugin
  user: myplugin
  password: "change-me"
  pool-size: 10
  connection-timeout-ms: 5000
  properties: "useSSL=false&allowPublicKeyRetrieval=true"

DatabaseConfig settings = DatabaseConfig.fromSection(getConfig().getConfigurationSection("database"));

Key	Required	Default	Meaning
`host`	no	`127.0.0.1`	Database host.
`port`	no	`3306`	Must be from 1 to 65535.
`name`	yes		Database (schema) name.
`user`	yes		Username.
`password`	no	empty	Password.
`pool-size`	no	`10`	Maximum pooled connections. Must be positive.
`connection-timeout-ms`	no	`5000`	How long to wait for a connection before failing.
`properties`	no	empty	Extra JDBC URL parameters, appended after `?`.

fromSection throws IllegalArgumentException if the section is missing, name or user is blank, the port is out of range, or the pool size is not positive. Catch it on enable and disable the plugin with a clear message.

Method	Meaning
`jdbcUrl()`	`jdbc:mysql://host:port/name`, plus `?properties` when set.
`toString()`	The URL, user, and pool size with the password shown as `***`, so the config can be logged.

You can also construct the record directly: new DatabaseConfig(host, port, database, username, password, poolSize, connectionTimeoutMillis, extraProperties).

ConnectionPool

A HikariCP pool. It implements AutoCloseable.

Method	Meaning
`new ConnectionPool(DatabaseConfig, String poolName)`	Opens the pool. `poolName` appears in HikariCP's logs and thread names.
`connection()`	Borrows a connection. Close it to return it to the pool, ideally with try-with-resources.
`update(sql, Object... params)`	Runs an insert, update, delete, or DDL statement. Returns the update count.
`query(sql, RowMapper<T>, Object... params)`	Maps every row. Returns a list.
`queryOne(sql, RowMapper<T>, Object... params)`	The first mapped row, or `null` if there are none.
`transaction(SqlConsumer)`	Runs your work on one connection, commits on return, rolls back on any exception and rethrows it.
`close()`	Shuts the pool down. Call it last in `onDisable`.

Parameters are bound with setObject, in order, so values are never concatenated into SQL.

ConnectionPool pool = new ConnectionPool(settings, "MyPlugin");

int updated = pool.update("UPDATE balances SET amount = amount + ? WHERE player = ?", 50, uuid.toString());

List<String> top = pool.query(
        "SELECT player FROM balances ORDER BY amount DESC LIMIT ?",
        rs -> rs.getString("player"),
        10);

pool.transaction(conn -> {
    try (PreparedStatement debit = conn.prepareStatement("UPDATE balances SET amount = amount - ? WHERE player = ?")) {
        debit.setInt(1, 50);
        debit.setString(2, from.toString());
        debit.executeUpdate();
    }
    try (PreparedStatement credit = conn.prepareStatement("UPDATE balances SET amount = amount + ? WHERE player = ?")) {
        credit.setInt(1, 50);
        credit.setString(2, to.toString());
        credit.executeUpdate();
    }
});

Pool settings

Setting	Value
Maximum pool size	`pool-size`
Connection timeout	`connection-timeout-ms`
Maximum connection lifetime	30 minutes, comfortably under MySQL's default 8 hour `wait_timeout`, so the pool never hands out a connection the server has already dropped.
Prepared statement cache	On, 250 statements, up to 2048 characters each.

:::caution Construction connects HikariCP opens a first connection while the pool is being created, and throws if the database is unreachable. Creating the pool therefore blocks for up to the connection timeout, and a wrong host or password surfaces as an exception from the constructor. Catch it in onEnable. :::

SchemaMigrator

Applies ordered, run-once schema changes.

new SchemaMigrator(pool, "MyPlugin")
        .migration(1, "CREATE TABLE IF NOT EXISTS balances (player VARCHAR(36) PRIMARY KEY, amount BIGINT NOT NULL DEFAULT 0)")
        .migration(2, "ALTER TABLE balances ADD COLUMN updated_at TIMESTAMP NULL")
        .migration(3,
                "CREATE INDEX balances_amount ON balances (amount)",
                "UPDATE balances SET updated_at = CURRENT_TIMESTAMP WHERE updated_at IS NULL")
        .migrate();

Method	Meaning
`new SchemaMigrator(ConnectionPool, String owner)`	`owner` identifies your plugin. Up to 64 characters.
`migration(int version, String... statements)`	Registers one version. Versions start at 1. Registering the same version twice throws.
`migrate()`	Applies every version above the recorded one, in ascending order. Returns how many it applied.
`currentVersion()`	The highest version recorded for this owner, or `0`.
`hasRun()`	Whether this owner has any recorded version. For diagnostics commands.

How versions are recorded

Applied versions are stored in a stonelib_schema_version table, created automatically, with one row per owner. Several plugins can share a database without colliding, as long as each uses a different owner.

Only the highest applied version is stored. Registering version 5 after a server has already recorded version 7 means version 5 never runs there.

Rules for migrations

Never edit a shipped migration. A server that already ran version 2 will not run it again, so a change to it only reaches new installs. Fix a mistake by adding version 3.
Never renumber. Versions are compared as numbers, and gaps are fine.
Each version runs in its own transaction. The version is recorded in the same transaction as its statements.

:::danger MySQL cannot roll back schema changes MySQL commits CREATE, ALTER, DROP, and other DDL statements immediately, even inside a transaction. If a version with several DDL statements fails part-way, the statements before the failure stay applied, and the version is not recorded. The next start then re-runs the whole version from the top and fails on the part that already exists.

Keep to one DDL statement per version, and write DDL defensively (CREATE TABLE IF NOT EXISTS) where MySQL allows it. Data changes such as INSERT and UPDATE are rolled back as expected. :::

MySqlRepository

The Repository contract on MySQL, built for more than one server writing to the same table.

new MySqlRepository<>(ConnectionPool pool, Logger logger, String tableName, RecordCodec<V> codec,
                      MySqlRepository.KeyParser<K> keyParser, List<String> columns)

Parameter	Meaning
`tableName`	Letters, digits, and underscores, not starting with a digit. Anything else throws.
`codec`	Converts records to and from maps. The map keys must match `columns`.
`keyParser`	Turns the stored key text back into your key type, such as `UUID::fromString`.
`columns`	Every value column, declared up front so the table can be created before any record exists. Same identifier rules as `tableName`.

MySqlRepository<UUID, Selection> selections = new MySqlRepository<>(pool, getLogger(), "selections",
        new SelectionCodec(), UUID::fromString, List.of("trail", "hat", "pet", "visible"));

// off the main thread
selections.createTable();
selections.load();

API

Method	Blocks	Meaning
`createTable()`	yes	Creates the table if missing, and adds any declared column the table lacks. Call once on enable, before `load`.
`load()`	yes	Replaces the cache with every row, and clears pending changes. On failure the cache is left as it was.
`reload(K key)`	yes	Re-reads one row into the cache, or drops the key if the row is gone. Returns the fresh value or `null`. On failure, logs and returns the cached value.
`save()`	yes	Writes pending changes in one transaction. On failure, the changes are put back so the next save retries them.
`saveNow(K key)`	yes	Writes one key immediately, deleting its row if the key is not in the cache. Throws `SQLException`.
`get(K key)`	no	From the cache.
`put(K key, V value)`	no	Updates the cache and marks the key to be written.
`remove(K key)`	no	Removes from the cache and marks the key to be deleted.
`all()`	no	A live view of the cached values.
`invalidate(K key)`	no	Drops a key from the cache without writing or deleting anything.
`hasPendingWrites()`	no	Whether `save` has anything to do.

The table

CREATE TABLE IF NOT EXISTS selections (
  `key` VARCHAR(191) NOT NULL PRIMARY KEY,
  `trail` TEXT, `hat` TEXT, `pet` TEXT, `visible` TEXT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

Keys are stored with toString() and may be up to 191 characters. Values are stored as text, and a null field is stored as SQL NULL. A map key your codec produces that is not in columns is ignored.

createTable only adds columns. Renaming, retyping, or backfilling a column belongs in a SchemaMigrator migration.

Why it is safe with several servers

Save writes only what changed. Each put marks its key and each remove marks its deletion. save upserts and deletes exactly those keys. It never clears the table, so one server saving cannot wipe rows another server wrote.
Reload reads one key. When another server announces a change, re-read that key with reload instead of reloading the whole table.

The unit of conflict is the record. If two servers change the same key, the last save wins for that whole record. Design records so one server owns each key at a time, which for per-player data means the server the player is on.

With the message bus

The pattern that keeps several backends in step:

// the server where the player changed something
selections.put(uuid, updated);
scheduler.runAsync(() -> {
    try {
        selections.saveNow(uuid);
        scheduler.runSync(() -> bus.publish("selection-changed", uuid.toString()));
    } catch (SQLException e) {
        getLogger().log(Level.WARNING, "Could not save selection for " + uuid, e);
    }
});

// every other server
bus.subscribe("selection-changed", message ->
        scheduler.runAsync(() -> selections.reload(UUID.fromString(message.payload()))));

Write first, then announce. A missed message costs a stale cache until the next read, never lost data. See Cross-Server Messaging.

Driver​

DatabaseConfig​

ConnectionPool​

Pool settings​

SchemaMigrator​

How versions are recorded​

Rules for migrations​

MySqlRepository​

API​

The table​

Why it is safe with several servers​

With the message bus​