search

tbar0970 / jethro-pmm

Jethro Pastoral Ministry Manager
GNU General Public License v3.0
35 stars 25 forks source link

SQL error in report #1084

Open tim-pearce opened 5 hours ago

tim-pearce commented 5 hours ago

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 commented 4 hours ago

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 commented 4 hours ago

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 commented 3 hours ago

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 commented 3 hours ago

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