False positive: AUTO_INCREMENT near max capacity reported for empty tables with bigint unsigned columns

[jordantrizz]

I stumbled upon this recently, there were 1696 lines. I just ran it through AI to come up with the following. Don't know if you're cool with that.

Description

MySQLTuner incorrectly reports autoincrement columns as being near or at 100% capacity on tables that are completely empty and have never had a row inserted.

Environment

• MySQLTuner version: 2.8.41
• MySQL/Percona version: Percona Server 8.0.45-36 (LTS)

Observed Behaviour

MySQLTuner reports:

✘  Table 'your_database.wp_woocommerce_payment_tokenmeta' has an autoincrement value near max capacity (100.00%)

Actual State of the Table

SELECT TABLE_NAME, AUTO_INCREMENT, COLUMN_TYPE
FROM information_schema.TABLES
JOIN information_schema.COLUMNS
  ON TABLES.TABLE_SCHEMA = COLUMNS.TABLE_SCHEMA
  AND TABLES.TABLE_NAME = COLUMNS.TABLE_NAME
  AND COLUMNS.EXTRA LIKE '%auto_increment%'
WHERE TABLES.TABLE_SCHEMA = 'your_database'
  AND TABLES.TABLE_NAME = 'wp_woocommerce_payment_tokenmeta';

+----------------------------------+----------------+-----------------+
| TABLE_NAME                       | AUTO_INCREMENT | column_type     |
+----------------------------------+----------------+-----------------+
| wp_woocommerce_payment_tokenmeta | 1              | bigint unsigned |
+----------------------------------+----------------+-----------------+

SELECT meta_id FROM wp_woocommerce_payment_tokenmeta ORDER BY meta_id DESC LIMIT 1;
-- Empty set (0.00 sec)

The table has zero rows. AUTO_INCREMENT = 1 is MySQL's default initial value for any newly created or truncated table — it does not indicate that any values have been consumed. The column type is bigint unsigned, with a theoretical maximum of 18,446,744,073,709,551,615. There is no capacity concern whatsoever.

Root Cause

The script computes a $percent value based on AUTO_INCREMENT relative to the column type's theoretical maximum (e.g. 4294967295 for int unsigned, 18446744073709551615 for bigint unsigned). The percentage formula is called with $auto_increment as the numerator.

The bug is that MySQL initialises AUTO_INCREMENT = 1 on all newly created or empty tables — this is not a consumed value, it is the starting counter. The script does not distinguish between:

• AUTO_INCREMENT = 1 with TABLE_ROWS = 0 → table is empty, counter is uninitialised
• AUTO_INCREMENT = 1 with TABLE_ROWS > 0 → one row exists, counter is at 2

In the bigint unsigned case specifically, 1 / 18446744073709551615 ≈ 0%, so a 100% result is impossible from the division alone. This means the script is likely using a smaller intermediate value as the denominator — possibly deriving the max from COLUMN_TYPE via a regex or lookup that misidentifies bigint unsigned columns, or it falls through to a default maximum that happens to equal 1, causing 1 / 1 = 100%.

The source code shows the output string as:

"Table '$db.$name' has an autoincrement value near max capacity ($percent%)"

Where $percent is computed via the percentage() helper which returns "100.00" when the total (denominator) is 0:

sub percentage {
    my $value = shift;
    my $total = shift;
    $total = 0 unless defined $total;
    $total = 0 if $total eq "NULL";
    return "100.00" if $total == 0;   # <-- this is the bug trigger
    ...
}

If the column type lookup fails or returns NULL/0 for a bigint unsigned column (e.g. due to a regex that doesn't match bigint unsigned), $total becomes 0, and percentage() returns 100.00 regardless of the actual AUTO_INCREMENT value — even when it is 1 on an empty table.

Expected Behaviour

• Tables where AUTO_INCREMENT = 1 and TABLE_ROWS = 0 should be skipped or reported as 0%
• The percentage() function returning 100.00 when $total == 0 should not be used for capacity calculations — a zero or unresolvable max should produce a skip or an error, not a false alarm
• The column type → max value lookup must correctly handle bigint unsigned (and bigint signed) column types

Suggested Fix

Two changes are needed:

1. Guard against uninitialised / empty tables:

# Skip tables where AUTO_INCREMENT is at default (never used) and table is empty
next if ( $auto_increment <= 1 && ( $table_rows // 0 ) == 0 );

2. Guard against unresolved column max producing a false 100%:

# Do not call percentage() if max_value is 0 or undef — it will always return 100.00
next unless defined $max_value && $max_value > 0;
my $percent = percentage( $auto_increment, $max_value );

Reproducibility

This warning appeared across every database on a shared WordPress hosting environment — every WooCommerce table that had been provisioned but never had a payment token recorded triggered the false alarm. On a multi-site hosting server this generated hundreds of identical warnings, obscuring all genuine findings in the output.