#!/usr/bin/perl -w # Matija Nalis, GPLv3+, 20080116 # find mysql duplicate primary keys which case errors like # "/etc/mysql/debian-start[24344]: error : Table upgrade required. Please do "REPAIR TABLE `some_table`" to fix it!" # in mysql5 after upgrade to etch, on debian maintenance scripts # which do "CHECK TABLE". "REPAIR TABLE" however says "Table is already up to date" and won't fix it. # # see http://blog.tigertech.net/posts/mysql-table-upgrade-required/ # # it reads sql dump ("mysqldump -A" for example) from stdin use strict; my $last_db = '_none_'; my $last_table = '_none_'; while () { if (/^USE\s+`?([^` ]+)`?;$/) { $last_db = $1 } if (/^CREATE\s+TABLE\s+`?([^` ]+)`?\s*\($/) { $last_table = $1 } # print "d,t=$last_db, $last_table\n"; if (/^\s*PRIMARY\s+KEY\s+\(`?([^` ]+)`?\s*,\s*`?([^` ]+)`?\)\s*,?\s*$/) { # composite primary key ? # print "composite $1 -- $2 for table $last_table in database $last_db\n"; my $id1=$1; my $id2=$2; if ($id1 =~ /^${id2}$/i) { print "duplicate PK ($id1, $id2) in table $last_table in database $last_db\n"; } } }