SQL Script to generate collation change sql statements

Nice answer from https://stackoverflow.com/questions/10859966/how-to-convert-all-tables-in-database-to-one-collation

I adjusted it, so you can specify an OLD collation and new collation.
This way staying away from other collations

-- set your database and new charsets/collations here
SET @MY_SCHEMA = "schema_name";
SET @MY_CHARSET = "utf8mb4";
SET @MY_COLLATION = "utf8mb4_unicode_ci";
SET @OLD_CHARSET = "utf8";
SET @OLD_COLLATION = "utf8_general_ci";

-- tables
SELECT DISTINCT
    CONCAT("ALTER TABLE ", TABLE_NAME," CONVERT TO CHARACTER SET ", @MY_CHARSET,  " COLLATE ", @MY_COLLATION) as queries
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA=@MY_SCHEMA
  AND TABLE_TYPE="BASE TABLE"
  AND TABLE_COLLATION=@OLD_COLLATION

UNION

-- table columns
SELECT DISTINCT
    CONCAT("ALTER TABLE ", C.TABLE_NAME, " CHANGE ", C.COLUMN_NAME, " ", C.COLUMN_NAME, " ", C.COLUMN_TYPE, " CHARACTER SET ", @MY_CHARSET, " COLLATE ",  @MY_COLLATION) as queries
FROM INFORMATION_SCHEMA.COLUMNS as C
    LEFT JOIN INFORMATION_SCHEMA.TABLES as T
        ON C.TABLE_NAME = T.TABLE_NAME
WHERE C.COLLATION_NAME is not null
    AND C.TABLE_SCHEMA=@MY_SCHEMA
    AND T.TABLE_TYPE="BASE TABLE"
    AND C.CHARACTER_SET_NAME = @OLD_CHARSET
    AND C.COLLATION_NAME  = @OLD_COLLATION

UNION

-- views
SELECT DISTINCT
    CONCAT("CREATE OR REPLACE VIEW ", V.TABLE_NAME, " AS ", V.VIEW_DEFINITION, ";") as queries
FROM INFORMATION_SCHEMA.VIEWS as V
    LEFT JOIN INFORMATION_SCHEMA.TABLES as T
        ON V.TABLE_NAME = T.TABLE_NAME
WHERE V.TABLE_SCHEMA=@MY_SCHEMA
    AND T.TABLE_TYPE="VIEW";
Share

Broken MySQL 8 – after upgrade 8.0.22_1 -> 8.0.23

This morning, I tried to upgrade MySQL 8:

mysql80-server upgraded: 8.0.22_1 -> 8.0.23

But then after upgrading. MySQL didn't start anymore !
It was broken:

2021-02-22T06:20:33.856568Z 4 [System] [MY-013381] [Server] Server upgrade from '80022' to '80023' started.
2021-02-22T06:20:34.351272Z 4 [ERROR] [MY-013178] [Server] Execution of server-side SQL statement '-- Create slow_log CREATE TABLE IF NOT EXISTS slow_log (start_time TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6), user_host MEDIUMTEXT NOT NULL, query_time TIME(6) NOT NULL, lock_time TIME(6) NOT NULL, rows_sent INTEGER NOT NULL, rows_examined INTEGER NOT NULL, db VARCHAR(512) NOT NULL, last_insert_id INTEGER NOT NULL, insert_id INTEGER NOT NULL, server_id INTEGER UNSIGNED NOT NULL, sql_text MEDIUMBLOB NOT NULL, thread_id BIGINT UNSIGNED NOT NULL) engine=CSV CHARACTER SET utf8 comment="Slow log"; ' failed with error code = 13, error message = 'Can't get stat of './mysql/slow_log.CSV' (OS errno 2 - No such file or directory)'.
2021-02-22T06:20:34.354494Z 0 [ERROR] [MY-013380] [Server] Failed to upgrade server.
2021-02-22T06:20:34.354946Z 0 [ERROR] [MY-010119] [Server] Aborting

Googling didn't solve my issue directly. So I tried to create this missing CSV file. (in the mysql database directory)
After this error I also received a missing slog_log.CSV.
Did the same thing.

touch ./mysql/general_log.CSV
chown mysql:mysql  ./mysql/general_log.CSV
touch ./mysql/slow_log.CSV
chown mysql:mysql  ./mysql/slow_log.CSV

Then it works again!

This seems to happen if you skip a certain upgrade. (from now on these CSV files are required). And these CSV files are only created in that particular update.. (not very robust!)

Share