358581 - Milestones for different products cannot have same value.

Status: RESOLVED INVALID

(Bugzilla :: Administration, task)

Description

[Sander Temme]

User-Agent:       Mozilla/5.0 (Macintosh; U; PPC Mac OS X; en) AppleWebKit/418.9 (KHTML, like Gecko) Safari/419.3
Build Identifier: 2.18.6

One of our users tried to make a milestone with value '2.2' in the product 'Slide'. He was thwarted by the following error message in his browser:

DBD::mysql::st execute failed: Duplicate entry '2.2' for key 1 [for Statement "INSERT INTO milestones ( value, product_id, sortkey ) VALUES ( '2.2', 31, 3)"] at Bugzilla/DB.pm line 65
	Bugzilla::DB::SendSQL('INSERT INTO milestones ( value, product_id, sortkey ) VALUES ...') called at /var/www/bugzilla/bugzilla/editmilestones.cgi line 324

I poked around in MySQL, and discovered that the product 'Commons' already had a milestone with value '2.2'. I also noticed the schema for the milestones table:

mysql> show fields from milestones;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| value      | varchar(20) |      | PRI |         |       |
| product    | varchar(64) |      | PRI |         |       |
| sortkey    | smallint(6) |      |     | 0       |       |
| product_id | smallint(6) |      | MUL | 0       |       |
+------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

The SQL statement above does not set a value for the product field, which is a Primary Key in conjunction with the value field. This effectively means that milestone values have to be unique across the entire Bugzilla installation. Indeed, when I searched for the existing milestone: 

mysql> select * from milestones where value = '2.2';
+-------+---------+---------+------------+
| value | product | sortkey | product_id |
+-------+---------+---------+------------+
| 2.2   |         |       0 |         23 |
+-------+---------+---------+------------+
1 row in set (0.00 sec)

the product name was not set. When I manually set the product field to 'Commons', I was able to create a test milestone with the same value in another product. This new test milestone showed up with an empty product field and I suspect that I will not be able to create another one with the same value. 

Would this be solved by also declaring product_id a Primary Key?

Reproducible: Always

Steps to Reproduce:
1. Edit a product. Make a milestone with value 'foo'.
2. Edit another product. Attempt to make a milestone with the same value.
3. Encounter error message

Actual Results:  
DBD::mysql::st execute failed: Duplicate entry '2.2' for key 1 [for Statement "INSERT INTO milestones ( value, product_id, sortkey ) VALUES ( '2.2', 31, 3)"] at Bugzilla/DB.pm line 65
	Bugzilla::DB::SendSQL('INSERT INTO milestones ( value, product_id, sortkey ) VALUES ...') called at /var/www/bugzilla/bugzilla/editmilestones.cgi line 324


Expected Results:  
A milestone with value foo should be created in the other product and be different from the milestone in the first product.

I won't call this minor since you have to go into MySQL to work around the issue. We first saw this in 2.18.5, but I have since upgraded to 2.18.6 and the faulty behaviour still exists.

Comment 1

[Max Kanat-Alexander]

Something is wrong with your database--at some point it was not properly upgraded.

The milestones table never had both a "product" and a "product_id" field.