ID Collation Changed after upgrade...

[k.rakviashvili@gmail.com]

Hi All,
After upgrade (5.8.4->5.8.5->5.9.3) i found that in new version when you create new entity, primary an foreign keys are created with different collation that was before.
Older one for keys was "utf8_unicode_ci", now it is utf8mb4_unicode_ci.
It is a big problem because when you join fields with different collation, index doesn't work or it converts, i don't know, but elementery report that needed less than second, now tooks more then 3 minutes.
i manually changed collation on newly created entity foreign key column to old colaation and everyting workes fine now. but i think it's a problem to change column collation manually. maybe it's better to update all old id colums to new collation, but to do it manually, it's not good idea. maybe you could provide some update or script that will change all id's to same collation?

[yuri]

Hi,

Supposed that all IDs have mb4 collation. For some reason it didn't change for your custom entities. It should have done it.

[k.rakviashvili@gmail.com]

No one standart table on fresh install has mb4 for id-s. i created many entities and they have utf8, not utf8mb4 for id-s.
only after update, new entities started generate id-s with utf8mb4 collation.
why you changed it? all new entities with link to standart entities like contacts and other will have problem after this update. please make some patch to make rebuild alter all id-s to same collation, or just return back same collation like old fresh install used.

[yuri]

> No one standart table on fresh install has mb4 for id-s

Do you mean HAD? Does a new install of a new version have mb4 or mb3 (w/o suffix) IDs?

What database system do you have? MySQL or MariaDB. Which version?

> please make some patch to make rebuild alter all id-s to same collation

The problem that if we add such a patch, an upgrade procecss can take very long time (hours) for some systems.

[yuri]

Please find a PHP file attached.

1, Copy it to espocrm root directory.

2. Run in CLI:

php convert-to-mb4.php

This will convert all mb3 columns into mb4.

3. Remove the file after the script is executed.

It's recommended to create a backup of your database before running.

[k.rakviashvili@gmail.com]

Thanks. It Worked! Great!

[k.rakviashvili@gmail.com]

Ooooops, My System Administrator is going to kill me after collation change IO statistict go to the moon. see attachment.
any ideas?

[item]

Hello,
i have make a "hack" of script of Yuri .. "on vit qu'une fois ".. on my computer and i can say : perfect .. i will try sunday on production

the hack, i change VARCHAR too.. because all relation type belongTo (one2many).. is varchar

As you can see in attachment, many related entity .. 6500000 record ... like a charm.
sort on name field, no problem, sort on another related field.. slow .. because need to be indexed
juste for information : i have not phpmyadmin on production, so i make a script for all database interaction.

Regards

$pdo = $this->getEntityManager()->getPDO();

$sql = "SHOW TABLES";
$sth = $pdo->prepare($sql);
$sth->execute();
$tableList= $sth->fetchAll();
foreach ($tableList as $table) {
$sql = 'ALTER TABLE ' .$table[0] .' CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;' ;
$pdo->prepare($sql);
$sth->execute();
$GLOBALS['log']->warning( $sql );

$query = "SHOW FULL COLUMNS FROM `". $table[0] ."` WHERE `Collation` <> 'utf8mb4_unicode_ci'";
$sth = $pdo->prepare($query);
$sth->execute();

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

$columns = array();
foreach ($rowList as $row) {
$columns[ $row['Field'] ] = $row;
}


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

fwrite(\STDOUT, ".");

$query = null;

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

switch ($columnType) {
case 'varchar':
$query = "ALTER TABLE `". $table[0] ."`
CHANGE COLUMN `". $columnName ."` `". $columnName ."` ". $columnParams['Type'] ."
CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
";
case 'text':
case 'mediumtext':
case 'longtext':
$query = "ALTER TABLE `". $table[0] ."`
CHANGE COLUMN `". $columnName ."` `". $columnName ."` ". $columnParams['Type'] ."
CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
";
break;
}
$GLOBALS['log']->warning( $sql );
$sth = $pdo->prepare($query);
$sth->execute();
}


$sql = 'ALTER TABLE ' .$table[0] .' ADD KEY(id);';
$pdo->prepare($sql);
$sth->execute();
$GLOBALS['log']->warning( $sql );

$sql = 'OPTIMIZE TABLE ' .$table[0] .';' ;
$pdo->prepare($sql);
$sth->execute();
$GLOBALS['log']->warning( $sql );
} 

[rodrigocoelho]

Please find a PHP file attached.

1, Copy it to espocrm root directory.

2. Run in CLI:

php convert-to-mb4.php

This will convert all mb3 columns into mb4.

3. Remove the file after the script is executed.

It's recommended to create a backup of your database before running.

Yuri, I recommend you doing this process in a next upgrade of Espocrm. I did this conversion and is quite faster now.

The administrator should do the upgrade by CLI and wait.

Thanks