Troubleshoot missing attachments in Confluence Data Center

Summary

Compare the attachment paths between the database and file storage in the Confluence version 4 storage layout format to identify any missing attachment files.

Background

Before the introduction of the v4 attachment storage structure in Confluence 8.1 onwards, you could encounter a situation in which the attachment itself was saved but wasn't located in the specific directory that it was expected to be in. Often, this was due to an incomplete earlier page move operation. The article How to resolve missing attachments in Confluence discusses how to handle those situations.

However, Confluence continues to depend on the underlying file system to manage and uphold the integrity of stored files. If attachments are found to be missing, the following steps will guide you through scanning the Confluence database and file system to identify discrepancies between the attachments that should be present according to the database and those that are actually available in the file system. This process will generate a report detailing the missing attachments.

Environment

Confluence Data Center 8.1 or later

Solution

To address the issue of missing attachments, the initial step is to verify their presence on the disk. Additionally, it is important to check for any permission-related problems or determine if the files are stored in a different location. Below are the messages that indicate issues related to the missing file. Typically, if a file is absent from the disk, you will encounter a message containing a string similar to:

atlassian-confluence.log

SQL statements can be utilized to identify the locations of attachments, while a bash script can be used to compare the relative paths on the disk. This approach allows us to extract results and determine if any files are missing.

• Please run the SQL query provided below and save the output as a text file (attachments.txt). Then, transfer the file to your cluster's shared location (for example, /opt/atlassian/shared/attachments.txt).

  select '/opt/atlassian/shared/attachments/v4/' || MOD(MOD(case when prevver is null then contentid else prevver end, 65535), 256) || '/' || TRUNC(MOD(case when prevver is null then contentid else prevver end, 65535) / 256) || '/' || case when prevver is null then contentid else prevver end || '/' || case when prevver is null then contentid else prevver end || '.' || version as Disk_Location from content where contenttype = 'ATTACHMENT' and pageid in ( select c.contentid FROM content c JOIN SPACES s ON s.spaceid = c.spaceid where c.contenttype = 'PAGE' and s.spaceid is not null AND c.prevver IS NULL);

• Save the below Bash script in a file (missing_attachments.sh) that will read a list of file paths from a source file (attachments.txt) and check if each file or folder exists in the specified directory. It will then output a list of missing files or directories to a specified output file (missing_attachments.txt).

  #!/bin/bash # Define the source file and the output file SOURCE_FILE="/opt/atlassian/shared/attachments.txt" OUTPUT_FILE="/opt/atlassian/shared/missing_attachments.txt" # Clear the output file if it exists > "$OUTPUT_FILE" # Read each line from the source file while IFS= read -r line; do # Remove the surrounding quotes from the line, if any line=$(echo "$line" | sed 's/^"//;s/"$//') # Check if the file or directory exists if [ ! -e "$line" ]; then # If it doesn't exist, write it to the output file echo "$line" >> "$OUTPUT_FILE" fi done < "$SOURCE_FILE" echo "Missing files or directories have been listed in $OUTPUT_FILE"

• Make the Script Executable: Run the following command to make the script executable 

  chmod +x missing_attachments.sh

• Run the Script: Execute the script by running the below, and the output file for missing attachments will be created under: /opt/atlassian/shared/missing_attachments.txt   

  ./missing_attachments.sh

The output of the script will enable you to check for these files also in any existing backup.