MySQL database does not support 4-byte UTF-8

[vytux-com]

I got this error when adding a record with a note, my guess it's the UFT images, but if they are not supported they should be cleaned up

PS. name anonymised

SQLSTATE[HY000]: General error: 1366 Incorrect string value: '\xF0\x9F\x98\x8D B...' for column 'new_gedcom' at row 1 (SQL: insert into `wt_change` (`gedcom_id`, `xref`, `old_gedcom`, `new_gedcom`, `user_id`) values (2, X926, , 0 @X926@ INDI
1 NAME L A /xyz/
2 GIVN L A
2 SURN xyz
1 SEX U
1 FAMC @X925@
1 BIRT
2 DATE BET 20 DEC 2019 AND 31 DEC 2019
1 NOTE Happy 2020 Everyone!
2 CONT Wanted to share our 2019 news..our baby arrived in the last week of this eventful decade!
2 CONT ❤️😍 Baby L A xyz 😍❤️
2 CONT Our loud, wiggly, chatty, bundle of hugs ❤️😍
1 CHAN
2 DATE 08 Jan 2020
3 TIME 12:44:53
2 _WT_USER vytautas, 1)) …/vendor/illuminate/database/Connection.php:664
#0 …/vendor/illuminate/database/Connection.php(624): Illuminate\Database\Connection->runQueryCallback('insert into `wt...', Array, Object(Closure))
#1 …/vendor/illuminate/database/Connection.php(459): Illuminate\Database\Connection->run('insert into `wt...', Array, Object(Closure))
#2 …/vendor/illuminate/database/Connection.php(411): Illuminate\Database\Connection->statement('insert into `wt...', Array)
#3 …/vendor/illuminate/database/Query/Builder.php(2646): Illuminate\Database\Connection->insert('insert into `wt...', Array)
#4 …/app/Tree.php(618): Illuminate\Database\Query\Builder->insert(Array)
#5 …/app/Http/Controllers/EditIndividualController.php(124): Fisharebest\Webtrees\Tree->createIndividual('0 @X926@ INDI\n1...')
#6 …/app/Http/Middleware/WrapHandler.php(79): Fisharebest\Webtrees\Http\Controllers\EditIndividualController->addChildAction(Object(Nyholm\Psr7\ServerRequest))
#7 …/vendor/oscarotero/middleland/src/Dispatcher.php(136): Fisharebest\Webtrees\Http\Middleware\WrapHandler->process(Object(Nyholm\Psr7\ServerRequest), Object(Middleland\Dispatcher))
#8 …/modules_v4/faces/src/Modules/FacesModule.php(82): Middleland\Dispatcher->handle(Object(Nyholm\Psr7\ServerRequest))
#9 …/vendor/oscarotero/middleland/src/Dispatcher.php(136): UksusoFF\WebtreesModules\Faces\Modules\FacesModule->process(Object(Nyholm\Psr7\ServerRequest), Object(Middleland\Dispatcher))
#10 …/app/Module/HitCountFooterModule.php(149): Middleland\Dispatcher->handle(Object(Nyholm\Psr7\ServerRequest))
#11 …/vendor/oscarotero/middleland/src/Dispatcher.php(136): Fisharebest\Webtrees\Module\HitCountFooterModule->process(Object(Nyholm\Psr7\ServerRequest), Object(Middleland\Dispatcher))
#12 …/app/Http/Middleware/AuthEditor.php(60): Middleland\Dispatcher->handle(Object(Nyholm\Psr7\ServerRequest))
#13 …/vendor/oscarotero/middleland/src/Dispatcher.php(136): Fisharebest\Webtrees\Http\Middleware\AuthEditor->process(Object(Nyholm\Psr7\ServerRequest), Object(Middleland\Dispatcher))
#14 …/app/Http/Middleware/CheckCsrf.php(75): Middleland\Dispatcher->handle(Object(Nyholm\Psr7\ServerRequest))
#15 …/vendor/oscarotero/middleland/src/Dispatcher.php(136): Fisharebest\Webtrees\Http\Middleware\CheckCsrf->process(Object(Nyholm\Psr7\ServerRequest), Object(Middleland\Dispatcher))
#16 …/vendor/oscarotero/middleland/src/Dispatcher.php(118): Middleland\Dispatcher->handle(Object(Nyholm\Psr7\ServerRequest))
#17 …/app/Http/Middleware/Router.php(121): Middleland\Dispatcher->dispatch(Object(Nyholm\Psr7\ServerRequest))
#18 …/vendor/oscarotero/middleland/src/Dispatcher.php(136): Fisharebest\Webtrees\Http\Middleware\Router->process(Object(Nyholm\Psr7\ServerRequest), Object(Middleland\Dispatcher))
#19 …/app/Http/Middleware/BootModules.php(62): Middleland\Dispatcher->handle(Object(Nyholm\Psr7\ServerRequest))
#20 …/vendor/oscarotero/middleland/src/Dispatcher.php(136): Fisharebest\Webtrees\Http\Middleware\BootModules->process(Object(Nyholm\Psr7\ServerRequest), Object(Middleland\Dispatcher))
#21 …/app/Http/Middleware/LoadRoutes.php(76): Middleland\Dispatcher->handle(Object(Nyholm\Psr7\ServerRequest))
#22 …/vendor/oscarotero/middleland/src/Dispatcher.php(136): Fisharebest\Webtrees\Http\Middleware\LoadRoutes->process(Object(Nyholm\Psr7\ServerRequest), Object(Middleland\Dispatcher))
#23 …/app/Http/Middleware/UseTransaction.php(45): Middleland\Dispatcher->handle(Object(Nyholm\Psr7\ServerRequest))
#24 …/vendor/illuminate/database/Concerns/ManagesTransactions.php(29): Fisharebest\Webtrees\Http\Middleware\UseTransaction::Fisharebest\Webtrees\Http\Middleware\{closure}(Object(Illuminate\Database\MySqlConnection))
#25 …/app/Http/Middleware/UseTransaction.php(46): Illuminate\Database\Connection->transaction(Object(Closure), 3)
#26 …/vendor/oscarotero/middleland/src/Dispatcher.php(136): Fisharebest\Webtrees\Http\Middleware\UseTransaction->process(Object(Nyholm\Psr7\ServerRequest), Object(Middleland\Dispatcher))
#27 …/app/Http/Middleware/DoHousekeeping.php(80): Middleland\Dispatcher->handle(Object(Nyholm\Psr7\ServerRequest))
#28 …/vendor/oscarotero/middleland/src/Dispatcher.php(136): Fisharebest\Webtrees\Http\Middleware\DoHousekeeping->process(Object(Nyholm\Psr7\ServerRequest), Object(Middleland\Dispatcher))
#29 …/app/Http/Middleware/UseTheme.php(70): Middleland\Dispatcher->handle(Object(Nyholm\Psr7\ServerRequest))
#30 …/vendor/oscarotero/middleland/src/Dispatcher.php(136): Fisharebest\Webtrees\Http\Middleware\UseTheme->process(Object(Nyholm\Psr7\ServerRequest), Object(Middleland\Dispatcher))
#31 …/app/Http/Middleware/CheckForMaintenanceMode.php(51): Middleland\Dispatcher->handle(Object(Nyholm\Psr7\ServerRequest))
#32 …/vendor/oscarotero/middleland/src/Dispatcher.php(136): Fisharebest\Webtrees\Http\Middleware\CheckForMaintenanceMode->process(Object(Nyholm\Psr7\ServerRequest), Object(Middleland\Dispatcher))
#33 …/app/Http/Middleware/UseLanguage.php(73): Middleland\Dispatcher->handle(Object(Nyholm\Psr7\ServerRequest))
#34 …/vendor/oscarotero/middleland/src/Dispatcher.php(136): Fisharebest\Webtrees\Http\Middleware\UseLanguage->process(Object(Nyholm\Psr7\ServerRequest), Object(Middleland\Dispatcher))
#35 …/app/Http/Middleware/UseSession.php(73): Middleland\Dispatcher->handle(Object(Nyholm\Psr7\ServerRequest))
#36 …/vendor/oscarotero/middleland/src/Dispatcher.php(136): Fisharebest\Webtrees\Http\Middleware\UseSession->process(Object(Nyholm\Psr7\ServerRequest), Object(Middleland\Dispatcher))
#37 …/app/Http/Middleware/UseFilesystem.php(58): Middleland\Dispatcher->handle(Object(Nyholm\Psr7\ServerRequest))
#38 …/vendor/oscarotero/middleland/src/Dispatcher.php(136): Fisharebest\Webtrees\Http\Middleware\UseFilesystem->process(Object(Nyholm\Psr7\ServerRequest), Object(Middleland\Dispatcher))
#39 …/app/Http/Middleware/UseCache.php(74): Middleland\Dispatcher->handle(Object(Nyholm\Psr7\ServerRequest))
#40 …/vendor/oscarotero/middleland/src/Dispatcher.php(136): Fisharebest\Webtrees\Http\Middleware\UseCache->process(Object(Nyholm\Psr7\ServerRequest), Object(Middleland\Dispatcher))
#41 …/app/Http/Middleware/UpdateDatabaseSchema.php(58): Middleland\Dispatcher->handle(Object(Nyholm\Psr7\ServerRequest))
#42 …/vendor/oscarotero/middleland/src/Dispatcher.php(136): Fisharebest\Webtrees\Http\Middleware\UpdateDatabaseSchema->process(Object(Nyholm\Psr7\ServerRequest), Object(Middleland\Dispatcher))
#43 …/app/Http/Middleware/UseDebugbar.php(67): Middleland\Dispatcher->handle(Object(Nyholm\Psr7\ServerRequest))
#44 …/vendor/oscarotero/middleland/src/Dispatcher.php(136): Fisharebest\Webtrees\Http\Middleware\UseDebugbar->process(Object(Nyholm\Psr7\ServerRequest), Object(Middleland\Dispatcher))
#45 …/app/Http/Middleware/UseDatabase.php(90): Middleland\Dispatcher->handle(Object(Nyholm\Psr7\ServerRequest))
#46 …/vendor/oscarotero/middleland/src/Dispatcher.php(136): Fisharebest\Webtrees\Http\Middleware\UseDatabase->process(Object(Nyholm\Psr7\ServerRequest), Object(Middleland\Dispatcher))
#47 …/vendor/middlewares/client-ip/src/ClientIp.php(81): Middleland\Dispatcher->handle(Object(Nyholm\Psr7\ServerRequest))
#48 …/app/Http/Middleware/ClientIp.php(47): Middlewares\ClientIp->process(Object(Nyholm\Psr7\ServerRequest), Object(Middleland\Dispatcher))
#49 …/vendor/oscarotero/middleland/src/Dispatcher.php(136): Fisharebest\Webtrees\Http\Middleware\ClientIp->process(Object(Nyholm\Psr7\ServerRequest), Object(Middleland\Dispatcher))
#50 …/app/Http/Middleware/HandleExceptions.php(75): Middleland\Dispatcher->handle(Object(Nyholm\Psr7\ServerRequest))
#51 …/vendor/oscarotero/middleland/src/Dispatcher.php(136): Fisharebest\Webtrees\Http\Middleware\HandleExceptions->process(Object(Nyholm\Psr7\ServerRequest), Object(Middleland\Dispatcher))
#52 …/app/Http/Middleware/BaseUrl.php(77): Middleland\Dispatcher->handle(Object(Nyholm\Psr7\ServerRequest))
#53 …/vendor/oscarotero/middleland/src/Dispatcher.php(136): Fisharebest\Webtrees\Http\Middleware\BaseUrl->process(Object(Nyholm\Psr7\ServerRequest), Object(Middleland\Dispatcher))
#54 …/app/Http/Middleware/ReadConfigIni.php(65): Middleland\Dispatcher->handle(Object(Nyholm\Psr7\ServerRequest))
#55 …/vendor/oscarotero/middleland/src/Dispatcher.php(136): Fisharebest\Webtrees\Http\Middleware\ReadConfigIni->process(Object(Nyholm\Psr7\ServerRequest), Object(Middleland\Dispatcher))
#56 …/app/Http/Middleware/EmitResponse.php(56): Middleland\Dispatcher->handle(Object(Nyholm\Psr7\ServerRequest))
#57 …/vendor/oscarotero/middleland/src/Dispatcher.php(136): Fisharebest\Webtrees\Http\Middleware\EmitResponse->process(Object(Nyholm\Psr7\ServerRequest), Object(Middleland\Dispatcher))
#58 …/app/Http/Middleware/PhpEnvironment.php(49): Middleland\Dispatcher->handle(Object(Nyholm\Psr7\ServerRequest))
#59 …/vendor/oscarotero/middleland/src/Dispatcher.php(136): Fisharebest\Webtrees\Http\Middleware\PhpEnvironment->process(Object(Nyholm\Psr7\ServerRequest), Object(Middleland\Dispatcher))
#60 …/vendor/oscarotero/middleland/src/Dispatcher.php(118): Middleland\Dispatcher->handle(Object(Nyholm\Psr7\ServerRequest))
#61 …/index.php(55): Middleland\Dispatcher->dispatch(Object(Nyholm\Psr7\ServerRequest))
#62 {main}

[fisharebest]

Older versions of MySQL only support UTF characters with 3 bytes.

This excludes emoji - which require 4 bytes.

Newer versions of MySQL support 4 byte UTF.

Supporting 4-byte UTF is easy.

Uprading existing sites is harder.

[fisharebest]

Another problem is that most servers cannot create indexes on more than 767 bytes.

For 3 byte (utf8), this is 255 characters.
For 4 byte (utf8_mb4), this is 191 characters.

We currently have lots of columns with indexes and 255 characters.

[vytux-com]

Is there an easy way to validate the input to warn or even disable the save button while 4byte uft is present in one field?

[fisharebest]

Is there an easy way

I can't think of an easy solution or workaround.

Using utf8mb4 on new installations is straightforward. We can create a test table that uses all the features that we need.

CREATE TABLE t (
  c VARCHAR(255) COLLATE utf8mb4_unicode_ci,
  INDEX(c)
) ENGINE=InnoDB ROW_FORMAT=dynamic;

If this is sucessful, then we can use utf8mb4. If it fails, then we use utf8.

We then store the value in data/config.ini.php.

Updating existing databases has many difficulties. It may be impossible on some servers.

[fisharebest]

Mysql <= 5.7.6 - does not support utf8mb4
Mysql > 5.7.7 and < 8.0.0 - will support utf8mb4 if innodb_large_prefix is set.
MySQL >= 8.00 - does support utf8mb4

MariaDB < 10.2.2- does not support utf8mb4
MariaDB >= 10.2.2 and < 10.3.1 - will support utf8mb4 if innodb_large_prefix is set.
MariaDB > 10.3.1 - does support utf8mb4

[HonkXL]

I can remember that the same problem was in Nextcloud 15. There the solution was:
https://docs.nextcloud.com/server/15/admin_manual/configuration_database/mysql_4byte_support.html

In the admin-backend there came up a message, that my database is not supporting utf8mb4 and I should update this.
Maybe this is a way to handle it in webtrees?

Doing
ALTER DATABASE nextcloud CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
for the webtrees-db should be simple.

But I don't know what
$ sudo -u www-data php occ maintenance:repair
is exactly doing.

[fisharebest]

ALTER DATABASE nextcloud CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

This simply changes the default used for new tables.

When we create tables/columns, we use an explicit collation - so this default value is never used.

To "upgrade" the database from 3-byte UTF to 4-byte UTF we would need to modify every column.
But we can't modify columns if foreign keys exist.
So we would need to temporarily delete all foreign keys, and recreate them afterwards.
But the foriegn keys can have many names - depending on which version of webtrees created them.

Also, modifying tables is slow. So an automatic upgrade is difficult, because each step may take longer than the webserver timeout limit.

[DaPoHou]

1. For users who can operate the server, an upgrade script can be given.
2. Users who can't operate the server to execute scripts can be advised to export gedcom files, reinstall webtrees and restore data. Or it is feasible to upgrade locally.

[DaPoHou]

Hope to support utf8mb4_general_ci in version 2.1.0.
Thanks!

[fisharebest]

Note: the surname/statistics code uses utf8_bin to disable the collation rules.