Monday, February 02, 2009

Testopia 2.2 is Here... Really... sort of...

Testopia 2.2 is out.

However, there still seem to be some lingering issues with upgrading from earlier versions. Specifically when it attempts to add foreign keys, you may get an error that looks something like this:

Adding foreign key: test_case_activity.case_id -> test_cases.case_id...
DBD::mysql::db do failed: Can't create table './bugs_bugzilla/#sql-965_5bde2.frm' (errno: 150)
[for Statement "ALTER TABLE test_case_activity ADD

CONSTRAINT fk_test_case_activity_case_id_test_cases_case_id FOREIGN KEY (case_id)
REFERENCES test_cases(case_id)
ON UPDATE CASCADE ON DELETE CASCADE"] at Bugzilla/DB.pm line 502

If you see this, it means (due to my earlier stupidity), that you will need to alter the columns referenced to remove the UNSIGNED attribute. This is most easily done using a nice GUI tool such as Mysql Query Browser where you can simply right click on a table, choose edit, and uncheck the box labeled UNSIGNED then click apply and then execute. However, you can also do it from the regular command line client with a command like:

ALTER TABLE `>table<`
MODIFY COLUMN `>column<` [INTEGER|SMALLINT|TINYINT] NOT NULL [AUTO_INCREMENT];

Care should be taken to ensure that the column in question really is an INTEGER type (it may be SMALLINT for example) and if it is already an auto_increment column, you will need to add AUTO_INCREMENT at the end. It is best to do a describe table on both tables before hand to ensure the column types and which are key fields. Oh, and you did back up your database first right?

You may have to repeat this process on a handful of columns. I am working on a script that will (hopefully) clean all these up, but in the mean time, this is how to work these out manually if you are so inclined.

This is on Mysql. I am not sure how do it in Postgres or if this is even an issue there.

15 comments:

md said...

Hi Greg, after doing this work around, should running ./checksetup.pl still be returning the same errors?

I was previously getting this errror:
Adding foreign key: test_environment_map.property_id -> test_environment_property.property_id...

I used the following commands for the work around and still see the same error afterwards:
ALTER TABLE test_environment_map MODIFY COLUMN property_id INTEGER NOT NULL;
ALTER TABLE test_environment_property MODIFY COLUMN property_id INTEGER NOT NULL;

Anonymous said...

Hi Greg, we are all eagerly looking forward to a Testopia 2.2 release, so that we can upgrade Bugzilla as well. Personally I shall wait until the database table issues are resolved by the 2.2 update itself.

King of Hearts said...

I use Bugzilla 3.2 on Windows 2003 platform & apache server and have implemented testopia 2.2 on it. But i find lots of bugs in the testopia 2.2 which life a hell. I am not sure if there is any one else has tried it in the combination i mentioned.

Anonymous said...

Gautam - We've used Testopia 2.2 BETA on Bugzilla 3.2, Windows 2003 with IIS 6.0. The only major issues we've encountered is the Attachment thing, which is fixed in the 2.2 full release.

Not sure what kind of difficulties you're having.

Anonymous said...

If you had upgraded previously to Testopia 2.2 BETA you may run into a similiar error message. However, if you check and find that you're marked as UNSIGNED already, and everything else seems in order - try simply deleting the foreign key and having it re-added. That did the trick for me. no more errors, and a swell running Testopia.

md said...

Thanks Ryan, what you recommended stopped the errors when running ./checksetup.pl.

Anonymous said...

I used Bugzilla 3.2.1 + Testopia 2.2-Beta1 and everything was alright =)

Yesterday, I've installed Bugzilla 3.2.2. on another PC (Win 2003, Apache 2.2). Then I've tried to install Testopia 2.2, but a problem appeared. I can't apply a patch. Problem appears in editproducts.cgi on 370 line.

Greg Hendricks said...

Bugzilla 3.2.2 includes the patch already. In other words you, you can simply ignore the failure. I will release a new patch for 3.2.2 with the next release.

Unknown said...

Re: the failed patch with Bugzilla 3.22, I experienced the same line 370 error, compared what was in the .rej patch file with the code at that location in editproducts.cgi ... and finding it was different, manually replaced the existing lines of code with the lines from the patch. From Greg's comment above, sounds like I shouldn't have done this, but if the patch was already applied why the difference in code?

So far Testopia 2.2 appears to be working properly.

Anonymous said...

Hi!
Just wanted to say "Great Job". Upgraded from bgz 2.22.1 and testopia 1.2 - to 3.2 / 2.2 - not to hard and works fine!

/Mikael, Sweden

qiang cao said...

hello, I installed the testopia 2.2on bugzilla 3.0.6 , it looks well, but when I create the new plan ,I found there is no items in "Plan Type" option. I logging in the mysql, I found the "test_plan_type" table is null.
so I'd like to know how I can do , and I would like to ask you if the "test_plan_type" table's data created during setup or it created manual affter setup ?
Can you mail to me ?
mail address: abc0012544@gmail.com
thank you very much !!!

Greg Hendricks said...

the test_plan_types table should have been created when you ran checksetup after the patch step.

Try running checksetup

qiang cao said...

hello , Can you help me ?
we have a bugzilla ver 3.2 runing, there has some data in it, the bugs database has 1.2GB, I'd like to setup a testopia on it, but unluckly, there are some error.


"Adding new table test_case_attachments ...
DBD::mysql::db do failed: Table 'test_case_attachments' already exists [for Stat
ement "CREATE TABLE test_case_attachments (
attachment_id integer NOT NULL,
case_id integer NOT NULL,
case_run_id integer
) ENGINE = InnoDB "] at Bugzilla/DB.pm line 647
Bugzilla::DB::_bz_add_table_raw('Bugzilla::DB::Mysql=HASH(0x2654698)', '
test_case_attachments') called at Bugzilla/DB.pm line 613
Bugzilla::DB::bz_add_table('Bugzilla::DB::Mysql=HASH(0x2654698)', 'test_
case_attachments') called at Bugzilla/DB.pm line 408
Bugzilla::DB::bz_setup_database('Bugzilla::DB::Mysql=HASH(0x2654698)') c
alled at Bugzilla/DB/Mysql.pm line 518
Bugzilla::DB::Mysql::bz_setup_database('Bugzilla::DB::Mysql=HASH(0x26546
98)') called at C:\bugzilla\checksetup.pl line 144"

so I can not to finish the setup, can you help ??
thank you very much !!!

qiang cao said...

I can setup the testopia on a new bugzilla of ver 3.2,but if I import old data to the bugzilla 3.2,the testopia setup will be error. but affter I setuped the bugzilla and testopia,it can import data to it .

Anonymous said...

I just upgraded to Bugzilla 3.2.3 and Testopia 2.2 (over 3.0.5).

The testopia patch gave a merge error in editproducts.cgi around line 370, but since it is only a delete confirmation check, it can be ignored. Googling found a reply from Greg somewhere stating it can be ignored.

However, checksetup.pl then gaves me the infamous update_table_defintions "can't create table" error.

As per Greg's instructions, I used MySQl Query Browser to edit the tables below and unchecked the SIGNED setting on the one collumn that had it. To save yourself having to run checksetup repeatedly, the tables are as follows:

test_Case_Activity
test_fielddefs
test_case_runs
test_plans
test_runs
test_builds
test_environments
test_tags
test_case_categories
test_environment_category
test_environment_element

It looks you can also work around this by commenting out the contents of bz_setup_foreign_keys method contents in DB.pm. Though that is likely not ideal.

I then got an error about invalid values in test_plan_permission.plan_id. I used MySQL Query Browser to delete the offending rows.

And now checksetup.pl runs fine. phew. finally.