How can I fix case insensitive database to be case sensitive?

Joshua Han
2019-07-09 22:05

Artifactory requires using DB that uses case-sensitivity. If your Artifactory is set up with case-insensitive DB, like MySQL is by default, you may use steps below to repair it. Please note that this solution is written for MySQL.

Also note that there is small downtime needed for making changes to the case sensitivity and restarting each node. Below is updated instructions that makes a use of the REST API method. Below is the complete steps for the path repair and converting database to be case sensitive. You may have already performed the dry-run, so you may skip the dry-run step.

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")