Deleting orphaned files
I found that there are big amount of orphaned files in store/[data]/[xchan]. I couldn't find item.body or iconfig.v rows for them. But I'm not sure it is really safe to delete them, I could something overlook. 🤷♂️
Is this script correct? If it's not in item/config, then can I delete rows where hash is in photo.resource_ip and delete files? Could some1 look at it a little bit, pls? 🙏
BTW, it's very slow script, it takes about 2h for 1 day. I will have to optimize yet.
(deleting and cleanup is commented yet...)
#!/bin/bash
# === Checking input ===
if [[ -z "$1" ]]; then
echo "Usage: $0 YYYYMMDD"
exit 1
fi
INPUT_DATE="$1"
DATE_START=$(date -d "$INPUT_DATE" +%s)
DATE_END=$(date -d "$INPUT_DATE +1 day" +%s)
if [[ -z "$DATE_START" || -z "$DATE_END" ]]; then
echo "Invalid date"
exit 1
fi
# === CONFIG ===
STORE_DIR="/var/www/hubzilla/store/[data]/[xchan]"
DB_NAME="hubzilla"
DB_USER="hubzilla"
DB_PASS="verysafepassword"
TEMP_ALL="hubzilla_files-$INPUT_DATE.txt"
TEMP_DELETE="hubzilla_files_to_delete-$INPUT_DATE.txt"
TMP_TABLE="tmp_cleanup"
CSV_FILE="hubzilla_files_load.csv"
echo $(date)
echo "Working on day: $INPUT_DATE (from $(date -d @$DATE_START) to $(date -d @$DATE_END))"
# === 1. Find files ===
find "$STORE_DIR" -type f -newermt "@$DATE_START" ! -newermt "@$DATE_END" > "$TEMP_ALL"
echo "Found: $(wc -l < "$TEMP_ALL")"
# === 2. TMP table ===
mysql -u "$DB_USER" -p"$DB_PASS" "$DB_NAME" < "$CSV_FILE"
while IFS= read -r file; do
filename=$(basename "$file")
uuid_hash="${filename%-*}"
echo "\"$file\",\"$filename\",\"$uuid_hash\"" >> "$CSV_FILE"
done < "$TEMP_ALL"
mysql --local-infile=1 -u "$DB_USER" -p"$DB_PASS" "$DB_NAME" < "$TEMP_DELETE"
mysql -N -u "$DB_USER" -p"$DB_PASS" "$DB_NAME" -e \
"SELECT DISTINCT uuid_hash FROM $TMP_TABLE ORDER BY uuid_hash;" > uuid_list.txt
TOTAL=$(wc -l < uuid_list.txt)
echo "Uniq hash: $TOTAL"
COUNTER=0
while IFS= read -r uuid_hash; do
COUNTER=$((COUNTER + 1))
PERCENT=$((COUNTER * 100 / TOTAL))
if (( COUNTER % 10 == 0 )); then
echo "→ Processed $COUNTER / $TOTAL ($PERCENT %)"
fi
FOUND=0
EXISTS=$(mysql -N -u "$DB_USER" -p"$DB_PASS" "$DB_NAME" \
-e "SELECT 1 FROM item WHERE body LIKE '%$uuid_hash%' LIMIT 1;")
[[ -n "$EXISTS" ]] && FOUND=1
if [[ $FOUND -eq 0 ]]; then
EXISTS=$(mysql -N -u "$DB_USER" -p"$DB_PASS" "$DB_NAME" \
-e "SELECT 1 FROM iconfig WHERE v LIKE '%$uuid_hash%' LIMIT 1;")
[[ -n "$EXISTS" ]] && FOUND=1
fi
if [[ $FOUND -eq 0 ]]; then
EXISTS=$(mysql -N -u "$DB_USER" -p"$DB_PASS" "$DB_NAME" \
-e "SELECT id FROM photo WHERE resource_id LIKE '%$uuid_hash%' LIMIT 1;")
if [[ -n "$EXISTS" ]]; then
# mysql -u "$DB_USER" -p"$DB_PASS" "$DB_NAME" -e \
# "DELETE FROM photo WHERE resource_id LIKE '%$uuid_hash%';"
mysql -N -u "$DB_USER" -p"$DB_PASS" "$DB_NAME" -e \
"SELECT fullpath FROM $TMP_TABLE WHERE uuid_hash = '$uuid_hash';" >> "$TEMP_DELETE"
echo " ✖ Not used - adding all files with hash $uuid_hash"
continue
fi
fi
if [[ $FOUND -eq 0 ]]; then
echo " ✖ Not used - adding all files with hash $uuid_hash"
mysql -N -u "$DB_USER" -p"$DB_PASS" "$DB_NAME" -e \
"SELECT fullpath FROM $TMP_TABLE WHERE uuid_hash = '$uuid_hash';" >> "$TEMP_DELETE"
fi
done < uuid_list.txt
# === 5. Delete ===
echo "Files for delete: $(wc -l < "$TEMP_DELETE")"
# while IFS= read -r file; do
# if [[ -f "$file" ]]; then
# rm "$file"
# echo "Deleted: $file"
# fi
# done < "$TEMP_DELETE"
# === 6. Cleanup ===
# mysql -u "$DB_USER" -p"$DB_PASS" "$DB_NAME" -e "DROP TABLE IF EXISTS $TMP_TABLE;"
echo "Done."
echo $(date)
