void dbTrash(char *db)
/* dbTrash - drop tables from a database older than specified N hours. */
{
char query[256];
struct sqlResult *sr;
char **row;
int updateTimeIx;
int createTimeIx;
int dataLengthIx;
int indexLengthIx;
int nameIx;
int timeIxUsed;
unsigned long long totalSize = 0;
// expiredTableNames: table exists and is in metaInfo and subject to age limits
struct slName *expiredTableNames = NULL;
struct slName *lostTables = NULL; // tables existing but not in metaInfo
unsigned long long lostTableCount = 0;
struct hash *expiredHash = newHash(10); // as determined by metaInfo
struct hash *notExpiredHash = newHash(10);
struct sqlConnection *conn = sqlConnect(db);
if (extFileCheck)
checkExtFile(conn);
time_t ageSeconds = (time_t)(ageHours * 3600); /* age in seconds */
sqlSafef(query,sizeof(query),"select name,UNIX_TIMESTAMP(lastUse) from %s WHERE "
"lastUse < DATE_SUB(NOW(), INTERVAL %ld SECOND);", CT_META_INFO,ageSeconds);
sr = sqlGetResult(conn, query);
while ((row = sqlNextRow(sr)) != NULL)
hashAddInt(expiredHash, row[0], sqlSigned(row[1]));
sqlFreeResult(&sr);
sqlSafef(query,sizeof(query),"select name,UNIX_TIMESTAMP(lastUse) from %s WHERE "
"lastUse >= DATE_SUB(NOW(), INTERVAL %ld SECOND);",CT_META_INFO,ageSeconds);
sr = sqlGetResult(conn, query);
while ((row = sqlNextRow(sr)) != NULL)
hashAddInt(notExpiredHash, row[0], sqlSigned(row[1]));
sqlFreeResult(&sr);
if (tableStatus) // show table status is very expensive, use only when asked
{
/* run through the table status business to get table size information */
sqlSafef(query,sizeof(query),"show table status");
STATUS_INIT;
while ((row = sqlNextRow(sr)) != NULL)
{
/* if not doing history too, and this is the history table, next row */
if ((!historyToo) && (sameWord(row[nameIx],"history")))
continue;
/* also skip the metaInfo table */
if ((!historyToo) && (sameWord(row[nameIx],CT_META_INFO)))
continue;
/* don't delete the extFile table */
if (sameWord(row[nameIx],CT_EXTFILE))
continue;
SCAN_STATUS;
if (hashLookup(expiredHash,row[nameIx]))
{
slNameAddHead(&expiredTableNames, row[nameIx]);
verbose(3,"%s %ld drop %s\n",row[timeIxUsed], (unsigned long)timep,
row[nameIx]);
/* If sizes are non-NULL, add them up */
if ( ((char *)NULL != row[dataLengthIx]) &&
((char *)NULL != row[indexLengthIx]) )
totalSize += sqlLongLong(row[dataLengthIx])
+ sqlLongLong(row[indexLengthIx]);
hashRemove(expiredHash, row[nameIx]);
}
else
{
if (hashLookup(notExpiredHash,row[nameIx]))
verbose(3,"%s %ld OK %s\n",row[timeIxUsed], (unsigned long)timep,
row[nameIx]);
else
{ /* table exists, but not in metaInfo, is it old enough ? */
if (timep < dropTime)
{
slNameAddHead(&expiredTableNames, row[nameIx]);
verbose(2,"%s %ld dropt %s lost table\n",
row[timeIxUsed], (unsigned long)timep, row[nameIx]);
/* If sizes are non-NULL, add them up */
if ( ((char *)NULL != row[dataLengthIx]) &&
((char *)NULL != row[indexLengthIx]) )
totalSize += sqlLongLong(row[dataLengthIx])
+ sqlLongLong(row[indexLengthIx]);
}
else
verbose(3,"%s %ld OKt %s\n",row[timeIxUsed],
(unsigned long)timep, row[nameIx]);
}
}
}
sqlFreeResult(&sr);
}
else
{ // simple 'show tables' is more efficient than 'show table status'
sqlSafef(query,sizeof(query),"show tables");
sr = sqlGetResult(conn, query);
while ((row = sqlNextRow(sr)) != NULL)
{
if (hashLookup(expiredHash,row[0]))
{
slNameAddHead(&expiredTableNames, row[0]);
time_t lastUse = (time_t)hashIntVal(expiredHash,row[0]);
struct tm *lastUseTm = localtime(&lastUse);
verbose(3,"%4d-%02d-%02d %02d:%02d:%02d %ld drop %s\n",
lastUseTm->tm_year+1900, lastUseTm->tm_mon+1,
lastUseTm->tm_mday, lastUseTm->tm_hour, lastUseTm->tm_min,
lastUseTm->tm_sec, (unsigned long)lastUse,row[0]);
hashRemove(expiredHash, row[0]);
}
else if (hashLookup(notExpiredHash,row[0]))
{
time_t lastUse = (time_t)hashIntVal(notExpiredHash,row[0]);
struct tm *lastUseTm = localtime(&lastUse);
verbose(3,"%4d-%02d-%02d %02d:%02d:%02d %ld OK %s\n",
lastUseTm->tm_year+1900, lastUseTm->tm_mon+1,
lastUseTm->tm_mday, lastUseTm->tm_hour, lastUseTm->tm_min,
lastUseTm->tm_sec, (unsigned long)lastUse,row[0]);
}
else
{
struct slName *el = slNameNew(row[0]);
slAddHead(&lostTables, el);
}
}
sqlFreeResult(&sr);
lostTableCount = slCount(lostTables);
// If tables exist, but not in metaInfo, check their age to expire them.
// It turns out even this show table status is slow too, so, only
// run thru it if asked to eliminate lost tables. It is better to
// do this operation with the stand-alone perl script on the customTrash
// database machine.
if (delLostTable && lostTables)
{
struct slName *el;
for (el = lostTables; el != NULL; el = el->next)
{
if (sameWord(el->name,"history"))
continue;
if (sameWord(el->name,CT_META_INFO))
continue;
if (sameWord(el->name,CT_EXTFILE))
continue;
boolean oneTableOnly = FALSE; // protect against multiple tables
/* get table time information to see if it is expired */
sqlSafef(query,sizeof(query),"show table status like '%s'", el->name);
STATUS_INIT;
while ((row = sqlNextRow(sr)) != NULL)
{
if (oneTableOnly)
errAbort("ERROR: query: '%s' returned more than one table "
"name\n", query);
else
oneTableOnly = TRUE;
if (differentWord(row[nameIx], el->name))
errAbort("ERROR: query: '%s' did not return table name '%s' != '%s'\n", query, el->name, row[nameIx]);
SCAN_STATUS;
if (timep < dropTime)
{
slNameAddHead(&expiredTableNames, row[nameIx]);
verbose(2,"%s %ld dropt %s lost table\n",
row[timeIxUsed], (unsigned long)timep, row[nameIx]);
}
else
verbose(3,"%s %ld OKt %s\n",
row[timeIxUsed], (unsigned long)timep, row[nameIx]);
}
sqlFreeResult(&sr);
}
}
}
/* perhaps the table was already dropped, but not from the metaInfo */
struct hashEl *elList = hashElListHash(expiredHash);
struct hashEl *el;
for (el = elList; el != NULL; el = el->next)
{
verbose(2,"%s exists in %s only\n", el->name, CT_META_INFO);
if (drop)
ctTouchLastUse(conn, el->name, FALSE); /* removes metaInfo row */
}
if (drop)
{
char comment[256];
if (expiredTableNames)
{
struct slName *el;
int droppedCount = 0;
/* customTrash DB user permissions do not have permissions to
* drop tables. Must use standard special user that has all
* permissions. If we are not using the standard user at this
* point, then switch to it.
*/
if (sameWord(db,CUSTOM_TRASH))
{
sqlDisconnect(&conn);
conn = sqlConnect(db);
}
for (el = expiredTableNames; el != NULL; el = el->next)
{
verbose(2,"# drop %s\n", el->name);
sqlDropTable(conn, el->name);
ctTouchLastUse(conn, el->name, FALSE); /* removes metaInfo row */
++droppedCount;
}
/* add a comment to the history table and finish up connection */
if (tableStatus)
safef(comment, sizeof(comment), "Dropped %d tables with "
"total size %llu, %llu lost tables",
droppedCount, totalSize, lostTableCount);
else
safef(comment, sizeof(comment),
"Dropped %d tables, no size info, %llu lost tables",
droppedCount, lostTableCount);
verbose(2,"# %s\n", comment);
hgHistoryComment(conn, "%s", comment);
}
else
{
safef(comment, sizeof(comment),
"Dropped no tables, none expired, %llu lost tables",
lostTableCount);
verbose(2,"# %s\n", comment);
}
}
else
{
char comment[256];
if (expiredTableNames)
{
int droppedCount = slCount(expiredTableNames);
if (tableStatus)
safef(comment, sizeof(comment), "Would have dropped %d tables with "
"total size %llu, %llu lost tables",
droppedCount, totalSize, lostTableCount);
else
safef(comment, sizeof(comment),
"Would have dropped %d tables, no size info, %llu lost tables",
droppedCount, lostTableCount);
verbose(2,"# %s\n", comment);
}
else
{
safef(comment, sizeof(comment),
"Would have dropped no tables, none expired, %llu lost tables",
lostTableCount);
verbose(2,"# %s\n", comment);
}
}
sqlDisconnect(&conn);
}
void dbTrash(char *db)
/* dbTrash - drop tables from a database older than specified N hours. */
{
char query[256];
struct sqlResult *sr;
char **row;
int updateTimeIx;
int createTimeIx;
int dataLengthIx;
int indexLengthIx;
int nameIx;
int timeIxUsed;
unsigned long long totalSize = 0;
struct slName *tableNames = NULL; /* subject to age limits */
struct hash *expiredHash = newHash(10);
struct hash *notExpiredHash = newHash(10);
struct sqlConnection *conn = sqlConnect(db);
if (extFileCheck)
checkExtFile(conn);
time_t ageSeconds = (time_t)(ageHours * 3600); /* age in seconds */
safef(query,sizeof(query),"select name from %s WHERE "
"lastUse < DATE_SUB(NOW(), INTERVAL %ld SECOND);", CT_META_INFO,ageSeconds);
sr = sqlGetResult(conn, query);
while ((row = sqlNextRow(sr)) != NULL)
hashAddInt(expiredHash, row[0], 1);
sqlFreeResult(&sr);
safef(query,sizeof(query),"select name from %s WHERE "
"lastUse >= DATE_SUB(NOW(), INTERVAL %ld SECOND);",CT_META_INFO,ageSeconds);
sr = sqlGetResult(conn, query);
while ((row = sqlNextRow(sr)) != NULL)
hashAddInt(notExpiredHash, row[0], 1);
sqlFreeResult(&sr);
/* run through the table status business to get table size information */
safef(query,sizeof(query),"show table status");
sr = sqlGetResult(conn, query);
nameIx = sqlFieldColumn(sr, "Name");
createTimeIx = sqlFieldColumn(sr, "Create_time");
updateTimeIx = sqlFieldColumn(sr, "Update_time");
dataLengthIx = sqlFieldColumn(sr, "Data_length");
indexLengthIx = sqlFieldColumn(sr, "Index_length");
while ((row = sqlNextRow(sr)) != NULL)
{
struct tm tm;
time_t timep = 0;
/* if not doing history too, and this is the history table, next row */
if ((!historyToo) && (sameWord(row[nameIx],"history")))
continue;
/* also skip the metaInfo table */
if ((!historyToo) && (sameWord(row[nameIx],CT_META_INFO)))
continue;
/* don't delete the extFile table */
if (sameWord(row[nameIx],CT_EXTFILE))
continue;
/* Update_time is sometimes NULL on MySQL 5
* so if it fails, then check the Create_time
*/
timeIxUsed = updateTimeIx;
if ((row[updateTimeIx] != NULL) &&
(sscanf(row[updateTimeIx], "%4d-%2d-%2d %2d:%2d:%2d",
&(tm.tm_year), &(tm.tm_mon), &(tm.tm_mday),
&(tm.tm_hour), &(tm.tm_min), &(tm.tm_sec)) != 6) )
{
timeIxUsed = createTimeIx;
if (sscanf(row[createTimeIx], "%4d-%2d-%2d %2d:%2d:%2d",
&(tm.tm_year), &(tm.tm_mon), &(tm.tm_mday),
&(tm.tm_hour), &(tm.tm_min), &(tm.tm_sec)) != 6)
{
verbose(2,"%s %s %s\n",
row[createTimeIx],row[updateTimeIx],row[nameIx]);
errAbort("could not parse date %s or %s on table %s\n",
row[createTimeIx], row[updateTimeIx], row[nameIx]);
}
}
tm.tm_year -= 1900;
tm.tm_mon -= 1;
tm.tm_isdst = -1; /* do not know timezone, figure it out */
timep = mktime(&tm);
if (hashLookup(expiredHash,row[nameIx]))
{
slNameAddHead(&tableNames, row[nameIx]);
verbose(3,"%s %ld drop %s\n",row[timeIxUsed], (unsigned long)timep,
row[nameIx]);
/* If sizes are non-NULL, add them up */
if ( ((char *)NULL != row[dataLengthIx]) &&
((char *)NULL != row[indexLengthIx]) )
totalSize += sqlLongLong(row[dataLengthIx])
+ sqlLongLong(row[indexLengthIx]);
hashRemove(expiredHash, row[nameIx]);
}
else
{
if (hashLookup(notExpiredHash,row[nameIx]))
verbose(3,"%s %ld OK %s\n",row[timeIxUsed], (unsigned long)timep,
row[nameIx]);
else
{ /* table exists, but not in metaInfo, is it old enough ? */
if (timep < dropTime)
{
slNameAddHead(&tableNames, row[nameIx]);
verbose(2,"%s %ld dropt %s\n",
row[timeIxUsed], (unsigned long)timep, row[nameIx]);
/* If sizes are non-NULL, add them up */
if ( ((char *)NULL != row[dataLengthIx]) &&
((char *)NULL != row[indexLengthIx]) )
totalSize += sqlLongLong(row[dataLengthIx])
+ sqlLongLong(row[indexLengthIx]);
}
else
verbose(3,"%s %ld OKt %s\n",row[timeIxUsed],
(unsigned long)timep, row[nameIx]);
}
}
}
sqlFreeResult(&sr);
/* perhaps the table was already dropped, but not from the metaInfo */
struct hashEl *elList = hashElListHash(expiredHash);
struct hashEl *el;
for (el = elList; el != NULL; el = el->next)
{
verbose(2,"%s exists in %s only\n", el->name, CT_META_INFO);
if (drop)
ctTouchLastUse(conn, el->name, FALSE); /* removes metaInfo row */
}
if (drop)
{
if (tableNames)
{
char comment[256];
struct slName *el;
int droppedCount = 0;
/* customTrash DB user permissions do not have permissions to
* drop tables. Must use standard special user that has all
* permissions. If we are not using the standard user at this
* point, then switch to it.
*/
if (sameWord(db,CUSTOM_TRASH))
{
sqlDisconnect(&conn);
conn = sqlConnect(db);
}
for (el = tableNames; el != NULL; el = el->next)
{
verbose(2,"# drop %s\n", el->name);
sqlDropTable(conn, el->name);
ctTouchLastUse(conn, el->name, FALSE); /* removes metaInfo row */
++droppedCount;
}
/* add a comment to the history table and finish up connection */
safef(comment, sizeof(comment),
"Dropped %d tables with total size %llu", droppedCount, totalSize);
verbose(2,"# %s\n", comment);
hgHistoryComment(conn, comment);
}
}
sqlDisconnect(&conn);
}