b628620dca257848dc6e9b18ea8e09585ecaf216 hiram Tue Jun 9 00:07:17 2026 -0700 hgcentralTidy also needs the 64 bit sessionDb and userDb IDs refs #33554 diff --git src/hg/hgcentralTidy/hgcentralTidy.c src/hg/hgcentralTidy/hgcentralTidy.c index 09998eaaaa0..8c040649451 100644 --- src/hg/hgcentralTidy/hgcentralTidy.c +++ src/hg/hgcentralTidy/hgcentralTidy.c @@ -118,94 +118,94 @@ 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, unsigned int id) +int toDaysAgo(char *useString, unsigned long 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=%u)", useString, id); + errAbort("strptime failed for firstUse %s (id=%lu)", useString, id); time_t use, now; now = time(NULL); use = mktime(&tmUse); return difftime(now, use) / (60*60*24); } -void cleanTableSection(char *table, unsigned int startId, unsigned int endId) +void cleanTableSection(char *table, unsigned long startId, unsigned long 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; -unsigned int maxId = startId - 1; +unsigned long maxId = startId - 1; if (startId == 0) maxId = 0; int useCount = 0; boolean deleteThis = FALSE; int delRobotCount = 0; int oldRecCount = 0; -struct slUnsigned *delList = NULL; +struct slUnsignedLong *delList = NULL; time_t cleanSectionStart = time(NULL); struct dyString *dy = dyStringNew(0); while(TRUE) { - verbose(2, "maxId: %u count=%d delCount=%d dc=%d\n", maxId, count, delCount, dc); + verbose(2, "maxId: %lu count=%d delCount=%d dc=%d\n", maxId, count, delCount, dc); sqlSafef(query,sizeof(query), "select id, firstUse, lastUse, useCount from %s" - " where id > %u " + " where id > %lu " " AND lastUse < NOW() - INTERVAL 1 HOUR" " 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]); + maxId = sqlUnsignedLong(row[0]); useCount = sqlSigned(row[3]); int daysAgoFirstUse = toDaysAgo(row[1], maxId); int daysAgoLastUse = toDaysAgo(row[2], maxId); - verbose(3, "id: %u, firstUse: [%s] [%d days ago], lastUse: [%s] [%d days ago], useCount: %d\n" + verbose(3, "id: %lu, 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; } @@ -229,253 +229,253 @@ else if (useCount <= 1) { deleteThis = TRUE; ++delRobotCount; } else if (daysAgoLastUse >= 365) /* reasonable new addition */ { deleteThis = TRUE; ++oldRecCount; } } if (deleteThis) { ++dc; - verbose(3, "TO DELETE id: %u, " + verbose(3, "TO DELETE id: %lu, " "firstUse: [%s] [%d days ago], lastUse: [%s] [%d days ago], useCount: %d\n" , maxId , row[1], daysAgoFirstUse , row[2], daysAgoLastUse , useCount ); - slAddHead(&delList, slUnsignedNew(maxId)); + slAddHead(&delList, slUnsignedLongNew(maxId)); } } sqlFreeResult(&sr); if (rc < 1) break; if (dc > 0) { - struct slUnsigned *i; + struct slUnsignedLong *i; for (i=delList;i;i=i->next) { if (!optionExists("skipDel")) { dyStringClear(dy); - sqlDyStringPrintf(dy, "delete from %s where id=%u", table, i->val); + sqlDyStringPrintf(dy, "delete from %s where id=%lu", table, i->val); sqlUpdate(conn,dy->string); } else { dyStringClear(dy); - sqlDyStringPrintf(dy, "delete from %s where id=%u", table, i->val); + sqlDyStringPrintf(dy, "delete from %s where id=%lu", table, i->val); verbose(4,"GALT DEBUG del dystring = [%s]\n", 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 %s deleted %d, robot recs %s deleted %d\n", optionExists("skipDel")?"would have been":"", oldRecCount, optionExists("skipDel")?"would have been":"", 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(unsigned int *ids, int numIds, char *table, int daysAgo, boolean endSearch) +int binaryIdSearch(unsigned long *ids, int numIds, char *table, int daysAgo, boolean endSearch) /* 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; while (TRUE) { if (a > b) { if (endSearch) return b; else return a; } 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=%u", table, ids[m]); + sqlSafef(query, sizeof(query), "select firstUse from %s where id=%lu", 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 if (daysAgoFirstUse == daysAgo) { if (endSearch) a = m + 1; else b = m - 1; } else { b = m - 1; } break; } else // rare event: record not found, was it deleted? { - errAbort("hgcentralTidy: unexpected error in binaryIdSearch() id %u not found in table %s", ids[m], table); + errAbort("hgcentralTidy: unexpected error in binaryIdSearch() id %lu not found in table %s", ids[m], table); } } } } boolean cleanTable(char *table) /* clean a specific table */ { struct sqlResult *sr; char **row; char query[256]; -unsigned int *ids; +unsigned long *ids; long 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=%ld\n", totalRows); if (totalRows==0) { verbose(1,"table %s is empty!", table); return FALSE; } AllocArray(ids, totalRows); // This is a super-fast query because it only needs to read the index which is cached in memory. sqlSafef(query,sizeof(query), "select id from %s" , table); sr = sqlGetResult(conn, query); int i = 0; while ((row = sqlNextRow(sr)) != NULL) { - ids[i++] = sqlUnsigned(row[0]); + ids[i++] = sqlUnsignedLong(row[0]); if (i >= totalRows) break; } sqlFreeResult(&sr); totalRows = i; // in case they differed. 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 > 8000) // up to 20 years ago 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, FALSE); purgeRangeEnd = binaryIdSearch(ids, totalRows, table, purgeEnd, TRUE); - verbose(1, "manual purge range: purgeStart %d purgeEnd %d rangeStart %d rangeEnd %d rangeSize=%d ids[rs]=%u\n", + verbose(1, "manual purge range: purgeStart %d purgeEnd %d rangeStart %d rangeEnd %d rangeSize=%d ids[rs]=%lu\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, FALSE); 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]=%u\n", + verbose(1, "old cleaner: firstUseAge=%d firstUseIndex = %d day %d: rangeStart %d rangeEnd %d rangeSize=%d ids[oldRangeStart]=%lu\n", firstUseAge, firstUseIndex, day, oldRangeStart, oldRangeEnd, oldRangeEnd-oldRangeStart, ids[oldRangeStart]); // 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, TRUE); - verbose(1, "newOld cleaner: firstUseAge=%d rangeStart %d rangeEnd %d rangeSize=%d ids[newOldRangeStart]=%u\n", + verbose(1, "newOld cleaner: firstUseAge=%d rangeStart %d rangeEnd %d rangeSize=%d ids[newOldRangeStart]=%lu\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, FALSE); int robo1RangeEnd = binaryIdSearch(ids, totalRows, table, 0, TRUE); - verbose(1, "robot cleaner1: twoDayIndex = %d zeroDayIndex %d rangeSize=%d ids[rs]=%u\n", + verbose(1, "robot cleaner1: twoDayIndex = %d zeroDayIndex %d rangeSize=%d ids[rs]=%lu\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, FALSE); robo2RangeEnd = binaryIdSearch(ids, totalRows, table, 6, TRUE); - verbose(1, "robot cleaner2: sevenDayIndex = %d sixDayIndex %d rangeSize=%d ids[rs]=%u\n", + verbose(1, "robot cleaner2: sevenDayIndex = %d sixDayIndex %d rangeSize=%d ids[rs]=%lu\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]); } @@ -488,39 +488,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, FALSE); if ((found >= 0) && (found < totalRows)) - verbose(1, "1 days ago found = %d, id == ids[found] = %u \n", found, ids[found]); + verbose(1, "1 days ago found = %d, id == ids[found] = %lu \n", found, ids[found]); found = binaryIdSearch(ids, totalRows, table, 2, FALSE); if ((found >= 0) && (found < totalRows)) - verbose(1, "2 days ago found = %d, id == ids[found] = %u \n", found, ids[found]); + verbose(1, "2 days ago found = %d, id == ids[found] = %lu \n", found, ids[found]); found = binaryIdSearch(ids, totalRows, table, 30, FALSE); if ((found >= 0) && (found < totalRows)) - verbose(1, "30 days ago found = %d, id == ids[found] = %u \n", found, ids[found]); + verbose(1, "30 days ago found = %d, id == ids[found] = %lu \n", found, ids[found]); */ /* if (daysAgoFirstUse < 14) { hitEnd = TRUE; break; } */ /* if (daysAgoFirstUse < 365) {