Jksql failover connections

From genomewiki
Revision as of 16:01, 8 February 2014 by Max (talk | contribs)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to navigationJump to search

For the browser-in-a-box several changes were made to jksql.c that allow a union of two mysql servers over the same connection. They transparently look like one mysql connection from the point of view of the browser. This means that one sqlConnection object can have mysql connections to two servers: a main (or local) one, and a failover (or slow) one.

  • The main connection data is configured as usual via the db.host/db.password etc settings in hg.conf
  • The failover/slow connection is configured via slow-db.host,slow-db.password/etc settings in hg.conf
  • databases can exist on either only the local connection or the remote connection.
    • This allows only-local databases loaded by a user but doesn't require the user to have local databases for all UCSC databases.

Connect to a given database:

  • The main connection is always connected. The failover one is always setup, but only connected if needed.
  • If a db does not exist in the main connection, it is connected but without a DB (It is therefore in an "invalid" state detected by sqlMustDoFailover later)
    • "Invalid" state is detected by comparing the mysql database with the database in sqlConnection->db.
  • If a db does not exist in the failover connection, silently allow this

Query:

  • All queries are run against the local connection first if possible, and if they fail, are run against the failover connection.
  • If the main connection is in an "invalid" state, go directly to the failover connection

Change database:

  • Database changes are reflected on both connections
  • On failover, connects are "lazy": if a connection is not connected, the database change is noted in sqlConnection->db, but not sent to mysql.
  • If a database does not exist on the main connection, the main connection is in an "invalid" state
  • If a database does not exist on the failover connection, the failover connection is in an "invalid" state

Some examples for a case where only hg19 exists locally:

  • connect with hg19, change to ce2:
  1. sqlConnect(hg19): main connected/hg19, failover unconnected/hg19
  2. sqlQuery: run against main first, if failed run on failover
  3. sqlConnChangeDb(ce2): main connected/invalid, failover connected/ce2
  4. sqlQuery: run against failover because main is invalid
  5. sqlConnChangeDb(hg19): main connected/hg19, failover connected/hg19
  • connect with ce2, change to hg19:
  1. sqlConnect(ce2): main connected/invalid, failover connected/ce2
  2. sqlQuery: run against failover
  3. sqlConnChangeDb(hg19): main connected/hg19, failover connected/hg19
  4. sqlQuery: run against main first, if failed run on failover
  • connect with ce2, change to braNey1
  1. sqlConnect(ce2): main connected/ce2, failover unconnected/ce2
  2. sqlQuery: run against main, failover on error
  3. sqlConnChangeDb(braNey1): main connected/braNey1, failover invalid
  4. sqlQuery: run against main, no failover (does this work?)
  • connect braNey1, change to hg19
  1. sqlConnect(braNey1): main connected/braNey1, failover not connected/braNey1
  2. sqlQuery: run against local (does it fail over?)
  3. sqlConnChangeDb(hg19): main connected/hg19, failover not connected/hg19
  4. sqlQuery: run against local, use failover