Add AUTO_INCREMENT to MyISAM without Recreating the Table

Here's a Filthy hack.

Using ALTER table to add AUTO_INCREMENT to a column in MySQL takes significantly longer than you might think.  It's because behind the scenes it has to recreate the whole table.  If you've been using sequence tables and now you'd like to do away with them, this may take much longer than you'd think.  I recently ran into this problem.  I had around 450 databases with tens of thousands of tables in each that I needed to convert.  I found this article on mysql performance blog from a while back.  This only works for MyISAM.  It suggests that at least at that time one could simply create a new table defined the way you wanted it ( in their example case removing AUTO_INCREMENT) and switch out the .frm files from your new table to the one you want to change.  They go on to specifically say it doesn't work to add AUTO_INCREMENT.  While that might have been the case at the time, it's not true now.  You can add AUTO_INCREMENT to a column this way, with the addition of switching out the .MYI file as well.  Here's the process:

Do a 'SHOW CREATE TABLE `tablename`' on the table you'd like to add AUTO_INCREMENT.  Take the output and create a new table.  Alter the new empty table adding AUTO_INCREMENT with  MAX+1 from the PRIMARY KEY of the original table as the AUTO_INCREMENT value.  Make sure your column type matches the existing type of the table you're converting.  (I.E. int(10) unsigned)

ALTER TABLE `convert_table` MODIFY COLUMN `id` INT(10) UNSIGNED AUTO_INCREMENT, AUTO_INCREMENT = 1000;

Now switch out the .frm and .MYI files in /var/lib/mysql/databasename/ from the new table to the old one.  Then it's likely a good idea to repair the table.  Flushing and Locking the tables is also likely a good idea.

Here's the Perl code I wrote to do it to all of the tables I needed to do this for.  Backup first and use at your own extreme risk, it really is a filthy hack.

#!/usr/bin/perl
use strict;

use warnings;
use DBI;
use Carp;
use File::Copy;
use Getopt::Long;

my $dbuser = q{root}; # Database user
my $dbpass = q{}; # Database password
my $db;
my $safe = undef; # Lock and Flush Tables
my $remove = undef; # Drop the sequence tables
my $type = undef; # Don't convert the table if the Primary key isn't an int(10) unsigned
my $ext = '_seq'; # Pattern appened to your sequence tables
 
GetOptions(
'database|d=s' => \$db,
'safe|s' => \$safe,
'remove|r' => \$remove,
'type|t' => \$type,
'extension|x=s' => \$ext,
);
unless ( $db ) {
croak qq{ You must provide a database (-d/--database)\n };
}
my $dbh = DBI->connect(
qq{dbi:mysql:host=localhost;database=$db},
$dbuser,
$dbpass,
{ RaiseError => 1 }
) or croak qq{Can't connect to the db, I haven't done anything yet, no cleanup needed.\n}.DBI->errstr;

# Get a list of all the sequence tables
my $sthm = $dbh->prepare(q{
SELECT `TABLE_NAME`
FROM `INFORMATION_SCHEMA`.`TABLE_CONSTRAINTS`
WHERE `TABLE_SCHEMA` = ?
AND `TABLE_NAME` LIKE ?
});
$sthm->execute($db,qq{\%_$ext});

print "Commencing filthy hack\n";

$| = 1; # So we get output during the while
MAIN:
while ( my $row = $sthm->fetchrow_hashref ) {
# Dump the $ext to get the table name
$row->{'TABLE_NAME'} =~ s/$ext$//;
my $table = $row->{'TABLE_NAME'};
# Make sure the table exists
my $sth = $dbh->prepare(q{
SELECT `TABLE_NAME`
FROM `INFORMATION_SCHEMA`.`TABLES`
WHERE `TABLE_SCHEMA` = ?
AND `TABLE_NAME` LIKE ?
});
$sth->execute($db,$table);
unless( $sth->fetchall_arrayref->[0]->[0]) {
print qq{\nTable $table missing, mabye you should drop $table$ext\n};
next MAIN;
}
# Get the table definition
$sth = $dbh->prepare(qq{SHOW CREATE TABLE $table});
$sth->execute;
my ($primary,$create_statement,$type);
while ( my $create = $sth->fetchrow_arrayref ) {
if ( $create->[1] =~ /AUTO_INCREMENT/ ) {
print "\nTable $table already has auto_increment, you might want to check this table out.\n";
next MAIN;
}

my $desc = $dbh->selectall_hashref(qq{DESC $table}, q{Key});
unless ( $desc->{'PRI'} ) {

print qq{\nWTF? No primary col found for table $table, this is going to need manual intervention.\n};
next MAIN;
}
# If type option set, don't convert if the type for the primary col isn't an int(10) unsigned
if ( $type ) {
unless ( $desc->{'PRI'}->{'Type'} eq 'int(10) unsigned' ) {
print qq{\nWTF? Your primary key isn't an 'int(10) unsigned' on table $table, it's a }. $desc->{'PRI'}->{'Type'} .qq{, I'm not touching it.\n};
next MAIN;
} 
}
$type = $desc->{'PRI'}->{'Type'};
# change the table name in the create statement to convert_table
$create->[1] =~ s/CREATE TABLE `[^`]+`(.+)/CREATE TABLE `convert_table`$1/;
# find the primary key
$primary = $desc->{'PRI'}->{'Field'};
$create_statement = $create->[1];
}

# Get the max val from the table to use for AUTO_INCREMENT
$sth = $dbh->prepare(qq{SELECT MAX(`$primary`) FROM `$table`});
$sth->execute;
my ($id) = @{ $sth->fetchall_arrayref} ;
$id = $id->[0] ? $id->[0]+1 : 1;

# Make sure we have an int cause the orginal DB might really be honked up
unless ( int($id) ) {
print "$id for table $table is jacked up. Manual intervention needed.\n";
next MAIN;
}

# Create the convert table
$sth = $dbh->prepare($create_statement);
$sth->execute or croak qq{Can't create the convert_table, you might need to do some cleanup\n}.DBI->errstr;

# For safty sake
if ( $safe ) {
$dbh->do(qq{FLUSH TABLES `$table`, `$table$ext`});
$dbh->do(qq{LOCK TABLES `$table` WRITE, `$table$ext` WRITE, `convert_table` WRITE});
}
# Alter the empty convert_table
my $alterquery = qq{
ALTER TABLE `convert_table`
MODIFY COLUMN `$primary` $type AUTO_INCREMENT,
AUTO_INCREMENT = $id
};
$sth = $dbh->prepare($alterquery);
$sth->execute or croak qq{WTF? Can't alter the convert table, cleanup needed for sure, you've got locked tables\n}.DBI->errstr;

# Switch out the .frm and .MYI files from the convert table the table we're fixing
copy(qq{/var/lib/mysql/$db/convert_table.frm},qq{/var/lib/mysql/$db/$table.frm}) or croak "Very bad, cleanup needed, you have locked tables. Copy failed: $!";
copy(qq{/var/lib/mysql/$db/convert_table.MYI},qq{/var/lib/mysql/$db/$table.MYI}) or croak "Very bad, cleanup needed, you have locked tables. Copy failed: $!";

# Just in case, Cause this is a flithy hack
$dbh->do(qq{REPAIR TABLE `$table`}) if $safe;

# Shouldn't need to do with but it's a good non-invasive test of the table
$alterquery = qq{ALTER TABLE `$table` AUTO_INCREMENT = $id };
my $sth7 = $dbh->prepare($alterquery);
$sth7->execute or croak qq{Hope you backed up, filthy hack failed. Cleanup needed for sure, you have locked tables and possible corruption, try repair with USE_FRM\n}.DBI->errstr;

# Clean up
$dbh->do(qq{DROP TABLE `convert_table`}) or croak qq{Clean up needed, you have locked tables\n}.DBI->errstr;
if ( $remove ) {
$dbh->do(qq{DROP TABLE `$table$ext`}) or croak qq{Clean up needed, you have locked tables\n}.DBI->errstr;
}
if ( $safe ) {
$dbh->do(q{UNLOCK TABLES}) or croak qq{Clean up needed, you have locked tables\n}.DBI->errstr;
}
print ".";
}

print "\nFilthy hack complete! Hope I didn't just corrupt everything\n"