225c0d55992aefae478461bba278644bdfdda3c5
max
  Wed Jan 15 08:33:57 2014 -0800
library changes for the browser box: This changes mostly hdb and jksql,plus - to a smaller extent - various other places in the code that deal
with /gbdb/ files.  The overall aim is to make it possible to have the
data remote at UCSC while having the CGIs on a machine far away. At up to
180msecs distance from UCSC (Europe,Japan), each query can get slow. So
I tried to reduce the number of queries sent to UCSC while allowing to
keep some mysql tables on localhost.

I changed four things:
- extend larry's table cache to include field names. The code uses
"describe" very often, which is slow from remote. With a table name
cache these queries can be handled locally. This is configured in
hg.conf
- mysql "failover" connections: a mysql connection can have a 2nd
connection that is used if a query fails, configured in hg.conf
(I didn't call it "remote" connections, because we use that term already
in the code)
- mysql lazy connects: don't connect a sqlConnection right away, but
only when needed. a mysql connect takes >500msecs from across the
atlantic.
- move gbdb: patch various places that use absolute "/gbdb/" pathnames
to go through a central function that can change the filename of
gbdb files to something else, as configured in hg.conf

Plus patch 1 or 2 lines for more speed + update the hgMirror script

diff --git src/hg/lib/jksql.c src/hg/lib/jksql.c
index 0a891b1..a5dedbd 100644
--- src/hg/lib/jksql.c
+++ src/hg/lib/jksql.c
@@ -9,30 +9,40 @@
  * Configuration:
  */
 
 
 #include "common.h"
 #include "portable.h"
 #include "errabort.h"
 #include <mysql.h>
 #include "dlist.h"
 #include "dystring.h"
 #include "jksql.h"
 #include "sqlNum.h"
 #include "hgConfig.h"
 #include "cheapcgi.h"
 
+/* a function to get mysql results, either mysql_use_result or mysql_store_result */
+/* a) mysql_use_result means that after a query, the results are stored on the server and return row-by-row 
+ * b) mysql_store_result means that the results are returned together 
+ * 
+ * a) means less memory, b) longer transfer times (latency of network * number of rows)
+ * */
+
+typedef MYSQL_RES *	STDCALL ResGetter(MYSQL *mysql);
+
+#define DEFAULTGETTER mysql_store_result
 
 /* flags controlling sql monitoring facility */
 static unsigned monitorInited = FALSE;      /* initialized yet? */
 static unsigned monitorFlags = 0;           /* flags indicating what is traced */
 static long monitorEnterTime = 0;           /* time current tasked started */
 static long long sqlTotalTime = 0;          /* total real milliseconds */
 static long sqlTotalQueries = 0;            /* total number of queries */
 static boolean monitorHandlerSet = FALSE;   /* is exit handler installed? */
 static unsigned traceIndent = 0;            /* how much to indent */
 static char *indentStr = "                                                       ";
 static boolean sqlParanoid = FALSE;         /* extra squawking */
 
 /* statistics */
 static unsigned totalNumConnects = 0;
 static unsigned maxNumConnections = 0;
@@ -49,30 +59,33 @@
     char *password;     // database server password
     struct slName *dbs; // database associated with profile, can be NULL.
     };
 
 struct sqlConnection
 /* This is an item on a list of sql open connections. */
     {
     MYSQL *conn;		    /* Connection. */
     struct sqlProfile *profile;     /* profile, or NULL if not opened via a profile */
     struct dlNode *node;	    /* Pointer to list node. */
     struct dlList *resultList;	    /* Any open results. */
     boolean hasHardLock;	    /* TRUE if table has a non-advisory lock. */
     boolean inCache;                /* debugging flag to indicate it's in a cache */
     boolean isFree;                /* is this connection free for reuse; alway FALSE
                                     * unless managed by a cache */
+    boolean hasTableCache;           /* to avoid repeated checks for cache table name existence, -1 if not initialized yet */
+    struct sqlConnection *slowConn; /* optional. tried if a query fails on the conn connection */
+    char *db;                       /* to be able to lazily connect later, we need to store the database */
     };
 
 struct sqlResult
 /* This is an item on a list of sql open results. */
     {
     MYSQL_RES *result;			/* Result. */
     struct dlNode *node;		/* Pointer to list node we're on. */
     struct sqlConnection *conn;		/* Pointer to connection. */
     long fetchTime;                     /* cummulative time taken by row fetches for this result */
     };
 
 static struct dlList *sqlOpenConnections = NULL;
 static unsigned sqlNumOpenConnections = 0;
 
 char *defaultProfileName = "db";                  // name of default profile
@@ -369,53 +382,69 @@
 /* called at the end of a routine that is monitored, updates time count.
  * returns time since enter. */
 {
 long deltaTime = 0;
 if (monitorFlags)
     {
     deltaTime = clock1000() - monitorEnterTime;
     assert(monitorEnterTime > 0);
     if (monitorFlags & JKSQL_PROF)
         sqlTotalTime += deltaTime;
     monitorEnterTime = 0;
     }
 return deltaTime;
 }
 
+static char *scConnHost(struct sqlConnection *sc)
+/* Return the host of a sqlConnection */
+{
+if (sc->conn)
+    return sc->conn->host;
+if (sc->profile->host)
+    return sc->profile->host;
+return NULL;
+}
+
 static char *scConnDb(struct sqlConnection *sc)
-/* Return sc->conn->db, unless it is NULL -- if NULL, return a string for
+/* Return sc->db, unless it is NULL -- if NULL, return a string for
  * fprint'd messages. */
 {
-return (sc->conn->db ? sc->conn->db : "db=?");
+return (sc->db ? sc->db : "db=?");
 }
 
 static void monitorPrintInfo(struct sqlConnection *sc, char *name)
 /* print a monitor message, with connection id and databases. */
 {
+long int threadId = 0;
+if (sc->conn)
+    threadId = sc->conn->thread_id;
 fprintf(stderr, "%.*s%s %ld %s\n", traceIndent, indentStr, name,
-        sc->conn->thread_id, scConnDb(sc));
+        threadId, scConnDb(sc));
 fflush(stderr);
 }
 
 static void monitorPrint(struct sqlConnection *sc, char *name,
                          char *format, ...)
 /* print a monitor message, with connection id, databases, and
  * printf style message.*/
 {
 va_list args;
-fprintf(stderr, "%.*s%s %ld %s ", traceIndent, indentStr, name,
-        sc->conn->thread_id, scConnDb(sc));
+long int threadId = 0;
+if (sc->conn)
+    threadId = sc->conn->thread_id;
+fprintf(stderr, "%.*s%s %ld %s %s ", traceIndent, indentStr, name,
+        threadId, scConnHost(sc), scConnDb(sc));
 va_start(args, format);
 vfprintf(stderr, format, args);
 va_end(args);
 fputc('\n', stderr);
 fflush(stderr);
 }
 
 static void monitorPrintTime(void)
 /* print total time */
 {
 /* only print if not explictly disabled */
 if (monitorFlags & JKSQL_PROF)
     {
     fprintf(stderr, "%.*sSQL_TOTAL_TIME %0.3fs\n", traceIndent, indentStr,
             ((double)sqlTotalTime)/1000.0);
@@ -495,30 +524,31 @@
         }
     if (res->node != NULL)
 	{
 	dlRemove(res->node);
 	freeMem(res->node);
 	}
     freez(pRes);
     }
 }
 
 void sqlDisconnect(struct sqlConnection **pSc)
 /* Close down connection. */
 {
 struct sqlConnection *sc = *pSc;
 long deltaTime;
+
 if (sc != NULL)
     {
     if (sc->inCache)
         errAbort("sqlDisconnect called on connection associated with a cache");
     assert(!sc->isFree);
     MYSQL *conn = sc->conn;
     struct dlList *resList = sc->resultList;
     struct dlNode *node = sc->node;
     if (resList != NULL)
 	{
 	struct dlNode *resNode, *resNext;
 	for (resNode = resList->head; resNode->next != NULL; resNode = resNext)
 	    {
 	    struct sqlResult *res = resNode->val;
 	    resNext = resNode->next;
@@ -532,143 +562,231 @@
 	    sqlHardUnlockAll(sc);
         if (monitorFlags & JKSQL_TRACE)
             monitorPrintInfo(sc, "SQL_DISCONNECT");
         monitorEnter();
 	mysql_close(conn);
 
 	deltaTime = monitorLeave();
 	if (monitorFlags & JKSQL_TRACE)
 	    monitorPrint(sc, "SQL_TIME", "%0.3fs", ((double)deltaTime)/1000.0);
 	}
     if (node != NULL)
 	{
 	dlRemove(node);
 	freeMem(node);
 	}
-    freez(pSc);
     sqlNumOpenConnections--;
     }
+    
+    // also close local cache connection
+    if (sc->slowConn != NULL)
+        sqlDisconnect(&sc->slowConn);
+
+    freez(pSc);
+        
 }
 
 char* sqlGetDatabase(struct sqlConnection *sc)
 /* Get the database associated with an connection. Warning: return may be NULL! */
 {
 assert(!sc->isFree);
+if (sc->conn)
     return sc->conn->db;
+else
+    return sc->db;
 }
 
 char* sqlGetHost(struct sqlConnection *sc)
 /* Get the host associated with an connection. */
 {
 assert(!sc->isFree);
 return sc->conn->host;
 }
 
 struct slName *sqlGetAllDatabase(struct sqlConnection *sc)
 /* Get a list of all database on the server */
 {
 char query[32];
 sqlSafef(query, sizeof query, "show databases");
 struct sqlResult *sr = sqlGetResult(sc, query);
 char **row;
 struct slName *databases = NULL;
 while ((row = sqlNextRow(sr)) != NULL)
     {
     if (!startsWith("mysql", row[0]))  /* Avoid internal databases. */
         slSafeAddHead(&databases, slNameNew(row[0]));
     }
 sqlFreeResult(&sr);
 return databases;
 }
 
-struct slName *sqlListTables(struct sqlConnection *conn)
-/* Return list of tables in database associated with conn. */
+// forward declaration of sqlUseOrStore so the order of functions does not change in this file
+// sqlUseOrStore is needed for sqlTableCacheFindConn
+// FIXME MAX: is this good practice? shall I rather reorder the functions in this file?
+static struct sqlResult *sqlUseOrStore(struct sqlConnection *sc,
+	char *query, ResGetter *getter, boolean abort);
+
+static struct sqlConnection *sqlTableCacheFindConn(struct sqlConnection *conn)
+/* Check if table name caching is configured and the cache table is also present 
+ * in the server of the connection. Returns the connection or NULL */
 {
+char *tableListTable = cfgOption("showTableCache");
+if (tableListTable == NULL) 
+    return NULL;
+
+// also check if cache table exists, without using sqlTableExists
+// (sqlTableExists will always use a cache table)
+if (conn->hasTableCache==-1)
+    {
+    // to avoid hundreds of repeated table existence checks, we keep the result
+    // of sqlTableCacheFindConn in the sqlConn object
+    conn->hasTableCache = FALSE;
+
+    char query[1024];
+    sqlSafef(query, sizeof(query), "SELECT 1 FROM %-s LIMIT 0", sqlCkIl(tableListTable));  
     struct sqlResult *sr;
-char **row;
-struct slName *list = NULL, *el;
-char *cfgName = "showTableCache";
-char *tableList = cfgOption(cfgName);
 
-if (tableList != NULL)
+    // temporarily remove failover connection, we don't want the failover switch here
+    struct sqlConnection *slowConn = conn->slowConn; 
+    conn->slowConn=NULL;
+    sr = sqlUseOrStore(conn, query, DEFAULTGETTER, FALSE);
+    conn->slowConn=slowConn;
+
+    if (sr!=NULL)
         {
-    // mysql does not cache "show tables", so use a cached run of show tables which is stored in the showTableCache table.
-    // See redmine 3780 for details.
-    if(sqlTableExists(conn, tableList))
+        conn->hasTableCache = TRUE;
+        sqlFreeResult(&sr);
+        }
+    else if (slowConn!=NULL)
+        errAbort("no table cache found, although mysql failover (slow-db) is being used.");
+
+    }
+
+if (conn->hasTableCache)
+    return conn;
+else
+    return NULL;
+}
+
+static bool sqlTableCacheTableExists(struct sqlConnection *conn, char* table)
+/* check if table exists in table name cache */
+// (see redmine 3780 for some historical background on this caching)
 {
-        char query[256];
-        sqlSafef(query, sizeof(query), "select * from %s order by name desc", tableList);
-        sr = sqlGetResult(conn, query);
+char query[1024];
+char *tableListTable = cfgVal("showTableCache");
+sqlSafef(query, sizeof(query), "SELECT count(*) FROM %s WHERE tableName='%s'", tableListTable, table);
+return (sqlQuickNum(conn, query)!=0);
+}
+
+static struct slName *sqlTableCacheQuery(struct sqlConnection *conn)
+/* return all table names from the table name cache as a list. */
+{
+char *tableList = cfgVal("showTableCache");
+struct slName *list = NULL, *el;
+char query[1024];
+sqlSafef(query, sizeof(query), "SELECT DISTINCT tableName FROM %s", tableList);
+
+struct sqlResult *sr = sqlGetResult(conn, query);
+char **row;
 while ((row = sqlNextRow(sr)) != NULL)
     {
     el = slNameNew(row[0]);
     slAddHead(&list, el);
     }
+sqlFreeResult(&sr);
+return list;
 }
-    else
+
+struct slName *sqlListTables(struct sqlConnection *conn)
+/* Return list of tables in database associated with conn. */
 {
-        errAbort("%s option is misconfigured in hg.conf: table '%s' does not exist", cfgName, tableList);
-        }
-    }
+struct slName *list = NULL, *el;
+struct sqlResult *sr;
+char **row;
+
+struct sqlConnection *cacheConn = sqlTableCacheFindConn(conn);
+
+if (cacheConn)
+    list = sqlTableCacheQuery(cacheConn);
 else
     {
     char query[256];
-    sqlSafef(query, sizeof(query), "show tables");
+    sqlSafef(query, sizeof(query), "SHOW TABLES");
     sr = sqlGetResult(conn, query);
     while ((row = sqlNextRow(sr)) != NULL)
         {
         el = slNameNew(row[0]);
         slAddHead(&list, el);
         }
     slReverse(&list);
-    }
     sqlFreeResult(&sr);
+    }
+
 return list;
 }
 
+struct sqlResult *sqlDescribe(struct sqlConnection *conn, char *table)
+/* run the sql DESCRIBE command or get a cached table description and return the sql result */
+{
+char query[1024];
+
+struct sqlConnection *cacheConn = sqlTableCacheFindConn(conn);
+
+if (cacheConn)
+    {
+    char *tableListTable = cfgVal("showTableCache");
+    sqlSafef(query, sizeof(query), "SELECT Field, Type, NullAllowed, isKey, hasDefault, Extra FROM %s WHERE tableName='%s'", \
+        tableListTable, table);
+    conn = cacheConn;
+    }
+
+else
+    sqlSafef(query, sizeof(query), "DESCRIBE %s", table);
+
+struct sqlResult *sr;
+sr = sqlGetResult(conn, query);
+return sr;
+}
+
 struct slName *sqlListFields(struct sqlConnection *conn, char *table)
 /* Return list of fields in table. */
 {
-char query[256];
 char **row;
 struct slName *list = NULL, *el;
 struct sqlResult *sr = NULL;
-sqlSafef(query, sizeof(query), "describe %s", table);
-sr = sqlGetResult(conn, query);
+sr = sqlDescribe(conn, table);
 while ((row = sqlNextRow(sr)) != NULL)
     {
     el = slNameNew(row[0]);
     slAddHead(&list, el);
     }
 sqlFreeResult(&sr);
 slReverse(&list);
 return list;
 }
 
 void sqlAddDatabaseFields(char *database, struct hash *hash)
 /* Add fields from the one database to hash. */
 {
 struct sqlConnection *conn = sqlConnect(database);
 struct slName *table, *tableList = sqlListTables(conn);
 struct sqlResult *sr;
-char query[256];
 char **row;
 char fullName[512];
 for (table = tableList; table != NULL; table = table->next)
     {
-    sqlSafef(query, sizeof(query), "describe %s", table->name);
-    sr = sqlGetResult(conn, query);
+    sr = sqlDescribe(conn, table->name);
     while ((row = sqlNextRow(sr)) != NULL)
 	{
 	safef(fullName, sizeof(fullName), "%s.%s.%s",
 	    database, table->name, row[0]);
 	hashAdd(hash, fullName, NULL);
 	}
     sqlFreeResult(&sr);
     }
 slFreeList(&tableList);
 sqlDisconnect(&conn);
 }
 
 struct hash *sqlAllFields(void)
 /* Get hash of all fields in database.table.field format.  */
 {
@@ -701,133 +819,182 @@
 	}
     freeDlList(&sqlOpenConnections);
     }
 }
 
 static void sqlInitTracking(void)
 /* Initialize tracking and freeing of resources. */
 {
 if (sqlOpenConnections == NULL)
     {
     sqlOpenConnections = newDlList();
     atexit(sqlCleanupAll);
     }
 }
 
-static struct sqlConnection *sqlConnRemote(char *host, unsigned int port, char *socket,
+static struct sqlConnection *sqlConnRemoteFillIn(struct sqlConnection *sc, char *host, unsigned int port, char *socket,
 					   char *user, char *password,
                                            char *database, boolean abort)
-/* Connect to database somewhere as somebody. Database maybe NULL to just
- * connect to the server.  If abort is set display error message and abort on
- * error. This is the core function that connects to a MySQL server. */
+/* Fill the sqlConnection object: Connect to database somewhere as somebody.
+ * Database maybe NULL to just connect to the server.  If abort is set display
+ * error message and abort on error. This is the core function that connects to
+ * a MySQL server. */
 {
-struct sqlConnection *sc;
 MYSQL *conn;
 long deltaTime;
 
 sqlInitTracking();
 
-AllocVar(sc);
 sc->resultList = newDlList();
 sc->node = dlAddValTail(sqlOpenConnections, sc);
 
+long oldTime = monitorEnterTime;
+monitorEnterTime = 0;
 monitorEnter();
+
 if ((sc->conn = conn = mysql_init(NULL)) == NULL)
-    {
-    monitorLeave();
+    // no need for monitorLeave here
     errAbort("Couldn't connect to mySQL.");
-    }
 // Fix problem where client LOCAL setting is disabled by default for security
 mysql_options(conn, MYSQL_OPT_LOCAL_INFILE, NULL);
 if (mysql_real_connect(
 	conn,
 	host, /* host */
 	user,	/* user name */
 	password,	/* password */
 	database, /* database */
 	port,	/* port */
 	socket,	/* socket */
 	0)	/* flags */  == NULL)
     {
     monitorLeave();
+    monitorEnterTime = oldTime;
     if (abort)
 	errAbort("Couldn't connect to database %s on %s as %s.\n%s",
 	    database, host, user, mysql_error(conn));
     else if (sqlParanoid)
 	fprintf(stderr, "ASH: Couldn't connect to database %s on %s as %s.  "
 		"mysql: %s  pid=%ld\n",
 		database, host, user, mysql_error(conn), (long)getpid());
     return NULL;
     }
 
 /* Make sure the db is correct in the connect, think usually happens if there
  * is a mismatch between MySQL library and code.  If this happens, please
  * figure out what is going on.  Contact markd if you need help. */
 if (((conn->db != NULL) && !sameString(database, conn->db))
    || ((conn->db == NULL) && (database != NULL)))
    errAbort("apparent mismatch between mysql.h used to compile jksql.c and libmysqlclient");
 
 if (monitorFlags & JKSQL_TRACE)
     monitorPrint(sc, "SQL_CONNECT", "%s %s", host, user);
 
 deltaTime = monitorLeave();
 if (monitorFlags & JKSQL_TRACE)
     monitorPrint(sc, "SQL_TIME", "%0.3fs", ((double)deltaTime)/1000.0);
+monitorEnterTime = oldTime;
+
 sqlNumOpenConnections++;
 if (sqlNumOpenConnections > maxNumConnections)
     maxNumConnections = sqlNumOpenConnections;
 totalNumConnects++;
+
+sc->hasTableCache=-1; // -1 => not determined 
+sc->db=database;
 return sc;
 }
 
+static struct sqlConnection *sqlConnRemote(char *host, unsigned int port, char *socket,
+					   char *user, char *password,
+                                           char *database, boolean abort)
+/* Connect to database somewhere as somebody. Database maybe NULL to just
+ * connect to the server.  If abort is set display error message and abort on
+ * error. */
+{
+struct sqlConnection *sc;
+AllocVar(sc);
+return sqlConnRemoteFillIn(sc, host, port, socket, user, password, database, abort);
+}
+
 struct sqlConnection *sqlConnectRemote(char *host, char *user, char *password,
                                        char *database)
 /* Connect to database somewhere as somebody. Database maybe NULL to
  * just connect to the server. Abort on error. */
 {
 return sqlConnRemote(host, 0, NULL, user, password, database, TRUE);
 }
 
 struct sqlConnection *sqlMayConnectRemote(char *host, char *user, char *password,
                                           char *database)
 /* Connect to database somewhere as somebody. Database maybe NULL to
  * just connect to the server.  Return NULL can't connect */
 {
 return sqlConnRemote(host, 0, NULL, user, password, database, FALSE);
 }
 
 static struct sqlConnection *sqlConnProfile(struct sqlProfile* sp, char *database, boolean abort)
 /* Connect to database using the profile.  Database maybe NULL to connect to
  * the server. Optionally abort on failure. */
 {
-struct sqlConnection *conn = sqlConnRemote(sp->host, sp->port, sp->socket, sp->user, sp->password, database, abort);
-if (conn != NULL)
-    conn->profile = sp;  // remember profile, mainly for debugging
-return conn;
+struct sqlConnection *sc;
+
+// connect with the default profile
+sc = sqlConnRemote(sp->host, sp->port, sp->socket, sp->user, sp->password, database, abort);
+sc->profile = sp; // remember the profile
+
+// optionally prepare the slower failover connection
+char *slowProfName = catTwoStrings("slow-", sp->name);
+struct sqlProfile *slow = sqlProfileGet(slowProfName, database);
+freez(&slowProfName);
+
+if (slow==NULL)
+    return sc;
+
+// don't connect the slow connection yet: lazily connect later when needed; saves 0.5
+// seconds per connection on transatlantic links
+// instead create a "placeholder" sqlConnection with all connection data, but no connection
+struct sqlConnection *slowSc;
+AllocVar(slowSc);
+slowSc->profile = slow; // remember the profile
+slowSc->db = database;
+slowSc->hasTableCache = -1;
+sc->slowConn = slowSc;
+return sc;
 }
 
 struct sqlConnection *sqlMayConnect(char *database)
 /* Connect to database on default host as default user.
  * Return NULL (don't abort) on failure. */
 {
 return sqlConnProfile(sqlProfileMustGet(NULL, database), database, FALSE);
 }
 
+static struct sqlConnection *sqlConnectIfUnconnected(struct sqlConnection *sc)
+/* Take a yet unconnected sqlConnection object and connect it to the sql server */
+{
+if (sc->conn!=NULL)
+    return sc;
+struct sqlProfile *sp = sqlProfileMustGet(sc->profile->name, sc->db);
+sqlConnRemoteFillIn(sc, sp->host, sp->port, sp->socket, sp->user, sp->password, sc->db, TRUE);
+return sc;
+}
+
 struct sqlConnection *sqlConnect(char *database)
 /* Connect to database on default host as default user. */
 {
-return sqlConnProfile(sqlProfileMustGet(NULL, database), database, TRUE);
+struct sqlProfile *defProf = sqlProfileMustGet(NULL, database);
+return sqlConnProfile(defProf, database, TRUE);
 }
 
 struct sqlConnection *sqlConnectProfile(char *profileName, char *database)
 /* Connect to profile or database using the specified profile.  Can specify
  * profileName, database, or both. The profile is the prefix to the host,
  * user, and password variables in .hg.conf.  For the default profile of "db",
  * the environment variables HGDB_HOST, HGDB_USER, and HGDB_PASSWORD can
  * override.
  */
 {
 struct sqlProfile* sp = sqlProfileMustGet(profileName, database);
 return sqlConnRemote(sp->host, sp->port, sp->socket, sp->user, sp->password, database, TRUE);
 }
 
 struct sqlConnection *sqlMayConnectProfile(char *profileName, char *database)
@@ -837,113 +1004,125 @@
  * the environment variables HGDB_HOST, HGDB_USER, and HGDB_PASSWORD can
  * override.  Return NULL if connection fails.
  */
 {
 struct sqlProfile* sp = sqlProfileGet(profileName, database);
 return sqlConnRemote(sp->host, sp->port, sp->socket, sp->user, sp->password, database, FALSE);
 }
 
 void sqlVaWarn(struct sqlConnection *sc, char *format, va_list args)
 /* Default error message handler. */
 {
 MYSQL *conn = sc->conn;
 if (format != NULL) {
     vaWarn(format, args);
     }
-warn("mySQL error %d: %s", mysql_errno(conn), mysql_error(conn));
+warn("mySQL error %d: %s (profile=%s, host=%s, db=%s)", mysql_errno(conn), 
+    mysql_error(conn), sc->profile->name, sc->conn->host, sc->conn->db);
 }
 
 void sqlWarn(struct sqlConnection *sc, char *format, ...)
 /* Printf formatted error message that adds on sql
  * error message. */
 {
 va_list args;
 va_start(args, format);
 sqlVaWarn(sc, format, args);
 va_end(args);
 }
 
 void sqlAbort(struct sqlConnection  *sc, char *format, ...)
 /* Printf formatted error message that adds on sql
  * error message and abort. */
 {
 va_list args;
 va_start(args, format);
 sqlVaWarn(sc, format, args);
 va_end(args);
 noWarnAbort();
 }
 
-typedef MYSQL_RES *	STDCALL ResGetter(MYSQL *mysql);
-
 static struct sqlResult *sqlUseOrStore(struct sqlConnection *sc,
 	char *query, ResGetter *getter, boolean abort)
 /* Returns NULL if result was empty and getter==mysql_use_result.
  * Otherwise returns a structure that you can do sqlRow() on.
  * Watch out for subtle differences between mysql_store_result and mysql_use_result.
  * We seem to be only using mysql_use_result these days,
  * but mysql_store_result has left a big footprint in the code/comments.
  * In particular, mysql_store_result can return NULL indicating an empty resultset.
  * But mysql_use_result cannot do that. Instead NULL return means error
  * and the user must call next_row to see if there's anything in the resultset.
  */
 {
-assert(!sc->isFree);
-MYSQL *conn = sc->conn;
 struct sqlResult *res = NULL;
 long deltaTime;
 boolean fixedMultipleNOSQLINJ = FALSE;
 
 ++sqlTotalQueries;
 
 if (monitorFlags & JKSQL_TRACE)
     monitorPrintQuery(sc, query);
 
 if (startsWith("NOSQLINJ ", query))
     {
     query += strlen("NOSQLINJ "); // We know this query has been vetted for sql injection, skip over this tag.
     }
 else
     {
     sqlCheckError("Unvetted query: %s", query);
     }
 
 // additional check finds errors of multiple NOSQLINJ tags
 if (strstr(query, "NOSQLINJ "))
     {
     sqlCheckError("Oops, multiple occurrences of NOSQLINJ tag in query: %s", query);
     query = replaceChars(query, "NOSQLINJ ", "");
     fixedMultipleNOSQLINJ = TRUE;
     }
 
+assert(!sc->isFree);
+
 monitorEnter();
-if (mysql_real_query(conn, query, strlen(query)) != 0)
+int mysqlError = mysql_real_query(sc->conn, query, strlen(query));
+
+// if the query fails on the main connection, connect the slower/failover connection and try there
+if (mysqlError != 0 && sc->slowConn)
+    {
+    if (monitorFlags & JKSQL_TRACE)
+        monitorPrint(sc, "SQL_FAILOVER %s to %s\n", sc->profile->name, sc->slowConn->profile->name);
+
+    sc = sc->slowConn;
+    sc = sqlConnectIfUnconnected(sc);
+    mysqlError = mysql_real_query(sc->conn, query, strlen(query));
+    }
+
+if (mysqlError != 0)
     {
     if (abort)
         {
         monitorLeave();
 	if (sameOk(cfgOption("noSqlInj.dumpStack"), "on"))
     	    dumpStack("DEBUG Can't start query"); // Extra debugging info. DEBUG REMOVE
 	sqlAbort(sc, "Can't start query:\n%s\n", query);
         }
     }
 else
     {
     MYSQL_RES *resSet;
-    if ((resSet = getter(conn)) == NULL)
+    if ((resSet = getter(sc->conn)) == NULL)
 	{
-	if (mysql_errno(conn) != 0)
+	if (mysql_errno(sc->conn) != 0)
 	    {
             monitorLeave();
 	    sqlAbort(sc, "Can't use query:\n%s", query);
 	    }
 	}
     else
         {
         AllocVar(res);
         res->conn = sc;
         res->result = resSet;
         res->node = dlAddValTail(sc->resultList, res);
         res->fetchTime = 0L;
         }
     }
 deltaTime = monitorLeave();
@@ -1084,92 +1263,108 @@
 {
 sqlDropTable(sc, table);
 sqlUpdate(sc, create);
 }
 
 boolean sqlDatabaseExists(char *database)
 /* Return TRUE if database exists. */
 {
 struct sqlConnection *conn = sqlMayConnect(database);
 boolean exists = (conn != NULL);
 sqlDisconnect(&conn);
 return exists;
 }
 
 boolean sqlTableExists(struct sqlConnection *sc, char *table)
-/* Return TRUE if a table exists. */
+/* Return TRUE if a table exists. 
+ *
+ * If a local connection is configured in hg.conf, looks up table in the local connection first 
+ * Use a table name cache table, if configured in hg.conf
+ */
 {
 char query[256];
 struct sqlResult *sr;
 if (sameString(table,""))
     {
     if (sameOk(cfgOption("noSqlInj.dumpStack"), "on"))
 	dumpStack("jksql sqlTableExists: Buggy code is feeding me empty table name. table=[%s].\n", table); fflush(stderr); // log only
     return FALSE;
     }
 // TODO If the ability to supply a list of tables is hardly used,
 // then we could switch it to simply %s below supporting a single
 // table at a time more securely.
 if (strchr(table,','))
     {
     if (sameOk(cfgOption("noSqlInj.dumpStack"), "on"))
 	dumpStack("sqlTableExists called on multiple tables with table=[%s]\n", table);
     }
 if (strchr(table,'%'))
     {
     if (sameOk(cfgOption("noSqlInj.dumpStack"), "on"))
 	dumpStack("jksql sqlTableExists: Buggy code is feeding me junk wildcards. table=[%s].\n", table); fflush(stderr); // log only
     return FALSE;
     }
 if (strchr(table,'-'))
     {
     return FALSE;  // mysql does not allow tables with dash (-) so it will not be found.
     // hg/lib/hdb.c can generate an invalid table names with dashes while looking for split tables,
     // if the first chrom name has a dash in it. Examples found were: scaffold_0.1-193456 scaffold_0.1-13376 HERVE_a-int 1-1
     // Assembly hubs also may have dashes in chrom names.
     }
+
+// use the table cache if we have one
+struct sqlConnection *cacheConn = sqlTableCacheFindConn(sc);
+if (cacheConn)
+    return sqlTableCacheTableExists(cacheConn, table);
+
 sqlSafef(query, sizeof(query), "SELECT 1 FROM %-s LIMIT 0", sqlCkIl(table));  
 //sqlSafef(query, sizeof(query), "SELECT 1 FROM %-s LIMIT 0", sqlCkId(table));  // DEBUG RESTORE
-if ((sr = sqlUseOrStore(sc,query,mysql_use_result, FALSE)) == NULL)
+if ((sr = sqlUseOrStore(sc, query, DEFAULTGETTER, FALSE)) == NULL)
+    {
+    if (!sc->slowConn)
+        return FALSE;
+    // if not found but we have a local connection, check the local connection, too
+    else if ((sr = sqlUseOrStore(sc->slowConn, query, DEFAULTGETTER, FALSE)) == NULL)
         return FALSE;
+    }
 // TODO consider using sqlGetResultExt or something that would
 // allow you to abort on all errors except the actual table not found:
 // ERROR 1146 (42S02): Table 'hg19.chr_est' doesn't exist
 sqlFreeResult(&sr);
 return TRUE;
 }
 
 bool sqlColumnExists(struct sqlConnection *conn, char *tableName, char *column)
 /* return TRUE if column exists in table. tableName can contain sql wildcards  */
 {
 char query[1024];
 sqlSafef(query, 1024, "SHOW COLUMNS FROM `%s` LIKE '%s'", tableName, column);
 char buf[1024];
 char *ret = sqlQuickQuery(conn, query, buf, 1024);
 return (ret!=NULL);
 }
 
 int sqlTableSizeIfExists(struct sqlConnection *sc, char *table)
 /* Return row count if a table exists, -1 if it doesn't. */
 {
 char query[256];
 struct sqlResult *sr;
 char **row = 0;
 int ret = 0;
 
 sqlSafef(query, sizeof(query), "select count(*) from %s", table);
-if ((sr = sqlUseOrStore(sc,query,mysql_use_result, FALSE)) == NULL)
+if ((sr = sqlUseOrStore(sc, query, DEFAULTGETTER, FALSE)) == NULL)
     return -1;
 row = sqlNextRow(sr);
 if (row != NULL && row[0] != NULL)
     ret = atoi(row[0]);
 sqlFreeResult(&sr);
 return ret;
 }
 
 boolean sqlTablesExist(struct sqlConnection *conn, char *tables)
 /* Check all tables in space delimited string exist. */
 {
 char *dupe = cloneString(tables);
 char *s = dupe, *word;
 boolean ok = TRUE;
 while ((word = nextWord(&s)) != NULL)
@@ -1218,57 +1413,56 @@
 	*retOk = TRUE;
     }
 else if (retOk != NULL)
     *retOk = TRUE;
 return row;
 }
 
 
 
 struct sqlResult *sqlGetResultExt(struct sqlConnection *sc, char *query, unsigned int *errorNo, char **error)
 /* Returns NULL if it had an error.
  * Otherwise returns a structure that you can do sqlRow() on.
  * If there was an error, *errorNo will be set to the mysql error number,
  * and *error will be set to the mysql error string, which MUST NOT be freed. */
 {
-struct sqlResult *sr = sqlUseOrStore(sc, query, mysql_use_result, FALSE);
+struct sqlResult *sr = sqlUseOrStore(sc, query, DEFAULTGETTER, FALSE);
 if (sr == NULL)
     {
     MYSQL *conn = sc->conn;
     if (errorNo)
     	*errorNo=mysql_errno(conn);
     if (error)
     	*error=(char *)mysql_error(conn);
     }
 else
     {
     if (errorNo)
     	*errorNo=0;
     if (error)
     	*error=NULL;
     }
 return sr;
 }
 
-
 struct sqlResult *sqlGetResult(struct sqlConnection *sc, char *query)
 /* 
  * Return a structure that you can do sqlNextRow() on. 
  * (You need to check the return value of sqlRow to find out if there are
  * any results.) */
 {
-return sqlUseOrStore(sc,query,mysql_use_result, TRUE);
+return sqlUseOrStore(sc, query, DEFAULTGETTER, TRUE);
 }
 
 struct sqlResult *sqlMustGetResult(struct sqlConnection *sc, char *query)
 /* 
  * Return a structure that you can do sqlNextRow() on. 
  * DOES NOT errAbort() IF THERE ARE NO RESULTS 
  * (These days, with mysql_use_result, we cannot know ahead of time
  * if there are results, we can only know by actually trying to fetch a row.
  * So in fact right now sqlMustGetResult is no different than sqlGetResult.) */
 {
 struct sqlResult *res = sqlGetResult(sc,query);
 if (res == NULL)
 	errAbort("Object not found in database.\nQuery was %s", query);
 return res;
 }
@@ -1555,38 +1749,36 @@
 return mysql_field_count(sr->result);
 }
 #endif /* SOMETIMES */
 
 int sqlFieldCount(struct sqlResult *sr)
 /* Return number of fields in a row of result. */
 {
 if (sr == NULL)
     return 0;
 return mysql_num_fields(sr->result);
 }
 
 int sqlCountColumnsInTable(struct sqlConnection *sc, char *table)
 /* Return the number of columns in a table */
 {
-char query[256];
 struct sqlResult *sr;
 char **row;
 int count;
 
 /* Read table description and count rows. */
-sqlSafef(query, sizeof(query), "describe %s", table);
-sr = sqlGetResult(sc, query);
+sr = sqlDescribe(sc, table);
 count = 0;
 while ((row = sqlNextRow(sr)) != NULL)
     {
     count++;
     }
 sqlFreeResult(&sr);
 return count;
 }
 
 char *sqlQuickQuery(struct sqlConnection *sc, char *query, char *buf, int bufSize)
 /* Does query and returns first field in first row.  Meant
  * for cases where you are just looking up one small thing.
  * Returns NULL if query comes up empty. */
 {
 struct sqlResult *sr;
@@ -1774,68 +1966,64 @@
 while ((row = sqlNextRow(sr)) != NULL)
     {
     n = slDoubleNew(atof(row[0]));
     slAddHead(&list, n);
     }
 sqlFreeResult(&sr);
 slReverse(&list);
 return list;
 }
 
 
 int sqlTableSize(struct sqlConnection *conn, char *table)
 /* Find number of rows in table. */
 {
 char query[128];
-sqlSafef(query, sizeof(query), "select count(*) from %s", table);
+sqlSafef(query, sizeof(query), "SELECT COUNT(*) FROM %s", table);
 return sqlQuickNum(conn, query);
 }
 
 int sqlFieldIndex(struct sqlConnection *conn, char *table, char *field)
 /* Returns index of field in a row from table, or -1 if it
  * doesn't exist. */
 {
-char query[256];
 struct sqlResult *sr;
 char **row;
 int i = 0, ix=-1;
 
 /* Read table description into hash. */
-sqlSafef(query, sizeof(query), "describe %s", table);
-sr = sqlGetResult(conn, query);
+sr = sqlDescribe(conn, table);
 while ((row = sqlNextRow(sr)) != NULL)
     {
     if (sameString(row[0], field))
         {
 	ix = i;
 	break;
 	}
     ++i;
     }
 sqlFreeResult(&sr);
 return ix;
 }
 
 struct slName *sqlFieldNames(struct sqlConnection *conn, char *table)
 /* Returns field names from a table. */
 {
 struct slName *list = NULL;
-char query[256];
 struct sqlResult *sr;
 char **row;
-sqlSafef(query, sizeof(query), "describe %s", table);
-sr = sqlGetResult(conn, query);
+sr = sqlDescribe(conn, table);
 while ((row = sqlNextRow(sr)) != NULL)
     slNameAddHead(&list, row[0]);
 sqlFreeResult(&sr);
 slReverse(&list);
 return list;
 }
 
 unsigned int sqlLastAutoId(struct sqlConnection *conn)
 /* Return last automatically incremented id inserted into database. */
 {
 assert(!conn->isFree);
 unsigned id;
 monitorEnter();
 id = mysql_insert_id(conn->conn);
 monitorLeave();
@@ -1927,52 +2115,84 @@
 /* create and add a new cache entry */
 {
 struct sqlConnCacheEntry *scce;
 AllocVar(scce);
 scce->profile = profile;
 scce->conn = conn;
 conn->inCache = TRUE;
 conn->isFree = TRUE;
 slAddHead(&cache->entries, scce);
 cache->entryCnt++;
 return scce;
 }
 
 static boolean sqlConnCacheEntryDbMatch(struct sqlConnCacheEntry *scce,
                                         char *database)
-/* does a database match the one in the connection cache */
+/* does a database match the one in the connection cache? */
 {
-return ((database == NULL) && (scce->conn->conn->db == NULL))
-    || sameString(database, scce->conn->conn->db);
+return ((database == NULL) && (scce->conn->db == NULL))
+    || sameString(database, scce->conn->db);
 }
 
-static boolean sqlConnCacheEntrySetDb(struct sqlConnCacheEntry *scce,
-                                      char *database,
-                                      boolean abort)
-/* set the connect cache and connect to the specified database */
+static boolean sqlConnChangeDb(struct sqlConnection *sc, char *database, boolean abort)
+/* change the database of an sql connection (and its failover connection)
+ * */
 {
-if (mysql_select_db(scce->conn->conn, database) != 0)
+// if we have a failover connection, keep its db in sync
+int slowConnErr = 0;
+if (sc->slowConn)
+    {
+    sc->slowConn->db = database;
+    if (monitorFlags & JKSQL_TRACE)
+        monitorPrint(sc->slowConn, "SQL_SET_DB", "%s %s", sc->slowConn->profile->name, database);
+    if (sc->slowConn->conn)
+        slowConnErr = mysql_select_db(sc->slowConn->conn, database);
+        // we ignore the errors here: this allows to have some DBs only locally
+    }
+
+sc->db=database;
+if (monitorFlags & JKSQL_TRACE)
+    monitorPrint(sc, "SQL_SET_DB", "%s %s", sc->profile->name, database);
+
+// we only fail if there is no failover connection, this allows to have a DB
+// that does not exist locally but only remote
+int localConnErr = 0;
+localConnErr = mysql_select_db(sc->conn, database);
+if (sc->slowConn==NULL && sc->conn!=NULL && localConnErr != 0)
     {
     if (abort) 
         errAbort("Couldn't set connection database to %s\n%s",
-                 database, mysql_error(scce->conn->conn));
+                 database, mysql_error(sc->conn));
     else
         return FALSE;
     }
+
+if (localConnErr!=0 && slowConnErr!=0 && abort)
+    errAbort("Couldn't set connection database to %s in both default and failover connection\n%s",
+             database, mysql_error(sc->conn));
+
 return TRUE;
 }
 
+static boolean sqlConnCacheEntrySetDb(struct sqlConnCacheEntry *scce,
+                                      char *database,
+                                      boolean abort)
+/* set the connect cache and connect to the specified database */
+{
+return sqlConnChangeDb(scce->conn, database, abort);
+}
+
 static struct sqlConnCacheEntry *sqlConnCacheFindFree(struct sqlConnCache *cache,
                                                       struct sqlProfile *profile,
                                                       char *database,
                                                       boolean matchDatabase)
 /* find a free entry associated with profile and database. Return NULL if no
  * entries are available.  Will attempt to match database if requested, this
  * includes connections to no database (database==NULL). */
 {
 struct sqlConnCacheEntry *scce;
 for (scce = cache->entries; scce != NULL; scce = scce->next)
     {
     if (!scce->inUse && (profile == scce->profile)
         && ((!matchDatabase) || sqlConnCacheEntryDbMatch(scce, database)))
         {
         return scce;
@@ -1982,31 +2202,33 @@
 }
 
 static struct sqlConnCacheEntry *sqlConnCacheAddNew(struct sqlConnCache *cache,
                                                     struct sqlProfile *profile,
                                                     char *database,
                                                     boolean abort)
 /* create and add a new connect to the cache */
 {
 struct sqlConnection *conn;
 if (cache->entryCnt >= sqlConnCacheMax)
     errAbort("Too many open sqlConnections for cache");
 if (cache->host != NULL)
     conn = sqlConnRemote(cache->host, 0, NULL, cache->user,
                          cache->password, database, abort);
 else
+    {
     conn = sqlConnProfile(profile, database, abort);
+    }
 if (conn != NULL)
     return sqlConnCacheAdd(cache, profile, conn);
 else
     {
     assert(!abort);
     return NULL;
     }
 }
 
 static struct sqlConnection *sqlConnCacheDoAlloc(struct sqlConnCache *cache,
                                                  char *profileName,
                                                  char *database,
                                                  boolean abort)
 /* Allocate a cached connection. errAbort if too many open connections.
  * errAbort if abort and connection fails. */
@@ -2366,36 +2588,34 @@
 }
 
 time_t sqlTableUpdateTime(struct sqlConnection *conn, char *table)
 /* Get last update time for table.
  * Note: does NOT work on innoDB! */
 {
 char *date = sqlTableUpdate(conn, table);
 time_t time = sqlDateToUnixTime(date);
 freeMem(date);
 return time;
 }
 
 char *sqlGetPrimaryKey(struct sqlConnection *conn, char *table)
 /* Get primary key if any for table, return NULL if none. */
 {
-char query[512];
 struct sqlResult *sr;
 char **row;
 char *key = NULL;
-sqlSafef(query, sizeof(query), "describe %s", table);
-sr = sqlGetResult(conn, query);
+sr = sqlDescribe(conn, table);
 while ((row = sqlNextRow(sr)) != NULL)
     {
     if (sameWord(row[3], "PRI"))
 	{
         key = cloneString(row[0]);
 	break;
 	}
     }
 sqlFreeResult(&sr);
 return key;
 }
 
 char *sqlVersion(struct sqlConnection *conn)
 /* Return version of MySQL database.  This will be something
  * of the form 5.0.18-standard. */
@@ -2418,61 +2638,58 @@
 {
 char *s = sqlVersion(conn);
 int ver;
 if (!isdigit(s[0]))
     errAbort("Unexpected format in version: %s", s);
 ver = atoi(s);		/* NOT sqlUnsigned please! */
 freeMem(s);
 return ver;
 }
 
 int sqlMinorVersion(struct sqlConnection *conn)
 /* Return minor version of database. */
 {
 char *s = sqlVersion(conn);
 char *words[5];
-int wordCount;
 int ver;
 
-wordCount = chopString(s, ".", words, ArraySize(words));
+chopString(s, ".", words, ArraySize(words));
 
 if (!isdigit(*words[1]))
     errAbort("Unexpected format in version: %s", s);
 ver = atoi(words[1]);           /* NOT sqlUnsigned please! */
 freeMem(s);
 return ver;
 }
 
 char** sqlGetEnumDef(struct sqlConnection *conn, char* table, char* colName)
 /* Get the definitions of a enum column in a table, returning a
  * null-terminated array of enum values.  Free array when finished.  */
 {
 static char *enumPrefix = "enum(";
 struct sqlResult *sr;
-char query[128];
 char **row;
 char *defStr, *defStrCp;
 int numValues, i;
 char **enumDef;
 
 /* get enum definition */
-sqlSafef(query, sizeof(query), "describe %s", table);
-sr = sqlGetResult(conn, query);
+sr = sqlDescribe(conn, table);
 while (((row = sqlNextRow(sr)) != NULL) && !sameString(row[0], colName))
     continue;
 if (row == NULL)
-    errAbort("can't find column %s in results of %s", colName, query);
+    errAbort("can't find column %s in DESCRIBE of %s", colName, table);
 
 /* parse definition in the form:
  * enum('unpicked','candidate',... ,'cantSequence') */
 if (!startsWith(enumPrefix, row[1]))
     errAbort("%s column %s isn't an enum: %s", table, colName, row[1]);
 defStr = row[1] + strlen(enumPrefix);
 
 /* build char** array with string space in same block */
 numValues = chopString(defStr, ",", NULL, 0);
 enumDef = needMem(((numValues+1) * sizeof (char**)) + strlen(defStr)+1);
 defStrCp = ((char*)enumDef) + ((numValues+1) * sizeof (char**));
 strcpy(defStrCp, defStr);
 chopString(defStrCp, ",", enumDef, numValues);
 
 /* remove quotes */