0

I have thousands of columns across hundreds of tables in about a hundred databases inside a MySQL instance that need to be upgraded from utf8mb3 to utf8mb4. Is there a way to generate ALTER statements for every table and column that needs to change?

I'm upgrading MySQL hosted on Amazon RDS from MySQL 5.7 to 8.0. The Pre-patch compatibility tool tells me:

The following objects use the utf8mb3 character set. It is recommended to convert them to use utf8mb4 instead, for improved Unicode support.

More Information: https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-utf8mb3.html

It then lists about 6,000 databases and columns that need to be updated:

mydb - schema's default character set: utf8
mydb.mytable.mycolumn - column's default character set: utf8

I'd like to be able to generate all the ALTER statements I'll need to run similar to how MySQL command querying all MyISAM database helped my convert all my tables from MyISAM to Innodb.

  • What's your question? The Q&A you link to shows how to dynamically generate ALTER statements. If there's something that you tried that didn't work, you might want to explain the exact problem you had. – mustaccio Jan 22 at 14:23
  • It shows how to generate the alter statements for a different use case: converting MyISAM to INNODB. I'd like to generate alter statements for the the case of changing to utf8mb4. – Stephen Ostermiller Jan 22 at 14:25
  • I understand what you'd like to do; what exactly prevents you from doing that? – mustaccio Jan 22 at 14:37
  • I couldn't find the script of how to do it on the internet anywhere already prepared the way that it is for the other case. I'm working on creating it myself and I'll put an answer in here to help others if I can get it working. – Stephen Ostermiller Jan 22 at 14:44
1

Here is a query what will generate all the necessary update statements.

  • It temporarily disables foreign key checks so that the update statements succeed.
  • It updates the default character set for each database that needs it.
  • It updates the default character set of each table:
    • If it doesn't have the right default character set.
    • If it contains text columns that are not the right character set.
    • The same ALTER TABLE ... CONVERT statement both updates the table default and all the columns within the table.

SELECT
    /* Disable foreign key checks temporily to be able to make these changes */
    'SET FOREIGN_KEY_CHECKS = 0;' AS alter_statement 
UNION SELECT 
    /* Alter the default character set of each database */
    CONCAT('ALTER DATABASE `', SCHEMA_NAME,'` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;') AS alter_statement 
FROM 
    information_schema.SCHEMATA 
WHERE 
    DEFAULT_CHARACTER_SET_NAME!='utf8mb4' AND 
    SCHEMA_NAME NOT IN('mysql','information_schema','performance_schema','sys')
UNION SELECT
    /* Alter the default character set of each table .
      This also converts all text columns in the table,
      So there is no need to have a statement to alter each
      column individually */
    DISTINCT CONCAT('ALTER TABLE `', TABLE_SCHEMA,'`.`',TABLE_NAME, '` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;') AS alter_statement 
FROM
(
    SELECT
        /* Find all tables with a text column that isn't utf8mb4 */
        TABLE_SCHEMA, TABLE_NAME
    FROM
        information_schema.COLUMNS 
    WHERE
        TABLE_SCHEMA NOT IN('mysql','information_schema','performance_schema','sys') AND 
        CHARACTER_SET_NAME IS NOT NULL AND 
        CHARACTER_SET_NAME!='utf8mb4'
    UNION SELECT
        /* Also find all tables that don't have the correct default character set */
        TABLE_SCHEMA, TABLE_NAME
    FROM 
        information_schema.TABLES AS T
    JOIN 
        information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` AS C ON C.collation_name = T.table_collation
    WHERE 
        CHARACTER_SET_NAME!='utf8mb4' AND 
        TABLE_SCHEMA NOT IN('mysql','information_schema','performance_schema','sys')
) AS TABLE_UPDATES
UNION SELECT
    /* Re-enable forign key checks */
    'SET FOREIGN_KEY_CHECKS = 1;' AS alter_statement 
;

When run, it should generate output like:

SET FOREIGN_KEY_CHECKS = 0;
ALTER DATABASE `mydb` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `mydb`.`mytable` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SET FOREIGN_KEY_CHECKS = 1;

Sources:

|improve this answer|||||
0

Checklist for UTF8MB4 Conversion:

  1. Space should be available
  2. Foriegn_key_check should be disabled
  3. Need to convert Database
  4. Need to convert Routines
  5. Need to convert Triggers
  6. Need to convert Tables

Note: Bulky tables should be converted usingLOAD DATA ... INTO table syntax

SAMPLE SYNTAX

  1. For the database:

    ALTER DATABASE  database123 CHARACTER SET utf8MB4 ; 
    
  2. For the routines:

    mysqldump --single-transaction  --set-gtid-purged=OFF   --force --routines  -h 192.168.X.X --no-data --skip-comments --skip-triggers --no-create-db --no-create-info  -u user -p --databases database --lock-tables=false > /backup/database_proc.sql
    

    Open the SQL file in vi & perform below commands.

    :%s/SET character_set_client  = latin1/SET character_set_client  = utf8mb4/
    :%s/SET character_set_results = latin1/SET character_set_results = utf8mb4/
    :%s/SET collation_connection  = latin1_swedish_ci/SET collation_connection  = utf8mb4_general_ci/
    :%s/latin1/utf8mb4/
    :wq
    

    then

    mysql -uuser -p -h192.168.X.X < proc.sql 
    
  3. For the triggers:

    mysqldump --single-transaction  --set-gtid-purged=OFF   --force -h 192.168.X.X --no-data --skip-comments --triggers --add-drop-trigger --no-create-db --no-create-info  -u user -p --all-databases --lock-tables=false > /backup/trig.sql
    
    :%s/SET character_set_client  = latin1/SET character_set_client  = utf8mb4/
    :%s/SET character_set_results = latin1/SET character_set_results = utf8mb4/
    :%s/SET collation_connection  = latin1_swedish_ci/SET collation_connection  = utf8mb4_general_ci/
    :wq
    
    mysql -uuser -p -h192.168.X.X < /backup/trig.sql
    
  4. For the tables:

    ALTER TABLE Inventory.PINCODE_MAST CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci
    SELECT  * FROM  database.table_21 INTO OUTFILE  '/backup1/csv/database_2.csv' CHARACTER SET utf8mb4 FIELDS TERMINATED BY ','  OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n'    ;
    LOAD  DATA INFILE '/backup1/csv/database_2.csv' INTO TABLE database.table_21 CHARACTER SET utf8mb4 FIELDS TERMINATED BY ','  OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' ;
    
  5. Queries to verify object are converted or not:

    select table_schema,table_name ,table_collation from information_schema.tables where table_schema not like '%latin%' and table_schema not in ('sys','mysql','performance_schema','information_schema') and TABLE_COLLATION<>'utf8mb4_0900_ai_ci';
    select * from information_schema.routines where routine_schema not like '%latin%' and routine_schema not in ('sys','mysql','performance_schema','information_schema') and COLLATION_NAME <>'utf8mb4_0900_ai_ci';
    select * from  information_schema.triggers where trigger_schema not like '%latin%' and trigger_schema not in ('sys','mysql','performance_schema','information_schema') and COLLATION_CONNECTION <>'utf8mb4_0900_ai_ci';
    
|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.