Closed Bug 475919 Opened 15 years ago Closed 15 years ago

Give Daniel's scripts stats access to the AMO db

Categories

(mozilla.org Graveyard :: Server Operations, task)

Product:
mozilla.org Graveyard
Component:
Server Operations
Platform:
All
Other
Type:
task
Priority:
Not set
Severity:
major

Tracking

(Not tracked)

Status:
RESOLVED FIXED

People

(Reporter: clouserw, Assigned: justdave)

References

Details

Daniel's scripts are going to need write access to the AMO database.  Let us know what other information you need (host maybe?).
cm-metricsetl01 already has r/o access to AMO db, and cm-metricsetl02 will have the same thing, too, as soon as bug 475905 is fixed... is this r/w request for something different?
I can't see that bug but this one is for the plan detailed here: http://micropipes.com/blog/2009/01/22/add-on-statistics-status/
So yeah, this is a new r/w request for something different.  We are going to be pushing some stats data back out to AMO for use in their website.

The easiest way we thought of to perform this push is for my transformation to query the metrics DB and then directly insert the results somewhere in AMO.
The "results" in question are new daily summary records destined for the download_counts and update_counts tables.

I'm fine with either having the existing metrics account be granted additional privs for those two tables, or to have a completely separate account for this purpose.

If there is push-back to having my ETL update the DB directly then we'll need to map out some alternate strategy as soon as possible.
Do you have specific tables you need to insert into?  I'd like to be as restrictive as possible and it isn't clear you need insert access to the whole database.

Copying justdave for mysql acls.
You should already have the list - the current AMO stats script is restricted per table.  Can you paste that list here so we can verify?
(In reply to comment #3)
> I'm fine with either having the existing metrics account be granted additional
> privs for those two tables, or to have a completely separate account for this
> purpose.

Will have to be a separate account, separate mysql server, etc., as you're currently using a slave for the r/o stuff, and you'll need to use the master for the r/w stuff.
(In reply to comment #4)
> Do you have specific tables you need to insert into?  I'd like to be as
> restrictive as possible and it isn't clear you need insert access to the whole
> database.
> 
> Copying justdave for mysql acls.

Right now the permissions are:
GRANT SELECT ON `addons_remora`.`versions` 
GRANT SELECT, INSERT, UPDATE, DELETE ON `addons_remora`.`update_counts` 
GRANT SELECT, INSERT, UPDATE, DELETE ON `addons_remora`.`download_counts` 
GRANT SELECT, INSERT, UPDATE, DELETE ON `addons_remora`.`collections` 
GRANT SELECT, UPDATE ON `addons_remora`.`addons` 
GRANT SELECT, INSERT, UPDATE, DELETE ON `addons_remora`.`addons_collections` 
GRANT SELECT ON `addons_remora`.`files` 
GRANT SELECT ON `addons_remora`.`translations` 
GRANT SELECT, INSERT, UPDATE ON `addons_remora`.`logs_parsed`
So I am currently working on updating two tables, update_counts and download_counts.  For future compatibility though, here is what I'd suggest:

GRANT SELECT, INSERT, UPDATE, DELETE ON `addons_remora`.`update_counts` 
GRANT SELECT, INSERT, UPDATE, DELETE ON `addons_remora`.`download_counts` 
GRANT SELECT, INSERT, UPDATE, DELETE ON `addons_remora`.`addons_collections` 
GRANT SELECT, INSERT, UPDATE ON `addons_remora`.`logs_parsed`

Wil, we didn't discuss the possibility of my needing to update any other tables.  Do you know if that might be necessary? Or is it likely that once you have my data in update_counts and download_counts, that AMO can take over anything else that is needed?
addons_collections will still need to be updated with stats from your script.  logs_parsed is how our script tracked what it had already looked at.  If you don't need it we can delete that table - it's not used in the app anywhere.
At the moment, I don't have a parser tracking collections pings.  We'll have to bring that online after we get the updates and downloads process completed.  I have our notes from the meeting where we discussed those requirements.

As far as the logs_parsed, If you want to drop it that is fine, I was just throwing it in there in case there was some way in the future I could push audit information to it so you guys would have a better understanding of what the integration has processed.
That sounds fine.  Unless Daniel needs to do some extra selects or something I think we are good with:

GRANT SELECT, INSERT, UPDATE, DELETE ON `addons_remora`.`update_counts` 
GRANT SELECT, INSERT, UPDATE, DELETE ON `addons_remora`.`download_counts` 
GRANT SELECT, INSERT, UPDATE, DELETE ON `addons_remora`.`addons_collections`

We'll write separate scripts to handle updating total downloads.
Severity: normal → major
Assignee: server-ops → justdave
Severity: major → blocker
>mysql> show grants for metrics@10.2.72.28;
>+-----------------------------------------------------------------------------------------------------------------+
>| Grants for metrics@10.2.72.28                                                                                   |
>+-----------------------------------------------------------------------------------------------------------------+
>| GRANT USAGE ON *.* TO 'metrics'@'10.2.72.28' IDENTIFIED BY PASSWORD 'xxxxxxxx'                                  | 
>| GRANT SELECT, CREATE TEMPORARY TABLES, LOCK TABLES ON `addons_remora`.* TO 'metrics'@'10.2.72.28'               | 
>| GRANT SELECT, INSERT, UPDATE, DELETE ON `addons_remora`.`addons_collections` TO 'metrics'@'10.2.72.28'          | 
>| GRANT SELECT, INSERT, UPDATE, DELETE ON `addons_remora`.`download_counts` TO 'metrics'@'10.2.72.28'             | 
>| GRANT SELECT, INSERT, UPDATE, DELETE ON `addons_remora`.`update_counts` TO 'metrics'@'10.2.72.28'               | 
>+-----------------------------------------------------------------------------------------------------------------+
>5 rows in set (0.00 sec)
>
>mysql> show grants for metrics@10.2.72.34;
>+-----------------------------------------------------------------------------------------------------------------+
>| Grants for metrics@10.2.72.34                                                                                   |
>+-----------------------------------------------------------------------------------------------------------------+
>| GRANT USAGE ON *.* TO 'metrics'@'10.2.72.34' IDENTIFIED BY PASSWORD 'xxxxxxxxx'                                 | 
>| GRANT SELECT, CREATE TEMPORARY TABLES, LOCK TABLES ON `addons_remora`.* TO 'metrics'@'10.2.72.34'               | 
>| GRANT SELECT, INSERT, UPDATE, DELETE ON `addons_remora`.`addons_collections` TO 'metrics'@'10.2.72.34'          | 
>| GRANT SELECT, INSERT, UPDATE, DELETE ON `addons_remora`.`update_counts` TO 'metrics'@'10.2.72.34'               | 
>| GRANT SELECT, INSERT, UPDATE, DELETE ON `addons_remora`.`download_counts` TO 'metrics'@'10.2.72.34'             | 
>+-----------------------------------------------------------------------------------------------------------------+
>5 rows in set (0.00 sec)

Your target database is tm-amo01-master01.  MySQL ACLs are in place, I'll add this to bug 475905 to get the network ports opened, since that hasn't been done yet.
Severity: blocker → major
Status: NEW → RESOLVED
Closed: 15 years ago
Resolution: --- → FIXED
More permissions added in Bug 477920.
Depends on: 477920
Product: mozilla.org → mozilla.org Graveyard
You need to log in before you can comment on or make changes to this bug.