Nextcloud 12 Upgrade and Collation Issues

[Gizmokid2005]

I recently upgraded from NC11.0.5 to 12.0.3 and was greeted with the convert banner:
Non UTF-8 charset for MySQL/MariaDB database detected! Learn how to convert your database to utf8mb4 (make a backup beforehand)

Using the provided link I was able to figure out what needed to be done. (This installation has been around since OwnCloud 8.x.) It took a while to go through and determine what all needed to be changed, as it appeared that only changing the database collation and character set didn't resolve the issue and as such I needed to also alter the tables and the columns as well. I ran into the issue specified in #4846 while trying to follow the link and as such spent today digging on how to programmatically handle this so a user doesn't have to think about much (I can't imagine anyone wants to spend time going through 50+ tables and their associated columns to try and perform this upgrade if their DB is old enough to need manual love).

I came up with the below solution, it may not be completely elegant but it is functional and while not entirely automatic (you need to manually specify the database name a few places), it takes the worry out of most of the steps. It may be worthwhile at least posting this somewhere if not trying to include this in an upgrade script? The SO page was helpful but ultimately not super useful for those with limited SQL knowledge and/or time.

ALTER DATABASE YOURDBNAMEHERE CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin;


--This procedure was needed as some of the tables were not InnoDB driven, and definitely not using the proper row_format.
CREATE PROCEDURE FixUpEngine()

BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE tbl, clm, tbls CHAR(100);
    DECLARE cur1 CURSOR FOR SELECT t.TABLE_NAME FROM information_schema.TABLES as t WHERE table_schema = 'YOURDBNAMEHERE' GROUP BY table_schema,table_name;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    OPEN cur1;
    
    read_loop: LOOP
        FETCH cur1 INTO tbls;
        IF done THEN
            LEAVE read_loop;
        END IF;
        SET @stmt_text=CONCAT("ALTER TABLE ",tbls," ENGINE = InnoDB ROW_FORMAT=DYNAMIC");
        
        PREPARE stmt FROM @stmt_text;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END LOOP;
    
    CLOSE cur1;
 
END;

CALL `YOURDBNAMEHERE`.`FixUpEngine`();


--This procedure is the meat that fixes up all of the columns and tables including the key length error
CREATE PROCEDURE FixUpCharSets()

BEGIN
	--Setup the variables for the cursor
    DECLARE done INT DEFAULT FALSE;
    DECLARE tbl, clm, tbls CHAR(100);
	--We only want to deal with tables where the collation is currently incorrect
    DECLARE cur1 CURSOR FOR SELECT t.TABLE_NAME FROM information_schema.TABLES as t WHERE table_schema = 'YOURDBNAMEHERE' AND table_collation NOT LIKE 'utf8mb4_bin' GROUP BY table_schema,table_name;
	--Subsequently we want to deal with any table *KEY* columns that have a length too long so we can fix the collation
    DECLARE cur2 CURSOR FOR SELECT c.TABLE_NAME,c.COLUMN_NAME FROM information_schema.KEY_COLUMN_USAGE AS k JOIN information_schema.COLUMNS AS c ON c.TABLE_SCHEMA = k.TABLE_SCHEMA AND c.TABLE_NAME = k.TABLE_NAME AND c.COLUMN_NAME = k.COLUMN_NAME WHERE k.TABLE_SCHEMA = 'YOURDBNAMEHERE' AND c.DATA_TYPE = 'varchar' AND c.CHARACTER_MAXIMUM_LENGTH > 191;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
	--First we want to fix up the key issues as this will cause table collation changes to fail
    OPEN cur2;
    
    read_loop: LOOP
        FETCH cur2 INTO tbl,clm;
        IF done THEN
            LEAVE read_loop;
        END IF;
        SET @stmt_text=CONCAT("ALTER TABLE ",tbl," CHANGE ",clm," ",clm," VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin");
        
        PREPARE stmt FROM @stmt_text;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END LOOP;
     
    CLOSE cur2;
    
    SET done = FALSE;
    

	--Second we want to update the table collation
    OPEN cur1;
    
    read_loop: LOOP
        FETCH cur1 INTO tbls;
        IF done THEN
            LEAVE read_loop;
        END IF;
        SET @stmt_text=CONCAT("ALTER TABLE ",tbls," CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin");
        
        PREPARE stmt FROM @stmt_text;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END LOOP;
    
    CLOSE cur1;
 
END;

CALL `YOURDBNAMEHERE`.`FixUpCharSets`();

This may not be perfect, I'm actually still seeing that notice on the top of my page even though all of the collation and character sets seem to be correct. That is unless collation of utf8mb4_unicode_ci is incorrect and it should instead be utf8mb4_bin (which if this is the case, the linked SO issue/comment is incorrect). I'm more than happy to help and provide more information if I can, I figure if nothing else this may help others who are running into this issue (it could even be useful on the related maintenance page? https://docs.nextcloud.com/server/12/admin_manual/configuration_database/mysql_4byte_support.html).

[nickvergessen]

Should be utf8mb4_bin or utf8_bin

[Gizmokid2005]

I updated my script to use utf8mb4_bin to no avail and then even switched to utf8mb4_general_ci without success (didn't update my script above though).

Turns out I missed this step from the docs:

$ sudo -u www-data occ config:system:set mysql.utf8mb4 --type boolean --value="true"