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.


ResolutionUse 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) in the event you need to regress back.

 

3. Shut down all Artifactory nodes

 

4. Remove conflicts

Run this rest API on each of your local repositories:

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

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.

5. After you find all of the bad path elements, you can rename them if there is no conflict

UPDATE nodes set node_path=REPLACE(node_path, ‘bad_case’, ‘good_case’) WHERE node_path like ‘com/yahoo/bad_case%’;


UPDATE nodes set node_name=REPLACE(node_name, ‘bad_case’, ‘good_case’) WHERE node_type = 0 AND node_name like ‘bad_case%’;

This should replace all the bad uppercase path elements for this particular path “com/company/…” on your DB. Note that if there are files in both folder locations, you should check to see if they are the same checksum and delete one of them.

6. Drop foreign keys by running the queries below

#Drop foreign keys:

ALTER TABLE nodes drop foreign key nodes_binaries_fk;

ALTER TABLE indexed_archives drop foreign key indexed_archives_binaries_fk;

#Convert Parent and Child tables:

ALTER TABLE binaries CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;

ALTER TABLE indexed_archives CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;

ALTER TABLE nodes CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;

#Adding the foreign key back:

ALTER TABLE indexed_archives ADD CONSTRAINT `indexed_archives_binaries_fk` FOREIGN KEY (`archive_sha1`) REFERENCES `binaries` (`sha1`);

ALTER TABLE nodes ADD CONSTRAINT `nodes_binaries_fk` FOREIGN KEY (`sha1_actual`) REFERENCES `binaries` (`sha1`);

After these queries have been run, and are successful, the binaries, indexed_archives, and nodes tables will be case sensitive, allowing artifacts to be case sensitive in the db. 

7. Start Artifactory