After upgrade from SMT 1.1.6 or earlier to SMT 1.2.1 mysql returns database errors

SUSE Linux Enterprise Server 11 SP2

After upgrading from SMT 1.1.6 to SMT 1.2.1 the following errors similar to the following are returned when smt is starting:


DBD::mysql::db selectall_arrayref failed: Unknown column 'ID' in 'field list'
at /usr/sbin/smt-mirror line 316. DBD::mysql::db selectall_arrayref failed:
Unknown column 'ID' in 'field list'
at /usr/sbin/smt-mirror line 316.

DBD::mysql::db do failed: Table 'StagingGroups' already exists at
/usr/lib/perl5/vendor_perl/5.10.0/DBIx/Transaction/db.pm line 135.
Installing database failed!
[/usr/share/schemas/smt/mysql/0.16-1.00/100-add-table-staginggroups.sql#1]DBD::m
ysql::db do failed: Table 'StagingGroups' already exists at
/usr/lib/perl5/vendor_perl/5.10.0/DBIx/Transaction/db.pm line 135.

 at /usr/lib/perl5/vendor_perl/5.10.0/DBIx/Migration/Directories/Base.pm line
97
 at /usr/lib/perl5/vendor_perl/5.10.0/DBIx/Migration/Directories/Base.pm line
113

Errors similar to the following occur during smt mirroring:

https://<xxx.xxxxx.xxx/repo/$RCE/SLE10-SDK-SP4-Updates/sles-10-x86_64/
Target:    /srv/www/htdocs/repo/$RCE/SLE10-SDK-SP4-Updates/sles-10-x86_64
D
/srv/www/htdocs/repo/$RCE/SLE10-SDK-SP4-Updates/sles-10-x86_64/.repodata/repomd.
xml
DBD::mysql::db selectall_hashref failed: Unknown column 'checksum_type' in
'field list' at /usr/lib/perl5/vendor_perl/5.10.0/SMT/Mirror/RpmMd.pm line 655.
DBD::mysql::db selectall_hashref failed: Unknown column 'checksum_type' in
'field list' at /usr/lib/perl5/vendor_perl/5.10.0/SMT/Mirror/RpmMd.pm line 655.

When migrating from earlier versions of SMT a necessary index is not created causing mysql to fail.  To resolve the issue do the following:

1. First ensure that the table StagingGroups exists:

$> mysql -u smt -h localhost -p smt
Password: <enter the smt mysql password>
mysql> show create table StagingGroups;

Which should return something similar to:

+---------------+----------------------------------------------------------+
| Table         | Create Table                                                                                                                                                                                                                                                                                                                                                                               |
+---------------+----------------------------------------------------------+
| StagingGroups | CREATE TABLE `StagingGroups` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `NAME` varchar(255) NOT NULL,
  `TESTINGDIR` varchar(255) NOT NULL,
  `PRODUCTIONDIR` varchar(255) NOT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `NAME` (`NAME`),
  UNIQUE KEY `TESTINGDIR` (`TESTINGDIR`),
  UNIQUE KEY `PRODUCTIONDIR` (`PRODUCTIONDIR`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 |
+---------------+-----------------------------------------------------------+
 
2. cd /usr/share/schemas/smt/mysql/
3. Backup the original schema directory
        cp -r 0.16-1.00 ORIG-0.16-1.00
4. cd 0.16-1.00
5. Remove the already applied schemas
       rm 100-add-table-staginggroups.sql
       rm 101-insert-default-into-staginggroups.sql

6. Modify the failing schema file
       vi 102-alter-table-filters.sql

       remove the following lines:
    
          alter table Filters add column STAGINGGROUP_ID INT NOT NULL DEFAULT 1;
          alter table Filters drop key CATALOG_ID;

          The last line should stay

7. Restart smt
          rcsmt restart