linux n00b

Upgrading Bugzilla 2.20 to 3.2.4 with Errno 150 on namedqueries

Posted in Howto by downforce on July 15, 2009

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.

The ./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
See http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html
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:

use bugzilla;
alter table namedqueries add id mediumint(9) NOT NULL AUTO_INCREMENT UNIQUE FIRST;

Re-ran ./checksetup.pl and all was good 😀

Now for the next upgrade error!

Advertisements

8 Responses

Subscribe to comments with RSS.

  1. John Lees said, on July 30, 2009 at 08:07

    THANK YOU. You just saved me the same few hours.
    Worked like a charm.

  2. Olivier Berger said, on July 31, 2009 at 23:51

    Excellent advise. Same here, and this solved it.

    Many thanks.

  3. Dmitry said, on October 3, 2009 at 00:33

    Thank you, man!
    Solved the same error while upgrading 2.18 to 3.4.2
    Regards.

  4. Ming Zhang said, on October 16, 2009 at 09:48

    Thank you very much for your excellent advise! It saved lots of my time.

  5. David Kelly said, on November 16, 2009 at 23:54

    Appreciate it much.

  6. Chris Day said, on June 17, 2010 at 02:09

    Excellent, thanks for sharing your solution to the problem!

  7. Will Moore said, on April 27, 2011 at 08:34

    You saved me hours! Thanks.

  8. Chabo Jackson said, on May 31, 2012 at 19:15

    Thank you…!!!!! I had to delete some tables and colums though, but after all this worked like a charm! 🙂


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: