SQL error in report

[tim-pearce]

I am testing an upgrade from 2.34.0 to 2.35.1
I have a report which includes 'Names of their adult family members'.
I get error 'Fatal Error (Exception)
SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect string value: '\xCC\x83' for column jethro2351._family_adults23.names at row 1146
Line 1294 of File /usr/local/www/apache24/data/JethroRCOC/db_objects/person_query.class.php
When I look at the code, there is no mention of a datetime field!
I am therefor perplexed.

The error does not occur if is select 'Names of all their family members'.

My PHP version on this PC is 8.2.23
SQL Server version: 10.6.19-MariaDB

[tim-pearce]

I did a test with PHPMyAdmin
Recreated the person view to remove all the security (this is just a test database)
Created a table like the one in the code.
Ran the query like in the code.
Got the same error - so it seems it's not my version of PHP
Ran just the SELECT part and there is no problem.

[tim-pearce]

In PHPMyAdmin the message is
#1366 - Incorrect string value: '\xCC\x83' for column jethro2351._family_adults555.names at row 1146
There are less than 1146 rows returned by the query!

[tim-pearce]

I ran this on my database
ALTER DATABASE jethro2351 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
Now when I create a new table it gets the right encoding.

[tim-pearce]

I have also run
ALTER TABLE xxxxx CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
on all tables that did not have utf8mb4_unicode_ci

[jefft]

That does look like a character encoding problem, e.g. the same error is in the description of #754. Did changing the database encoding help at all?

There is some diagnotic SQL which shows the encodings tables at:

#754 (comment)