Unexpected Server response

[Yves]

Hi,

We migrated our very stable 24/7 application form a Nexus database to a PostgreSQL 9.5 database. In Nexus we used original Nexus components. For PostgreSql we replaced the original Nexus components by UniDac.
Now, we test on several systems the stability. From time to time, e.g. once a day an "Unexpected Server Response" happens. If it happens, it's mostly on different queries, often very simple ones. These queries were already executed succesfully hundreds or thousands times before.

We added at PostgreSQL on server side different log functionality. PostgreSQL reports no problems. At the moment PostrgreSQL server is installed on the same computer to exclude network stability or problems.

Several queries are executed, let's say, at the same time in one thread. Due the asynchronous error handling in UniDac I found out that it's not always the query that shows "unexpected server response" in dbMonitor is the query that causes the real exception.

I understand a reproduction is required. Unfortunatly it's not that easy.
What causes the "unexpected server response"? What can be done to find out what the real problem is?
Is the OnError event in TUniConnection fired in the same thread that causes the problem? Or also in the Error handling thread from UniDac?
Any advice is welcome.


Kind regards,

Yves

[azyk]

UniDAC and PostgreSQL server interaction is implemented using a specific protocol which involves a strict sequence of commands sent to the server and responses received from it. In the case if commands exchange sequence with the server for some reason does not correspond to the protocol, UniDAC generates the error 'Unexpected server response'. The causes of this error can be different.

For example, this error can occur if you use a single instance of TUniConnection in multiple threads. Therefore, we strongly recommend you to use for each thread a separate instance of TUniConnection.

Try to determine at what exact moment the specified error occurs. At the moment of connection to a database, at the moment of opening a table, inserting or editing a record, opening/committing a transaction or others. Try to localize the place of the error occurrence in your application code and make it stable playback.

[Yves]

We use several threads. Each one has a separate instance of TUniConnection. In TUniQuery, TUnitable and TUniConnection in OnBeforeOpen, OnBeforeExecute and OnBeforeConnect I've added a call to procedure CheckThreadId

Code: Select all

procedure TSomeObject.OnCheckThreadId(Sender: TObject);
var ThreadId: Cardinal;
begin
  ThreadId := GetCurrentThreadId;
  if (ThreadIdCreate <> ThreadId) then
    raise Exception.Create(Format('ThreadIdCreate %d <> CurrentThreadId %d', [ThreadIdCreate, ThreadId]));
end;

That Exception is never raised. ThreadIdCreate is assigned in the OnCreate method of the object. That means all calls to PostGreSQL are thread safe. By disabling some option in our application to avoid database communciation in a thread that results in a stable situation.

'Unexpected server response' happens on random SQL and often on very simple select statements in a TUniQuery. Also in the VCL-thread.

It looks like UniDac is not stable when using several threads. The challenge is: how to make it stable also with UniDac?
Are there other reasons besides unthread safe code that causes 'Unexpected server response'?

[azyk]

'Unexpected server response' probably occurs not due to UniDAC functionality, but as a result of user application and server interaction.

To understand how to avoid this error, we need to reproduce it in our environment. For this we need a sample where it is stably reproduced. Please try to compose a sample and send it to us using the contact form on our website: https://www.devart.com/company/contactform.html

[Yves]

I try to make a test application to reproduce. Until now without success. It's not only 'Unexpected server response'. This morning the call stack of the crash was:

Code: Select all

EurekaLog 7.5.0.0  

Exception:
----------------------------------------------------------------------------------------
  2.1 Date          : Thu, 29 Dec 2016 09:02:11 +0100
  2.2 Address       : 00BFA0C0
  [...]
  2.5 Type          : EAssertionFailed
  2.6 Message       : Assertion failure (C:\Program Files (x86)\Devart\Source\UniProviders\PostgreSQL\PgSQLProtocolUni.pas, line 1306).

Call Stack Information:
----------------------------------------------------------------------------------------
|Methods |   |Unit             |Class                     |Procedure/Method   |Line    |
----------------------------------------------------------------------------------------
|*Exception Thread: ID=5180; Parent=0; Priority=0                                      |
|Class=; Name=MAIN                                                                     |
|DeadLock=0; Wait Chain=                                                               |
|Comment=                                                                              |
|--------------------------------------------------------------------------------------|
|00000020|04 |PgSQLProtocolUni |TPgSQLProtocol            |InternalExecuteStmt|1512[13]|
|7FFFFFFE|04 |PgSQLProtocolUni |TPgSQLProtocol            |ProcessMessageQueue|1306[1] |
|00000020|04 |PgSQLProtocolUni |TPgSQLProtocol            |ExecuteStmt        |822[20] |
|00000020|04 |PgClassesUni     |TPgSQLCommand             |InternalExecute    |6628[45]|
|00000020|04 |PgClassesUni     |TPgSQLCommand             |InternalExecute    |6644[61]|
|00000020|04 |PgClassesUni     |TPgSQLCommand             |Execute            |6141[5] |
|00000020|04 |PgClassesUni     |TPgSQLConnection          |ExecCommand        |4354[3] |
|00000020|04 |PgClassesUni     |TPgSQLAlerter             |SendEvent          |9093[2] |
|00000020|04 |DAAlerter        |TDAAlerter                |SendEvent          |142[3]  |
|00000020|04 |UniDacMessaging  |TDataModuleUniDacMessaging|Broadcast          |153[8]  |
|00000020|04 |Main             |TMainForm                 |WMHISTORYREFRESH   |2525[5] |

What can cause that crash? The Assertion is raised in the PgSQLProtocolUni.pas, line 1306. See call stack above.
The procedure Broadcast was succesfully called befor for hundreds, or thousands times.

Code: Select all

procedure TDataModuleUniDacMessaging.Broadcast(MessageID: Integer);
var
  XmlNode : TXmlNode;
begin
  if not UniAlerter.Active then
    exit;

  XmlNode := FXmlDoc.Root.FindNode('MessageID');
  XmlNode.Clear;
  XmlNode.WriteAttributeInteger('Value', MessageID);
  OnCheckThreadId(Self);
  UniAlerter.SendEvent(UniAlerter.Events, FXmlDoc.WriteToString);
end;

[azyk]

The information you provided is insufficient to investigate the causes of Assertion failure. We need an example to reproduce the problem.

[Shapoval]

I also experience the "Unexpected server response" issue with a PostgreSQL database. In my case it's rather easy to reproduce. I have two PostgreSQL servers. Server 1 is a PostgreSQL 9.3.5 server, located on my development PC (localhost). Server 2 is a PostgreSQL 9.6.1 server, located on a virtual machine in the Amazon cloud.

If I connect to the Server 2 and execute a query like "select from SOMETABLE" (with no fields specified, just like it's spelled here), I get the "Unexpected server response" error. If I connect to the Server 1 and do the same, I get the expected "syntax error at or near 'FROM'" error message.

The worst thing is that in the first case the whole connection crashes and the subsequent valid queries return the same error message. My application allows user to create own SQL queries, so the situation above doesn't seem too hypothetical.

The steps to reproduce the error are quite simple. The only code I have in the application is the following:

Code: Select all

  UniConnection1.Server:='...';
  UniConnection1.Database:='...';
  UniConnection1.Username:='...';
  UniConnection1.Password:='...';
  UniConnection1.Connect;
  UniQuery1.SQL.Text:='select from bicapp.bic_datamart';
  UniQuery1.Open;

I use Delphi 7 if it matters.

[Yves]

We found the cause of the "Unexpected server response" errors. It was a rare situation that uses an uniconnection from an other wrong thread.
The "Unexpected server response" is raised in an other thread then the one were the source of the problem was located. That made it very difficult to find out the source of the problem.