mORMot Open Source

Thanks.
This happened several times during testing.
Since the database wasn't generating the auto-increment key, I thought the problem was related to my class definition.
Anyway, for now I used soft delete.

At the beginning of each log, I can see `BeginCurrentThread`, but I don’t see `EndCurrentThread`.
However, the error does not occur immediately after that.

@flydev
Thanks for checking.

Sometimes it stops inside `mormot.db.sql.postgres`.
As I mentioned earlier, I had already pointed out which function it stops in.

It happened in this sample code as well.
I also included the log files in the RAR archive.

https://mega.nz/file/Qs5XEBqD#s-JGX1rwX … xqj8PwZOFQ

The database connection is set up like this:

{$ifdef DBZeos}
  DBProps := TSqlDBZeosConnectionProperties.Create('zdbc:postgresql://' + Host,DBName, DBUser, DBPass);
{$else}
  DBProps := TSqlDBPostgresConnectionProperties.Create(Host, DBName, DBUser, DBPass);
{$endif}
DBProps.UseCache := False;
DBProps.ReconnectAfterConnectionError := True;

*DBZeos is not being used.

I set ptOneFilePerThread.
I did not change the connection properties threading mode, so it is still using the default value.
As you suggested earlier, I already switched to sicShared. The previous log was also generated with that setting.
I have not changed TInterfaceMethodOptions at all.
For each table, I register it like this:
VirtualTableExternalRegister(Model, TOrmAlarm, DBProps);
And each service is defined like this:
Server.ServiceDefine(TViAlarmService, [IViAlarmService], sicShared);
Inside the services, I only use ORM.
New log : https://mega.nz/file/8k4SUYyQ#f5pgGyFb6 … 7_m5hd2B7s

I implemented the interface the same way as the others. Only ORM is used inside these methods.
ViewTask only receives an ID and performs a simple update.
GetDetail reads values from several tables and puts them into a single JSON object.

@ttomas
Ok - Thanks

---Edit---
It failed and generated 9 log files. However, after reviewing the logs, I couldn’t identify the problem.

I switched back to `useHttpSocket` again.
The database currently contains only about 20 records.

Now I got this error:
`Project Vi.exe raised exception class ESqlDBPostgres with message 'TSqlDBPostgresLib Exec failed: [insufficient data in "T" message]'`
The exception occurred in `mormot.db.sql.postgres`, inside `TSqlDBPostgresStatement.ExecutePrepared`, at this line:
`PQ.Check(c.fPGConn, 'Exec', fRes, @fRes, {andclear=}false);`
It was triggered through `TRestStorageExternal.EngineRetrieve` and `TOrm.Create`.

At first, I was using sicShared with useHttpSocket.
I thought it might work better if each instance had its own thread, so I switched to sicPerThread.

Now I’ve changed it back to sicShared and switched to useHttpAsync.
I’ll test it and see whether the database issue still happens.

Understood, I’ll remove the logs.

The code is not owned by me, so I cannot share the original project directly.

However, I already simplified the same codebase.
The server initialization, service registration, and the database access layer were not changed.
Only the models were reduced to two simple models, and the service layer was reduced to two services.

I also wrote a new and much simpler client to communicate with the server.

However, after testing the simplified version for about one hour, the issue did not occur.

In the main application, I also tried repeating the same actions many times, and the issue still did not happen consistently.
At first, I thought it was related to navigating from one menu to another, but later I realized that is not the case. It just happens from time to time.

Sometimes it happens 5 minutes after startup, and sometimes after one hour.

Since I could not reproduce the issue in the simplified version, I did not upload it anywhere yet.
I will continue testing it further, and if I manage to reproduce the issue there as well, I will upload it.

However, the error message itself suggests that two threads are using the same database connection at the same time, and this is not something I directly control in the application.

Well, now I’m getting this error:

Project Vi.exe raised exception class ESqlDBPostgres with message
“TSqlDBPostgresLib Exec failed: [message contents do not agree with length in message type "T"
server sent data ("D" message) without prior row description ("T" message)]”.

It happened.

TSqlDBPostgresStatement object 016914ac4940 was previously used by conn-2 (ThreadID=29bc) across multiple queries (lines 201–232).

After conn-2 called EndCurrentThread, the same statement object was reused by conn-4 (ThreadID=23376) — visible at line 273.

When conn-4 (ThreadID=23376) and conn-5 (ThreadID=24728) both called Prepare simultaneously on statement objects 016914ac4940 and 016914ac4ac0 respectively, a PostgreSQL protocol-level exception was raised:
message contents do not agree with length in message type "T" / server sent data ("D" message) without prior row description ("T" message)
line 309-323

Both threads were handling the same two service methods (ViTaskService.ViewTask and ViTaskService.GetDetail) concurrently, using sicPerThread instance mode.

The Statement field in the exception object is null, suggesting the statement reference was lost or corrupted at the time of the error.

Environment:

mORMot version: 2.4.14728
PostgreSQL: 127.0.0.1:5432, libpq v170006
OS: Windows 10 x64
App: x64
Service instance mode: sicPerThread
Thread pool size: 32

Does this connection method help?

{$ifdef DBZeos}
  DBProps := TSqlDBZeosConnectionProperties.Create('zdbc:postgresql://'+Host,
                  DBName, DBUser, DBPass);
{$else}
  DBProps := TSqlDBPostgresConnectionProperties.Create(Host, DBName, DBUser, DBPass);
{$endif}
DBProps.UseCache := False;
DBProps.ReconnectAfterConnectionError := True;

{$ifdef DBZeos}
DBProps.ThreadingMode := tmMainConnection;
{$else}
(DBProps as TSQLDBConnectionPropertiesThreadSafe).ThreadingMode := tmMainConnection;
{$endif}

@ab I didn’t use pointers in my code.

Everything is wrapped in try/finally blocks.
I have 21 models, 87 endpoints, and 10 services.
I used interfaces.

Also, I created the HTTP server like this:

Http := TRestHttpServer.Create(Port, [Server], '+', useHttpSocket);

And the server is created as:

Server := TRestServerDB.Create(Model, ':memory:', False);

@ttomas : What exactly was your issue?

I only used the ORM.
I should create a smaller sample project, and if the same issue still occurs, I will send it to you.

@flydev @danielkuettner

I removed the DLL files from next to the application and the error appeared, so it seems it is using those DLLs. Maybe it’s related.

I registered the services like this:

Server.ServiceDefine(TViAuthService, [IViAuthService], sicShared);

Would `sicPerThread` help?

In my application I don’t have multiple threads, but the client can send asynchronous requests. I assumed that on the server side it would be handled internally.

It happened again. This time the error was:

Project Vi.exe raised exception class EZSQLException with message 
'SQL Error: message contents do not agree with length in message type "T"
server sent data ("D" message) without prior row description ("T" message)

Code: 7 
SQL: select ID,TenantID,Email,FullName,PasswordHash,Role,AvatarUrl,CreatedAt 
from public.User where ID=?'.

After that, the application raised the following exception:

Project Vi.exe raised exception class $C0000005 with message 
'c0000005 ACCESS_VIOLATION'.

The client is using HTMX.

No, I haven’t installed it. I thought it didn’t require installation. I’ll install it now.

Ok, thanks. I’ll check it and let you know.

@ab
I updated it and everything was working fine.
After some time—without rapidly switching between menus and without any errors—everything stopped working.

The application reaches this line but never returns from it:

u := TOrmUser.Create;
try
  if not Self.Server.Orm.Retrieve('Email=?',[],[Email],u) then

Yes, I would appreciate that.

@flydev
Thanks

Hello Michalis,

I have been following Castle Game Engine for many years, but I have not used it yet.
It is a great work, I hope you succeed.
It's a good thing that you used MORMOT 2.

All relational databases operate this way and do not allow deletion. 
I said the reason for this is to provide a more appropriate message to the user. 
When the primary key of my table is registered in several other tables, I give the user more accurate and clearer information. 
Of course, everyone uses their own approach.

@JD
It’s true that my work increases. 
Managing tables and dependencies becomes difficult and time-consuming, and development must be done carefully. 
However, in return, I can show more understandable messages to the end user. 

Imagine a software user wants to delete a product. 
I check the inventory table and inform them that the product cannot be deleted because it still exists in the inventory. 
Or I let them know that the product is part of a sales invoice and therefore cannot be deleted.

I know what you're saying, but I still prefer to control the table dependencies myself. When I delete a value in one table and know it's used in another table (like master/detail tables, etc.), I use a select to make sure the record isn't being used in the related tables. The benefit of this method is that my API tells the user they cannot delete this record because it is being used in the (table name) table.