515194 - checksetup.pl errors on foreign key creation when upgrading from 2.22.2 to 3.4.1

Status: RESOLVED DUPLICATE of bug 508181

(Bugzilla :: Installation & Upgrading, defect)

Description

[erinlduffy]

User-Agent:       Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.1.2) Gecko/20090729 Firefox/3.5.2 (.NET CLR 3.5.30729)
Build Identifier: 3.4.1

I recently upgraded our bugzilla installation from 2.22.2 to 3.4.1 in order to take advantage of some new features.  

Env:
  Windows Server
  Perl 5.10  
  MySQl 5.0


On running checksetup I got a whole series of error messages like these:

DBD::mysql::db do failed: Can't create table './bugs/#sql-cad_28a17.frm'
(errno: 150) [for Statement "ALTER TABLE keywords ADD
     CONSTRAINT fk_keywords_bug_id_bugs_bug_id FOREIGN KEY (bug_id)
     REFERENCES bugs(bug_id)
      ON UPDATE CASCADE ON DELETE CASCADE"] at Bugzilla/DB.pm line 515
        Bugzilla::DB::bz_add_fk('Bugzilla::DB::Mysql=HASH(0x9458244)',
'keywords', 'bug_id', 'HASH(0x9475a24)') called at Bugzilla/DB.pm line 450
       
Bugzilla::DB::bz_setup_foreign_keys('Bugzilla::DB::Mysql=HASH(0x9458244)')
called at Bugzilla/Install/DB.pm line 567
        Bugzilla::Install::DB::update_table_definitions('HASH(0x8402450)')
called at ./checksetup.pl line 191

 
I figured out what was going on.  The primary keys of all the tables were set at unsigned ints (of some int type), while the foreign key columns in the reference tables had not been updated to 'unsigned'.  E.G.  bug_id in bugs had been marked as 'unsigned', but keywords.bug_id was NOT marked as unsigned, so that the script fails to create the foreign key constraint because the types of the 2 columns are not the same.   I manually went into MySql and changed keywords.bug_id to 'unsigned', and then I was able to run the script and get past the error.  However, I probably had to do this about 75 times or so - essentially for every foreign key that the checksetup.pl is trying to create.  As I said, I was able to work around it, but the error was definitely not obvious to fix at first. 

Could the checksetup.pl make sure all the foreign key columns are set to 'unsigned'?  I'm assuming I had this issue mostly because I was upgrading from on older installation, but it could happen to others as well.




Reproducible: Always

Steps to Reproduce:
1. Upgrade from 2.22.2 using MySql
2. run checksetup.pl
3. 
Actual Results:  
See db foreign key constraint creation errors

Expected Results:  
The bugzilla database update/creation scripts should have altered the tables to ensure that the foreign key columns were to to 'unsigned' (as the primary key columns are).