f89a906b46c4848dee65ca534ad0962531dbb2e6 galt Thu Apr 25 00:12:09 2024 -0700 Fix for hgcentralTidy cleaner, handle unsigned int ID over 2 billion correctly. This will work until we someday exceed the 4 billion sessions which make the maximum the field can take. I did not want to work, but the failures were too important to ignore. If the tables are not cleaned regularly, they grow very fast indeed. I have manually run the purge to catch up. We should be good now. diff --git src/hg/hgcentralTidy/hgcentralTidy.c src/hg/hgcentralTidy/hgcentralTidy.c index eda46e9..fcd1d51 100644 --- src/hg/hgcentralTidy/hgcentralTidy.c +++ src/hg/hgcentralTidy/hgcentralTidy.c @@ -117,91 +117,92 @@ safef(cmdLine, sizeof(cmdLine), "echo '%s'|mail -s 'WARNING hgcentral cleanup detected data_length max size %d GB exceeded' %s" , msg , squealSize , emailList ); system(cmdLine); squealed = TRUE; } sqlFreeResult(&sr); return squealed; } -int toDaysAgo(char *useString, int id) +int toDaysAgo(char *useString, unsigned int id) /* Convert mysql datetime into days ago */ { struct tm tmUse; zeroBytes(&tmUse, sizeof(struct tm)); if (!strptime(useString, "%Y-%m-%d %H:%M:%S", &tmUse)) - errAbort("strptime failed for firstUse %s (id=%d)", useString, id); + errAbort("strptime failed for firstUse %s (id=%u)", useString, id); time_t use, now; now = time(NULL); use = mktime(&tmUse); return difftime(now, use) / (60*60*24); } -void cleanTableSection(char *table, int startId, int endId) +void cleanTableSection(char *table, unsigned int startId, unsigned int endId) /* clean a specific table section */ { struct sqlResult *sr; char **row; char query[256]; int rc = 0; int dc = 0; int delCount = 0; int count = 0; -int maxId = startId - 1; +unsigned int maxId = startId - 1; +if (startId == 0) maxId = 0; int useCount = 0; boolean deleteThis = FALSE; int delRobotCount = 0; int oldRecCount = 0; -struct slInt *delList = NULL; +struct slUnsigned *delList = NULL; time_t cleanSectionStart = time(NULL); struct dyString *dy = dyStringNew(0); while(TRUE) { - verbose(2, "maxId: %d count=%d delCount=%d dc=%d\n", maxId, count, delCount, dc); + verbose(2, "maxId: %u count=%d delCount=%d dc=%d\n", maxId, count, delCount, dc); sqlSafef(query,sizeof(query), "select id, firstUse, lastUse, useCount from %s" - " where id > %d order by id limit %d" + " where id > %u order by id limit %d" , table , maxId , chunkSize ); sr = sqlGetResult(conn, query); rc = 0; dc = 0; dyStringClear(dy); while ((row = sqlNextRow(sr)) != NULL) { ++count; ++rc; maxId = sqlUnsigned(row[0]); useCount = sqlSigned(row[3]); int daysAgoFirstUse = toDaysAgo(row[1], maxId); int daysAgoLastUse = toDaysAgo(row[2], maxId); - verbose(3, "id: %d, firstUse: [%s] [%d days ago], lastUse: [%s] [%d days ago], useCount: %d\n" + verbose(3, "id: %u, firstUse: [%s] [%d days ago], lastUse: [%s] [%d days ago], useCount: %d\n" , maxId , row[1], daysAgoFirstUse , row[2], daysAgoLastUse , useCount ); deleteThis = FALSE; if (sameString(table, sessionDbTableName)) { if (daysAgoLastUse >= 14) { deleteThis = TRUE; ++oldRecCount; } @@ -222,131 +223,130 @@ else if ((daysAgoFirstUse >= 2) && useCount <= 1) { deleteThis = TRUE; ++delRobotCount; } else if (daysAgoLastUse >= 365) /* reasonable new addition */ { deleteThis = TRUE; ++oldRecCount; } } if (deleteThis) { ++dc; - verbose(3, "TO DELETE id: %d, " + verbose(3, "TO DELETE id: %u, " "firstUse: [%s] [%d days ago], lastUse: [%s] [%d days ago], useCount: %d\n" , maxId , row[1], daysAgoFirstUse , row[2], daysAgoLastUse , useCount ); - slAddHead(&delList, slIntNew(maxId)); + slAddHead(&delList, slUnsignedNew(maxId)); } } sqlFreeResult(&sr); if (rc < 1) break; if (dc > 0) { - struct slInt *i; + struct slUnsigned *i; for (i=delList;i;i=i->next) { dyStringClear(dy); - sqlDyStringPrintf(dy, "delete from %s where id=%d", table, i->val); + sqlDyStringPrintf(dy, "delete from %s where id=%u", table, i->val); sqlUpdate(conn,dy->string); } slFreeList(&delList); } delCount+=dc; if (maxId >= endId) { break; // we have done enough } verbose(3, "sleeping %d seconds\n", chunkWait);fflush(stderr); sleep(chunkWait); verbose(3, "awake\n");fflush(stderr); } verbose(1, "old recs deleted %d, robot recs deleted %d\n", oldRecCount, delRobotCount);fflush(stderr); time_t cleanEnd = time(NULL); int minutes = difftime(cleanEnd, cleanSectionStart) / 60; verbose(1, "%s\n", ctime(&cleanEnd)); verbose(1, "%d minutes\n\n", minutes); } -int binaryIdSearch(int *ids, int numIds, char *table, int daysAgo) +int binaryIdSearch(unsigned int *ids, int numIds, char *table, int daysAgo) /* Find the array index in ids which holds the id that contains * the oldest record satisfying the daysAgo criterion. * If not found, return -1 */ { char query[256]; int a = 0; int b = numIds - 1; int m = 0; -//verbose(1, "\nDEBUG:\n"); // DEBUG REMOVE while (TRUE) { if (a > b) return a; // is this right? m = (b + a) / 2; //verbose(1,"bin a=%d, b=%d, m=%d\n", a, b, m); while (TRUE) { - sqlSafef(query, sizeof(query), "select firstUse from %s where id=%d", table, ids[m]); + sqlSafef(query, sizeof(query), "select firstUse from %s where id=%u", table, ids[m]); char *firstUse = sqlQuickString(conn,query); if (firstUse) { int daysAgoFirstUse = toDaysAgo(firstUse, ids[m]); //verbose(1, "DEBUG: %d %d %s %d\n", m, ids[m], firstUse, daysAgoFirstUse); // DEBUG REMOVE if (daysAgoFirstUse > daysAgo) { a = m + 1; } else { b = m - 1; } break; } else // rare event: record not found, was it deleted? { - errAbort("hgcentralTidy: unexpected error in binaryIdSearch() id %d not found in table %s", ids[m], table); + errAbort("hgcentralTidy: unexpected error in binaryIdSearch() id %u not found in table %s", ids[m], table); } } } } boolean cleanTable(char *table) /* clean a specific table */ { struct sqlResult *sr; char **row; char query[256]; -int *ids; +unsigned int *ids; int totalRows = 0; boolean squealed = FALSE; time_t cleanStart = time(NULL); verbose(1, "-------------------\n"); verbose(1, "Cleaning table %s\n", table); verbose(1, "%s\n", ctime(&cleanStart)); totalRows = sqlTableSize(conn, table); verbose(1,"totalRows=%d\n", totalRows); if (totalRows==0) { verbose(1,"table %s is empty!", table); @@ -370,85 +370,86 @@ int purgeRangeStart = -1; int purgeRangeEnd = -1; if (optionExists("purgeStart")) // manual purge range specified { purgeStart = optionInt("purgeStart", -1); purgeEnd = optionInt("purgeEnd", -1); if (purgeStart < 1 || purgeStart > 720) errAbort("Invalid purgeStart"); if (purgeEnd < 0) purgeEnd = 0; if (purgeStart < purgeEnd) errAbort("purgeStart should be greater than purgeEnd (in days ago)"); purgeRangeStart = binaryIdSearch(ids, totalRows, table, purgeStart); purgeRangeEnd = binaryIdSearch(ids, totalRows, table, purgeEnd); - verbose(1, "manual purge range: purgeStart %d purgeEnd %d rangeStart %d rangeEnd %d rangeSize=%d ids[rs]=%d\n", + verbose(1, "manual purge range: purgeStart %d purgeEnd %d rangeStart %d rangeEnd %d rangeSize=%d ids[rs]=%u\n", purgeStart, purgeEnd, purgeRangeStart, purgeRangeEnd, purgeRangeEnd-purgeRangeStart, ids[purgeRangeStart]); if (!optionExists("dryRun")) cleanTableSection(table, ids[purgeRangeStart], ids[purgeRangeEnd]); } else // figure out purge-ranges automatically { int firstUseAge = 0; if (sameString(table, sessionDbTableName)) firstUseAge = 14; if (sameString(table, userDbTableName)) firstUseAge = 365; sqlSafef(query,sizeof(query), "select dayofweek(now())"); int day = sqlQuickNum(conn, query); // These old records take a long time to go through, 5k sessionDb to 55k userDb old recs to look at, // and typically produce only a few hundred deletions. // they are growing slowly and expire rarely, so we don't need to scan them // frequently and aggressively. So ONLY scan them once per week by doing 1/7 per day. // Also don't need to worry much about the // borders of the split-over-7-days divisions shifting much because the set is so nearly static. YAWN. + int firstUseIndex = binaryIdSearch(ids, totalRows, table, firstUseAge); int oldRangeSize = (firstUseIndex - 0) / 7; int oldRangeStart = oldRangeSize * (day-1); int oldRangeEnd = oldRangeStart + oldRangeSize; - verbose(1, "old cleaner: firstUseAge=%d firstUseIndex = %d day %d: rangeStart %d rangeEnd %d rangeSize=%d ids[oldRangeStart]=%d\n", + verbose(1, "old cleaner: firstUseAge=%d firstUseIndex = %d day %d: rangeStart %d rangeEnd %d rangeSize=%d ids[oldRangeStart]=%u\n", firstUseAge, firstUseIndex, day, oldRangeStart, oldRangeEnd, oldRangeEnd-oldRangeStart, ids[oldRangeStart]); //int oldRangeStart = 0; //int oldRangeEnd = firstUseIndex; - //verbose(1, "old cleaner: firstUseAge=%d firstUseIndex = %d rangeStart %d rangeEnd %d rangeSize=%d ids[firstUseIndex]=%d\n", + //verbose(1, "old cleaner: firstUseAge=%d firstUseIndex = %d rangeStart %d rangeEnd %d rangeSize=%d ids[firstUseIndex]=%u\n", //firstUseAge, firstUseIndex, oldRangeStart, oldRangeEnd, oldRangeEnd-oldRangeStart, ids[firstUseIndex]); // newly old can be expected to have some delete action // these records have newly crossed the threshold into being old enough to have possibly expired. int newOldRangeStart = firstUseIndex; int newOldRangeEnd = binaryIdSearch(ids, totalRows, table, firstUseAge - 1); - verbose(1, "newOld cleaner: firstUseAge=%d rangeStart %d rangeEnd %d rangeSize=%d ids[newOldRangeStart]=%d\n", + verbose(1, "newOld cleaner: firstUseAge=%d rangeStart %d rangeEnd %d rangeSize=%d ids[newOldRangeStart]=%u\n", firstUseAge, newOldRangeStart, newOldRangeEnd, newOldRangeEnd-newOldRangeStart, ids[newOldRangeStart]); // this is the main delete action of cleaning out new robots (20k to 50k or more) int robo1RangeStart = binaryIdSearch(ids, totalRows, table, 2); int robo1RangeEnd = binaryIdSearch(ids, totalRows, table, 1); - verbose(1, "robot cleaner1: twoDayIndex = %d oneDayIndex %d rangeSize=%d ids[rs]=%d\n", + verbose(1, "robot cleaner1: twoDayIndex = %d oneDayIndex %d rangeSize=%d ids[rs]=%u\n", robo1RangeStart, robo1RangeEnd, robo1RangeEnd-robo1RangeStart, ids[robo1RangeStart]); int robo2RangeStart = -1; int robo2RangeEnd = -1; if (sameString(table, userDbTableName)) { // secondary robot cleaning only for userDb., produces a somewhat lesser, perhaps 3 to 5k deletions robo2RangeStart = binaryIdSearch(ids, totalRows, table, 7); robo2RangeEnd = binaryIdSearch(ids, totalRows, table, 6); - verbose(1, "robot cleaner2: sevenDayIndex = %d sixDayIndex %d rangeSize=%d ids[rs]=%d\n", + verbose(1, "robot cleaner2: sevenDayIndex = %d sixDayIndex %d rangeSize=%d ids[rs]=%u\n", robo2RangeStart, robo2RangeEnd, robo2RangeEnd-robo2RangeStart, ids[robo2RangeStart]); } /* cannot clean until we have all the ranges determined since deleting messes up binSearch */ if (!optionExists("dryRun")) { verbose(1, "old cleaner:\n"); cleanTableSection(table, ids[oldRangeStart], ids[oldRangeEnd]); } if (!optionExists("dryRun")) { verbose(1, "newOld cleaner:\n"); cleanTableSection(table, ids[newOldRangeStart], ids[newOldRangeEnd]); } @@ -461,39 +462,39 @@ if (sameString(table, userDbTableName)) { if (!optionExists("dryRun")) { verbose(1, "robot cleaner2:\n"); cleanTableSection(table, ids[robo2RangeStart], ids[robo2RangeEnd]); } } } /* int found = binaryIdSearch(ids, totalRows, table, 1); if ((found >= 0) && (found < totalRows)) - verbose(1, "1 days ago found = %d, id == ids[found] = %d \n", found, ids[found]); + verbose(1, "1 days ago found = %d, id == ids[found] = %u \n", found, ids[found]); found = binaryIdSearch(ids, totalRows, table, 2); if ((found >= 0) && (found < totalRows)) - verbose(1, "2 days ago found = %d, id == ids[found] = %d \n", found, ids[found]); + verbose(1, "2 days ago found = %d, id == ids[found] = %u \n", found, ids[found]); found = binaryIdSearch(ids, totalRows, table, 30); if ((found >= 0) && (found < totalRows)) - verbose(1, "30 days ago found = %d, id == ids[found] = %d \n", found, ids[found]); + verbose(1, "30 days ago found = %d, id == ids[found] = %u \n", found, ids[found]); */ /* if (daysAgoFirstUse < 14) { hitEnd = TRUE; break; } */ /* if (daysAgoFirstUse < 365) {