RC is not far away: https://github.com/Elgg/Elgg/milestone/98. You can speed things up by proposing pull requests.

Your problem sounds like something that will be encountered by many admins, so please log an issue on Github. Instead of hacking it, why don't you propose a viable solution? The approach would be to check the value of innodb_large_prefix before executing that query using SHOW VARIABLES syntax: https://dev.mysql.com/doc/refman/8.0/en/show-variables.html

The upgrade class is here: https://github.com/Elgg/Elgg/blob/3.x/engine/classes/Elgg/Upgrades/AlterDatabaseToMultiByteCharset.php

Exciting

Thank you for your answer.

I could run the upgrade modifing the line 121 with :

$innodb_large_prefix = get_data_row("SHOW GLOBAL VARIABLES LIKE 'innodb_large_prefix'");
if( $innodb_large_prefix->Value !== "ON" ){
  // required to allow bigger index sizes required for utf8mb4
  update_data("SET GLOBAL innodb_large_prefix = 'ON'");
}

Emojis are not working though. They are replaced by '????' and will investigate on my side. (#11716)

Ok I got something.

I edited the file : engine/classes/Elgg/Database/DbConfig.php (on master) :
line 130 : changed 'utf8' by 'utf8mb4' and emojis are now correctly handled.

However, I don't know if it is the right way to do (I don't know Doctrine\DBAL\DriverManager) and thus won't do a PR. It might be related to #11716

EDIT: funny thing : now mysql treat all emojis as equivalent. When I search for a specific emoji, elgg return all entities containing any emoji. Found an explication on wordpress bug tracker.

Yeah I probably should open an issue on github.

Edit your elgg-settings.php and make sure config value for dbencoding is set to utf8mb4

Thanks, this value wasn't defined and now it works. (I probably missed it during the upgrade)

(Searching a specific emoji still behave weirdly by returning all entities containing 1 emoji. It seems to be caused by utf8mb4_unicode_ci / utf8mb4_unicode_520_ci not sure if it is important)

I wrote a test and it passes for me: https://github.com/Elgg/Elgg/pull/12039

Give it a try and let me know if it passes for you, and which MySQL version you are using.

Also post the exact query you are using. If you are using literal SQL it might behave differently, using elgg's DB API now runs everything through PDO and sanitizes the values according to the driver. That probably has different results than just using plain sql.

Kerjo,

do you mind sharing your process for upgrading from 2.3.x to 3.x

Upgrade the code base (using git or composer) then just run "php ./elgg-cli upgrade async", you should be done.
You may want to remove dbencoding value from elgg-settings

http://learn.elgg.org/en/3.0/admin/upgrading.html