Long upgrade time with collation change, need efficiency help

[dugjohnson]

We are trying to run the 5.85-5.93 upgrade and we hit a long wall during the after installation section. Enough so that the update stops, or is killed by the daily backup or...who knows? We have VERY large tables
3 questions
1) Is the character set and collation for utf8mb4 going to be the standard going forward?
2) Is it safe for me to change that everywhere?
3) Rather than changing the individual columns, is it safe to change the character set for each table and let MariaDB handle the conversion?

I understand what the after installation script is doing...I just need to write more specific php code that will get through the process faster.

[item]

Hello,

Yuri have make a script and upload in forum. Maybe it can be help you. check Yuri post for... the file name is : convert-to-mb4.zip. i can not upload this kind of file. (below the content but find the right post of Yuri)

Please find for original post of Yuri .. there was tips and recommandation https://forum.espocrm.com/forum/inst...-after-upgrade

<?php

if (substr(php_sapi_name(), 0, 3) != 'cli') exit;

include "bootstrap.php";

$app = new \Espo\Core\Application();
$app->setupSystemUser();

$mb4Converter = new Mb4Converter($app->getContainer());
$mb4Converter->run();

class Mb4Converter
{
private $container;

public function __construct($container)
{
$this->container = $container;
}

protected function getContainer()
{
return $this->container;
}

public function run()
{
fwrite(\STDOUT, "We strongly recommend to create a backup of your database, https://docs.espocrm.com/administration/backup-and-restore/#step-2-backup-database.\n");
sleep(1);

fwrite(\STDOUT, "All fields will be converted to utf8mb4_unicode_ci collation. Enter [Y] to continue.\n");

if (!$this->confirm()) {
echo "Canceled.\n";
return;
}

$this->convert();
}

protected function confirm()
{
$fh = fopen('php://stdin', 'r');
$inputLine = trim(fgets($fh));
fclose($fh);
if (strtolower($inputLine) !== 'y'){
return false;
}
return true;
}

protected function convert()
{
$container = $this->getContainer();

$pdo = $container->get('entityManager')->getPDO();
$ormMeta = $container->get('ormMetadata')->getData(true);

try {
$sth = $pdo->prepare('show tables');
$sth->execute();
} catch (\Exception $e) {
fwrite(\STDOUT, "Error: cannot get table list.\n");
exit;
}

$tableList = $sth->fetchAll(\PDO::FETCH_NUM);

if (empty($tableList)) {
fwrite(\STDOUT, "Error: empty table list.\n");
exit;
}

$listTypes = [];

foreach ($tableList as $item) {

$tableName = $item[0];

//Get table columns params
$query = "SHOW FULL COLUMNS FROM `". $tableName ."` WHERE `Collation` <> 'utf8mb4_unicode_ci'";

try {
$sth = $pdo->prepare($query);
$sth->execute();
} catch (\Exception $e) {
$GLOBALS['log']->debug('Utf8mb4: Table does not exist - ' . $e->getMessage());
continue;
}

$rowList = $sth->fetchAll(\PDO::FETCH_ASSOC);

$columns = array();
foreach ($rowList as $row) {
$columns[ $row['Field'] ] = $row;
}
//END: get table columns params

if (empty($columns)) {
continue;
}

fwrite(\STDOUT, "Table: " . $tableName);

foreach ($columns as $columnName => $columnParams) {

fwrite(\STDOUT, ".");

$query = null;

$columnType = $this->getColumnType($columnParams['Type']);

switch ($columnType) {
case 'varchar':
case 'text':
case 'mediumtext':
case 'longtext':
$query = "ALTER TABLE `". $tableName ."`
CHANGE COLUMN `". $columnName ."` `". $columnName ."` ". $columnParams['Type'] ."
CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
";
break;
}

if (!empty($query)) {
$GLOBALS['log']->debug('Utf8mb4: execute the query - [' . $query . '].');

try {
$sth = $pdo->prepare($query);
$sth->execute();
} catch (\Exception $e) {
$GLOBALS['log']->warning('Utf8mb4: FAILED executing the query - [' . $query . '], details: '. $e->getMessage() .'.');
}
}
}

fwrite(\STDOUT, "\n");
}

$config = $container->get('config');
$database = $config->get('database');
if (!isset($database['charset']) || $database['charset'] != 'utf8mb4') {
$database['charset'] = 'utf8mb4';
$config->set('database', $database);
$config->save();
}

$this->getContainer()->get('dataManager')->rebuild();

fwrite(\STDOUT, "Successfully completed.\n");
}

protected function getColumnType($type)
{
if (preg_match('/^(.*)\(/i', $type, $match)) {
return strtolower($match[1]);
}

return strtolower($type);
}
}

[dugjohnson]

Yes, that script is essentially the same as the current after installation script that is taking so long to run. And Yuri has acknowledged else where that this script will take a VERY long time to run on a VERY large database, which is what I am dealing with.
That is why I asked the 3 questions. I need information on a DIFFERENT way to do the conversion, if possible

[yuri]

Hi Doug,

> 1) Is the character set and collation for utf8mb4 going to be the standard going forward?

Yes.

> 2) Is it safe for me to change that everywhere?

Yes.

> 3) Rather than changing the individual columns, is it safe to change the character set for each table and let MariaDB handle the conversion?

I don't think you if change a charset for a table it will change for all existing columns.

[dugjohnson]

We are finally updated. The method that finally worked involved copying the email table to a different database, running the update on that table, then when that was all finished, renaming email table in live system, copying modified email table to live system, then inserting missing email records from former live email table.

At that point, the other tables were small enough that it only took a few hours to run the update.

Alternatively we COULD have done a single field update every night. The problem was that we were running into our backup routine so doing the conversion "off to the side" worked best