v9.0.1 Error 500 adding note to stream on Opportunity, something about collations?

[Taz_1999]

Hello

I recently upgraded to 9.01 and shortly after to 9.0.2.

Started creating Opportunities, wich works, but then I wanted to add a Note in the stream of the Opportunity.
Then I get an error 500. Tried shortening the note to just one ord, still doesn't work.

Here are some logs from when I try to create a note on an Opportunity.

# console in my browser (Brave)

POST [URL]https://crm.MYDOMAIN.se/api/v1/Note[/URL] 500 (Internal Server Error)
espo-main.js:31515
XHR failed loading: POST "https://crm.MYDOMAIN.se/api/v1/Note".
espo-main.js:31515
XHR finished loading: GET "https://crm.MYDOMAIN.se/api/v1/Notification/action/notReadCount".
espo-main.js:31515
XHR finished loading: GET "https://crm.MYDOMAIN.se/api/v1/PopupNotification/action/grouped".
espo-main.js:31515
XHR finished loading: GET "https://crm.MYDOMAIN.se/api/v1/Notification/action/notReadCount".
espo-main.js:31515
XHR finished loading: GET "https://crm.MYDOMAIN.se/api/v1/PopupNotification/action/grouped".
espo-main.js:31515
XHR finished loading: GET "https://crm.MYDOMAIN.se/api/v1/Notification/action/notReadCount".
espo-main.js:31515

And it keeps adding those last two lines for ever.

Here is the error 500, from server access log.

# Web server access log:

192.168.1.1 [01/Feb/2025:13:28:12 +0100] crm.MYDOMAIN.se "POST /api/v1/Note HTTP/1.1" 500 - "https://crm.MYDOMAIN.se/" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/132.0.0.0 Safari/537.36"

192.168.1.1 [01/Feb/2025:13:28:14 +0100] crm.MYDOMAIN.se "GET /api/v1/PopupNotification/action/grouped HTTP/1.1" 200 12 "https://crm.MYDOMAIN.se/" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/132.0.0.0 Safari/537.36"

192.168.1.1 [01/Feb/2025:13:28:19 +0100] crm.MYDOMAIN.se "GET /api/v1/Notification/action/notReadCount HTTP/1.1" 200 1 "https://crm.MYDOMAIN.se/" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/132.0.0.0 Safari/537.36"

# /data/log

[2025-02-01 12:10:20] CRITICAL: (HY000) SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_swedish_ci,IMPLICIT) for operation '=' :: GET /Opportunity/679b69767030fbcc4/stream :: /usr/local/var/www/(MYDOMAIN(/crm/application/Espo/ORM/Executor/DefaultSqlExecutor.php(77)

[2025-02-01 12:10:57] CRITICAL: (HY000) SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_swedish_ci,IMPLICIT) for operation '=' :: POST /Note :: /usr/local/var/www/(MYDOMAIN)/crm/application/Espo/ORM/Executor/DefaultSqlExecutor.php(77)

This might be a clue?

A long time ago I think I remember changing the collation of some tables in EspoCRM to get the correct sorting in my language, which is Swedish.

So most tables are utf8mb4_swedish_ci.

But not theese:

- address_country - utf8mb3_unicode_ci
- app_log_record - utf8mb3_unicode_ci
- app_secret - utf8mb3_unicode_ci


The line 77 in the line that trows the error is this:

$sth = $this->pdo->query($sql);

Same file from line 72:

private function executeSqlWithDeadlockHandling(string $sql, ?int $counter = null): PDOStatement
{
$counter = $counter ?? self::MAX_ATTEMPT_COUNT;
try {
$sth = $this->pdo->query($sql);
} catch (Exception $e) {
$counter--;
if ($counter === 0 || !$this->isExceptionIsDeadlock($e)) {
if ($this->logFailed) {
$this->logger?->error("SQL failed: " . $sql, ['isSql' => true]);
}
/** @var PDOException $e */
throw $e;
}

I don't know what field/table it is that has the incorrect collation. Don't know where to look either.

Any ideas?

PHP version 8.2.27
MySQL 8.0.30

[shalmaxb]

The log says, that there is a not allowed mix of collations. You should not use utf8_mb3 at all. You can change the collation of all tables in the database via PHPMyAdmin under "Operations" at the bottom.
Always making Backup before!

[Taz_1999]

Thank you shalmaxb.

I have not set utf8_mb3 but maybe if theese are new tables from a recent update they where created with that collation?

I did a backup and found the Operations page. First I did just the tables. But error 500 persisted.

Then I changed collation on all tables and all fields and then Error 500 disappeared.

Also, all my test notes on one Opportunity appeared, so they where saved somehow.

Seems to have solved it.

Also, there was another error 500 that is also gone: Cant select account for an opportunity.

This works now as well too.

[shalmaxb]

I doubt, that utf_mb3 was created by a recent update. It is more likely, that the database created one day long before had utf_mb3 as default and it has been overseen. I already saw providers, who used outdated MySQL Versions causing that.
But fortunately you solved it.