Hacktoberfest 2026: the issues maintainers tagged for October, open and beginner-friendly. Browse Hacktoberfest issues

[BUG] False positive for BIGINT UNSIGNED AUTO_INCREMENT primary keys

Open Beginner friendly
  • Is something wrong?
#1,007 0 comments 0 reactions 0 assignees View on GitHub

Maintainers usually reply within 1 day

Nobody has claimed this yet.

Assessment

Difficulty
2/5
Estimated time
1-3 hours
Newbie friendliness
78/100
Issue type
Bug
Clarity
Clearly specified
Activity status
Active
Tech stack
mysql, perl, sql
Domain
database

Research direction

The bug lives in the advanced PK checks of mysqltuner.pl, around the Surrogate Key Recommendation block near line 10666, where the check tests $columntype for auto_increment. Read the select_array query just above it, which pulls COLUMN_TYPE but not EXTRA, then reproduce with the CREATE TABLE in the issue against MySQL 8 to confirm EXTRA holds auto_increment. Done when the auto_increment test reads the EXTRA value and the false-positive message no longer appears for a BIGINT UNSIGNED AUTO_INCREMENT primary key.

Written by the indexing model from the issue text.

Description

bug

Describe the bug
When checking primary keys in the "Surrogate Key Recommendation" section, MySQLTuner reports a false positive for tables using a recommended surrogate key:

BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY

The current implementation checks for auto_increment within COLUMN_TYPE, but in MySQL 8.x the AUTO_INCREMENT attribute is stored in the EXTRA column of information_schema.columns.

As a result, valid surrogate keys are incorrectly reported with:

Primary key 'id' is not a recommended surrogate key (BIGINT UNSIGNED AUTO_INCREMENT)

Execution Command
Please provide the exact command used to run MySQLTuner:

perl mysqltuner.pl --host xxx --user xxx --password xxx \
--dbstat --colstat --tbstat --sysstat --structstat --pfstat --idxstat --myisamstat \
--experimental --buffers \
--reportfile=mysqltuner.html

Environment info:

  • MySQLTuner version: 2.9.2
  • Database Server: MySQL
  • Database Version: 8.0.36
  • Operating System & Version: Ubuntu 26.04
  • Perl version: v5.40.1

MySQLTuner Debug Output
Not included because the issue was identified through source code review and verification against information_schema.columns.

To Reproduce
Steps to reproduce the behavior if not fully captured by the debug output:

  1. Create Table: 
    CREATE TABLE tbl (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  PRIMARY KEY(id)
);
  2. Verify metadata: 
    SELECT
  COLUMN_NAME,
  DATA_TYPE,
  COLUMN_TYPE,
  EXTRA
FROM information_schema.columns
WHERE table_name = 'tbl'
  AND column_name = 'id';
  3. Result: 
    COLUMN_NAME = id
DATA_TYPE   = bigint
COLUMN_TYPE = bigint unsigned
EXTRA       = auto_increment
  4. Run MySQLTuner with --reportfile option.
  5. Open the HTML report.
  6. Navigate to: Modeling > SQL Schema & Modeling Recommendations

Expected behavior
The following message should not be reported:

Primary key 'id' is not a recommended surrogate key (BIGINT UNSIGNED AUTO_INCREMENT)

Additional context
The current implementation validates AUTO_INCREMENT using COLUMN_TYPE:

$columntype !~ /auto_increment/i

However, AUTO_INCREMENT is exposed through the EXTRA column rather than COLUMN_TYPE in information_schema.columns.

The following patch resolves the false positive:

diff --git a/mysqltuner.pl b/mysqltuner.pl
index cf23ceb..f4409d0 100755
--- a/mysqltuner.pl
+++ b/mysqltuner.pl
@@ -10628,7 +10628,7 @@ having sum(if(c.column_key in ('PRI', 'UNI'), 1, 0)) = 0"

     # Advanced PK checks
     my @pkInfo = select_array(
-"SELECT c.TABLE_SCHEMA, c.TABLE_NAME, c.COLUMN_NAME, c.DATA_TYPE, c.COLUMN_TYPE
+"SELECT c.TABLE_SCHEMA, c.TABLE_NAME, c.COLUMN_NAME, c.DATA_TYPE, c.COLUMN_TYPE, c.EXTRA
 FROM information_schema.columns c
 JOIN information_schema.tables t USING (TABLE_SCHEMA, TABLE_NAME)
 WHERE t.TABLE_TYPE = 'BASE TABLE'
@@ -10642,13 +10642,14 @@ WHERE t.TABLE_TYPE = 'BASE TABLE'
       ("Schema,Table,Column,DataType,ColumnType,IssueType,Description");

     foreach my $pk (@pkInfo) {
-        my ( $schema, $table, $column, $datatype, $columntype ) = split /\t/,
+        my ( $schema, $table, $column, $datatype, $columntype, $extra ) = split /\t/,
           $pk;
         $schema     //= '';
         $table      //= '';
         $column     //= '';
         $datatype   //= '';
         $columntype //= '';
+        $extra      //= '';

         # PK Naming Convention
         if ( $column ne 'id' && $column ne "${table}_id" ) {
@@ -10665,7 +10666,7 @@ WHERE t.TABLE_TYPE = 'BASE TABLE'
         # Surrogate Key Recommendation
         if (   $datatype !~ /int/i
             || $columntype !~ /unsigned/i
-            || $columntype !~ /auto_increment/i )
+            || $extra !~ /auto_increment/i )
         {
             # Check if it might be a UUID
             if ( $column =~ /uuid/i ) {
Dominant language
Perl
Stars
9.5k
Forks
1.3k
Avg merge
10h 18m
Merged PRs (30d)
10

Getting set up

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

More from major/MySQLTuner-perl

All issues in major/MySQLTuner-perl

Similar issues

More Perl issues

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.