642258 - open prod graphs db to graphs1.dmz.sjc1.mozilla.com and update schema

Status: RESOLVED FIXED

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

Description

[Robert Helmer [:rhelmer]]

Bug 627446 brought up a new server for graphs 2.0

In order to use this, we need:

1) allow it to connect to production graphserver DB (r/w)
2) make this backwards-compatible schema change:

CREATE TABLE IF NOT EXISTS valid_test_combinations_updated (
  -- This matches test_runs.date_run:
  last_updated INT NOT NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS valid_test_combinations (
  test_id INT NOT NULL,
  branch_id INT NOT NULL,
  os_id INT NOT NULL
) ENGINE=InnoDB;

I plan to not have the new box write to the database, except for the above two tables; we could use a separate user to ensure this if you'd like.

I don't think this needs to happen before Fx4; graphs.allizom.org has the latest code and a nightly prod DB import.

Comment 1

[Jake Maul [:jakem]]

I have updated the MySQL grants to allow this server to log in, using the existing credentials. This seems more straightforward to me, especially since this seems to be aimed at ultimately replacing the current system (being "version 2.0" instead of "graphs02" or similar).

However, there appears to be a networking issue making this server actually communicate with the db server... I'm going to work with netops on this... I'm hoping it's an easy ACL fix.

As for the schema change, I presume you want these new tables in the graphs_mozilla_org_new database, because that seems to be what the current code is pointing at. This is completed as well:

mysql> CREATE TABLE IF NOT EXISTS valid_test_combinations_updated (
    ->   -- This matches test_runs.date_run:
    ->   last_updated INT NOT NULL
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.04 sec)

mysql> 
mysql> CREATE TABLE IF NOT EXISTS valid_test_combinations (
    ->   test_id INT NOT NULL,
    ->   branch_id INT NOT NULL,
    ->   os_id INT NOT NULL
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)

Comment 2

[Robert Helmer [:rhelmer]]

(In reply to comment #1)
> I have updated the MySQL grants to allow this server to log in, using the
> existing credentials. This seems more straightforward to me, especially since
> this seems to be aimed at ultimately replacing the current system (being
> "version 2.0" instead of "graphs02" or similar).
> 
> However, there appears to be a networking issue making this server actually
> communicate with the db server... I'm going to work with netops on this... I'm
> hoping it's an easy ACL fix.
> 
> As for the schema change, I presume you want these new tables in the
> graphs_mozilla_org_new database, because that seems to be what the current code
> is pointing at. This is completed as well:
> 
> mysql> CREATE TABLE IF NOT EXISTS valid_test_combinations_updated (
>     ->   -- This matches test_runs.date_run:
>     ->   last_updated INT NOT NULL
>     -> ) ENGINE=InnoDB;
> Query OK, 0 rows affected (0.04 sec)
> 
> mysql> 
> mysql> CREATE TABLE IF NOT EXISTS valid_test_combinations (
>     ->   test_id INT NOT NULL,
>     ->   branch_id INT NOT NULL,
>     ->   os_id INT NOT NULL
>     -> ) ENGINE=InnoDB;
> Query OK, 0 rows affected (0.01 sec)

All sounds correct, thanks!

Comment 3

[Jake Maul [:jakem]]

The ACL is in place, and I can confirm this DB connection is working. Let us know if you need anything further.

Thanks!