Closed Bug 755610 Opened 12 years ago Closed 12 years ago

Metrics team needs access to Socorro db

Categories

(Data & BI Services Team :: DB: MySQL, task)

Product:
Data & BI Services Team
Component:
DB: MySQL
Platform:
x86
macOS
Type:
task
Priority:
Not set
Severity:
normal

Tracking

(Not tracked)

Status:
RESOLVED FIXED

People

(Reporter: pclemente, Assigned: mpressman)

References

Details

Need access from app1.metrics.scl3.mozilla.com
(app1 used to be cm-metricsapp01.mozilla.org)

- Socorro Stage DB

nc -zv socorro1.stage.db.phx1.mozilla.com 6432
nc: connect to socorro1.stage.db.phx1.mozilla.com port 6432 (tcp) failed: Connection timed out

- Socorro Prod DB

nc -zv tp-socorro01-ro-zeus.phx1.mozilla.com 6432
nc: connect to tp-socorro01-ro-zeus.phx1.mozilla.com port 6432 (tcp) failed: Connection timed out


Related bugs: 738394, 687906
Assignee: server-ops → mpressman
Component: Server Operations → Server Operations: Database
QA Contact: phong → cshields
Hi.

Any ETA for this?

Thanks.
Blocks: 738394
pg on socorro1.stage.db.phx1.mozilla.com is on port 5432, not 6432 and it should be open for 5432
Also note - the stage VIP should be socorro-stage-zlb.db.phx1.mozilla.com
Matt,

Um, actually, they should be connecting to pgbouncer, not directly to PostgreSQL ... as they do in prod.  That's why the 6432.
Josh, the db VIPs are open on port 5432, though.
Ah! I see. There is a VIP for stage and prod db's on both 5432 and 6432 - my bad:

So stage is:
socorro-stage-zlb.db.phx1.mozilla.com

and prod is:
tp-socorro01-ro-zeus.phx1.mozilla.com (as paula mentioned)

I'll open a bug to get the netflow opened, to the right port.
Depends on: 758289
dependent bug 758289 files.
Now that the netflow is open, the breakpad_metrics user exists. Is there anything else preventing you from accessing socorro?
I can connect to Production DB through pgAdmin3, but not to Stage DB:
  "Error connection to the server: ERROR: no working server connection"

Our username is "analyst".
Paula,

Stage is currently down for unrelated reasons.  We'll let you know when it's back up.
Paula,
Stage is back up and there is a user named analyst in the system. Can you try it again and let me know?

Thanks!
Hum...
Unfortunately, I still got the same error message :(

(Also, it takes some time to connect to ProductionDB. Is this normal? Is there any config that could decrease the loadtime?)

Thanks!
Paula,

Try the same password with StageDB that you're using with production.

I don't know about the connection time.  Network issues, maybe?
> Try the same password with StageDB that you're using with production.

The same error :(
Paula,

Try restarting pgAdmin.  It can get stuck if there are connection issues.

If it's still not connecting ... do you have access to the "psql" command-line client?  Its errors are much more informative.
I've manually added the ip for app1.metrics.scl3.mozilla.com and the vip in pg_hba.conf. You should be able to connect, I apologize for this taking so long. Please let me know if you still cannot connect. In regards to the time it takes to connect to production, how long is it taking? postgres as opposed to mysql does indeed take longer to establish a connection, but there certainly could be other issues. Let me know and we'll see about getting it sped up!
Sorry, but probably I'm doing something wrong :s


I tried to connect through psql command-line, using this:

'/Applications/pgAdmin3.app/Contents/SharedSupport/psql' --host 'localhost' --port 6432 --username 'analyst' 'breakpad'

(I'm using a tunnel so I can work locally)

When I open the ProdDB tunnel, this command-line works.
When I open the StageDB tunnel, I got exactly the same error as through pgAdmin UI.


Should I test it in a different way?
Paula,

Nope, I can work on debugging the issue now.  I'll let you know when it's fixed.
Hi,

I'm getting this error when trying to connect to socorro prod BD (not through pgAdmin, but when I try to run some queries through our ETL tool - kettle (the same if I try to connect through SQuirreLSQL) ):

Error connecting to database: (using class org.postgresql.Driver)
ERROR: Unsupported startup parameter: extra_float_digits


(We have this jar:  postgresql-8.4-702.jdbc3.jar)


Is this something that you can fix?

Thanks!
I remember that this is something we ran into when we first started working on JDBC access to Postgres.  The problem is with the pg proxy that is used.  It needs to be configured to allow this parameter.  I believe the JDBC driver is hardwired to pass it through.
Daniel,

Actually, you should update your JDBC driver, to the 9.1 driver.  Thanks!
I'll also change this in pgBouncer.  But you should update the driver, there will be other issues if you're using an out-of-date JDBC.
Driver is now updated to: postgresql-9.1-902.jdbc3

It now works through kettle. Thanks.
These changes have been implemented in stage and prod. Is the metrics team good on accessing now?
(In reply to Matt Pressman [:mpressman] from comment #24)
> These changes have been implemented in stage and prod. Is the metrics team
> good on accessing now?

Yes, we can connect to Production DB
(still the same issue regarding Stage DB - comment 9)
Hi Paula, can you pm me your connection params? the config on stage and prod are the same, so I would like to test to get you up and running on stage.
Paula, thanks for sending me the connect information. After a bunch of debugging I finally discovered what I probably should have looked at first, the password! Anyway, the stage user has a different password set for authentication. It's usually best to have a different pass, but because it's hashed I don't know what it is. So, we can either change the stage password, or I can make the stage password the same one you're using in prod. Let me know what your preferences are.
Hi Matt, did you received my last email?
Hi Paula - Matt's on PTO right now, if you come onto IRC either Josh or I can help you with this issue.
Hi Sheeri,
Paula is also on PTO this week, I'm not sure what the content of her email was, but I'll be around if you guys want to sort it out with me, instead of waiting for her return.
Thanks!
Hi Pedro - I honestly don't know what problems are still occurring, so I think this should wait until Matt and Paula come back. If folks on the metrics team know specifically what is needed, I'm happy to facilitate.
I belatedly responded to Paula, but wanted to set the password to what she sent me. I haven't changed it yet as I'm not sure of the state either and didn't want to lock anyone out. I am just waiting for the go-ahead to set it.
Go ahead :)

Thanks.
Hi Matt,
Any ETA for this?

Thanks.
Ping, Matt! 

Sounds like last status was setting a password in .stage for Paula.
changing stage password now
password changed on host and puppet
Thanks, Matt. 

Paula - you should be able to login to StageDB with user 'analyst' now, using the password you sent to Matt a while back. 

Please confirm!
Hi Matt, Selena

Good news: Both the connection are working now (Prod and Stage)! Thanks  :)

Bad news: I forgot to mention that we recently moved our machines... Sorry :(
So, we will need access from webapp1.metrics.scl3.mozilla.com (instead of app1)
I'll check and see where you have access from
I created a request to open the net flow to get you access
ok, looks like you are good to go now:

- Socorro Stage DB

nc -zv socorro1.stage.db.phx1.mozilla.com 6432
Connection to socorro1.stage.db.phx1.mozilla.com 6432 port [tcp/pgbouncer] succeeded!

- Socorro Prod DB

nc -zv tp-socorro01-ro-zeus.phx1.mozilla.com 6432
Connection to tp-socorro01-ro-zeus.phx1.mozilla.com 6432 port [tcp/pgbouncer] succeeded!
Great.
Thanks.
Status: NEW → RESOLVED
Closed: 12 years ago
Resolution: --- → FIXED
Product: mozilla.org → Data & BI Services Team
You need to log in before you can comment on or make changes to this bug.