Skip to content

1.3.2.1

Choose a tag to compare

View all tags
@SaltyAlpha SaltyAlpha released this 05 Sep 04:13
· 57 commits to master since this release
v1.3.2.1
25918ce

Imported from GrimAnticheat/GrimAPI release v1.3.2.1. Original publication: 05/01/2026 20:33:34. Original URL: https://github.com/GrimAnticheat/GrimAPI/releases/tag/v1.3.2.1.

MariaDB support + MySQL prefix-LIKE perf fix

Drop-in 1.3.2.0 → 1.3.2.1. No public surface change. Two things land:

  1. MariaDB 10.6+ now actually works as a MysqlBackendProvider target. Before, init failed at first DDL with a MariaDB server version syntax error because the v7 schema used a MySQL 8 functional index (INDEX … ((LOWER(col)))) that MariaDB doesn't implement (MDEV-22597, still open).
  2. listPlayersByNamePrefix on MySQL becomes O(matches) instead of O(table size). The functional index path was never sargable for prefix LIKE — full table scan regardless of selectivity. Verified by EXPLAIN at 1M rows + measured speedup.

What changed under ac.grim.grimac.internal.storage.backend.mysql.*

Both flavors now share the same v8 baseline DDL — a STORED current_name_lower generated column with a plain B-tree index — and the same read SQL. Only divergence left is the upsert syntax:

  • MySQL 8.0.19+ (MysqlEightDialect) — INSERT … AS new ON DUPLICATE KEY UPDATE col = new.col. Avoids the deprecated VALUES() form.
  • MariaDB 10.6+ (MariaDbDialect) — INSERT … ON DUPLICATE KEY UPDATE col = VALUES(col). MariaDB never adopted MySQL 8.0.19's aliased-row syntax, so it stays on the legacy form (which it never deprecated).

Dialect is selected at backend init via SELECT VERSION() — same shape as the SqliteBackend legacy/modern dialect probe. Handles MariaDB's legacy 5.5.5- compat prefix (MDEV-9788) so 5.5.5-10.11.16-MariaDB-… parses correctly. Floors at MariaDB 10.6 with a clean BackendException if older — 10.6 is the oldest LTS still in upstream support, comfortably above the 10.2.1 floor for STORED generated columns and enforced CHECK constraints.

Why the perf side existed at all

MySQL's optimiser refuses to use a functional index for WHERE LOWER(col) LIKE 'prefix%' — only equality (= ?) is sargable. EXPLAIN at 1M rows with FORCE INDEX still shows type: ALL for prefix patterns. The gen col shape gets type: range and scales with selectivity instead of table size.

Measured on mysql:8.4.9 against a 1M-row players table:

Prefix pattern v7 (functional idx) v8 (gen col) Speedup
selective 5+ char (typical autocomplete) ~1100 ms ~1 ms ~1000×
4-char play (~2.6% selective) ~1095 ms ~1270 ms ~0.85× — random-IO penalty for many row fetches outweighs the index range benefit at this selectivity
equality getPlayerIdentityByName already fast already fast unchanged

The dominant case in real usage (admins typing 4+ chars in slash commands, autocomplete after a near-full prefix) is selective, so the practical impact is "autocomplete that was unusable on 1M-row tables becomes instant."

Schema v7 → v8 auto-migration

Existing v7 MySQL deployments auto-migrate on backend init via an additive ALTER TABLE:

  1. ADD COLUMN current_name_lower VARCHAR(32) AS (LOWER(current_name)) STORED
  2. DROP INDEX idx_<players>_name_lower, ADD INDEX idx_<players>_name_lower (current_name_lower) (one ALTER, no window without a lower-name index)
  3. ANALYZE TABLE so the optimiser sees fresh stats on the new index

⚠️ MySQL 8 requires ALGORITHM=COPY for adding STORED generated columns — INSTANT and INPLACE both error out. That means a full table rewrite, blocking writes for the duration. Fine on alpha tables (~1 minute per 1M rows on NVMe SSD with default buffer pool) but if you're running a production-scale players table in the millions, plan a maintenance window or use gh-ost / pt-online-schema-change to do the rewrite online and skip the auto-migration.

No-op on MariaDB — the dialect was always at the v8 column layout in this PR's earlier commits, so its v7 schema already matched; the migration just bumps schema_version.

Compat

  • 1.3.2.0 source + binary compatible. No public method removed, no signature changed. MysqlBackendProvider, MysqlBackend.ID, Backend#capabilities, Backend#supportedCategories all unchanged.
  • Existing MySQL deployments auto-upgrade on next boot. No config change needed (subject to the migration-cost caveat above).
  • New MariaDB deployments — point storage.mysql.jdbc-url at the MariaDB instance exactly as you would for MySQL; the dialect probe handles flavor detection at init.

Tests

  • MysqlBackendDialectTest parameterised on flavor — fresh-DB v8 baseline round-trip. Identity upsert merging, case-insensitive name lookup, prefix search, session UPSERT, settings UPSERT on composite PK, paged violation reads. Both flavors green against the live test-mysql-zone-a (mysql:8.4.9) and test-mariadb-zone-a (mariadb:10.11.16) fixtures.
  • v7ToV8Migration (MySQL only) — pre-seeds a v7-shape DB with a CamelCaseName identity row, boots the backend, verifies schema_version bumped to 8, the STORED column backfilled to camelcasename, the index now references the column not the LOWER() expression, and post-migration name + prefix queries find the row.
  • EXPLAIN on MariaDB 10.11 confirms idx_<players>_name_lower is used (range scan for prefix, ref lookup for exact).

Related

GrimAPI PR: #23

Assets 3
Loading