Agent Skills: MySQL Knowledge Patch

MySQL Knowledge Patch

Use this skill when writing MySQL SQL, planning an upgrade, changing authentication or replication, sizing a server, operating InnoDB, or adopting JSON Duality Views and stored JavaScript. Check the relevant reference before relying on older defaults, plugins, client behavior, or downgrade procedures.

Reference index

| Reference | Topics | | --- | --- | | Compatibility, Upgrades, and Installation | Upgrade checks, downgrade prohibition, removed features, spatial-index safety, Clone, packages, and platform support | | Security, Authentication, and Components | Privileges, authentication policy, password storage, roles, connection control, keyrings, firewall, and masking | | Replication and High Availability | Version compatibility, encrypted connections, GTIDs, Group Replication, retries, and binary-log behavior | | SQL, Schema, and Optimizer | Primary-key equivalents, DDL algorithms, collation, subqueries, EXPLAIN, temporal validation, and optimizer behavior | | JSON Duality Views and MLE | Duality View definition and DML, stored JavaScript types and APIs, reusable libraries, and WebAssembly | | Server, InnoDB, and Resource Sizing | Container and cgroup awareness, automatic sizing, change buffering, log writers, redo diagnostics, and Thread Pool | | Clients, Observability, and Audit | mysql and mysqldump, Configurator, Option Tracker, telemetry, account locks, diagnostics, and Audit Log |

Check migration blockers first

Do not automate Innovation-release rollback

Downgrades between individual Innovation releases are unsupported, including a rollback between point releases. Treat rollback as restore or rebuild work, not as starting an older binary over the upgraded data directory.

Protect spatial indexes during upgrade

Before an affected upgrade, drop spatial indexes and recreate them afterward. If they remain through the upgrade, recreate them before querying their tables. Run CHECK TABLE ... EXTENDED afterward to compare each spatial-index MBR with the geometry MBR stored in the clustered record.

Validate stored expressions

Ask the server to inspect functions used in constraints, defaults, partitioning expressions, and virtual columns:

mysqld --check-table-functions=ABORT

ABORT is the default and stops on invalid tables. Use WARN only when an interactive repair procedure is ready.

Replace removed and deprecated facilities

• Remove Version Tokens plugin loading, its functions, privilege, and session variables.
• Migrate server plugins and keyring plugins to components. The plugin API and --early-plugin-load are deprecated.
• Replace semisync_master and semisync_slave with semisync_source and semisync_replica.
• Install classic_hashing only when an application still needs MD5() or SHA1().
• Remove settings that no longer exist, including innodb_log_file_size, innodb_log_files_in_group, and innodb_undo_tablespaces.
• Move Enterprise Linux 7 deployments to a supported operating system before upgrading.

Read Compatibility, Upgrades, and Installation for the complete removal list and installation constraints.

Update security configuration deliberately

Stop depending on privilege-cache flushes

Account-management statements update privileges directly. FLUSH PRIVILEGES, its dedicated privilege, related mysqladmin commands, SIGHUP-based flushing, and using the statement to clear the caching-SHA-2 cache are deprecated.

Choose password storage independently of clients

caching_sha2_password can store credentials with PBKDF2 and SHA-512, including for X Protocol authentication. Administrators can change or enforce the storage format without client changes. Review the digest-round setting as well; its newer default is 10000.

Account for automatic role activation

activate_mandatory_roles is enabled by default. When activate_all_roles_on_login is off, mandatory roles supplement default roles. When it is on, mandatory and granted roles activate regardless of the new setting.

Use components and policy DDL

Prefer the connection-control, firewall, and keyring components over their deprecated plugin predecessors. Enterprise masking policies attach to base-table columns and can use CURRENT_ROLE_IN() or CURRENT_USER_IN() as policy gatekeepers.

Recheck changed command and server defaults

Enable mysql client commands explicitly

Most commands in the mysql client are disabled unless requested:

mysql --commands=ON

Audit interactive tooling and scripts that expect client-side commands.

Make replication intent explicit

Replication connections now default to encryption, and GTID mode defaults on. Confirm certificates and topology behavior instead of assuming an unencrypted or anonymous setup. A lower-version replica requires replica_allow_higher_version_source before accepting a higher-version source.

Re-baseline plans and diagnostics

explain_format defaults to TREE, while JSON EXPLAIN defaults to format version 2. Consumers must recognize schema 2.0, the reduced top-level shape, and lookup_references.

Recheck automatic sizing

The server observes container CPU and memory limits, including cpuset assignments. server_memory limits the physical-memory value used to derive defaults; it is not a hard cap on process memory. Explicit settings remain the safest way to preserve a tuned deployment.

Use the current SQL and schema behavior

Avoid unnecessary generated invisible primary keys

A UNIQUE NOT NULL key is a primary-key equivalent for CREATE and ALTER. It satisfies sql_require_primary_key=ON and prevents an extra generated invisible primary key when sql_generate_invisible_primary_key=ON:

CREATE TABLE events (
  event_id BIGINT NOT NULL UNIQUE,
  payload JSON
);

Expect empty-table column changes to use INPLACE

For an empty InnoDB table, adding or dropping a column selects INPLACE rather than INSTANT by default. This avoids consuming a row version; do not assert INSTANT in automation unless that algorithm is specifically required.

Opt into the Hypergraph Optimizer at the right scope

The Hypergraph Optimizer is available in Community Edition and can be selected at session, global, persisted, startup, or statement scope:

SET optimizer_switch='hypergraph_optimizer=on';

Adopt JSON Duality View DML consciously

Duality View definitions grant or deny INSERT, UPDATE, and DELETE per table. Runtime DML is checked against those tags. Community Server permits all three DML forms through the views and supports auto-increment columns, including automatic primary-key generation.

Documents selected from a Duality View include _metadata.etag. Use the four JSON_DUALITY_VIEW* Information Schema tables to inspect the mapping instead of reverse-engineering the generated document.

Build stored JavaScript with the expanded MLE APIs

Stored JavaScript accepts ENUM, SET, BIT, and full DECIMAL/NUMERIC input, output, binding, and return paths. Decimal values are strings by default; request decimalType=NUMBER only when JavaScript numeric precision is acceptable.

Reusable libraries support create, alter, status, comments, routine USING clauses, and dynamic imports. A WebAssembly library can be encoded as hexadecimal or base64:

CREATE LIBRARY math_wasm
  LANGUAGE WASM
  AS '0061736d...';

WebAssembly libraries cannot call MySQL-specific APIs or WASI system, clock, or I/O services. See JSON Duality Views and MLE for transaction, routine-call, localization, and memory details.

Make telemetry and audit configuration explicit

Telemetry logs, metrics, and traces default off, and OTLP endpoints have no default. Configure each signal and endpoint deliberately. Linux exporters may be placed in separate network namespaces, and exporter credentials may come from external secret-header providers.

The component-based Audit Log supports time-based rotation and startup recovery from invalid filters. Pick a recovery mode explicitly for environments where logging everything, logging nothing, and aborting startup have materially different risk.