Migrate MySQL Database from utf8 to utf8mb4

[abrookbanks]

phpMyAdmin handles this very well

[abrookbanks]

public function __invoke(): void
    {
        global $db, $cfg, $errorUrl;

        if (! $this->response->isAjax()) {
            return;
        }

        if (empty($_POST['db_collation'])) {
            $this->response->setRequestStatus(false);
            $this->response->addJSON('message', Message::error(__('No collation provided.')));

            return;
        }

        Util::checkParameters(['db']);

        $errorUrl = Util::getScriptNameForOption($cfg['DefaultTabDatabase'], 'database');
        $errorUrl .= Url::getCommon(['db' => $db], '&');

        if (! $this->hasDatabase()) {
            return;
        }

        $sql_query = 'ALTER DATABASE ' . Util::backquote($db)
            . ' DEFAULT' . Util::getCharsetQueryPart($_POST['db_collation'] ?? '');
        $this->dbi->query($sql_query);
        $message = Message::success();

        /**
         * Changes tables charset if requested by the user
         */
        if (isset($_POST['change_all_tables_collations']) && $_POST['change_all_tables_collations'] === 'on') {
            [$tables] = Util::getDbInfo($db, '');
            foreach ($tables as $tableName => $data) {
                if ($this->dbi->getTable($db, $tableName)->isView()) {
                    // Skip views, we can not change the collation of a view.
                    // issue #15283
                    continue;
                }

                $sql_query = 'ALTER TABLE '
                    . Util::backquote($db)
                    . '.'
                    . Util::backquote($tableName)
                    . ' DEFAULT '
                    . Util::getCharsetQueryPart($_POST['db_collation'] ?? '');
                $this->dbi->query($sql_query);

                /**
                 * Changes columns charset if requested by the user
                 */
                if (
                    ! isset($_POST['change_all_tables_columns_collations']) ||
                    $_POST['change_all_tables_columns_collations'] !== 'on'
                ) {
                    continue;
                }

                $this->operations->changeAllColumnsCollation($db, $tableName, $_POST['db_collation']);
            }
        }

        $this->response->setRequestStatus($message->isSuccess());
        $this->response->addJSON('message', $message);
    }

public function changeAllColumnsCollation($db, $table, $tbl_collation): void
    {
        $this->dbi->selectDb($db);

        $change_all_collations_query = 'ALTER TABLE '
            . Util::backquote($table)
            . ' CONVERT TO';

        [$charset] = explode('_', $tbl_collation);

        $change_all_collations_query .= ' CHARACTER SET ' . $charset
            . ($charset == $tbl_collation ? '' : ' COLLATE ' . $tbl_collation);

        $this->dbi->query($change_all_collations_query);
    }

[abrookbanks]

Alter Database

ALTER DATABASE `{db_name}` DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

Alter Each Table in loop

ALTER TABLE `{db_name}`.`{table_name}` DEFAULT  CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `{db_name}`.`{table_name}` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

[abrookbanks]

This cr*p needs to go.
setup/setup.upgrade.php

/* ########################
            ##	Following code based on;
            ##	Migrating MySQL Data to Unicode
            ##	http://daveyshafik.com/archives/166-migrating-mysql-data-to-unicode.html
            ##	Thanks to Davey Shafik
            ## ######################## */
            $tables = $db->getRows();
            foreach ($tables as $table) {
                ## Get Schema
                if ($schema = $db->misc('SHOW CREATE TABLE '.$table['Name'])) {
                    ## Fix Schema and Create Temp Table
                    $find   = array("latin1", $table['Name']);
                    $replace  = array("utf8", $table['Name'].'_utf8');

                    if ($db->misc(str_replace($find, $replace, $schema[0]['Create Table']))) {
                        if ($db->misc("SHOW TABLES LIKE '".$table['Name']."_utf8'", false)) {
                            ## Copy Data
                            $db->misc('INSERT INTO '.$table['Name'].'_utf8 SELECT * FROM '.$table['Name']);
                            ## Deleting Original Table
                            $db->misc('DROP TABLE '.$table['Name']);
                            ## Renaming Temporary Table
                            $db->misc('ALTER TABLE '.$table['Name'].'_utf8 RENAME TO '.$table['Name']);
                        }
                    }
                }
            }

[abrookbanks]

Another typo. See: 4a66807

Needs testing at least on upgrade.

[abrookbanks]

Works flawlessly.