Error in Populating Duplicate File Paths in Venio Systems

I.  Overview

This article explains the error that occurs when running the "populate duplicate filepaths for tags or media" process in Venio Systems workspaces. The error is typically a violation of a PRIMARY KEY constraint in a temporary table during the computation of duplicate file paths. It covers the relevant SQL tables, fields, stored procedures (sprocs), potential causes, and troubleshooting steps, including an example root cause from a real scenario.

II. Relevant SQL Tables and Fields

The primary table involved in this process is tbl_Ex_fileinfo. This table stores metadata about files and media in the project database, including grouping information for file families (e.g., emails with attachments).

• fileid: Unique identifier for each file or media item. This is often used as a key in queries and computations.
• groupid: Identifier for the group or family that the file belongs to. Files in the same family (e.g., a parent email and its attachments) share the same groupid.
• OriginalGroupId: Stores the original group identifier before any modifications, such as deletions or regrouping. Useful for tracking changes in file relationships.
• isparent: A flag (BIT type, 1 for true) indicating if the file is the parent in its group. In a healthy file family, only one file should have isparent=1 per groupid.
• parentid: References the fileid of the parent file in the group. This helps maintain hierarchical relationships within file families.

These fields are crucial for maintaining file relationships and detecting duplicates based on file paths. The computation process relies on accurate grouping to avoid inserting duplicate keys into temporary tables.

III. Stored Procedures (Sprocs) Involved

The process uses two main stored procedures to compute and populate duplicate file paths:

sp_ex_ComputeDuplicateFilePath

This sproc computes duplicate file paths for specified media or tags. It processes the file paths and groups them, often inserting data into a temporary table like #dupFilePath. Parameters:

• @UserId (BIGINT): The user ID running the process (e.g., 1 for superuser).
• @AddFileName (BIT): Flag to include file names in the path computation (1 to include, 0 to exclude).
• @SourceType (NVARCHAR(50)): Specifies the source type, either 'MEDIA' or 'TAG'.
• @SourceIdList (NVARCHAR(MAX)): Comma-separated list of media IDs or tag IDs to process. For all media, provide a list like '1,2,3,...' or run individually if errors occur.

Example execution:

EXEC [dbo].[sp_ex_ComputeDuplicateFilePath]
@UserId = 1,
@AddFileName = 1,
@SourceType = N'MEDIA',
@SourceIdList = N'1'
  

If errors occur on bulk processing, run this sproc one media/tag at a time to isolate issues.

sp_ex_PopulateDuplicateFilePath

This sproc populates the duplicate file paths after computation. It finalizes the data in the system tables. Parameters:

• @UserId (BIGINT): The user ID (e.g., 1 for superuser).
• @AddFileName (BIT): Flag to include file names (0 or 1).

Example execution (run after successful computation):

EXEC [dbo].[sp_ex_PopulateDuplicateFilePath]
@UserId = 1,
@AddFileName = 0
  

IV. Why This Error May Occur

The error manifests as a PRIMARY KEY constraint violation, e.g.:

Violation of PRIMARY KEY constraint 'PK_#dupFile_...'. Cannot insert duplicate key in object 'dbo.#dupFilePath'.
The duplicate key value is (<xxxxxx>).
  

This happens during insertion into a temporary table (#dupFilePath) used for duplicate detection. The root cause is often inconsistencies in file grouping, such as:

• Multiple files marked as parents (isparent=1) in the same group, leading to duplicate entries for the same groupid or fileid.
• Deleted parent files, leaving orphan children.
• Archive files (e.g., attachments) not properly regrouped after parent deletion.
• Data corruption or manual modifications to the database that break family hierarchies.

The process assumes one parent per group; violations cause duplicate key attempts when aggregating paths.

V. Example Root Cause and Resolution

Querying tbl_Ex_fileinfo (e.g., SELECT * FROM tbl_Ex_fileinfo WHERE fileid = <xxxxxx> OR groupid = <xxxxxx> OR OriginalGroupId = <xxxxxx>) Where <xxxxxx> is the duplicate key value returned by the error message in section IV., showed two files in the group marked as isparent=1. The root cause was the deletion of the parent message file from the project, leaving the attached archive files with inconsistent flags.

Resolution involved updating the groupid and parentid for the affected files to restore proper hierarchy. This was done in a screenshare session, as it requires careful database updates to avoid further issues.

This deletion of parents can occur in other scenarios, such as during data cleanup or exports, leading to similar errors. Always verify file families after deletions.

Troubleshooting Steps

1. Reproduce the error and note the duplicate key value (e.g., <xxxxxx>).
2. Run sp_ex_ComputeDuplicateFilePath on individual media/tags to isolate the problematic one.
3. Query tbl_Ex_fileinfo using the key value to inspect affected records.
4. Check for multiple isparent=1 entries or missing parents.
5. Contact support for database updates if inconsistencies are found.
6. After fixes, rerun sp_ex_ComputeDuplicateFilePath on all items, then sp_ex_PopulateDuplicateFilePath.

If the issue persists, provide query results, logs, and screenshots to support for further analysis.