Partitioning in MySQL: pruning and retention

layout_session.py

"""One table, seven partitions: what the server keeps for each, and where a new row goes.

A throwaway MySQL 8.4.11 server (mysql_session.py) loads events_load.sql quietly, runs layout.sql through the mysql
client, and then lists the files of the app database: every partition is a tablespace with an .ibd file of its own.
"""

from mysql_session import Server

# More sample pages than any partition has leaf pages, so the statistics are the same on every run.
with Server(options=["--innodb-stats-persistent-sample-pages=300"]) as my:
    my.up()
    my.mysql("events_load.sql", flags=("--skip-comments",))
    my.mysql("layout.sql", errors=1)
    my.listing("data/app")

$ mysql --skip-comments < events_load.sql
Table	Op	Msg_type	Msg_text
app.events	analyze	status	OK

$ mysql -t -vvv --skip-comments --force < layout.sql
--------------
SELECT PARTITION_NAME AS name, PARTITION_METHOD AS method, PARTITION_EXPRESSION AS expr,
       PARTITION_DESCRIPTION AS less_than, TABLE_ROWS AS estimate
FROM information_schema.PARTITIONS
WHERE TABLE_SCHEMA = 'app' AND TABLE_NAME = 'events'
ORDER BY PARTITION_ORDINAL_POSITION
--------------

+----------+---------------+--------------+--------------+----------+
| name     | method        | expr         | less_than    | estimate |
+----------+---------------+--------------+--------------+----------+
| p2025_01 | RANGE COLUMNS | `created_on` | '2025-02-01' |     3100 |
| p2025_02 | RANGE COLUMNS | `created_on` | '2025-03-01' |     2800 |
| p2025_03 | RANGE COLUMNS | `created_on` | '2025-04-01' |     3100 |
| p2025_04 | RANGE COLUMNS | `created_on` | '2025-05-01' |     3000 |
| p2025_05 | RANGE COLUMNS | `created_on` | '2025-06-01' |     3100 |
| p2025_06 | RANGE COLUMNS | `created_on` | '2025-07-01' |     3000 |
| pmax     | RANGE COLUMNS | `created_on` | MAXVALUE     |        0 |
+----------+---------------+--------------+--------------+----------+
7 rows in set

--------------
SELECT COUNT(*) FROM events PARTITION (p2025_01)
--------------

+----------+
| COUNT(*) |
+----------+
|     3100 |
+----------+
1 row in set

--------------
SELECT COUNT(*) FROM events PARTITION (p2025_02, p2025_03)
--------------

+----------+
| COUNT(*) |
+----------+
|     5900 |
+----------+
1 row in set

--------------
INSERT INTO events PARTITION (p2025_01) VALUES (900001, '2025-02-14', 7, 'view', SHA2(900001, 256))
--------------

ERROR 1748 (HY000) at line 18: Found a row not matching the given partition set
--------------
INSERT INTO events VALUES (900001, '2025-02-14', 7, 'view', SHA2(900001, 256))
--------------

Query OK, 1 row affected

--------------
SELECT id, created_on FROM events PARTITION (p2025_02) WHERE id = 900001
--------------

+--------+------------+
| id     | created_on |
+--------+------------+
| 900001 | 2025-02-14 |
+--------+------------+
1 row in set

Bye

$ ls -p data/app
events#p#p2025_01.ibd  events#p#p2025_02.ibd  events#p#p2025_03.ibd
events#p#p2025_04.ibd  events#p#p2025_05.ibd  events#p#p2025_06.ibd
events#p#pmax.ibd

layout.sql

-- The events table of events_load.sql: one name over seven partitions.
USE app;

-- information_schema.PARTITIONS lists each partition and the bound that routes rows to it. TABLE_ROWS is an
-- estimate InnoDB keeps; ANALYZE TABLE has just read every page, so here it matches the exact counts.
SELECT PARTITION_NAME AS name, PARTITION_METHOD AS method, PARTITION_EXPRESSION AS expr,
       PARTITION_DESCRIPTION AS less_than, TABLE_ROWS AS estimate
FROM information_schema.PARTITIONS
WHERE TABLE_SCHEMA = 'app' AND TABLE_NAME = 'events'
ORDER BY PARTITION_ORDINAL_POSITION;

-- Partition selection reads only the partitions it names, and counts their rows exactly.
SELECT COUNT(*) FROM events PARTITION (p2025_01);
SELECT COUNT(*) FROM events PARTITION (p2025_02, p2025_03);

-- A row goes where its created_on value says. Naming another partition for it is refused; without a name, the
-- server routes it to p2025_02 itself.
INSERT INTO events PARTITION (p2025_01) VALUES (900001, '2025-02-14', 7, 'view', SHA2(900001, 256));
INSERT INTO events VALUES (900001, '2025-02-14', 7, 'view', SHA2(900001, 256));
SELECT id, created_on FROM events PARTITION (p2025_02) WHERE id = 900001;

events_load.sql

-- The lesson's events table: six months of 2025, January to June, one partition a month on created_on, and a
-- catch-all partition for later dates. 100 events a day, made by arithmetic, so every run loads the same 18,100 rows.
CREATE DATABASE app;
USE app;

-- Every PRIMARY KEY and UNIQUE KEY of a partitioned table must contain the partitioning column, so the key is
-- (id, created_on), not id alone.
CREATE TABLE events (
  id BIGINT NOT NULL,
  created_on DATE NOT NULL,
  account_id INT NOT NULL,
  kind VARCHAR(10) NOT NULL,
  payload CHAR(64) NOT NULL,
  PRIMARY KEY (id, created_on)
)
PARTITION BY RANGE COLUMNS (created_on) (
  PARTITION p2025_01 VALUES LESS THAN ('2025-02-01'),
  PARTITION p2025_02 VALUES LESS THAN ('2025-03-01'),
  PARTITION p2025_03 VALUES LESS THAN ('2025-04-01'),
  PARTITION p2025_04 VALUES LESS THAN ('2025-05-01'),
  PARTITION p2025_05 VALUES LESS THAN ('2025-06-01'),
  PARTITION p2025_06 VALUES LESS THAN ('2025-07-01'),
  PARTITION pmax VALUES LESS THAN (MAXVALUE)
);

-- Row i (0 to 18,099) falls on day i DIV 100 after the first of January: 100 rows a day for 181 days.
SET SESSION cte_max_recursion_depth = 20000;
INSERT INTO events
WITH RECURSIVE n (i) AS (SELECT 0 UNION ALL SELECT i + 1 FROM n WHERE i < 18099)
SELECT i + 1, DATE '2025-01-01' + INTERVAL (i DIV 100) DAY, i MOD 500 + 1,
       ELT(i MOD 4 + 1, 'login', 'view', 'click', 'purchase'), SHA2(i, 256)
FROM n;

-- Fresh statistics for every partition, so EXPLAIN's estimates are the same on every run.
ANALYZE TABLE events;

mysql_session.py

"""Throwaway MySQL 8.4.11 servers for the examples of this lesson.

Every session creates a server of its own: `mysqld --initialize-insecure` makes a new data directory inside a new
folder in the examples folder, the server starts on it, the session runs `mysql` client scripts and MySQL's programs
against it and prints what they printed, and then the server is stopped and the folder deleted, also when something
fails. The server is the pinned MySQL 8.4.11 from the mise installations next to the Python that runs this file;
Server(basedir=...) runs the programs of another unpacked MySQL tarball instead (dump_defaults.py uses it).

Safety: the server listens only on 127.0.0.1, an address no other computer can reach, on a port the operating system
reports as unused; the X Protocol listener is off (--mysqlx=OFF); its Unix socket, temporary files, error log and
import/export folder all stay inside the run folder. Right after the first start the session gives root a password made
up for the run (never printed) and connects only with a client option file that holds it, so no password ever appears
on a command line. Every program starts with --no-defaults or --defaults-file, so no option file of the computer
(/etc/my.cnf, ~/.my.cnf) can change a result, and HOME points at the run folder, so ~/.mylogin.cnf is not read either.
A printed mysqld command shows the options that matter to the lesson; the session also passes, on the command line,
the folders it keeps inside the run folder (--basedir, --tmpdir, --log-error, --pid-file, --secure-file-priv) and
the port, which is why performance_schema.variables_info reports those variables as COMMAND_LINE.
learn:verify's run sandbox cannot read the home folder, where mise keeps MySQL, so these sessions are recorded with
LEARN_VERIFY_NETWORK=allow; they read only this folder and the toolchain and connect to nothing but 127.0.0.1.

The output is exactly what the programs printed, with these substitutions so that a record never shows the computer it
ran on and is the same on every run: the run folder is shown as ".", the folder of MySQL's programs is left out of
their paths and MySQL's own folder is shown as <basedir>, the computer's name is shown as "lab-host" and the operating-system user's name as "learner", the time a
statement took (the client's "(0.01 sec)") is left out, the time stamp SET PERSIST writes into mysqld-auto.cnf is
shown as <when it was set>, error-log lines lose their timestamp and thread number, and a session can name more values
that change between runs with my.mask(pattern, replacement), which then prints in their place. A client table whose cells
change is drawn again so that its borders still line up.
The sessions never print the password, and the run's port prints as <port>. A session stops with status 1 when a command that should have worked
fails, or when a script reports a different number of errors than the session expects, so a run that went wrong is
never recorded as if it had worked.
"""

import os
import pwd
import re
import secrets
import shlex
import shutil
import socket
import subprocess
import sys
import tempfile
import textwrap
import time
from pathlib import Path

MYSQL_VERSION = "8.4.11"
# .../installs/python/3.14.8/bin/python3 -> .../installs
INSTALLS = Path(sys.executable).resolve().parents[3]
PINNED = INSTALLS / "mysql" / MYSQL_VERSION
OS_USER = pwd.getpwuid(os.geteuid()).pw_name
HOST = socket.gethostname().split(".")[0]
# 2026-10-17T16:50:01.123456Z 0 [ERROR] ... -> [ERROR] ...
LOG_PREFIX = re.compile(r"^\d{4}-\d\d-\d\dT\d\d:\d\d:\d\d\.\d+Z \d+ ", re.M)


def free_port():
    """A TCP port on 127.0.0.1 that nothing listens on now."""
    with socket.socket() as probe:
        probe.bind(("127.0.0.1", 0))
        return probe.getsockname()[1]


class Server:
    """One throwaway server: initialise, start; stopped and deleted when the with block ends.

    options: extra mysqld options for every start (for example "--sql-mode=" or "--character-set-server=latin1").
    basedir: the folder of an unpacked MySQL tarball to run instead of the pinned 8.4.11.
    masks: (pattern, replacement) pairs, as for mask().
    """

    def __init__(self, options=(), redo=None, basedir=None, masks=()):
        self.options = [str(o) for o in options]
        self.redo = redo
        self.basedir = Path(basedir).resolve() if basedir else PINNED
        self.bin = self.basedir / "bin"
        self.masks = []
        for pattern, replacement in masks:
            self.mask(pattern, replacement)
        self.proc = None

    def mask(self, pattern, replacement):
        """From now on every printed line shows text matching the regular expression as the replacement: for a value
        that changes from run to run (an uptime, a time stamp), so that the recorded output stays the same."""
        self.masks.append((re.compile(pattern), replacement))

    # ---- the folder ------------------------------------------------------------------------------------------------
    def __enter__(self):
        if not (self.bin / "mysqld").exists():
            sys.exit(f"no MySQL server program in {self.basedir}" if self.basedir != PINNED
                     else f"MySQL {MYSQL_VERSION} was not found next to this Python")
        self.base = Path(tempfile.mkdtemp(prefix="my", dir=".")).resolve()
        for sub in ("tmp", "files"):
            (self.base / sub).mkdir(mode=0o700)
        self.port = free_port()
        self.password = secrets.token_urlsafe(24)
        self.client_cnf = self.base / "client.cnf"
        self._write_client_cnf(password=None)
        self.env = {key: value for key, value in os.environ.items()
                    if not key.startswith("MYSQL") and key not in ("TZ", "HOME", "LANG", "LC_ALL")}
        self.env.update({"HOME": str(self.base), "TZ": "UTC", "LANG": "en_US.UTF-8",
                         "MYSQL_TEST_LOGIN_FILE": str(self.base / "no-login-paths.cnf")})
        for pattern in ("*.sql", "*.cnf", "*.csv", "*.txt"):
            for f in Path(".").glob(pattern):
                shutil.copy(f, self.base / f.name)
        return self

    def __exit__(self, *exc):
        if self.proc and self.proc.poll() is None:
            self.stop(quiet=True)
        shutil.rmtree(self.base, ignore_errors=True)

    def _write_client_cnf(self, password):
        lines = ["[client]", "user=root", "host=127.0.0.1", f"port={self.port}", "protocol=TCP"]
        if password:
            lines.append(f"password={password}")
        self.client_cnf.write_text("\n".join(lines) + "\n")
        self.client_cnf.chmod(0o600)

    def server_options(self):
        b = self.base
        return ["--no-defaults", f"--basedir={self.basedir}", f"--datadir={b / 'data'}", f"--tmpdir={b / 'tmp'}",
                f"--log-error={b / 'mysqld.err'}", f"--pid-file={b / 'mysqld.pid'}",
                f"--secure-file-priv={b / 'files'}",
                *([f"--innodb-redo-log-capacity={self.redo}"] if self.redo else [])]

    # ---- the server ------------------------------------------------------------------------------------------------
    def initialize(self, show=False):
        """mysqld --initialize-insecure: a new data directory whose root account has no password yet."""
        args = ["mysqld", *self.server_options(), "--initialize-insecure"]
        code, out = self._call(args)
        if show:
            _show(["mysqld", "--no-defaults", "--initialize-insecure", "--datadir=./data"], out)
        if code != 0:
            print(self.log())
            sys.exit(f"mysqld --initialize-insecure ended with status {code}")

    def start(self, extra=(), defaults_file=None, expect_fail=False, show=False):
        """Starts mysqld on 127.0.0.1 and waits until it answers. With expect_fail, prints the error-log lines of a
        start that must fail, and returns them."""
        log = self.base / "mysqld.err"
        before = log.read_text() if log.exists() else ""
        opts = self.server_options()
        if defaults_file:
            opts[0] = f"--defaults-file={self.base / defaults_file}"
        args = [str(self.bin / "mysqld"), *opts, "--bind-address=127.0.0.1", f"--port={self.port}",
                "--socket=mysql.sock", "--mysqlx=OFF", *self.options, *[str(e) for e in extra]]
        if show:
            shown = ["mysqld", opts[0].replace(str(self.base) + "/", ""), "--datadir=./data", "--bind-address=127.0.0.1",
                     "--port=$PORT", "--socket=mysql.sock", "--mysqlx=OFF", *self.options, *extra]
            print("$ " + " ".join(_arg(a) if a != "--port=$PORT" else a for a in shown) + " &")
            print()
        with open(self.base / "mysqld.out", "a") as out:
            self.proc = subprocess.Popen(args, stdout=out, stderr=subprocess.STDOUT, cwd=self.base, env=self.env)
        for _ in range(600):
            if self.proc.poll() is not None:
                break
            if self._ping():
                break
            time.sleep(0.1)
        if self.proc.poll() is not None:
            new = (log.read_text() if log.exists() else "")[len(before):]
            lines = [l for l in self.clean(LOG_PREFIX.sub("", new)).splitlines() if "[ERROR]" in l]
            if expect_fail:
                print("\n".join(lines))
                print(f"(mysqld ended with status {self.proc.returncode})")
                print()
                self.proc = None
                return lines
            print("\n".join(lines))
            sys.exit(f"mysqld ended with status {self.proc.returncode} while starting")
        if expect_fail:
            sys.exit("mysqld started, but the session expected it to fail")
        if not self._ping():
            sys.exit("mysqld did not answer within 60 seconds")
        return []

    def _ping(self):
        code, _ = self._call(["mysqladmin", f"--defaults-file={self.client_cnf}", "--connect-timeout=2", "ping"])
        return code == 0

    def up(self, extra=(), defaults_file=None, show=False):
        """Initialise, start (with start()'s options), and give root the run's password, quietly."""
        self.initialize()
        self.start(extra=extra, defaults_file=defaults_file, show=show)
        return self.login()

    def login(self):
        """Gives root the run's password (never printed) on a server started from a new data directory, and makes
        every later client of the session use it."""
        self.silent(f"ALTER USER 'root'@'localhost' IDENTIFIED BY '{self.password}'")
        self._write_client_cnf(self.password)
        return self

    def stop(self, quiet=False):
        """A clean shutdown (mysqladmin shutdown), then waits until the process has ended."""
        code, out = self._call(["mysqladmin", f"--defaults-file={self.client_cnf}", "shutdown"])
        if code != 0 and not quiet:
            sys.exit(f"mysqladmin shutdown failed: {out}")
        try:
            self.proc.wait(timeout=120)
        except subprocess.TimeoutExpired:
            self.proc.terminate()
            self.proc.wait(timeout=60)
        self.proc = None

    def restart(self, extra=(), defaults_file=None, show=False):
        self.stop()
        self.start(extra=extra, defaults_file=defaults_file, show=show)

    # ---- running things --------------------------------------------------------------------------------------------
    def _call(self, args, stdin=None, merge=True):
        """Runs one of MySQL's programs in the run folder: (status, cleaned output). merge=False keeps only what it
        wrote to standard output (its warnings on standard error are dropped)."""
        args = [str(a) for a in args]
        done = subprocess.run([str(self.bin / args[0]), *args[1:]], stdout=subprocess.PIPE,
                              stderr=subprocess.STDOUT if merge else subprocess.DEVNULL,
                              text=True, cwd=self.base, env=self.env, input=stdin)
        return done.returncode, self.clean(done.stdout)

    def output(self, *args, connect=False, status=0):
        """Runs one of MySQL's programs without printing anything and returns its standard output, for example the
        SQL that mysql_tzinfo_to_sql writes, which the session saves with write_file(..., show=False) and runs next."""
        args = [str(a) for a in args]
        run = [args[0], f"--defaults-file={self.client_cnf}", *args[1:]] if connect else args
        code, out = self._call(run, merge=False)
        if code != status:
            sys.exit(f"{args[0]} ended with status {code}, not {status}")
        return out

    def clean(self, text):
        """The substitutions of the docstring. A table of the client whose cells change is drawn again with the
        new column widths, so its borders still line up."""
        lines, out, i = text.split("\n"), [], 0
        while i < len(lines):
            if lines[i].startswith("+-"):
                j = i
                while j < len(lines) and lines[j][:1] in ("+", "|"):
                    j += 1
                block = lines[i:j]
                done = [self._clean(l) for l in block]
                out.extend(done if done == block else _redraw(block, self._clean))
                i = j
            else:
                out.append(self._clean(lines[i]))
                i += 1
        return "\n".join(out)

    def _clean(self, text):
        text = text.replace(str(self.bin) + "/", "").replace(str(self.basedir), "<basedir>")
        text = text.replace(str(self.base) + "/", "./").replace(str(self.base), ".")
        text = re.sub(r" \(\d+\.\d\d sec\)", "", text)
        text = re.sub(r'"Timestamp": \d+', '"Timestamp": <when it was set>', text)
        text = re.sub(rf"\b{re.escape(HOST)}\b", "lab-host", text)
        text = re.sub(rf"\b{re.escape(OS_USER)}\b", "learner", text)
        text = re.sub(rf"(?<![\d.]){self.port}(?![\d.])", "<port>", text)
        for pattern, replacement in self.masks:
            text = pattern.sub(replacement, text)
        return text

    def log(self, pattern=None):
        """The error log without timestamps and thread numbers, optionally only the lines matching a pattern."""
        text = self.clean(LOG_PREFIX.sub("", (self.base / "mysqld.err").read_text()))
        if pattern:
            text = "\n".join(l for l in text.splitlines() if re.search(pattern, l))
        return text

    def show_log(self, pattern, note=None):
        """Prints the error-log lines that match a pattern, as grep would."""
        print(f"$ grep -E {_arg(pattern)} mysqld.err" + (f"   # {note}" if note else ""))
        print(self.log(pattern))
        print()

    def query(self, sql, db=None):
        """One value from one query, unprinted (the session uses it to wait or to decide)."""
        args = ["mysql", f"--defaults-file={self.client_cnf}", "-N", "-B", *([db] if db else [])]
        code, out = self._call(args, stdin=sql)
        if code != 0:
            sys.exit(f"the query failed: {out}")
        return out.strip()

    def silent(self, sql):
        """Runs a statement without printing it (used only for the run's password)."""
        self.query(sql)

    def mysql(self, script, errors=0, status=None, db=None, flags=("-t", "-vvv", "--skip-comments"), note=None, force=None):
        """Runs a client script from standard input and prints the command and its output. `errors` is how many
        ERROR lines the script must print; with errors > 0 the client runs on past an error (--force) and then ends
        with status 0, without --force it stops at the first error with status 1. The default flags print each
        statement (-vvv) as a table (-t) without the script's comments (--skip-comments)."""
        force = errors > 0 if force is None else force
        shown = ["mysql", *flags, *(["--force"] if force else []), *([db] if db else [])]
        args = ["mysql", f"--defaults-file={self.client_cnf}", *shown[1:]]
        code, out = self._call(args, stdin=(self.base / script).read_text())
        if note:
            print(f"-- {note}")
        print("$ " + " ".join(_arg(a) for a in shown) + f" < {script}")
        if out.strip():
            print(out.rstrip("\n"))
        print()
        found = sum(1 for line in out.splitlines() if line.startswith("ERROR "))
        expected_status = (1 if errors and not force else 0) if status is None else status
        if found != errors or code != expected_status:
            sys.exit(f"{script}: {found} errors and status {code}; the session expects {errors} and {expected_status}")
        return out

    def shell(self, *args, status=0, shown=None, connect=False):
        """Runs one of MySQL's programs and prints the command and its output. connect=True gives a client program
        (mysqladmin, mysqlshow, mysql) the run's client option file, which the printed command leaves out."""
        args = [str(a) for a in args]
        run = [args[0], f"--defaults-file={self.client_cnf}", *args[1:]] if connect else args
        code, out = self._call(run)
        _show(shown or args, out)
        if code != status:
            sys.exit(f"{args[0]} ended with status {code}, not {status}")
        return out

    def sql(self, statement, flags=("-t",), errors=0, db=None, note=None):
        """Runs one statement with mysql -e and prints the command and its output."""
        shown = ["mysql", *flags, *([db] if db else []), "-e", statement]
        code, out = self._call(["mysql", f"--defaults-file={self.client_cnf}", *shown[1:]])
        if note:
            print(f"-- {note}")
        _show(shown, out)
        found = sum(1 for line in out.splitlines() if line.startswith("ERROR "))
        if found != errors or (code != 0) != (errors > 0):
            sys.exit(f"{statement}: {found} errors and status {code}; the session expects {errors}")
        return out

    def write_file(self, path, text, show=True):
        """Writes a text file into the run folder (an option file, a script) and prints it, as cat would."""
        (self.base / path).write_text(textwrap.dedent(text).lstrip("\n"))
        if show:
            self.show_file(path)

    def show_file(self, path):
        print(f"$ cat {path}")
        print(self.clean((self.base / path).read_text()).rstrip("\n"))
        print()

    def listing(self, folder, prefix=""):
        """Prints the names in a folder of the run (sorted, folders with a slash), as `ls -p` would list them."""
        path = self.base / folder
        names = sorted(n + ("/" if (path / n).is_dir() else "") for n in os.listdir(path) if n.startswith(prefix))
        print(f"$ ls -p {folder}" + (f" | grep '^{prefix}'" if prefix else ""))
        print(textwrap.fill("  ".join(names), width=78, break_on_hyphens=False))
        print()
        return names


def _redraw(block, clean):
    """Draws a client table again after its cells were cleaned. The columns come from the first border line; the row
    after it is the header (left-aligned); a column whose data cells were padded on the left (numbers) stays
    right-aligned (an empty cell says nothing about its column)."""
    cuts = [k for k, ch in enumerate(block[0]) if ch == "+"]
    rows = [None if line.startswith("+") else [line[x + 1:y] for x, y in zip(cuts, cuts[1:])] for line in block]
    header = next(k for k, r in enumerate(rows) if r is not None)
    ncol = len(cuts) - 1
    right = [any(r is not None and k != header and r[c].strip() and r[c].startswith("  ") for k, r in enumerate(rows))
             for c in range(ncol)]
    cells = [None if r is None else [clean(x.strip()) for x in r] for r in rows]
    widths = [max(len(r[c]) for r in cells if r is not None) for c in range(ncol)]
    drawn = []
    for k, r in enumerate(cells):
        if r is None:
            drawn.append("+" + "+".join("-" * (w + 2) for w in widths) + "+")
        else:
            drawn.append("|" + "|".join(" " + (t.rjust(w) if right[c] and k != header else t.ljust(w)) + " "
                                        for c, (t, w) in enumerate(zip(r, widths))) + "|")
    return drawn


def _arg(a):
    """A command-line argument as a reader would type it: quotes only where the shell needs them."""
    a = str(a)
    if re.fullmatch(r"[\w@%+=:,./-]+", a):
        return a
    if not re.search(r"[\"$`\\!]", a):
        return f'"{a}"'
    return shlex.quote(a)


def _show(args, out):
    print("$ " + " ".join(_arg(a) for a in args))
    if out.strip():
        print(out.rstrip("\n"))
    print()

types_session.py

"""LIST COLUMNS, HASH and KEY partitioning on the rows of the events table: the rows each partition received, and
which ranges a HASH table can prune.

A throwaway MySQL 8.4.11 server (mysql_session.py) loads events_load.sql quietly, then runs types.sql through the mysql
client. The one error the script prints is expected: a state code that no LIST partition names.
"""

from mysql_session import Server

# More sample pages than any partition has leaf pages, so the EXPLAIN rows estimates are the same on every run.
with Server(options=["--innodb-stats-persistent-sample-pages=300"]) as my:
    my.up()
    my.mysql("events_load.sql", flags=("--skip-comments",))
    my.mysql("types.sql", errors=1)

$ mysql --skip-comments < events_load.sql
Table	Op	Msg_type	Msg_text
app.events	analyze	status	OK

$ mysql -t -vvv --skip-comments --force < types.sql
--------------
CREATE TABLE orders_by_region (
  id BIGINT NOT NULL,
  region CHAR(2) NOT NULL,
  PRIMARY KEY (id, region)
)
PARTITION BY LIST COLUMNS (region) (
  PARTITION p_west VALUES IN ('MH', 'GJ', 'GA'),
  PARTITION p_south VALUES IN ('KA', 'TN', 'KL', 'TG'),
  PARTITION p_north VALUES IN ('DL', 'UP', 'HR')
)
--------------

Query OK, 0 rows affected

--------------
INSERT INTO orders_by_region VALUES (1, 'MH'), (2, 'KA'), (3, 'DL'), (4, 'TN')
--------------

Query OK, 4 rows affected
Records: 4  Duplicates: 0  Warnings: 0

--------------
INSERT INTO orders_by_region VALUES (5, 'RJ')
--------------

ERROR 1526 (HY000) at line 18: Table has no partition for value from column_list
--------------
CREATE TABLE events_by_account (
  id BIGINT NOT NULL,
  account_id INT NOT NULL,
  PRIMARY KEY (id, account_id)
)
PARTITION BY HASH (account_id) PARTITIONS 4
--------------

Query OK, 0 rows affected

--------------
INSERT INTO events_by_account SELECT id, account_id FROM events
--------------

Query OK, 18100 rows affected
Records: 18100  Duplicates: 0  Warnings: 0

--------------
SELECT 'p0' AS part, COUNT(*) AS row_count FROM events_by_account PARTITION (p0)
UNION ALL SELECT 'p1', COUNT(*) FROM events_by_account PARTITION (p1)
UNION ALL SELECT 'p2', COUNT(*) FROM events_by_account PARTITION (p2)
UNION ALL SELECT 'p3', COUNT(*) FROM events_by_account PARTITION (p3)
--------------

+------+-----------+
| part | row_count |
+------+-----------+
| p0   |      4525 |
| p1   |      4525 |
| p2   |      4525 |
| p3   |      4525 |
+------+-----------+
4 rows in set

--------------
EXPLAIN SELECT COUNT(*) FROM events_by_account WHERE account_id BETWEEN 3 AND 5
--------------

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: events_by_account
   partitions: p0,p1,p3
         type: index
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 12
          ref: NULL
         rows: 13575
     filtered: 11.11
        Extra: Using where; Using index
1 row in set, 1 warning

--------------
EXPLAIN SELECT COUNT(*) FROM events_by_account WHERE account_id BETWEEN 3 AND 6
--------------

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: events_by_account
   partitions: p0,p1,p2,p3
         type: index
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 12
          ref: NULL
         rows: 18100
     filtered: 11.11
        Extra: Using where; Using index
1 row in set, 1 warning

--------------
CREATE TABLE sessions (
  token CHAR(16) NOT NULL PRIMARY KEY,
  account_id INT NOT NULL
)
PARTITION BY KEY () PARTITIONS 4
--------------

Query OK, 0 rows affected

--------------
INSERT INTO sessions SELECT LEFT(payload, 16), account_id FROM events
--------------

Query OK, 18100 rows affected
Records: 18100  Duplicates: 0  Warnings: 0

--------------
SELECT 'p0' AS part, COUNT(*) AS row_count FROM sessions PARTITION (p0)
UNION ALL SELECT 'p1', COUNT(*) FROM sessions PARTITION (p1)
UNION ALL SELECT 'p2', COUNT(*) FROM sessions PARTITION (p2)
UNION ALL SELECT 'p3', COUNT(*) FROM sessions PARTITION (p3)
--------------

+------+-----------+
| part | row_count |
+------+-----------+
| p0   |      4547 |
| p1   |      4451 |
| p2   |      4528 |
| p3   |      4574 |
+------+-----------+
4 rows in set

Bye

types.sql

-- Three more partitioning types on the same rows: LIST COLUMNS by an exact value, HASH by an integer modulo the
-- partition count, KEY by the server's own hash of any column type.
USE app;

-- LIST COLUMNS: each partition names the values it holds, here state codes grouped by region.
CREATE TABLE orders_by_region (
  id BIGINT NOT NULL,
  region CHAR(2) NOT NULL,
  PRIMARY KEY (id, region)
)
PARTITION BY LIST COLUMNS (region) (
  PARTITION p_west VALUES IN ('MH', 'GJ', 'GA'),
  PARTITION p_south VALUES IN ('KA', 'TN', 'KL', 'TG'),
  PARTITION p_north VALUES IN ('DL', 'UP', 'HR')
);
INSERT INTO orders_by_region VALUES (1, 'MH'), (2, 'KA'), (3, 'DL'), (4, 'TN');
-- A value no list names has no partition to go to.
INSERT INTO orders_by_region VALUES (5, 'RJ');

-- HASH: account_id MOD 4 picks the partition, so 500 accounts spread evenly over four partitions.
CREATE TABLE events_by_account (
  id BIGINT NOT NULL,
  account_id INT NOT NULL,
  PRIMARY KEY (id, account_id)
)
PARTITION BY HASH (account_id) PARTITIONS 4;
INSERT INTO events_by_account SELECT id, account_id FROM events;
SELECT 'p0' AS part, COUNT(*) AS row_count FROM events_by_account PARTITION (p0)
UNION ALL SELECT 'p1', COUNT(*) FROM events_by_account PARTITION (p1)
UNION ALL SELECT 'p2', COUNT(*) FROM events_by_account PARTITION (p2)
UNION ALL SELECT 'p3', COUNT(*) FROM events_by_account PARTITION (p3);

-- HASH prunes a range only when it holds fewer values than there are partitions: 3 values of 4, then 4 of 4.
EXPLAIN SELECT COUNT(*) FROM events_by_account WHERE account_id BETWEEN 3 AND 5\G
EXPLAIN SELECT COUNT(*) FROM events_by_account WHERE account_id BETWEEN 3 AND 6\G

-- KEY: the server hashes the primary key (KEY () with no column), a 16-character token that is no integer.
CREATE TABLE sessions (
  token CHAR(16) NOT NULL PRIMARY KEY,
  account_id INT NOT NULL
)
PARTITION BY KEY () PARTITIONS 4;
INSERT INTO sessions SELECT LEFT(payload, 16), account_id FROM events;
SELECT 'p0' AS part, COUNT(*) AS row_count FROM sessions PARTITION (p0)
UNION ALL SELECT 'p1', COUNT(*) FROM sessions PARTITION (p1)
UNION ALL SELECT 'p2', COUNT(*) FROM sessions PARTITION (p2)
UNION ALL SELECT 'p3', COUNT(*) FROM sessions PARTITION (p3);

events_by_month_session.py

"""Partition pruning in EXPLAIN on a table partitioned by month: five queries and the partitions each one reads.

A throwaway MySQL 8.4.11 server (mysql_session.py) loads events_load.sql quietly, then runs events_by_month.sql
through the mysql client.
"""

from mysql_session import Server

# More sample pages than any partition has leaf pages, so the EXPLAIN rows estimates are the same on every run.
with Server(options=["--innodb-stats-persistent-sample-pages=300"]) as my:
    my.up()
    my.mysql("events_load.sql", flags=("--skip-comments",))
    my.mysql("events_by_month.sql")

$ mysql --skip-comments < events_load.sql
Table	Op	Msg_type	Msg_text
app.events	analyze	status	OK

$ mysql -t -vvv --skip-comments < events_by_month.sql
--------------
EXPLAIN SELECT COUNT(*) FROM events WHERE created_on >= '2025-03-01' AND created_on < '2025-04-01'
--------------

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: events
   partitions: p2025_03
         type: index
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 11
          ref: NULL
         rows: 3100
     filtered: 11.11
        Extra: Using where; Using index
1 row in set, 1 warning

--------------
EXPLAIN SELECT COUNT(*) FROM events WHERE created_on BETWEEN '2025-03-01' AND '2025-04-01'
--------------

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: events
   partitions: p2025_03,p2025_04
         type: index
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 11
          ref: NULL
         rows: 6100
     filtered: 11.11
        Extra: Using where; Using index
1 row in set, 1 warning

--------------
EXPLAIN SELECT COUNT(*) FROM events WHERE created_on BETWEEN '2025-02-15' AND '2025-04-10'
--------------

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: events
   partitions: p2025_02,p2025_03,p2025_04
         type: index
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 11
          ref: NULL
         rows: 8900
     filtered: 11.11
        Extra: Using where; Using index
1 row in set, 1 warning

--------------
EXPLAIN SELECT COUNT(*) FROM events WHERE created_on >= '2025-06-15'
--------------

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: events
   partitions: p2025_06,pmax
         type: index
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 11
          ref: NULL
         rows: 3000
     filtered: 33.33
        Extra: Using where; Using index
1 row in set, 1 warning

--------------
EXPLAIN SELECT * FROM events WHERE id = 1234
--------------

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: events
   partitions: p2025_01,p2025_02,p2025_03,p2025_04,p2025_05,p2025_06,pmax
         type: ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning

Bye

events_by_month.sql

-- Pruning on the monthly partitions of events: the partitions column of EXPLAIN names every partition the query
-- will read, and every partition it leaves out was pruned.
USE app;

-- One month as a half-open range: only March's partition.
EXPLAIN SELECT COUNT(*) FROM events WHERE created_on >= '2025-03-01' AND created_on < '2025-04-01'\G

-- BETWEEN includes its upper end, and the first of April lives in the next partition.
EXPLAIN SELECT COUNT(*) FROM events WHERE created_on BETWEEN '2025-03-01' AND '2025-04-01'\G

-- A range from mid-February to mid-April reads the three partitions it touches.
EXPLAIN SELECT COUNT(*) FROM events WHERE created_on BETWEEN '2025-02-15' AND '2025-04-10'\G

-- A range with no upper end always includes the catch-all partition.
EXPLAIN SELECT COUNT(*) FROM events WHERE created_on >= '2025-06-15'\G

-- A lookup by id alone says nothing about created_on: every partition is read, one index dive in each.
EXPLAIN SELECT * FROM events WHERE id = 1234\G

defeats_session.py

"""What defeats partition pruning: a function on the column, and a comparison that converts the column's values.

A throwaway MySQL 8.4.11 server (mysql_session.py) loads events_load.sql quietly, then runs defeats.sql through the
mysql client.
"""

from mysql_session import Server

# More sample pages than any partition has leaf pages, so the EXPLAIN rows estimates are the same on every run.
with Server(options=["--innodb-stats-persistent-sample-pages=300"]) as my:
    my.up()
    my.mysql("events_load.sql", flags=("--skip-comments",))
    my.mysql("defeats.sql")

$ mysql --skip-comments < events_load.sql
Table	Op	Msg_type	Msg_text
app.events	analyze	status	OK

$ mysql -t -vvv --skip-comments < defeats.sql
--------------
EXPLAIN SELECT COUNT(*) FROM events WHERE YEAR(created_on) = 2025 AND MONTH(created_on) = 3
--------------

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: events
   partitions: p2025_01,p2025_02,p2025_03,p2025_04,p2025_05,p2025_06,pmax
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 11
          ref: NULL
         rows: 18100
     filtered: 100.00
        Extra: Using where; Using index
1 row in set, 1 warning

--------------
EXPLAIN SELECT COUNT(*) FROM events WHERE created_on = 20250315
--------------

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: events
   partitions: p2025_03
         type: index
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 11
          ref: NULL
         rows: 3100
     filtered: 10.00
        Extra: Using where; Using index
1 row in set, 1 warning

--------------
CREATE TABLE deliveries (id BIGINT NOT NULL, postal_code VARCHAR(6) NOT NULL, PRIMARY KEY (id, postal_code))
PARTITION BY KEY (postal_code) PARTITIONS 4
--------------

Query OK, 0 rows affected

--------------
INSERT INTO deliveries VALUES (1, '411001'), (2, '560001'), (3, '110001'), (4, '400001')
--------------

Query OK, 4 rows affected
Records: 4  Duplicates: 0  Warnings: 0

--------------
EXPLAIN SELECT id FROM deliveries WHERE postal_code = 411001
--------------

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: deliveries
   partitions: p0,p1,p2,p3
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 34
          ref: NULL
         rows: 4
     filtered: 25.00
        Extra: Using where; Using index
1 row in set, 2 warnings

--------------
SHOW WARNINGS
--------------

*************************** 1. row ***************************
  Level: Warning
   Code: 1739
Message: Cannot use range access on index 'PRIMARY' due to type or collation conversion on field 'postal_code'
*************************** 2. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select `app`.`deliveries`.`id` AS `id` from `app`.`deliveries` where (`app`.`deliveries`.`postal_code` = 411001)
2 rows in set

--------------
EXPLAIN SELECT id FROM deliveries WHERE postal_code = '411001'
--------------

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: deliveries
   partitions: p1
         type: index
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 34
          ref: NULL
         rows: 2
     filtered: 50.00
        Extra: Using where; Using index
1 row in set, 1 warning

Bye

defeats.sql

-- Conditions that defeat pruning, each next to one that keeps it.
USE app;

-- A function around the partitioning column: the optimizer cannot tell which partitions hold March.
EXPLAIN SELECT COUNT(*) FROM events WHERE YEAR(created_on) = 2025 AND MONTH(created_on) = 3\G

-- A constant of another type is converted once, before pruning, so this one still reads one partition.
EXPLAIN SELECT COUNT(*) FROM events WHERE created_on = 20250315\G

-- A VARCHAR partitioning column compared with a number: MySQL compares them as numbers, so every row's value would
-- have to be converted, and no partition can be ruled out.
CREATE TABLE deliveries (id BIGINT NOT NULL, postal_code VARCHAR(6) NOT NULL, PRIMARY KEY (id, postal_code))
PARTITION BY KEY (postal_code) PARTITIONS 4;
INSERT INTO deliveries VALUES (1, '411001'), (2, '560001'), (3, '110001'), (4, '400001');
EXPLAIN SELECT id FROM deliveries WHERE postal_code = 411001\G
SHOW WARNINGS\G

-- The same value written as a string: one partition.
EXPLAIN SELECT id FROM deliveries WHERE postal_code = '411001'\G

keys_rule_session.py

"""The rules of a partitioned InnoDB table, as errors, and the cost of 256 partitions, as counters.

A throwaway MySQL 8.4.11 server (mysql_session.py) loads events_load.sql quietly, then runs keys_rule.sql through the
mysql client. The five errors the script prints are expected: a primary key and a unique key without the
partitioning column, two foreign keys, and one partition over the limit.
"""

from mysql_session import Server

# Building and loading 256 partitions can take over a minute on a loaded machine, and the client then prints a
# "(1 min 28.17 sec)" timing that the driver's own mask (which strips only "(N.NN sec)") leaves in place. Strip the
# minute form too, so two runs stay byte-identical whatever the load (the track forbids timings in printed output).
with Server(
    options=["--innodb-stats-persistent-sample-pages=300"],
    masks=[(r" \(\d+ min \d+\.\d+ sec\)", "")],
) as my:
    my.up()
    my.mysql("events_load.sql", flags=("--skip-comments",))
    my.mysql("keys_rule.sql", errors=5)

$ mysql --skip-comments < events_load.sql
Table	Op	Msg_type	Msg_text
app.events	analyze	status	OK

$ mysql -t -vvv --skip-comments --force < keys_rule.sql
--------------
CREATE TABLE events_by_id (id BIGINT NOT NULL PRIMARY KEY, created_on DATE NOT NULL)
PARTITION BY RANGE COLUMNS (created_on) (PARTITION p0 VALUES LESS THAN ('2025-02-01'), PARTITION p1 VALUES LESS THAN (MAXVALUE))
--------------

ERROR 1503 (HY000) at line 5: A PRIMARY KEY must include all columns in the table's partitioning function (prefixed columns are not considered).
--------------
ALTER TABLE events ADD UNIQUE KEY uk_payload (payload)
--------------

ERROR 1503 (HY000) at line 9: A UNIQUE INDEX must include all columns in the table's partitioning function (prefixed columns are not considered).
--------------
CREATE TABLE accounts (id INT NOT NULL PRIMARY KEY)
--------------

Query OK, 0 rows affected

--------------
ALTER TABLE events ADD FOREIGN KEY (account_id) REFERENCES accounts (id)
--------------

ERROR 1506 (HY000) at line 13: Foreign keys are not yet supported in conjunction with partitioning
--------------
CREATE TABLE event_notes (id INT NOT NULL PRIMARY KEY, event_id BIGINT NOT NULL, created_on DATE NOT NULL,
  FOREIGN KEY (event_id, created_on) REFERENCES events (id, created_on))
--------------

ERROR 1506 (HY000) at line 14: Foreign keys are not yet supported in conjunction with partitioning
--------------
CREATE TABLE too_many (id INT NOT NULL) PARTITION BY HASH (id) PARTITIONS 8193
--------------

ERROR 1499 (HY000) at line 18: Too many partitions (including subpartitions) were defined
--------------
CREATE TABLE plain (id BIGINT NOT NULL PRIMARY KEY, v INT NOT NULL, KEY (v))
--------------

Query OK, 0 rows affected

--------------
CREATE TABLE hashed (id BIGINT NOT NULL PRIMARY KEY, v INT NOT NULL, KEY (v)) PARTITION BY HASH (id) PARTITIONS 256
--------------

Query OK, 0 rows affected

--------------
INSERT INTO plain SELECT id, id MOD 1000 FROM events
--------------

Query OK, 18100 rows affected
Records: 18100  Duplicates: 0  Warnings: 0

--------------
INSERT INTO hashed SELECT id, id MOD 1000 FROM events
--------------

Query OK, 18100 rows affected
Records: 18100  Duplicates: 0  Warnings: 0

--------------
SELECT COUNT(*) AS tablespaces, MIN(FILE_SIZE) AS smallest, MAX(FILE_SIZE) AS largest, SUM(FILE_SIZE) AS all_files
FROM information_schema.INNODB_TABLESPACES WHERE NAME LIKE 'app/hashed#p#%'
--------------

+-------------+----------+---------+-----------+
| tablespaces | smallest | largest | all_files |
+-------------+----------+---------+-----------+
|         256 |   131072 |  131072 |  33554432 |
+-------------+----------+---------+-----------+
1 row in set

--------------
FLUSH STATUS
--------------

Query OK, 0 rows affected

--------------
SELECT COUNT(*) FROM plain WHERE v = 5
--------------

+----------+
| COUNT(*) |
+----------+
|       19 |
+----------+
1 row in set

--------------
SHOW SESSION STATUS LIKE 'Handler_read_key'
--------------

+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| Handler_read_key | 1     |
+------------------+-------+
1 row in set

--------------
FLUSH STATUS
--------------

Query OK, 0 rows affected

--------------
SELECT COUNT(*) FROM hashed WHERE v = 5
--------------

+----------+
| COUNT(*) |
+----------+
|       19 |
+----------+
1 row in set

--------------
SHOW SESSION STATUS LIKE 'Handler_read_key'
--------------

+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| Handler_read_key | 256   |
+------------------+-------+
1 row in set

Bye

keys_rule.sql

-- What a partitioned InnoDB table refuses, and what its partitions cost.
USE app;

-- A primary key must contain every column of the partitioning expression.
CREATE TABLE events_by_id (id BIGINT NOT NULL PRIMARY KEY, created_on DATE NOT NULL)
PARTITION BY RANGE COLUMNS (created_on) (PARTITION p0 VALUES LESS THAN ('2025-02-01'), PARTITION p1 VALUES LESS THAN (MAXVALUE));

-- So must every unique key: payload unique across all partitions would need created_on in the key.
ALTER TABLE events ADD UNIQUE KEY uk_payload (payload);

-- No foreign key may start from a partitioned InnoDB table, or point at one.
CREATE TABLE accounts (id INT NOT NULL PRIMARY KEY);
ALTER TABLE events ADD FOREIGN KEY (account_id) REFERENCES accounts (id);
CREATE TABLE event_notes (id INT NOT NULL PRIMARY KEY, event_id BIGINT NOT NULL, created_on DATE NOT NULL,
  FOREIGN KEY (event_id, created_on) REFERENCES events (id, created_on));

-- 8,192 partitions, subpartitions included, is the limit.
CREATE TABLE too_many (id INT NOT NULL) PARTITION BY HASH (id) PARTITIONS 8193;

-- The same 18,100 rows in one tablespace and in 256: each partition is a file and a set of B+trees of its own.
CREATE TABLE plain (id BIGINT NOT NULL PRIMARY KEY, v INT NOT NULL, KEY (v));
CREATE TABLE hashed (id BIGINT NOT NULL PRIMARY KEY, v INT NOT NULL, KEY (v)) PARTITION BY HASH (id) PARTITIONS 256;
INSERT INTO plain SELECT id, id MOD 1000 FROM events;
INSERT INTO hashed SELECT id, id MOD 1000 FROM events;
SELECT COUNT(*) AS tablespaces, MIN(FILE_SIZE) AS smallest, MAX(FILE_SIZE) AS largest, SUM(FILE_SIZE) AS all_files
FROM information_schema.INNODB_TABLESPACES WHERE NAME LIKE 'app/hashed#p#%';

-- A lookup by the secondary index v cannot be pruned (v is not the partitioning column): one index dive per
-- partition, counted by Handler_read_key in one client session.
FLUSH STATUS;
SELECT COUNT(*) FROM plain WHERE v = 5;
SHOW SESSION STATUS LIKE 'Handler_read_key';
FLUSH STATUS;
SELECT COUNT(*) FROM hashed WHERE v = 5;
SHOW SESSION STATUS LIKE 'Handler_read_key';

retention_session.py

"""Removing the oldest month: DELETE, DROP PARTITION and TRUNCATE PARTITION, measured in rows and binary log bytes.

A throwaway MySQL 8.4.11 server (mysql_session.py) loads events_load.sql quietly, runs retention.sql through the mysql
client, then lists the tables' files: the DELETE and the TRUNCATE left January's .ibd file in place, the DROP removed
it. The session prints no timings: how long each statement takes depends on the machine, while the rows touched and
the bytes logged do not.
"""

from mysql_session import Server

with Server(options=["--innodb-stats-persistent-sample-pages=300"]) as my:
    my.up()
    my.mysql("events_load.sql", flags=("--skip-comments",))
    my.mysql("retention.sql")
    my.listing("data/app")

$ mysql --skip-comments < events_load.sql
Table	Op	Msg_type	Msg_text
app.events	analyze	status	OK

$ mysql -t -vvv --skip-comments < retention.sql
--------------
CREATE TABLE events_b LIKE events
--------------

Query OK, 0 rows affected

--------------
INSERT INTO events_b SELECT * FROM events
--------------

Query OK, 18100 rows affected
Records: 18100  Duplicates: 0  Warnings: 0

--------------
CREATE TABLE events_c LIKE events
--------------

Query OK, 0 rows affected

--------------
INSERT INTO events_c SELECT * FROM events
--------------

Query OK, 18100 rows affected
Records: 18100  Duplicates: 0  Warnings: 0

--------------
SELECT LOCAL->>'$.binary_log_position' INTO @before FROM performance_schema.log_status
--------------

Query OK, 1 row affected

--------------
DELETE FROM events WHERE created_on < '2025-02-01'
--------------

Query OK, 3100 rows affected

--------------
SELECT LOCAL->>'$.binary_log_position' - @before AS binlog_bytes FROM performance_schema.log_status
--------------

+--------------+
| binlog_bytes |
+--------------+
|       275891 |
+--------------+
1 row in set

--------------
SELECT LOCAL->>'$.binary_log_position' INTO @before FROM performance_schema.log_status
--------------

Query OK, 1 row affected

--------------
ALTER TABLE events_b ALGORITHM=INPLACE, LOCK=NONE, DROP PARTITION p2025_01
--------------

Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

--------------
SELECT LOCAL->>'$.binary_log_position' - @before AS binlog_bytes FROM performance_schema.log_status
--------------

+--------------+
| binlog_bytes |
+--------------+
|          237 |
+--------------+
1 row in set

--------------
SELECT LOCAL->>'$.binary_log_position' INTO @before FROM performance_schema.log_status
--------------

Query OK, 1 row affected

--------------
ALTER TABLE events_c TRUNCATE PARTITION p2025_01
--------------

Query OK, 0 rows affected

--------------
SELECT LOCAL->>'$.binary_log_position' - @before AS binlog_bytes FROM performance_schema.log_status
--------------

+--------------+
| binlog_bytes |
+--------------+
|          205 |
+--------------+
1 row in set

--------------
SELECT OBJECT_NAME, COUNT_DELETE FROM performance_schema.table_io_waits_summary_by_table
WHERE OBJECT_SCHEMA = 'app' ORDER BY OBJECT_NAME
--------------

+-------------+--------------+
| OBJECT_NAME | COUNT_DELETE |
+-------------+--------------+
| events      |         3100 |
| events_b    |            0 |
| events_c    |            0 |
+-------------+--------------+
3 rows in set

--------------
SELECT (SELECT COUNT(*) FROM events) AS events_rows, (SELECT COUNT(*) FROM events_b) AS events_b_rows,
       (SELECT COUNT(*) FROM events_c) AS events_c_rows
--------------

+-------------+---------------+---------------+
| events_rows | events_b_rows | events_c_rows |
+-------------+---------------+---------------+
|       15000 |         15000 |         15000 |
+-------------+---------------+---------------+
1 row in set

Bye

$ ls -p data/app
events#p#p2025_01.ibd  events#p#p2025_02.ibd  events#p#p2025_03.ibd
events#p#p2025_04.ibd  events#p#p2025_05.ibd  events#p#p2025_06.ibd
events#p#pmax.ibd  events_b#p#p2025_02.ibd  events_b#p#p2025_03.ibd
events_b#p#p2025_04.ibd  events_b#p#p2025_05.ibd  events_b#p#p2025_06.ibd
events_b#p#pmax.ibd  events_c#p#p2025_01.ibd  events_c#p#p2025_02.ibd
events_c#p#p2025_03.ibd  events_c#p#p2025_04.ibd  events_c#p#p2025_05.ibd
events_c#p#p2025_06.ibd  events_c#p#pmax.ibd

retention.sql

-- Retention: the oldest month removed from three identical copies of the events table, by DELETE from events, by
-- DROP PARTITION from events_b and by TRUNCATE PARTITION from events_c. The server writes its binary log in ROW
-- format, the 8.4 default.
USE app;
CREATE TABLE events_b LIKE events;
INSERT INTO events_b SELECT * FROM events;
CREATE TABLE events_c LIKE events;
INSERT INTO events_c SELECT * FROM events;

-- The binary log position before and after a statement is the number of bytes that statement logged.
SELECT LOCAL->>'$.binary_log_position' INTO @before FROM performance_schema.log_status;
DELETE FROM events WHERE created_on < '2025-02-01';
SELECT LOCAL->>'$.binary_log_position' - @before AS binlog_bytes FROM performance_schema.log_status;

SELECT LOCAL->>'$.binary_log_position' INTO @before FROM performance_schema.log_status;
ALTER TABLE events_b ALGORITHM=INPLACE, LOCK=NONE, DROP PARTITION p2025_01;
SELECT LOCAL->>'$.binary_log_position' - @before AS binlog_bytes FROM performance_schema.log_status;

SELECT LOCAL->>'$.binary_log_position' INTO @before FROM performance_schema.log_status;
ALTER TABLE events_c TRUNCATE PARTITION p2025_01;
SELECT LOCAL->>'$.binary_log_position' - @before AS binlog_bytes FROM performance_schema.log_status;

-- The rows each table had deleted, row by row, as the Performance Schema counted them.
SELECT OBJECT_NAME, COUNT_DELETE FROM performance_schema.table_io_waits_summary_by_table
WHERE OBJECT_SCHEMA = 'app' ORDER BY OBJECT_NAME;

-- The same rows are gone from all three tables.
SELECT (SELECT COUNT(*) FROM events) AS events_rows, (SELECT COUNT(*) FROM events_b) AS events_b_rows,
       (SELECT COUNT(*) FROM events_c) AS events_c_rows;

catchall_session.py

"""The catch-all partition: why ADD PARTITION fails while it exists, and what REORGANIZE PARTITION copies.

A throwaway MySQL 8.4.11 server (mysql_session.py) loads events_load.sql quietly, then runs catchall.sql through the
mysql client. The three errors the script prints are expected: ADD PARTITION after MAXVALUE, REORGANIZE with
LOCK=NONE, and a row past the last partition of a table without a catch-all.
"""

from mysql_session import Server

with Server(options=["--innodb-stats-persistent-sample-pages=300"]) as my:
    my.up()
    my.mysql("events_load.sql", flags=("--skip-comments",))
    my.mysql("catchall.sql", errors=3)

$ mysql --skip-comments < events_load.sql
Table	Op	Msg_type	Msg_text
app.events	analyze	status	OK

$ mysql -t -vvv --skip-comments --force < catchall.sql
--------------
INSERT INTO events VALUES (900001, '2025-07-01', 7, 'view', SHA2(900001, 256)),
                          (900002, '2025-07-01', 8, 'click', SHA2(900002, 256))
--------------

Query OK, 2 rows affected
Records: 2  Duplicates: 0  Warnings: 0

--------------
ALTER TABLE events ADD PARTITION (PARTITION p2025_07 VALUES LESS THAN ('2025-08-01'))
--------------

ERROR 1493 (HY000) at line 9: VALUES LESS THAN value must be strictly increasing for each partition
--------------
ALTER TABLE events ALGORITHM=INPLACE, LOCK=NONE, REORGANIZE PARTITION pmax INTO (
  PARTITION p2025_07 VALUES LESS THAN ('2025-08-01'), PARTITION pmax VALUES LESS THAN (MAXVALUE))
--------------

ERROR 1845 (0A000) at line 12: LOCK=NONE is not supported for this operation. Try LOCK=SHARED.
--------------
SELECT VARIABLE_VALUE INTO @inserted FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_rows_inserted'
--------------

Query OK, 1 row affected

--------------
ALTER TABLE events REORGANIZE PARTITION pmax INTO (
  PARTITION p2025_07 VALUES LESS THAN ('2025-08-01'), PARTITION pmax VALUES LESS THAN (MAXVALUE))
--------------

Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

--------------
SELECT VARIABLE_VALUE - @inserted AS rows_copied FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_rows_inserted'
--------------

+-------------+
| rows_copied |
+-------------+
|           2 |
+-------------+
1 row in set

--------------
SELECT VARIABLE_VALUE INTO @inserted FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_rows_inserted'
--------------

Query OK, 1 row affected

--------------
ALTER TABLE events REORGANIZE PARTITION pmax INTO (
  PARTITION p2025_08 VALUES LESS THAN ('2025-09-01'), PARTITION pmax VALUES LESS THAN (MAXVALUE))
--------------

Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

--------------
SELECT VARIABLE_VALUE - @inserted AS rows_copied FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_rows_inserted'
--------------

+-------------+
| rows_copied |
+-------------+
|           0 |
+-------------+
1 row in set

--------------
CREATE TABLE events_no_max LIKE events
--------------

Query OK, 0 rows affected

--------------
ALTER TABLE events_no_max REMOVE PARTITIONING
--------------

Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

--------------
ALTER TABLE events_no_max PARTITION BY RANGE COLUMNS (created_on) (PARTITION p2025_06 VALUES LESS THAN ('2025-07-01'))
--------------

Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

--------------
INSERT INTO events_no_max VALUES (1, '2025-07-01', 7, 'view', SHA2(1, 256))
--------------

ERROR 1526 (HY000) at line 31: Table has no partition for value from column_list
--------------
ALTER TABLE events_no_max ALGORITHM=INPLACE, LOCK=NONE, ADD PARTITION (PARTITION p2025_07 VALUES LESS THAN ('2025-08-01'))
--------------

Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

--------------
INSERT INTO events_no_max VALUES (1, '2025-07-01', 7, 'view', SHA2(1, 256))
--------------

Query OK, 1 row affected

Bye

catchall.sql

-- Making room for July: ADD PARTITION and REORGANIZE PARTITION against the catch-all partition pmax.
USE app;

-- Two early rows for July arrive before July has a partition, so they land in pmax.
INSERT INTO events VALUES (900001, '2025-07-01', 7, 'view', SHA2(900001, 256)),
                          (900002, '2025-07-01', 8, 'click', SHA2(900002, 256));

-- ADD PARTITION can only append after the last partition, and nothing comes after MAXVALUE.
ALTER TABLE events ADD PARTITION (PARTITION p2025_07 VALUES LESS THAN ('2025-08-01'));

-- REORGANIZE splits pmax in two. It copies the rows it moves, and it permits no concurrent writes: LOCK=NONE is refused.
ALTER TABLE events ALGORITHM=INPLACE, LOCK=NONE, REORGANIZE PARTITION pmax INTO (
  PARTITION p2025_07 VALUES LESS THAN ('2025-08-01'), PARTITION pmax VALUES LESS THAN (MAXVALUE));

-- Innodb_rows_inserted counts the rows the split copies into the new partitions.
SELECT VARIABLE_VALUE INTO @inserted FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_rows_inserted';
ALTER TABLE events REORGANIZE PARTITION pmax INTO (
  PARTITION p2025_07 VALUES LESS THAN ('2025-08-01'), PARTITION pmax VALUES LESS THAN (MAXVALUE));
SELECT VARIABLE_VALUE - @inserted AS rows_copied FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_rows_inserted';

-- Split a month ahead, while pmax is still empty, and there is nothing to copy.
SELECT VARIABLE_VALUE INTO @inserted FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_rows_inserted';
ALTER TABLE events REORGANIZE PARTITION pmax INTO (
  PARTITION p2025_08 VALUES LESS THAN ('2025-09-01'), PARTITION pmax VALUES LESS THAN (MAXVALUE));
SELECT VARIABLE_VALUE - @inserted AS rows_copied FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_rows_inserted';

-- Without a catch-all, ADD PARTITION works in place, but a row past the last bound has nowhere to go.
CREATE TABLE events_no_max LIKE events;
ALTER TABLE events_no_max REMOVE PARTITIONING;
ALTER TABLE events_no_max PARTITION BY RANGE COLUMNS (created_on) (PARTITION p2025_06 VALUES LESS THAN ('2025-07-01'));
INSERT INTO events_no_max VALUES (1, '2025-07-01', 7, 'view', SHA2(1, 256));
ALTER TABLE events_no_max ALGORITHM=INPLACE, LOCK=NONE, ADD PARTITION (PARTITION p2025_07 VALUES LESS THAN ('2025-08-01'));
INSERT INTO events_no_max VALUES (1, '2025-07-01', 7, 'view', SHA2(1, 256));

exchange_session.py

"""EXCHANGE PARTITION into an archive table, and what the exchange requires of that table.

A throwaway MySQL 8.4.11 server (mysql_session.py) loads events_load.sql quietly, then runs exchange.sql through the
mysql client. The two errors the script prints are expected: an archive table with an extra index, and an exchange
after a column was added with ALGORITHM=INSTANT.
"""

from mysql_session import Server

with Server(options=["--innodb-stats-persistent-sample-pages=300"]) as my:
    my.up()
    my.mysql("events_load.sql", flags=("--skip-comments",))
    my.mysql("exchange.sql", errors=2)

$ mysql --skip-comments < events_load.sql
Table	Op	Msg_type	Msg_text
app.events	analyze	status	OK

$ mysql -t -vvv --skip-comments --force < exchange.sql
--------------
CREATE TABLE events_2025_01 LIKE events
--------------

Query OK, 0 rows affected

--------------
ALTER TABLE events_2025_01 REMOVE PARTITIONING
--------------

Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

--------------
SELECT VARIABLE_VALUE INTO @inserted FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_rows_inserted'
--------------

Query OK, 1 row affected

--------------
ALTER TABLE events ALGORITHM=INPLACE, LOCK=NONE, EXCHANGE PARTITION p2025_01 WITH TABLE events_2025_01
--------------

Query OK, 0 rows affected

--------------
SELECT (SELECT COUNT(*) FROM events PARTITION (p2025_01)) AS in_partition,
       (SELECT COUNT(*) FROM events_2025_01) AS in_archive,
       (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_rows_inserted') - @inserted AS rows_copied
--------------

+--------------+------------+-------------+
| in_partition | in_archive | rows_copied |
+--------------+------------+-------------+
|            0 |       3100 |           0 |
+--------------+------------+-------------+
1 row in set

--------------
ALTER TABLE events DROP PARTITION p2025_01
--------------

Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

--------------
SELECT PARTITION_NAME FROM information_schema.PARTITIONS
WHERE TABLE_SCHEMA = 'app' AND TABLE_NAME = 'events' ORDER BY PARTITION_ORDINAL_POSITION
--------------

+----------------+
| PARTITION_NAME |
+----------------+
| p2025_02       |
| p2025_03       |
| p2025_04       |
| p2025_05       |
| p2025_06       |
| pmax           |
+----------------+
6 rows in set

--------------
CREATE TABLE events_2025_02 LIKE events_2025_01
--------------

Query OK, 0 rows affected

--------------
ALTER TABLE events_2025_02 ADD INDEX (account_id)
--------------

Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

--------------
ALTER TABLE events EXCHANGE PARTITION p2025_02 WITH TABLE events_2025_02
--------------

ERROR 1736 (HY000) at line 23: Tables have different definitions
--------------
ALTER TABLE events ADD COLUMN note VARCHAR(20), ALGORITHM=INSTANT
--------------

Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

--------------
CREATE TABLE events_2025_03 LIKE events
--------------

Query OK, 0 rows affected

--------------
ALTER TABLE events_2025_03 REMOVE PARTITIONING
--------------

Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

--------------
ALTER TABLE events EXCHANGE PARTITION p2025_03 WITH TABLE events_2025_03
--------------

ERROR 1731 (HY000) at line 29: Non matching attribute 'INSTANT COLUMN(s)' between partition and table
Bye

exchange.sql

-- Archiving the oldest month: EXCHANGE PARTITION swaps January's partition with an empty table of the same shape.
USE app;

-- The archive table: the same columns, keys and row format as events, without the partitioning.
CREATE TABLE events_2025_01 LIKE events;
ALTER TABLE events_2025_01 REMOVE PARTITIONING;

-- The swap copies no rows: Innodb_rows_inserted, which counts every row InnoDB inserts, does not move.
SELECT VARIABLE_VALUE INTO @inserted FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_rows_inserted';
ALTER TABLE events ALGORITHM=INPLACE, LOCK=NONE, EXCHANGE PARTITION p2025_01 WITH TABLE events_2025_01;
SELECT (SELECT COUNT(*) FROM events PARTITION (p2025_01)) AS in_partition,
       (SELECT COUNT(*) FROM events_2025_01) AS in_archive,
       (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_rows_inserted') - @inserted AS rows_copied;

-- The partition is empty now, so dropping it deletes nothing.
ALTER TABLE events DROP PARTITION p2025_01;
SELECT PARTITION_NAME FROM information_schema.PARTITIONS
WHERE TABLE_SCHEMA = 'app' AND TABLE_NAME = 'events' ORDER BY PARTITION_ORDINAL_POSITION;

-- The archive must match exactly: one extra index is a different definition.
CREATE TABLE events_2025_02 LIKE events_2025_01;
ALTER TABLE events_2025_02 ADD INDEX (account_id);
ALTER TABLE events EXCHANGE PARTITION p2025_02 WITH TABLE events_2025_02;

-- A column added to events with ALGORITHM=INSTANT ends every later exchange with this table.
ALTER TABLE events ADD COLUMN note VARCHAR(20), ALGORITHM=INSTANT;
CREATE TABLE events_2025_03 LIKE events;
ALTER TABLE events_2025_03 REMOVE PARTITIONING;
ALTER TABLE events EXCHANGE PARTITION p2025_03 WITH TABLE events_2025_03;

validation_session.py

"""EXCHANGE PARTITION WITH VALIDATION and WITHOUT VALIDATION, on a staging table that holds one row too late.

A throwaway MySQL 8.4.11 server (mysql_session.py) loads events_load.sql quietly, then runs validation.sql through the
mysql client. The one error the script prints is expected: the validated exchange refusing the August row.
"""

from mysql_session import Server

with Server(options=["--innodb-stats-persistent-sample-pages=300"]) as my:
    my.up()
    my.mysql("events_load.sql", flags=("--skip-comments",))
    my.mysql("validation.sql", errors=1)

$ mysql --skip-comments < events_load.sql
Table	Op	Msg_type	Msg_text
app.events	analyze	status	OK

$ mysql -t -vvv --skip-comments --force < validation.sql
--------------
ALTER TABLE events REORGANIZE PARTITION pmax INTO (
  PARTITION p2025_07 VALUES LESS THAN ('2025-08-01'), PARTITION pmax VALUES LESS THAN (MAXVALUE))
--------------

Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

--------------
CREATE TABLE july_load LIKE events
--------------

Query OK, 0 rows affected

--------------
ALTER TABLE july_load REMOVE PARTITIONING
--------------

Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

--------------
INSERT INTO july_load VALUES (900001, '2025-07-01', 7, 'view', SHA2(900001, 256)),
                             (900002, '2025-07-02', 8, 'click', SHA2(900002, 256)),
                             (900003, '2025-08-02', 9, 'view', SHA2(900003, 256))
--------------

Query OK, 3 rows affected
Records: 3  Duplicates: 0  Warnings: 0

--------------
ALTER TABLE events EXCHANGE PARTITION p2025_07 WITH TABLE july_load
--------------

ERROR 1737 (HY000) at line 14: Found a row that does not match the partition
--------------
ALTER TABLE events EXCHANGE PARTITION p2025_07 WITH TABLE july_load WITHOUT VALIDATION
--------------

Query OK, 0 rows affected

--------------
SELECT COUNT(*) FROM events WHERE created_on >= '2025-08-01'
--------------

+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+
1 row in set

--------------
SELECT id, created_on FROM events PARTITION (p2025_07) ORDER BY id
--------------

+--------+------------+
| id     | created_on |
+--------+------------+
| 900001 | 2025-07-01 |
| 900002 | 2025-07-02 |
| 900003 | 2025-08-02 |
+--------+------------+
3 rows in set

--------------
ALTER TABLE events CHECK PARTITION p2025_07
--------------

+------------+-------+----------+-------------------------------------------------------------------------------------+
| Table      | Op    | Msg_type | Msg_text                                                                            |
+------------+-------+----------+-------------------------------------------------------------------------------------+
| app.events | check | error    | Found a misplaced row in part 6 should be in part 7:
 created_on:2025-08-02         |
| app.events | check | error    | Partition p2025_07 returned error                                                   |
| app.events | check | error    | Table upgrade required. Please do "REPAIR TABLE `events`" or dump/reload to fix it! |
+------------+-------+----------+-------------------------------------------------------------------------------------+
3 rows in set

--------------
ALTER TABLE events REPAIR PARTITION p2025_07
--------------

+------------+--------+----------+------------------------+
| Table      | Op     | Msg_type | Msg_text               |
+------------+--------+----------+------------------------+
| app.events | repair | warning  | Moved 1 misplaced rows |
| app.events | repair | status   | OK                     |
+------------+--------+----------+------------------------+
2 rows in set

--------------
SELECT COUNT(*) FROM events WHERE created_on >= '2025-08-01'
--------------

+----------+
| COUNT(*) |
+----------+
|        1 |
+----------+
1 row in set

Bye

validation.sql

-- Loading a month by exchange: July is prepared in a staging table and swapped into an empty partition. One row of
-- the staging table carries an August date by mistake.
USE app;
ALTER TABLE events REORGANIZE PARTITION pmax INTO (
  PARTITION p2025_07 VALUES LESS THAN ('2025-08-01'), PARTITION pmax VALUES LESS THAN (MAXVALUE));

CREATE TABLE july_load LIKE events;
ALTER TABLE july_load REMOVE PARTITIONING;
INSERT INTO july_load VALUES (900001, '2025-07-01', 7, 'view', SHA2(900001, 256)),
                             (900002, '2025-07-02', 8, 'click', SHA2(900002, 256)),
                             (900003, '2025-08-02', 9, 'view', SHA2(900003, 256));

-- WITH VALIDATION, the default, checks every row against the partition's range and refuses the swap.
ALTER TABLE events EXCHANGE PARTITION p2025_07 WITH TABLE july_load;

-- WITHOUT VALIDATION skips the check, and the August row now sits in July's partition.
ALTER TABLE events EXCHANGE PARTITION p2025_07 WITH TABLE july_load WITHOUT VALIDATION;

-- A query for August prunes to pmax and misses the row; reading July's partition whole shows it there.
SELECT COUNT(*) FROM events WHERE created_on >= '2025-08-01';
SELECT id, created_on FROM events PARTITION (p2025_07) ORDER BY id;

-- CHECK PARTITION reports the misplaced row, and REPAIR PARTITION moves it where it belongs.
ALTER TABLE events CHECK PARTITION p2025_07;
ALTER TABLE events REPAIR PARTITION p2025_07;
SELECT COUNT(*) FROM events WHERE created_on >= '2025-08-01';