Closed Bug 629733 Opened 15 years ago Closed 15 years ago

Builds table does not match up with anything else in the database

Categories

(Socorro :: General, task)

Product:
Socorro
Component:
General
Platform:
x86
macOS
Type:
task
Priority:
Not set
Severity:
normal

Tracking

(Not tracked)

Status:
RESOLVED WONTFIX

People

(Reporter: jberkus, Assigned: laura)

Details

Currently the builds table does not contain a foriegn key to the productdims table (where we keep a heirarchical representation of products); instead, it contains a text reperesentation of product and version which doesn't exactly correspond to anything else in the database. In order to output everything which webdev wants for "first appearance of signature" we need to make productdims and builds correlate. Steps required: 1. Backfill builds table to link to productdims. This will be partly by script and partly by hand-correction 2. Update the code which populates builds to link to the productdims table
More ... 3. Fix the keys for the builds table
Assignee: nobody → josh
So looks like the only real missing data is that builds has several of what look like valid release numbers which were not included in productdims, e.g.: firefox | 3.6.15pre seamonkey | 2.0.13pre firefox | 3.6.15pre So I think the simplest way to deal with this immediately would be a trigger which populates the productdims_id column. There's the question of what we do on a missing product, though ... does it add the product, or throw an error?
We don't scrape builds for every single product. IIRC, there is no builds page for Camino. The other products should be supported. We specify the supported products in the builds config file, so we won't grab any additional products accidentally. It would be nice to automatically add new versions that we find on the builds FTP site. My only concern is that a few of the FTP URLs have changed over the past 4-6 months without warning, and it requires that we update the builds config file to deal with that issue. We would also have to continue using the admin interface in order to support Camino.
Note there's a bug to replace scraping the FTP server with a REST API, so that problem will go away sometime soonish. (https://bugzilla.mozilla.org/show_bug.cgi?id=625417)
Ryan, If we automatically add missing products, where do we get the branch and release information for productdims?
Laura, That doesn't sound like it's getting fixed in time for 1.7.7
No one seems to know when the ReST API will be available that we can use as a single resource for all products/versions/builds, but that would be the ideal solution. Branches are the Gecko versions. Currently the only valid versions are 1.9.0, 1.9.1, 1.9.1 and 2.0. 1.9.3 can also be found in the database, but my understanding was that we weren't using 1.9.3 on future products. Branches are currently added manually. We could add 2.0 by default and ask that the user updates it manually as needed. Releases are determined by the version numbering. * major - only contains numbers/decimals. e.g. 4.0, 4.1 * milestone - contains 'b' in the version. e.g. 4.0b7, 4.0b8 * development - contains 'pre' in the version, 4.0b7pre, 4.0b8pre
(In reply to comment #2) > seamonkey | 2.0.13pre Just reminded me that I need to fix bug 626573 :P
Ryan, How would we ask the user to fix the version number? Given that we're talking about a cron job, here. As for release, what about version numbers which contain something other than a b or a pre? Such as 3.6.3plugin1? Also, the current database has alphas ("a") as milestones. Actually, for release, the current database currently follows the rule: * ends in plan number == major * ends in number,string,number == milestone * ends in number,string or number,string,number,string == development *except* for 3.6.3plugin1 which is marked as "major".
Per discussion with Ryan, we're going to break this into 1.7.7 and 1.7.8 portions. For 1.7.7 we will just go the quick-and-dirty route, where a trigger or other mechanism will automatically assign a link to the productdims table and give up if it can't find a match. This means that for the handful of cases where the build doesn't match any existing products, we'll ignore those for reporting purposes. This takes siginficant required code out of 1.7.7 and doesn't leave us any worse of than we are now. And the ommitted product-versions are only a handful; four currently, so this isn't a frequent occurance. For 1.7.8 (or later) we will want to code up some kind of auto-insert for new products which show up via the builds list. Hopefully we might have something better than FTP scraping to retrieve builds by then too.
Ryan, So, we have another issue with the Builds table. That's the "platform" column. Platform is part of the key for the builds table, but it relates to no other data anywhere else in the database. This makes it rather impossible to query things like "first build" in which a signature appears. This isn't actually the build table's fault; the main issue is that osname/osversion is not at all reliable in the reports table (i.e. full of garbage). Otherwise it would be relatively simple to create a mapping between the two. Ideas?
Hrm, right, those don't match up at all. So, we basically have different platform information in the reports, osdims and builds table. And I believe osdims is only used to process topcrasherbyurl reports. I think osdims might be a useful table, except the versioning is too finite. We obviously cannot match a build version specifically to Windows NT 6.1.7232 or Windows NT 6.1.7022. If we could focus the versions to major releases like 6.0, 6.1, etc., then we may be able to tie the builds data to the osdims data a little easier.
We should probably file a bug on doing some OS version normalization on incoming reports. It'd be easy enough to handle the simple cases (Windows NT 5.2 -> WinXP, 6.0 -> Vista, 6.1 -> Win7, OS X, Linux) and throw anything you don't recognize in an "other" bucket.
Yeah, I don't think that an OS/version total cleanup is in the cards for 1.7.7 though. In the meantime, I can create a mapping column in osdims, which will be auto-uptaded by a trigger; this should allow me to map reports to builds, which is what I'm looking for here. However, that's a temporary measure; since we have no way to predict how the build folks will change the formatting of their "platform" information in the future, the mapping will be fragile.
Actually, digging into this further, the case is worse than I thought. It turns out that we're not processing ... and possible not collecting ... unique information about builds at all. What's in reports is the buildid. But ... that buildid is not unique; it's only unique when qualified with product, version, os *and plaform (32/64 bit)*. We're not including that last bit of information in reports at all. Currently we only release both 32 and 64 bit versions for Linux and Mac. However, in the future Mozilla is liable to release a Win64 version. Anyway, what it comes down to is that I can't use anything from the builds tables for the "first appearance" reports. So I'm going to need to do something else. I think we also need to look at how we're using informaiton from the "builds" table currently in Socorro, because the results we're getting are probably wrong.
Summary: Builds table needs to link to products table → Builds table does not match up with anything else in the database
Target Milestone: 1.7.7 → 1.7.8
Making this issue versionless and assingning to Laura. This bug has become an issue more about interdeparmental data cleanup and a lot less of a database issue. Also, we don't need to resolve it for 1.7.8 features, I've worked around it.
Assignee: josh → laura
Target Milestone: 1.7.8 → ---
No longer blocks: 629054
This issue has now become irrelevant with the new release structure.
Status: NEW → RESOLVED
Closed: 15 years ago
Resolution: --- → WONTFIX
Component: Socorro → General
Product: Webtools → Socorro
You need to log in before you can comment on or make changes to this bug.