Upgrading Bugzilla 2.20 to 3.2.4 with Errno 150 on namedqueries
I’ve spent most of this morning trying to figure out how to upgrade our antiquated Bugzilla 2.20 installation to the latest and greatest 3.2.4.
./checksetup.pl kept failing with the error message :
Converting table namedqueries… DBD::mysql::db do failed: Error on rename of ‘./bugzilla/#sql-3967_1c’ to ‘./bugzilla/namedqueries’ (errno: 150) [for Statement “ALTER TABLE namedqueries TYPE = InnoDB”] at Bugzilla/DB/Mysql.pm line 337
Bugzilla::DB::Mysql::bz_setup_database(‘Bugzilla::DB::Mysql=HASH(0x9c766d0)’) called at ./checksetup.pl line 144
Running the SQL command
SHOW ENGINE INNODB STATUS; within mysql gave me the following bit of useful info:
LATEST FOREIGN KEY ERROR
090715 8:58:13 Error in foreign key constraint of table bugzilla/namedqueries_link_in_footer:
there is no index in referenced table which would contain
the columns as the first columns, or the data types in the
referenced table do not match to the ones in table. Constraint:
CONSTRAINT fk_namedqueries_link_in_footer_namedquery_id_namedqueries_id FOREIGN KEY (namedquery_id) REFERENCES namedqueries (id) ON DELETE CASCADE ON UPDATE CASCADE
The index in the foreign key in table is namedqueries_link_in_footer_id_idx
for correct foreign key definition.
So after a couple hours of research I’ve discovered the problem is that the table namedqueries doesn’t have the column id.
So in mysql:
alter table namedqueries add id mediumint(9) NOT NULL AUTO_INCREMENT UNIQUE FIRST;
./checksetup.pl and all was good 😀
Now for the next upgrade error!