MySQL - Change default character set for all tables to utf8mb4

[adkinsrs]

Currently in our MySQL 8 database tables, many if not all of our tables are using the "latin1" character set. We are starting to see some datasets, such as the dataset at https://nemoanalytics.org/dataset_curator.html?dataset_id=63d168b4-30b5-b230-52d7-94034c417c5c where some categories (in this case, cytokine family) have Greek symbols. These symbols cannot be saved into the "plotly_config" field in the dataset_displays table due to the "latin1" encoding, essentially preventing saving these displays.

I looked at https://stackoverflow.com/questions/56365197/insert-greek-character-to-a-mysql-table-column-without-using-php and it suggests that the character set needs to be updated to at least utf8 to resolve this. However, a lot of these tables are linked together, so I think all of them will need to be updated appropriately.

[carlocolantuoni]

hi shaun - is this the itcket that is related to why i cant make a "cytokine_family' violin plot with this dataset (greek characters in the meta data values)?:
https://nemoanalytics.org/dataset_curator.html?dataset_id=63d168b4-30b5-b230-52d7-94034c417c5c

[adkinsrs]

Yes

[carlocolantuoni]

would a simple but hack fix to this simply be to translate the greek characters into normal/latin before asking plotly to save? id b ok with that if the real fix is a pain in the ass. eg, just change the beta character to "b"

[adkinsrs]

I think the issue with translating from greek to latin to save the configuration would be that we'd have to determine if we should translate back from latin to greek when reading from the plot curation. In many cases, we have entries that would use a "b" instead of a "beta", so there would need to be extra code and steps in a lot of scripts just to see which one to use (and this is true for any of the proposed translations). Personally, the better fix would be to make the database table permissive for Greek characters.

[carlocolantuoni]

Ok, i for sure defer to your preference here 👍

…

On Mon, Aug 12, 2024, 07:22 Shaun Adkins ***@***.***> wrote: I think the issue with translating from greek to latin to save the configuration would be that we'd have to determine if we should translate back from latin to greek when reading from the plot curation. In many cases, we have entries that would use a "b" instead of a "beta", so there would need to extra code and steps in a lot of scripts just to see which one to use (and this is true for any of the proposed translations). Personally, the better fix would be to make the database table permissive for Greek characters. — Reply to this email directly, view it on GitHub <#846 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AH7KC7VRFJX3UMWEBW4WB43ZRCSI5AVCNFSM6AAAAABL3HN7SGVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDEOBTHAZDMNJZGY> . You are receiving this because you commented.Message ID: ***@***.***>

[adkinsrs]

@jorvis We could clone the nemo-prod server to a devel area, backup the database to a new database name, apply the change, and test the dataset curator link in the first comment (as well as our standard round of testing).

[carlocolantuoni]

by "database" do u mean dataset, as in the 1 dataset with the odd characters in the metadata, or "database" as in something more broad? but plan sounds good

…

On Wed, Aug 14, 2024 at 12:57 PM Shaun Adkins ***@***.***> wrote: @jorvis <https://github.com/jorvis> We could clone the nemo-prod server to a devel area, backup the database to a new database name, apply the change, and test the dataset curator link in the first comment (as well as our standard round of testing). — Reply to this email directly, view it on GitHub <#846 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AH7KC7RXN3J5ZBRBCQFMG3TZROD6VAVCNFSM6AAAAABL3HN7SGVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDEOBZGMYTONBTGE> . You are receiving this because you commented.Message ID: ***@***.***>

-- Carlo

[adkinsrs]

I meant the actual database where the encoding changes would need to take place, not the dataset

[jorvis]

I'm up for this, and this weekend I can create the new devel instances.

[carlocolantuoni]

thnx gyus

…

On Wed, Aug 14, 2024 at 10:55 PM Joshua Orvis ***@***.***> wrote: I'm up for this, and this weekend I can create the new devel instances. — Reply to this email directly, view it on GitHub <#846 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AH7KC7S2AJQARRMXNPD4V5LZRQKAPAVCNFSM6AAAAABL3HN7SGVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDEOJQGQ2DINBQGM> . You are receiving this because you commented.Message ID: ***@***.***>

-- Carlo