3d74e4cad09c9738e4057c0cbb6cf76af01180e4 max Wed Feb 5 07:54:52 2014 -0800 putting existing code into a new function "sqlTableExistsNoCache" as Ineed this code twice diff --git src/hg/lib/jksql.c src/hg/lib/jksql.c index b2b30d7..db4d7ef 100644 --- src/hg/lib/jksql.c +++ src/hg/lib/jksql.c @@ -60,53 +60,59 @@ 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. 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 */ + -1 if not initialized yet, otherwise like a boolean */ struct sqlConnection *slowConn; /* tried if a query fails on the main conn 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") 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 declaration of sqlUseOrStore so the order of functions does not change +// (sqlUseOrStore is needed for sqlTableCacheFindConn), to keep the git diffs clean +static struct sqlResult *sqlUseOrStore(struct sqlConnection *sc, + char *query, ResGetter *getter, boolean abort); + 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); if (isEmpty(val)) return defaultVal; else return val; } static struct sqlProfile *sqlProfileNew(char *profileName, char *host, unsigned int port, char *socket, char *user, char *password) /* create a new profile object */ { @@ -278,31 +284,31 @@ //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 */ { if (sp==NULL || sp->name==NULL) return NULL; -char *slowProfName = catTwoStrings("slow-", sp->name); +char *slowProfName = catTwoStrings(slowProfPrefix, sp->name); struct sqlProfile *slow = sqlProfileGet(slowProfName, database); freez(&slowProfName); return slow; } 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) @@ -621,70 +627,71 @@ { 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; } -// 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) +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) */ { - // to avoid hundreds of repeated table existence checks, we keep the result - // of sqlTableCacheFindConn in the sqlConn object - conn->hasTableCache = FALSE; +bool ret = FALSE; char query[1024]; - sqlSafef(query, sizeof(query), "SELECT 1 FROM %-s LIMIT 0", sqlCkIl(tableListTable)); +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 = conn->slowConn; - conn->slowConn=NULL; - sr = sqlUseOrStore(conn, query, DEFAULTGETTER, FALSE); - conn->slowConn=slowConn; +struct sqlConnection *slowConn = sc->slowConn; +sc->slowConn=NULL; +sr = sqlUseOrStore(sc, query, DEFAULTGETTER, FALSE); +sc->slowConn=slowConn; if (sr!=NULL) { - conn->hasTableCache = TRUE; + ret = TRUE; sqlFreeResult(&sr); } - else if (slowConn!=NULL) - errAbort("no table cache found, although mysql failover (slow-db) is being used."); +return ret; +} + +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; + +// 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) + 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[1024]; char *tableListTable = cfgVal("showTableCache"); sqlSafef(query, sizeof(query), "SELECT count(*) FROM %s WHERE tableName='%s'", tableListTable, table); @@ -889,45 +896,45 @@ 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"); +sc->db=cloneString(database); 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=cloneString(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, TRUE); } @@ -967,60 +974,60 @@ return sc; // we still have a failover profile to setup if (sc==NULL) { // We have a failover connection configured, but no main connection. // This can happen if the requested database exists only on the failover connection // We only create a failover connection in this case and return it as the main one sc = sqlConnRemote(slow->host, slow->port, slow->socket, slow->user, slow->password, database, abort); if (sc!=NULL)// sc can be NULL if abort was FALSE sc->profile = sp; // remember the profile if (monitorFlags & JKSQL_TRACE) - fprintf(stderr, "SQL_FAILOVER_AT_CONNECT"); + fprintf(stderr, "SQL_FAILOVER_AT_CONNECT\n"); return sc; } // at this point we know that sc cannot be NULL sc->profile = sp; // remember the profile // 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 mysql connection // object struct sqlConnection *slowSc; AllocVar(slowSc); slowSc->profile = slow; // remember the profile -slowSc->db = database; -slowSc->hasTableCache = -1; +slowSc->db = cloneString(database); +slowSc->hasTableCache = -1; // -1 => undefined 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 failover sqlConnection object and connect it to the sql server. - * Don't add it to the list of open connections, as it is freed by it's non-failover parent */ + * Don't add it to the list of open connections, as it is freed by its non-failover parent */ { if (sc->conn!=NULL) return sc; char *profName = NULL; if (sc->profile) profName = sc->profile->name; struct sqlProfile *sp = sqlProfileMustGet(profName, sc->db); sqlConnRemoteFillIn(sc, sp->host, sp->port, sp->socket, sp->user, sp->password, sc->db, TRUE, FALSE); return sc; } struct sqlConnection *sqlConnect(char *database) /* Connect to database on default host as default user. */ { struct sqlProfile *defProf = sqlProfileMustGet(NULL, database); @@ -2464,35 +2471,43 @@ hashAdd(databases, db->name, NULL); 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); + // add other databases explicitly associated with other profiles struct hashCookie cookie = hashFirst(dbToProfile); struct hashEl *hel; while ((hel = hashNext(&cookie)) != NULL) - hashAdd(databases, ((struct sqlProfile*)hel->val)->name, 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. */ { /* build hash and convert to names list to avoid duplicates due to visiting * multiple profiles to the same server */ struct hash *dbHash = sqlHashOfDatabases(); struct hashCookie cookie = hashFirst(dbHash); struct hashEl *hel; struct slName *dbs = NULL; while ((hel = hashNext(&cookie)) != NULL) slSafeAddHead(&dbs, slNameNew(hel->name)); hashFree(&dbHash); @@ -2616,34 +2631,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); -if (conn->slowConn) - // the failover strategy does not work for this command, - // as it never returns an error. So default to slowConn instead. +// 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); + monitorPrintInfo(conn->slowConn, "SQL_TABLE_STATUS_FAILOVER"); sr = sqlGetResult(conn->slowConn, 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! */ {