Setting all MySQL autoincrement values to next value
I recently had to migrate multiple MySQL databases using AWS DMS which only does the bare minimum of schema support. (Tip create the schema manually and then import the data don't let AWS do it for you).
One problem with MySQL is that the autoincrement value of columns would get out of sync e.g. if DMS inserts a row it will set the id value directly but the autoincrement value doesn't increase.
To fix this I wrote a perl script, it's dirty but works:
#!/usr/bin/perl
use strict;
my @ref = qx(mysql -N -e "SELECT TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME FROM COLUMNS WHERE EXTRA = 'auto_increment' AND TABLE_SCHEMA != 'mysql'" information_schema);
foreach my $line (@ref) {
$line =~ s/\n//;
my @fields = split(/\t/,$line);
my $schema = $fields[0];
my $table = $fields[1];
my $col = $fields[2];
my $max = qx(mysql -N -e "SELECT MAX($col) FROM $schema.$table");
$max =~ s/\n//;
if ($max != "NULL") {
my $sql = "ALTER TABLE $schema.$table AUTO_INCREMENT=$max";
print "$sql\n";
print qx(mysql -e "$sql") or die ('Failed');
}
}