Encoding mismatch between schema.rb and database.yml

[jvendetti]

Recently I needed to run the RSpec tests for the License model:

$ bin/rails db:migrate RAILS_ENV=test
$ bundle exec rspec spec/models/license_spec.rb

... and according to git, this resulted in modifications to the schema.rb file, e.g.:

The underlying cause is that my database.yml file in my local dev environment is configured to use utf8 encoding, which matches the encoding configuration of database.yml in our private bioportal_config repository (used for official deployments of BIoPortal to both staging and production). This is a mismatch with the utf8mb4 charset specified in the schema.rb file that was recently modified by adoption of code from AgroPortal.

If the intention going forward is to use utf8mb4 encoding, then the database.yml used for local dev and for official deployments should be properly configured with that encoding. The database.yml file in the private bioportal_config repository is currently configured to use utf8 for appliance, development, and test modes, and no encoding is specified for staging and production modes.

Another issue is the collation setting of utf8mb4_0900_ai_ci. This type of collation was introduced in MySQL 8, and we're running on MySQL 5.7, so the collation is ignored. There's no documentation in the pull request that included the change to schema.rb to indicate if there's a reason for specifying the collation. If this is important for new functionality adopted from AgroPortal, then we should consider using a collation setting that applies to the version of MySQL that we're actually running in our production environment (5.7), e.g.:

• utf8mb4_general_ci (faster but less accurate sorting)
• utf8mb4_unicode_ci (slightly slower but more precise Unicode sorting)

Note that this isn't an exhaustive list of collation options.

[syphax-bouazzouni]

If this is important for new functionality adopted from AgroPortal, then we should consider using a collation setting that applies to the version of MySQL that we're actually running in our production environment (5.7), e.g.:

Hello @jvendetti, no we don't need it in AgroPortal alignment work, I think it was introduced automatically somehow, you can remove that change, if it is not a good thing to have.

[jvendetti]

@alexskr - I generated a new Rails application from scratch using Rails 7.0.8.7 and MySQL 5.7.44. It looks like the default encoding was generated as utf8mb4 in the database.yml file:

default: &default
  adapter: mysql2
  encoding: utf8mb4

Can you think of any problem with going from utf8 to the utf8mb4 encoding for production BioPortal and/or the appliance distribution?

[alexskr]

Can you think of any problem with going from utf8 to the utf8mb4 encoding for production BioPortal and/or the appliance distribution?

utf8 is a legacy charset so moving to utf8mb4 is the right thing to do
If we do that then the next major version of the appliance (v4) could integrate this change.