MY.L6.PK_NOT_BIGINT — An integer primary key too narrow to grow into
- Category: idiom
- Level: 6
- 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.
The timing is the whole problem
An INT runs out at 2,147,483,647 signed and 4,294,967,295 unsigned, a MEDIUMINT at 8,388,607
and 16,777,215, a SMALLINT at 32,767 and 65,535. increments() gives you INT UNSIGNED, so
the example below is near the second number, not the first — and the finding names whichever
applies to the key it found. Nothing warns on the way there. The first symptom is an INSERT
failing on a table that
has been working for years.
And the moment the fix becomes necessary is precisely the moment it is most expensive: widening the key rewrites the table and every index over it, under a lock, on the largest table you have.
Chosen early, the same decision costs four bytes a row. That asymmetry is the entire argument, and it is why this sits at level 6 — an appetite for being told now, not a defect to fail a build on.
Flagged
Schema::create('events', function (Blueprint $table) {
$table->increments('id');
});
Preferred
Schema::create('events', function (Blueprint $table) {
$table->id();
});
Widen the referencing columns in the same change
A key widened on its own is half a fix: every foreign-key column pointing at it has to widen too, or the constraint stops matching.
Those columns live on other tables, and an audit judges one object at a time — so this finding says they must be widened without naming them. Claiming to enumerate them from here would be a promise the rule cannot keep.
How full the key is, and which rule reads that
This rule judges the type and stays blind to how far the key has got, so one schema reads the same on
every run. How far AUTO_INCREMENT has got is read beside the catalog by
MY.L5.PK_NEAR_CEILING, which reports the same key one level earlier
once it has used half of its ceiling, or the share set in sqlens.audit.key_ceiling.min_percent_used.
The value comes from SHOW CREATE TABLE, because information_schema.TABLES serves a cached one that
can be a day old. It needs no privilege beyond the SELECT the recommended audit role
already holds.
The framework's own migrations ledger is exempt
Laravel creates migrations itself, from DatabaseMigrationRepository, with increments('id') —
on every migrate:install and regardless of what the application would prefer. The advice this
rule gives cannot be taken there: the table belongs to the framework, and rewriting it would mean
writing against the framework's own default on a table the application does not own.
So the rule skips it, and it is the only table it treats that way. The name comes from
database.migrations.table rather than from a constant here — rename the ledger and an ordinary
table called migrations is judged like any other.
What is not flagged
A bigint key, a UUID or text key, and any composite key. A composite key's range is the
product of its parts, so "too narrow" stops being a property of one column, and a rule reporting one
anyway would be doing arithmetic nobody asked it to do.
A small lookup table will never approach the ceiling and gains nothing from widening. Ignore those deliberately rather than converting the whole schema:
'audit' => [
'ignore' => [
'pairs' => [
['rule' => 'MY.L6.PK_NOT_BIGINT', 'objects' => ['shop.currencies']],
],
],
],
Sources
- MySQL 8.4 reference: integer types —
the range table:
TINYINTto 127 signed,SMALLINTto 32,767,MEDIUMINTto 8,388,607,INTto 2,147,483,647,BIGINTto 9,223,372,036,854,775,807. Unsigned doubles each ceiling, which postpones the problem rather than removing it.