Character set problems in mysql8

[lfrank]

The default character set and collation changed from mysql 5.3 to mysql 8, which causes problems for datajoint.

Right now we I have changed the defaults back to those used in 5.3, but despite my best efforts, those values seem to get reset from time to time. We either need to fix that or change all the databases to use the new collation.

From ChatGPT, here is code that should change the character set and collation. I'm not sure this would work, but it would likely be close

ALTER DATABASE `your_default_database_name` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Get a list of all databases
SET @schema_query = NULL;
SELECT GROUP_CONCAT('ALTER DATABASE `', schema_name, '` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;' SEPARATOR '\n')
  INTO @schema_query
  FROM information_schema.schemata
  WHERE schema_name NOT IN ('mysql', 'information_schema', 'performance_schema');

-- Execute the queries for changing the character set of databases
PREPARE schema_query FROM @schema_query;
EXECUTE schema_query;
DEALLOCATE PREPARE schema_query;

-- Iterate over all databases to change character set of tables
SET @table_query = NULL;
SELECT GROUP_CONCAT('ALTER TABLE `', table_schema, '`.`', table_name, '` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;' SEPARATOR '\n')
  INTO @table_query
  FROM information_schema.tables
  WHERE table_schema NOT IN ('mysql', 'information_schema', 'performance_schema');

-- Execute the queries for changing the character set of tables
PREPARE table_query FROM @table_query;
EXECUTE table_query;
DEALLOCATE PREPARE table_query;

-- Convert data in tables from Latin1 to utf8mb4
SET @convert_query = NULL;
SELECT GROUP_CONCAT('ALTER TABLE `', table_schema, '`.`', table_name, '` MODIFY COLUMN `', column_name, '` ', column_type, ' CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;' SEPARATOR '\n')
  INTO @convert_query
  FROM information_schema.columns
  WHERE table_schema NOT IN ('mysql', 'information_schema', 'performance_schema') AND character_set_name = 'latin1';

-- Execute the queries to convert data to utf8mb4
PREPARE convert_query FROM @convert_query;
EXECUTE convert_query;
DEALLOCATE PREPARE convert_query;