How To Change Case-Insensitive MySQL Database To Case-Sensitive

Reason: Artifactory is case-sensitive in order to support many tools that are case-sensitive, but the MySQL is set to be case insensitive. For more information, please visit our documentation page.

Resolution: The suggestion is to perform full system export and then import to the new schema. However, this will require a downtime depending on your amount of data. As a temporary workaround with minimal downtime use following steps will convert the binaries, indexed_archives, and nodes tables to case-sensitive (utf8_bin) and allow the deployment of case-sensitive artifacts:

1. Discover the issue

mysql> SELECT @@character_set_database, @@collation_database;

If the result has "_ci" it means that it is case-insensitive, and you need to use steps below.

+-----------------+---------------+

| @@character_set_database | @@collation_database |

+-----------------+---------------+

| utf8                     | utf8_general_ci      |

+-----------------+---------------+

1 row in set (0.00 sec)

 

2. Make a backup of your database

Please get a current incremental backup or no content export (System Export with "Exclude Content" option - https://www.jfrog.com/confluence/display/RTF/Importing+and+Exporting#ImportingandExporting-SystemImportandExport) in the event you need to regress back.
 

3. Detect the conflicts by running the following REST API (with dry run enabled) for each local repositories

4. Put Artifactory out of Load balancer to block incoming traffics, in order to ensure that no files are changed during the process below

5. For any Maven local repositories, Suppress POM Consistency Checks (Admin => Local repositories => your maven repository => Basic => Enable "Suppress POM Consistency Checks")

6. Repair Conflicting Paths by using below REST API  (with dry run DISABLED, dry=false) 

Description: Repair all artifact paths that conflict with their hierarchical parent in the given local non-cache repository (on case insensitive database). Dry run can be performed on any local repository.
Prerequisites: On a Maven repository check the "Suppress POM Consistency Check" checkbox in the repository settings panel. This test is only valid for Case Insensitive DataBases. 
Since: 3.0.5
Security: Requires admin privileges
Usage: POST /api/repairPaths/{path}[?dry=true]
Produces: application/json
Sample Output:

POST /api/repairPaths/libs-release-local?dry=false
{
    "conflicts": [
        {
            "conflicts": [
                "ext-releases-local:org/jdom"
            ],
            "path""ext-releases-local:org/JDOM/test"
        },
        {
            "conflicts": [
                "ext-releases-local:org/jdom"
            ],
            "path""ext-releases-local:org/JDOM/test/build/1/build-1.json"
        },
    ],
    "message""Completed",
    "numConflicts"2,
    "numRepaired"2
}
  

7. STOP all Artifactory nodes

8.  Change DB to be case sensitive by running following mysql query

ALTER DATABASE artdb CHARACTER SET utf8 COLLATE utf8_bin;

9. Confirm the change

mysql> SELECT @@character_set_database, @@collation_database;

The result should show utf8_bin as COLLATE

10. Start Artifactory nodes

11Re-enable POM Consistency Checks  (Admin => Local repositories => your maven repository => Basic => Uncheck "Suppress POM Consistency Checks")

All set! Now Artifactory is able to use case sensitive database properly!