Skip to main content

MY.L5.PK_NEAR_CEILING — A primary key whose AUTO_INCREMENT is closing in on its ceiling

  • Category: safety
  • Level: 5
  • Confidence: deterministic
  • Downtime class: none
  • Stability: stable
  • Suites: audit
  • Applies to: MySQL 8.4
  • Not transferable to MariaDB: this page describes MySQL 8.4 behavior. MariaDB answers the same driver and does not share these semantics, so SQLens refuses it outright rather than reasoning about it — see drivers/unsupported.

What it checks​

A single-column AUTO_INCREMENT primary key narrower than BIGINT whose counter has handed out at least half of the values the key can hold. The share is a setting:

'audit' => [
    'key_ceiling' => [
        'min_percent_used' => 50,
    ],
],

Reported:

Schema::create('events', function (Blueprint $table) {
    $table->increments('id');   // INT UNSIGNED: 4,294,967,295 values
});

// Years later, AUTO_INCREMENT stands at 3,865,470,567: 90.0% of the ceiling.

Not reported, because the key was chosen wide enough when the table was created:

Schema::create('events', function (Blueprint $table) {
    $table->id();               // BIGINT UNSIGNED
});

Not reported: the same table while its counter is below the share, a BIGINT key, and a key without AUTO_INCREMENT.

A type choice, and then a date​

MY.L6.PK_NOT_BIGINT reports every key narrower than BIGINT, on its type alone, from the day the table exists. That finding reads the same on a key that has handed out four values and on one that has handed out four billion, and a project can reasonably leave it for later at level 6.

This rule reads the next AUTO_INCREMENT value. Once a key has used half of its ceiling, it has handed out as many values as it has left: at the rate that brought it there, the room that remains lasts as long as the table has existed so far, and the insert rate of a growing table rarely stays where it was. From then on the failing INSERT is a date, and widening the key gets more expensive with every row.

The ceiling is the column's​

AUTO_INCREMENT has no maximum of its own: it stops where the column's type stops. The signedness is read from the catalog, because increments() produces INT UNSIGNED, which reaches 4,294,967,295 rather than 2,147,483,647. Measured on MySQL 8.0.36:

CREATE TABLE t (id TINYINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, v INT);
ALTER TABLE t AUTO_INCREMENT = 255;
INSERT INTO t (v) VALUES (1);   -- takes 255
INSERT INTO t (v) VALUES (2);
-- ERROR 1062 (23000): Duplicate entry '255' for key 't.PRIMARY'

Read fresh, from SHOW CREATE TABLE​

information_schema.TABLES.AUTO_INCREMENT is one of the cached statistics columns, and information_schema_stats_expiry keeps a cached value for a day by default. Measured on MySQL 8.0.36 at that default: the column still answered 4 after five more inserts, and still 4 after ALTER TABLE t AUTO_INCREMENT = 4000000000, while SHOW CREATE TABLE reported AUTO_INCREMENT=4000000000.

A SET_VAR hint cannot change that setting for one statement (warning 3637), and setting it for the session would make every later statistics read on the connection fetch fresh numbers for every table. So the value is read from SHOW CREATE TABLE, one statement per key narrower than BIGINT; a schema whose keys are all BIGINT sends none.

SHOW CREATE TABLE needs a privilege on the table, which the SELECT of the recommended audit role is. It takes a shared metadata lock on the table: a table whose lock is not granted in time, or one dropped since the keys were listed, is reported undetermined on its own, and the other keys are still read.

What it cannot read​

A key without AUTO_INCREMENT, filled by the application, has no counter. Its fill could only be read from the table's rows, and an audit does not read rows.

Sources​

The fix material this rule carries​

A finding from this rule carries a payload whose strategy is none: this rule has looked, and there is no safe standard sequence. That is a conclusion rather than an omission — a finding with no payload at all says only that nobody wrote one.

The payload is material for you or an agent to apply. SQLens writes no migration and runs no DDL. See the remediation payload for every field, the placeholder semantics, and the version rules a consumer has to follow.