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 */