api call gives Invalid datetime format??

[jamie]

I am making up my first custom api call to make a report based on some sql but i can't get past this error

CRITICAL: (22007) SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect string value: '\xE6\xABz\xBD<w...' for column `espo`.`auth_log_record`.`username` at row 1 :: GET /allWR/getByParentAccount :: /var/www/html/application/Espo/ORM/Executor/DefaultSqlExecutor.php(77)

i've got ssl enabled and it doesn't even seem to hit the service

<?php
namespace Espo\Custom\Services;
use Espo\Core\Services\Base;
class allWR extends \Espo\Core\Templates\Services\BasePlus
{
public function getByParentAccount($parentAccountId)
{
// Safe placeholder
return [
'list' => [
['name' => 'Test Work Record']
],
'count' => 1
];
}
}​

i have created an api user, and Postman is giving me a 500 so it seems to work on auth


but i feel i have missed something somewhere to make this work

[rabii]

do you mean building an external api endpoint for the app ?

[jamie]

Do you mean building an external api endpoint for the app?

I want to develop an endpoint within ESPO at the moment to serve up a custom SQL query, though I am accessing it via Postman, the auth seems to work as I am getting an error 500 and entries in the logs, but that error just has me vexed

[rabii]

have a look at this

https://docs.espocrm.com/development/api-action/#api-actions

[jamie]

rabii no mater what i do i just keep on getting this error, maybe yuri knows something about it

[2025-10-14 12:19:27] CRITICAL: (22007) SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect string value: '\xE6\xABz\xBD<w...' for column `espo`.`auth_log_record`.`username` at row 1 :: GET /allWR :: /var/www/html/application/Espo/ORM/Executor/DefaultSqlExecutor.php(77)

[rabii]

this error indicate you have a problem on mysql query level - i don't see any issue with the api endpoint here.

[rabii]

jamie

i can't tell what you have done so far. unless you share your code here i am afraid that i won't be able to figure out what is wrong.

[jamie]

rabii i did the routes.json and Action

{
"route": "/allWR",
"method": "get",
"actionClassName": "Espo\\Modules\\company\\Api\\AllWRAction"
}

<?php
namespace Espo\Modules\company\Api;
use Espo\Core\Api\Action;
use Espo\Core\Api\Request;
use Espo\Core\Api\Response;
use Espo\Core\Api\ResponseComposer;
class AllWRAction implements Action
{
public function process(Request $request): Response
{
// Implement your logic here
// For example, fetching data from the database
$data = [
'message' => 'Hello from allWR API',
// Add more data as needed
];
return ResponseComposer::json($data);
}
}​

[rabii]

you might need to fix the module name it should Company not company (Espo will look for a capitalized name of your module)

[jamie]

it is capitalized i just changed it because this is the open internet

[jamie]

rabii yeah the error is all about sql but it gets triggered when i call the end point even though there is no sql in the endpoint

[rabii]

i understand looks as mentioned looking into the error it has nothing to do with your api endpoint. this error indicates that a string containing invalid characters is being inserted into the username column of the auth_log_record table, which likely has a VARCHAR or TEXT data type. The presence of \xE6\xABz\xBD<w... suggests that the string includes non-UTF-8 characters, possibly due to encoding mismatches.

You need to fix this issue and everything will work fine.

[jamie]

i understand looks as mentioned looking into the error it has nothing to do with your api endpoint. this error indicates that a string containing invalid characters is being inserted into the username column of the auth_log_record table, which likely has a VARCHAR or TEXT data type. The presence of \xE6\xABz\xBD
You need to fix this issue and everything will work fine.

o yeah i know thats my problem but i have no idea where its coming from or how to even start fixing it i and i don't do anyting in my code with auth_log_record, here is what the api user looks like

[rabii]

check the auth log table in the database and check row 1 - username value. or you can just delete auth log record under Administration > Auth Log

then clear cache and rebuild the system