Error enabling historic events

[gregoiregentil]

Congratulations for all the work. It's an amazing php software.

I'm on 2.1.17. When I enable "Faits historiques de France" in Historic events, I get the following error. Any idea what's wrong?

SQLSTATE[HY000]: General error: 1366 Incorrect string value: '\xF0\x9F\x87\xAB\xF0\x9F...' for column 'session_data' at row 1 (SQL: update `session` set `session_data` = initiated|b:1;_GUEST_sessiontime|s:10:"1695591394";language|s:5:"en-US";theme|s:8:"webtrees";last_gedcom_id|i:1;last_page_name|s:14:"individual.php";last_page_parameter|s:2:"I1";last_count|i:71;CSRF_TOKEN|s:32:"lwgkdj6CLwQLfsr4CLD8yhITtXcl7tW0";wt_user|i:1;flash_messages|a:2:{i:0;O:8:"stdClass":2:{s:4:"text";s:71:"The module “Faits historiques de France 🇫🇷” has been enabled.";s:6:"status";s:7:"success";}i:1;O:8:"stdClass":2:{s:4:"text";s:42:"The website preferences have been updated.";s:6:"status";s:7:"success";}} where `session_id` = 6kl4ikvtc8ir4q6rtg5o230ssn) …/vendor/illuminate/database/Connection.php:712
#0 …/vendor/illuminate/database/Connection.php(672): Illuminate\Database\Connection->runQueryCallback()
#1 …/vendor/illuminate/database/Connection.php(533): Illuminate\Database\Connection->run()
#2 …/vendor/illuminate/database/Connection.php(466): Illuminate\Database\Connection->affectingStatement()
#3 …/vendor/illuminate/database/Query/Builder.php(3063): Illuminate\Database\Connection->update()
#4 …/app/SessionDatabaseHandler.php(125): Illuminate\Database\Query\Builder->update()
#5 [internal function]: Fisharebest\Webtrees\SessionDatabaseHandler->write()
#6 …/app/Session.php(100): session_write_close()
#7 …/app/Http/Middleware/UseSession.php(80): Fisharebest\Webtrees\Session::save()
#8 …/vendor/oscarotero/middleland/src/Dispatcher.php(136): Fisharebest\Webtrees\Http\Middleware\UseSession->process()
#9 …/app/Http/Middleware/UpdateDatabaseSchema.php(57): Middleland\Dispatcher->handle()
#10 …/vendor/oscarotero/middleland/src/Dispatcher.php(136): Fisharebest\Webtrees\Http\Middleware\UpdateDatabaseSchema->process()
#11 …/app/Http/Middleware/UseDatabase.php(122): Middleland\Dispatcher->handle()
#12 …/vendor/oscarotero/middleland/src/Dispatcher.php(136): Fisharebest\Webtrees\Http\Middleware\UseDatabase->process()
#13 …/app/Http/Middleware/BadBotBlocker.php(287): Middleland\Dispatcher->handle()
#14 …/vendor/oscarotero/middleland/src/Dispatcher.php(136): Fisharebest\Webtrees\Http\Middleware\BadBotBlocker->process()
#15 …/app/Http/Middleware/CompressResponse.php(73): Middleland\Dispatcher->handle()
#16 …/vendor/oscarotero/middleland/src/Dispatcher.php(136): Fisharebest\Webtrees\Http\Middleware\CompressResponse->process()
#17 …/app/Http/Middleware/ContentLength.php(40): Middleland\Dispatcher->handle()
#18 …/vendor/oscarotero/middleland/src/Dispatcher.php(136): Fisharebest\Webtrees\Http\Middleware\ContentLength->process()
#19 …/vendor/middlewares/client-ip/src/ClientIp.php(65): Middleland\Dispatcher->handle()
#20 …/app/Http/Middleware/ClientIp.php(47): Middlewares\ClientIp->process()
#21 …/vendor/oscarotero/middleland/src/Dispatcher.php(136): Fisharebest\Webtrees\Http\Middleware\ClientIp->process()
#22 …/app/Http/Middleware/HandleExceptions.php(90): Middleland\Dispatcher->handle()
#23 …/vendor/oscarotero/middleland/src/Dispatcher.php(136): Fisharebest\Webtrees\Http\Middleware\HandleExceptions->process()
#24 …/app/Http/Middleware/BaseUrl.php(73): Middleland\Dispatcher->handle()
#25 …/vendor/oscarotero/middleland/src/Dispatcher.php(136): Fisharebest\Webtrees\Http\Middleware\BaseUrl->process()
#26 …/app/Http/Middleware/ReadConfigIni.php(68): Middleland\Dispatcher->handle()
#27 …/vendor/oscarotero/middleland/src/Dispatcher.php(136): Fisharebest\Webtrees\Http\Middleware\ReadConfigIni->process()
#28 …/app/Http/Middleware/SecurityHeaders.php(48): Middleland\Dispatcher->handle()
#29 …/vendor/oscarotero/middleland/src/Dispatcher.php(136): Fisharebest\Webtrees\Http\Middleware\SecurityHeaders->process()
#30 …/app/Http/Middleware/EmitResponse.php(57): Middleland\Dispatcher->handle()
#31 …/vendor/oscarotero/middleland/src/Dispatcher.php(136): Fisharebest\Webtrees\Http\Middleware\EmitResponse->process()
#32 …/vendor/oscarotero/middleland/src/Dispatcher.php(118): Middleland\Dispatcher->handle()
#33 …/app/Webtrees.php(275): Middleland\Dispatcher->dispatch()
#34 …/app/Webtrees.php(262): Fisharebest\Webtrees\Webtrees::dispatch()
#35 …/index.php(51): Fisharebest\Webtrees\Webtrees->httpRequest()
#36 {main}

[fisharebest]

You have added a 4-byte UTF-8 character. Probably an emoji or flag.

There is currently an open issue (#2950) to convert webtrees from 3-byte UTF-8 to 4-byte UTF-8.

This is currently in progress. The difficulty is that depending on the history of your site, the exact structure of your database is unknown (e.g. index names, foreign keys, etc.). Our database library assumes you know the exact structure.

So, I need to convert all the database migrations (and possibly the rest of the database abstraction layer) to use doctrine/dbal instead of illuminate/database.

[gregoiregentil]

I'm 100% sure that I didn't import an emoji in my gedcom. And I'm exporting - re-importing frequently my gedcom file and I know its content.

It might be a stupid suggestion but the problem might come from the history event itself with its title:

“Faits historiques de France 🇫🇷”

\xF0\x9F\x87\xAB\xF0\x9F... in the error message is actually the French flag.

[fisharebest]

It might be a stupid suggestion but the problem might come from the history event itself with its title:
“Faits historiques de France 🇫🇷”

This should not be a problem. This value is not stored in the database - only displayed.

I just enabled the french historic events on my dev machine and also on the demo site - and do not get any errors.

The error message says that this data is being stored in the "session data".

Could you have used the clippings cart to copy something? There isn't much else stored in the session.

[gregoiregentil]

I don't think so and I have re-imported my gedcom if that matters. You know your stuff... I have access to mysql and I can dump the session table. I see my id for the row mentioned in the error.

Is the problem in that table or is it somewhere else? How can I search my mysql database to find the culprit?

[fisharebest]

The data isn't in the database. webtrees attempted to write the data to the database, but MySQL rejected it.

[gregoiregentil]

I'm super confused! Sorry about that. But then, where is my problem? I have only one tree, with full control of the gedcom that I can re-import. Then, I have standard medias. That's all. In my gedcom file, there is no \xF0 character...

[fisharebest]

Is this the module that is supplied with webtrees, or is it a third-party one?

Do you have any other third-party modules installed?

[gregoiregentil]

No third-party module.

[gregoiregentil]

I'm just clicking on historic events.

[ric2016]

This should not be a problem. This value is not stored in the database - only displayed.

The original stack trace clearly shows that the module title is definitely stored in the database, as part of the session data, via the flash messages:

flash_messages|a:2:{i:0;O:8:"stdClass":2:{s:4:"text";s:71:"The module “Faits historiques de France 🇫🇷” has been enabled."

[gregoiregentil]

I have done something very stupid: I went to the php file and removed that damned flag. Problem solved! The error disappears. It seems to make sense: when enabling, the title is copied in the session row of the table in the database and the 4-byte creates the problem.

Now, where do those "faits historiques" appear? Because I don't see them anywhere. That was my initial objective to understand what this feature is about...

[fisharebest]

These facts are shown on the individuals "Facts and events" tab - but only when you use the French language.

What version of MySQL/MariaDB do you use?

[gregoiregentil]

At last, I see the events!!! I was in English language though I'm French.

mysql --version
mysql Ver 8.0.34-0ubuntu0.22.04.1 for Linux on x86_64 ((Ubuntu))

So to recap the situation, I can make it work by removing the flag (4-byte character) in the php file.

[fisharebest]

Can you run this SQL statement for me

SHOW CREATE TABLE wt_session;

Originally, the column session_data was LONGBLOB. Recently, it changed to LONGTEXT.
This was to support SQL-Server.

A LONGBLOB column will accept the 4-byte UTF8 characters.
A LONGTEXT column will give the error that you see.

So, the error depends on when you created your database.
Older installations will work OK.
Newer installations will have this error.

I am currently working on an update from utf8mb3 to utf8mb4 - which will fix this problem.
Until then, you should be able to fix it locally by changing the column from LONGTEXT to LONGBLOB;
You can do this with the following SQL statement:

ALTER TABLE wt_session CHANGE session_data session_data LONGBLOB;

[gregoiregentil]

Did you mean?

describe session;

It's longtext

mysql> describe session;
+--------------+-------------+------+-----+-------------------+-------------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+-------------------+-------------------+
| session_id | varchar(32) | NO | PRI | NULL | |
| session_time | timestamp | NO | MUL | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
| user_id | int | NO | MUL | NULL | |
| ip_address | varchar(45) | NO | | NULL | |
| session_data | longtext | NO | | NULL | |
+--------------+-------------+------+-----+-------------------+-------------------+
5 rows in set (0.04 sec)

[fisharebest]

OK - as I guessed.

Alter this column and it should fix your problem.

[gregoiregentil]

Thank you for your help. I let you close the bug and modify the code of the table creation if you think it's appropriate.

Once again, really great software!

[durangod]

Hi, i was just curious why you dont use utf8mb4_unicode_520_ci when you upgrade the db, isnt that the new standard for international acceptance? Is that because of possible legacy issue?

And i agree great software. I am sure you dont hear this enough, thank you so much :)

[fisharebest]

Hi, i was just curious why you dont use utf8mb4_unicode_520_ci when you upgrade the db, isnt that the new standard for international acceptance? Is that because of possible legacy issue?

The software was written long before utf8mb4 was available.

Database updates are problematic for many reasons. For example; on large databases, simple things like "CREATE INDEX" will take longer than most server's HTTP timeout. Also, depending on the history, some sites may have slightly different indexes (names, columns, unique, etc.). Updating databases when you can't be 100% sure of the structure is risky.

I'm currently working on this, as it is blocking most significant code development.

[durangod]

I'm currently working on this, as it is blocking most significant code development.

Thank you for the quick reply. I understand completely. I understand that for now these budget hosting companies still support old versions of php and other dependencies and are feeling the pressure now to upgrade. They have put it off for so long and now its crunch time and that puts so much pressure on developers to meet the need. Please remember that no matter what you do there is no way to make everyone happy. Someone along the way is not going to be able to use the software (or have to make custom changes to it) in their environment. That is why i stopped supporting WAMP and LAMP and a few others for my software, and you may find yourself having to cut some cords yourself in order to move forward more gracefully.

I just wanted to say you have a nice piece of software here and i thank you for your time doing it. You have some nice config options that i like very much. Even though my software project (not a competitor) is just straight PHP, HTML, CSS, JS with no packages, and no frameworks just from blank page with no helpers, i really love many of the features you have incorporated, so great job with that. I may not know my way around the framework but i do know how to search and find things.

To all that may see this message, please be patient, i can tell a good developer when i see one and this project is in good hands. So please just relax and let them do what they need to do in the time they need to do it.

Hats off to ya!