ARTIFACTORY: How can I retain the current directory structure (with the same names but different letter casing) and restore any missing subdirectories in the UI after converting the database to be case-sensitive

Subject

Certain subdirectories are absent in the UI following the modification of the database collation to be case-sensitive. These missing subdirectories show a pattern wherein artifacts were previously uploaded with identical names but varying casings. Our objective is to restore their visibility while preserving the structure where directory names remain consistent, but casing may differ.

Affected Versions: Artifactory 7.x

Description

If your database utilizes a case-insensitive collation, as commonly found in databases such as MySQL and MSSQL, by default, it is advisable to transition to a case-sensitive collation, aligning with the case-sensitive nature of Artifactory.

To implement this change across existing tables in the database, each table should undergo alteration using commands similar to the examples below:

ALTER TABLE access_configs CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
…
ALTER TABLE nodes CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
…
ALTER TABLE watches CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;

Below is an explanation of the issue and how to address it. The demonstration provided here is based on MySQL.

Resolution

In MySQL databases operating in a case-insensitive mode, directory names are not distinguished by letter case. If files were initially uploaded to a directory named “FOLDER”' and subsequent uploads are made to this directory with different letter cases, such as “folder”, the database will associate the uploaded content with the existing directory, regardless of the letter case used (e.g., “Folder”, “foldeR”, etc.). However, the paths for each uploaded file remain distinguishable based on the casing used.



Here, we see that only the subdirectory "FOLDER" (line 7) is recorded in the nodes table in the database (where node_type 0 denotes a directory). As mentioned earlier, in a case-insensitive database, it does not generate additional entries to distinguish directories with identical names but different casing. Instead, it utilizes the existing directory as long as the name aligns.

Each uploaded artifact (node_type 1) has distinct paths with varying letter casing. For instance, the artifact "test.0.0.1" is deployed under the directory "path/to/FOLDER/0.0.1”,



while the artifact “test.0.0.3” is deployed under the directory “path/to/folder/0.0.3”.



Due to the case-insensitive nature of the database, all items are displayed under the "FOLDER" directory in the UI.




Following the collation conversion queries, the database is now configured to be case-sensitive. Then, we see that the 0.0.3 and 0.0.4 subdirectories under the "FOLDER" directory are missing.




This is due to the fact that the node with the node_name "folder" (node_type 0)  is not present in the database. In a case-sensitive database, the absence of this specific node impacts the way subdirectories are displayed in the UI.

To address this issue, you may refer to this KB article and consolidate directories with the same name but different cases into a unified directory with consistent casing. As a result, all paths have been converted to use the “FOLDER” directory.



However, if your preference is to preserve the existing structure with directory names maintained in different casings, it is necessary to add the missing nodes to the database. This can be achieved by deploying a new artifact to the specific subdirectory, thereby creating a corresponding entry in the database.



Then, the hidden subdirectories, along with newly uploaded artifacts, will become visible again in the UI.



And we can see that the subdirectory "folder" (line 5) has been added to the nodes table in the database.