ALTER TABLE security_entity_type MODIFY COLUMN assembly_qualified_name varchar(768) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL;
ALTER TABLE api_action MODIFY COLUMN assembly_name varchar(768) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL;
ALTER TABLE servicecorelogin_client MODIFY COLUMN allowed_cors_origins TEXT CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL NULL;
ALTER TABLE servicecorelogin_client MODIFY COLUMN uri TEXT CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL NULL;
ALTER TABLE servicecorelogin_client MODIFY COLUMN logout_uri TEXT CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL NULL;
ALTER TABLE servicecorelogin_client MODIFY COLUMN post_logout_redirect_uris TEXT CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL NULL;
ALTER TABLE servicecorelogin_client MODIFY COLUMN identity_provider_restrictions TEXT CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL NULL;
ALTER TABLE servicecorelogin_client MODIFY COLUMN email_verification_redirect_uri TEXT CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL NULL;
ALTER TABLE servicecorelogin_client MODIFY COLUMN redirect_uri TEXT CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL;
ALTER TABLE servicedmcore_reporting_powershell_report MODIFY COLUMN description TEXT CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL NULL;
ALTER TABLE servicedmcore_reporting_powershell_report MODIFY COLUMN powershell_modules TEXT CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL NULL;
ALTER TABLE servicedmcore_reporting_powershell_report MODIFY COLUMN powerhsell_snapins TEXT CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL NULL;
ALTER TABLE servicedmcore_reporting_powershell_report MODIFY COLUMN powershell_script_file_path TEXT CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL;
DELIMITER //
CREATE PROCEDURE ConvertToUtf8mb4MultipleDatabases()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE stmt_str VARCHAR(1000);
DECLARE cur CURSOR FOR
SELECT CONCAT('ALTER TABLE `', table_schema, '`.`', table_name, '` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;')
FROM information_schema.tables
WHERE table_schema IN ('moving_appcustomer_900000', 'moving_global', 'moving_site')
AND (table_collation LIKE 'latin1%' OR table_collation LIKE 'utf8%')
AND table_collation NOT LIKE 'utf8mb4%';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur;
read_loop: LOOP
FETCH cur INTO stmt_str;
IF done THEN
LEAVE read_loop;
END IF;
SET @s = stmt_str;
PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END LOOP;
CLOSE cur;
END //
DELIMITER ;
CALL ConvertToUtf8mb4MultipleDatabases();
DROP PROCEDURE ConvertToUtf8mb4MultipleDatabases;
ALTER DATABASE moving_appcustomer_900000 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
ALTER DATABASE moving_global CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
ALTER DATABASE moving_site CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
SET GLOBAL character_set_server = 'utf8mb4';
SET GLOBAL collation_server = 'utf8mb4_unicode_ci'; |