Upgrading MariaDB (from broken utf8) to Modern Unicode in 2022

generate_execute_commands.mysql

-- Reminder: Replace YOUR_DATABASE with your own database.
SELECT concat
    (
        'execute("ALTER TABLE ', 
            t1.table_name, 
            ' MODIFY `', 
            t1.column_name, 
            '` ', 
            t1.column_type,
            ' CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci',
            if(t1.is_nullable='YES', ' NULL', ' NOT NULL'),
            if(t1.column_default is not null, concat(' DEFAULT ', t1.column_default, ''), ''),
            '")'
    )
from 
    information_schema.columns t1
where 
    t1.TABLE_SCHEMA like 'YOUR_DATABASE' AND
    t1.COLLATION_NAME IS NOT NULL AND
    t1.COLLATION_NAME NOT IN ('utf8mb4_unicode_ci');

upgrade_to_utf8mb4.rb

# This is referred to in the blog post mentioned below (https://world.hey.com/cknoxrun/upgrading-mariadb-from-broken-utf8-to-modern-unicode-in-2022-052f6ed2)
class UpgradeToUtf8mb4 < ActiveRecord::Migration[6.1]
  def up
    database = Rails.configuration.database_configuration[Rails.env]['database']

    execute("ALTER DATABASE #{database} CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci")

    ActiveRecord::Base.connection.tables.each do |table|
      execute("ALTER TABLE `#{table}` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci")
    end
     
    # This is just a sample, see how to generate these below:
    execute("ALTER TABLE schema_migrations MODIFY `version` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL")
    execute("ALTER TABLE terminology_synonyms MODIFY `synonym` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL")
    execute("ALTER TABLE api_endpoints MODIFY `description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL")
    execute("ALTER TABLE api_components MODIFY `type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'smart'")
  end

  def down
    # You could make this reversible, if you so choose.
    raise ActiveRecord::IrreversibleMigration
  end
end

This is referred to in a blog post about this topic, available here.