a7822e03cd4ec551754b42b58b5238a1b0efb259 max Thu Mar 13 15:52:57 2014 -0700 after code review: changed comments and variable names in jksql.Only aesthestics, no logic change in this commit. Should make the code more readable. refs #12849 diff --git src/hg/lib/jksql.c src/hg/lib/jksql.c index 10fc14e..dfa8cd1 100644 --- src/hg/lib/jksql.c +++ src/hg/lib/jksql.c @@ -61,50 +61,50 @@ }; struct sqlConnection /* This is an item on a list of sql open connections. */ { MYSQL *conn; /* Connection. Can be NULL if not connected yet. */ 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 */ int hasTableCache; /* to avoid repeated checks for cache table name existence, -1 if not initialized yet, otherwise like a boolean */ - struct sqlConnection *slowConn; /* tried if a query fails on the main conn connection. */ + struct sqlConnection *failoverConn; /* tried if a query fails on the main connection. */ /* Can be NULL. */ char *db; /* to be able to connect later (if conn is NULL), 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 -char *slowProfPrefix = "slow-"; // prefix for failover profile for profile (="slow-db") +char *defaultProfileName = "db"; // name of default profile for main connection +char *failoverProfPrefix = "slow-"; // prefix for failover profile of main profile (="slow-db") static struct hash *profiles = NULL; // profiles parsed from hg.conf, by name static struct sqlProfile *defaultProfile = NULL; // default profile, also in profiles list static struct hash* dbToProfile = NULL; // db to sqlProfile // forward declarations to keep the git diffs clean static struct sqlResult *sqlUseOrStore(struct sqlConnection *sc, char *query, ResGetter *getter, boolean abort); static void sqlConnectIfUnconnected(struct sqlConnection *sc, bool abort); bool sqlConnMustUseFailover(struct sqlConnection *sc); static char *envOverride(char *envName, char *defaultVal) /* look up envName in environment, if it exists and is non-empty, return its * value, otherwise return defaultVal */ { char *val = getenv(envName); @@ -282,38 +282,38 @@ * the default profile of "db" * return NULL if not found. */ { //assert((profileName != NULL) || (database != NULL)); if (profiles == NULL) sqlProfileLoad(); if (profileName != NULL) return sqlProfileFindByName(profileName, database); else return sqlProfileFindByDatabase(database); } static struct sqlProfile* sqlProfileGetFailover(struct sqlProfile* sp, char *database) -/* try to find a failover slow-x profile for a profile x */ +/* try to find a failover profile for a profile x or return NULL*/ { if (sp==NULL || sp->name==NULL) return NULL; -char *slowProfName = catTwoStrings(slowProfPrefix, sp->name); -struct sqlProfile *slow = sqlProfileGet(slowProfName, database); -freez(&slowProfName); -return slow; +char *failoverProfName = catTwoStrings(failoverProfPrefix, sp->name); +struct sqlProfile *failoverProf = sqlProfileGet(failoverProfName, database); +freez(&failoverProfName); +return failoverProf; } static struct sqlProfile* sqlProfileMustGet(char *profileName, char *database) /* lookup a profile using the profile resolution algorithm or die trying */ { struct sqlProfile* sp = sqlProfileGet(profileName, database); if (sp == NULL) { if (profileName == NULL) errAbort("can't find database %s in hg.conf, should have a default named \"db\"", database); else if (database == NULL) errAbort("can't find profile %s in hg.conf", profileName); else errAbort("can't find profile %s for database %s in hg.conf", profileName, database); @@ -584,33 +584,33 @@ 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); } freeMem(sc->db); - // also close local cache connection - if (sc->slowConn != NULL) - sqlDisconnect(&sc->slowConn); + // also close failover connection + if (sc->failoverConn != NULL) + sqlDisconnect(&sc->failoverConn); freez(pSc); sqlNumOpenConnections--; } } char* sqlGetDatabase(struct sqlConnection *sc) /* Get the database associated with an connection. Warning: return may be NULL! */ { return sc->db; } char* sqlGetHost(struct sqlConnection *sc) @@ -629,75 +629,75 @@ 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; } -static bool sqlTableExistsNoCache(struct sqlConnection *sc, char *tableName) -/* check if cache table exists, without using sqlTableExists - (sqlTableExists will try to use a cache table itself) */ +static bool sqlTableExistsOnMain(struct sqlConnection *sc, char *tableName) +/* Return TRUE if the table can be queried using sc's main conn; + * don't check failoverConn or the table cache (showTableCache in hg.conf). */ { -// if the DB does not exist on the fast server, then a table cache makes no sense +// if the whole db does not exist on the main server, then the table is certainly not there if (sqlConnMustUseFailover(sc)) return FALSE; char query[1024]; sqlSafef(query, sizeof(query), "SELECT 1 FROM %-s LIMIT 0", sqlCkIl(tableName)); struct sqlResult *sr; // temporarily remove failover connection, we don't want the failover switch here -struct sqlConnection *slowConn = sc->slowConn; -sc->slowConn=NULL; +struct sqlConnection *failoverConn = sc->failoverConn; +sc->failoverConn=NULL; sr = sqlUseOrStore(sc, query, DEFAULTGETTER, FALSE); -sc->slowConn=slowConn; +sc->failoverConn=failoverConn; bool ret = FALSE; if (sr!=NULL) { monitorPrint(sc, "SQL_TABLE_EXISTS", "%s", tableName); ret = TRUE; sqlFreeResult(&sr); } else monitorPrint(sc, "SQL_TABLE_NOT_EXISTS", "%s", tableName); return ret; } static struct sqlConnection *sqlTableCacheFindConn(struct sqlConnection *conn) /* Check if table name caching is configured and the cache table is also present * on the server of the connection. Returns the connection or NULL */ { char *tableListTable = cfgOption("showTableCache"); if (tableListTable == NULL) return NULL; // to avoid hundreds of repeated table existence checks, we keep the result // of sqlTableCacheFindConn in the sqlConn object if (conn->hasTableCache==-1) // -1 => undefined { - conn->hasTableCache = (int) sqlTableExistsNoCache(conn, tableListTable); - if (conn->slowConn && !conn->hasTableCache) + conn->hasTableCache = (int) sqlTableExistsOnMain(conn, tableListTable); + if (conn->failoverConn && !conn->hasTableCache) { monitorPrint(conn, "SQL_FAILOVER_NO_TABLE_CACHE_FOR_DB", "%s", conn->db); return NULL; } } if (conn->hasTableCache) { monitorPrint(conn, "SQL_FOUND_TABLE_CACHE", "%s", tableListTable); return conn; } else { monitorPrint(conn, "SQL_NOT_FOUND_TABLE_CACHE", "%s", tableListTable); return NULL; @@ -978,58 +978,58 @@ AllocVar(sc); sc->conn = NULL; sc->profile = profile; // remember the profile, needed to connect later sc->db = cloneString(database); sc->hasTableCache = -1; // -1 => undefined return sc; } 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. */ { bool mainAbort = abort; struct sqlConnection *sc; -// get the slow-db profile for the profile, if it exists -struct sqlProfile *slow = sqlProfileGetFailover(sp, database); +// get the failover profile for the profile, if it exists +struct sqlProfile *failoverProf = sqlProfileGetFailover(sp, database); // if we have a failover profile, don't abort right away -if (slow!=NULL) +if (failoverProf!=NULL) mainAbort = FALSE; // connect with the default profile sc = sqlConnRemote(sp->host, sp->port, sp->socket, sp->user, sp->password, database, mainAbort); -if (slow==NULL) +if (failoverProf==NULL) // the default case, without a failover connection: just return sc, can be NULL return sc; -// we still have a failover profile to setup +// we still have a failover profile to setup: // if the requested database exists only on the failover connection, then the main connect // failed. We just connect again without a database, but note the database if (sc==NULL) { if (monitorFlags & JKSQL_TRACE) fprintf(stderr, "SQL_CONNECT_MAIN_FAIL %s\n", database); sc = sqlConnRemote(sp->host, sp->port, sp->socket, sp->user, sp->password, NULL, TRUE); sc->db = cloneString(database); } sc->profile = sp; // remember the profile -// don't connect the slow connection yet: lazily connect later when needed -sc->slowConn = sqlUnconnectedConn(slow, database); +// don't connect the failOver connection yet: lazily connect later when needed +sc->failoverConn = sqlUnconnectedConn(failoverProf, database); 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 void sqlConnectIfUnconnected(struct sqlConnection *sc, bool abort) /* Take a yet unconnected sqlConnection object and connect it to the sql server. */ { if (sc->conn!=NULL) return; @@ -1094,45 +1094,45 @@ 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(); } bool sqlConnMustUseFailover(struct sqlConnection *sc) -/* Returns true, if a connection has a failover connection and - * the sqlConnection db attribute is different from the mysql DB +/* Returns true if a connection has a failover connection and + * the current db does not exist on the main connection. */ { -// don't do anything when running on the RR -if (sc->slowConn == NULL) - return FALSE; - -if (sc->db != NULL && sc->conn->db != NULL && sameWord(sc->db, sc->conn->db) ) - return FALSE; - +// a db that is different between the sqlConnection object and mysql means that we have +// moved previously to a db that does not exist on the main connection server +if ((sc->failoverConn != NULL) && differentStringNullOk(sc->db, sc->conn->db)) + { monitorPrint(sc, "SQL_MAINCONN_DB_INVALID", "%s != %s", sc->db, sc->conn->db); return TRUE; } +return FALSE; +} + 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. */ { struct sqlResult *res = NULL; long deltaTime; boolean fixedMultipleNOSQLINJ = FALSE; @@ -1148,45 +1148,45 @@ } 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; } if (sqlConnMustUseFailover(sc)) - sc = sc->slowConn; + sc = sc->failoverConn; sqlConnectIfUnconnected(sc, abort); assert(!sc->isFree); monitorEnter(); 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 && sameWord(sqlGetDatabase(sc), sqlGetDatabase(sc->slowConn))) +// if the query fails on the main connection, connect the failover connection and try there +if (mysqlError != 0 && sc->failoverConn && sameWord(sqlGetDatabase(sc), sqlGetDatabase(sc->failoverConn))) { if (monitorFlags & JKSQL_TRACE) - monitorPrint(sc, "SQL_FAILOVER", "%s -> %s", scConnProfile(sc), scConnProfile(sc->slowConn)); + monitorPrint(sc, "SQL_FAILOVER", "%s -> %s", scConnProfile(sc), scConnProfile(sc->failoverConn)); - sc = sc->slowConn; + sc = sc->failoverConn; sqlConnectIfUnconnected(sc, TRUE); 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 @@ -1349,32 +1349,32 @@ 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. * - * 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 + * If a failover connection is configured in hg.conf, looks up table in the main connection first + * Uses 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,',')) { @@ -1392,34 +1392,34 @@ 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, DEFAULTGETTER, FALSE)) == NULL) { - if (!sc->slowConn) + if (!sc->failoverConn) 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) + // if not found but we have a main connection, check the main connection, too + else if ((sr = sqlUseOrStore(sc->failoverConn, 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]; @@ -2242,70 +2242,70 @@ resCode = mysql_select_db(sc->conn, database); if (resCode!=0) monitorPrint(sc, "SQL_SET_DB_ERROR", "%d", resCode); } sc->hasTableCache = -1; // -1 = undefined return resCode; } static boolean sqlConnChangeDbFailover(struct sqlConnection *sc, char *database, boolean abort) /* only fail if both main and failover cannot connect */ /* This allows to have databases that exist only on one of both servers */ { int mainConnErr = sqlConnChangeDb(sc, database, TRUE); -int foConnErr = sqlConnChangeDb(sc->slowConn, database, sqlConnMustUseFailover(sc)); +int foConnErr = sqlConnChangeDb(sc->failoverConn, database, sqlConnMustUseFailover(sc)); if (mainConnErr!=0 && foConnErr!=0) { if (abort) { struct sqlConnection *errSc; if (foConnErr!=0) - errSc = sc->slowConn; + errSc = sc->failoverConn; else errSc = sc; sqlAbort(sc, "Couldn't set connection database to %s\n%s", database, mysql_error(errSc->conn)); } return FALSE; } return TRUE; } static boolean sqlConnChangeDbMain(struct sqlConnection *sc, char *database, boolean abort) /* change the database of an sql connection */ { int connErr = sqlConnChangeDb(sc, database, abort); if (connErr != 0) { if (abort) sqlAbort(sc, "Couldn't set connection database to %s", database); return FALSE; } return TRUE; } static boolean sqlConnCacheEntrySetDb(struct sqlConnCacheEntry *scce, char *database, boolean abort) /* set the connect cache and connect to the specified database */ { struct sqlConnection *sc = scce->conn; -if (sc->slowConn == NULL) +if (sc->failoverConn == NULL) return sqlConnChangeDbMain(sc, database, abort); else return sqlConnChangeDbFailover(sc, 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) @@ -2532,33 +2532,33 @@ sqlDisconnect(&sc); slFreeList(&dbs); } } struct hash *sqlHashOfDatabases(void) /* Get hash table with names of all databases that are online. */ { if (profiles == NULL) sqlProfileLoad(); struct hash *databases = newHash(8); // add databases found using default profile addProfileDatabases(defaultProfileName, databases); // add databases found in failover profile -char *slowProfName = catTwoStrings(slowProfPrefix, defaultProfileName); -addProfileDatabases(slowProfName, databases); -freez(&slowProfName); +char *failoverProfName = catTwoStrings(failoverProfPrefix, defaultProfileName); +addProfileDatabases(failoverProfName, databases); +freez(&failoverProfName); // add other databases explicitly associated with other profiles struct hashCookie cookie = hashFirst(dbToProfile); struct hashEl *hel; while ((hel = hashNext(&cookie)) != NULL) { char *db = ((struct sqlProfile*)hel->val)->name; hashAdd(databases, db, NULL); } return databases; } struct slName *sqlListOfDatabases(void) /* Get list of all databases that are online. */ { @@ -2691,37 +2691,38 @@ } } } return updateFieldIndex; } char *sqlTableUpdate(struct sqlConnection *conn, char *table) /* Get last update time for table as an SQL string * Note: does NOT work on innoDB! */ { char query[512], **row; struct sqlResult *sr; int updateIx; char *ret; sqlSafef(query, sizeof(query), "show table status like '%s'", table); -// the failover strategy for slowConn does not work for this command, -// as it never returns an error. So need to check for table presence first -if (conn->slowConn && !sqlTableExistsNoCache(conn, table)) - { - sqlConnectIfUnconnected(conn->slowConn, TRUE); - monitorPrintInfo(conn->slowConn, "SQL_TABLE_STATUS_FAILOVER"); - sr = sqlGetResult(conn->slowConn, query); +// the failover strategy for failoverConn does not work for this command, +// as it never returns an error. So we run this on the failover server +// if we have a failover connection and the table is not on the main server +if (conn->failoverConn && !sqlTableExistsOnMain(conn, table)) + { + sqlConnectIfUnconnected(conn->failoverConn, TRUE); + monitorPrintInfo(conn->failoverConn, "SQL_TABLE_STATUS_FAILOVER"); + sr = sqlGetResult(conn->failoverConn, query); } else sr = sqlGetResult(conn, query); updateIx = getUpdateFieldIndex(sr); row = sqlNextRow(sr); if (row == NULL) errAbort("Database table %s doesn't exist", table); ret = cloneString(row[updateIx]); sqlFreeResult(&sr); return ret; } time_t sqlTableUpdateTime(struct sqlConnection *conn, char *table) /* Get last update time for table. * Note: does NOT work on innoDB! */