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
 

Repair Conflicting Paths REST API

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
}
  

Be sure to save the output to files so you do not need to run it again, it will take a while and have a medium impact on performance.

4. Shut down all Artifactory nodes

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

ALTER DATABASE artdb CHARACTER SET utf8 COLLATE utf8_bin;

6. Confirm the change

mysql> SELECT @@character_set_database, @@collation_database;

The result should show utf8_bin as COLLATE

7. Start Artifactory nodes

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

9. Fix the conflicts by running the following REST API (with dry run disabled) for each local repositories

Run this rest API on each of your local repositories:
POST http://<your-host>/artifactory/api/repairPaths/<repo>?dry=false

10. Verify the fix by running running the following REST API (with dry run enabled) for each local repositories

POST http://<your-host>/artifactory/api/repairPaths/<repo>?dry=true

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

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