Broker Rejects Connection

[Brandlhuber Florian]

Hello Community,

we have written an application where we are connecting to a progress database via ODBC and SQL. On the other side there is running an proAlpha application.
Our application is written in PHP and is running an a Debian Linux Server.

When we are executing a SQL statement sometimes the broker rejects the connection and proAlpha and our application is not running anymore and the whole company cannot work anymore and we have to restart the environment.

Our odbc.ini looks like this:

[ODBC Data Sources]
#Progress=Progress_SQL92_Driver
progress_production=Progress_SQL92_Driver
progress_test01=Progress_SQL92_Driver
progress_test02=Progress_SQL92_Driver

[progress_production]
Driver=/usr/dlc/odbc/lib/pgoe27.so
DatabaseName=pavar
PortNumber=12010
HostName=192.168.50.244
DefaultIsolationLevel = READ UNCOMMITTED
DataSourceTransactionIsolation = READ UNCOMMITTED
TRANSACTIONISOLATION = READ UNCOMMITTED

We already added the "WITH(NOLOCK)" statement to each SELECT-statement so that we won't get the error "Failure getting record lock on a record from table".

The proAlpha-Logfile contains the following entries

[2015/11/05@12:35:56.140+0100] P-14344      T-14604 I SRV     3: (8873)  Login usernum 156, remote SQL client.
[2015/11/05@12:35:56.141+0100] P-14344      T-14604 I SRV     3: (7129)  Usr 156 setzte Name zu odbc_read.
[2015/11/05@12:35:56.519+0100] P-14344      T-14604 I SRV     3: (453)   Logout von odbc_read auf  .
[2015/11/05@12:36:01.412+0100] P-14344      T-10756 I SRV     3: (8873)  Login usernum 156, remote SQL client.
[2015/11/05@12:36:01.412+0100] P-14344      T-10756 I SRV     3: (7129)  Usr 156 setzte Name zu odbc_read.
[2015/11/05@12:36:01.449+0100] P-14344      T-10756 I SRV     3: (453)   Logout von odbc_read auf  .
[2015/11/05@12:36:03.757+0100] P-14344      T-14612 I SRV     3: (8873)  Login usernum 156, remote SQL client.
[2015/11/05@12:36:03.757+0100] P-14344      T-14612 I SRV     3: (7129)  Usr 156 setzte Name zu odbc_read.
[2015/11/05@12:36:03.791+0100] P-14344      T-14612 I SRV     3: (453)   Logout von odbc_read auf  .
[2015/11/05@12:36:08.213+0100] P-14344      T-5508  I SRV     3: (8873)  Login usernum 156, remote SQL client.
[2015/11/05@12:36:08.214+0100] P-14344      T-5508  I SRV     3: (7129)  Usr 156 setzte Name zu odbc_read.
[2015/11/05@12:36:12.778+0100] P-14344      T-5508  I SRV     3: (453)   Logout von odbc_read auf  .
[2015/11/05@12:36:17.762+0100] P-14344      T-15128 I SRV     3: (8873)  Login usernum 156, remote SQL client.
[2015/11/05@12:36:17.763+0100] P-14344      T-15128 I SRV     3: (7129)  Usr 156 setzte Name zu odbc_read.
[2015/11/05@12:36:18.615+0100] P-14344      T-7864  I SRV     3: (8873)  Login usernum 128, remote SQL client.
[2015/11/05@12:36:18.616+0100] P-14344      T-7864  I SRV     3: (7129)  Usr 128 setzte Name zu odbc_read.
[2015/11/05@12:36:21.844+0100] P-14344      T-7864  I SRV     3: (453)   Logout von odbc_read auf  .
[2015/11/05@12:36:22.487+0100] P-14344      T-13620 I SRV     3: (8873)  Login usernum 128, remote SQL client.
[2015/11/05@12:36:22.488+0100] P-14344      T-13620 I SRV     3: (7129)  Usr 128 setzte Name zu odbc_read.
[2015/11/05@12:36:22.576+0100] P-14344      T-13620 I SRV     3: (453)   Logout von odbc_read auf  .
[2015/11/05@12:36:27.664+0100] P-14344      T-11504 I SRV     3: (8873)  Login usernum 128, remote SQL client.
[2015/11/05@12:36:27.664+0100] P-14344      T-11504 I SRV     3: (7129)  Usr 128 setzte Name zu odbc_read.
[2015/11/05@12:36:28.232+0100] P-14344      T-10692 I SRV     3: (8873)  Login usernum 127, remote SQL client.
[2015/11/05@12:36:28.232+0100] P-14344      T-10692 I SRV     3: (7129)  Usr 127 setzte Name zu odbc_read.
[2015/11/05@12:36:28.379+0100] P-14344      T-10692 I SRV     3: (453)   Logout von odbc_read auf  .
[2015/11/05@12:36:41.467+0100] P-13960      T-9348  I BROKER  2: (1153)  BROKER entdeckte toten Server 14344.
[2015/11/05@12:36:41.468+0100] P-13960      T-9348  I BROKER  2: (8839)  Keine SQL Server sind verfügbar.  Versuche später nochmal.
[2015/11/05@12:36:43.673+0100] P-13960      T-9348  I BROKER  2: (1153)  BROKER entdeckte toten Server 14344.
[2015/11/05@12:36:43.673+0100] P-13960      T-9348  I BROKER  2: (8839)  Keine SQL Server sind verfügbar.  Versuche später nochmal.
[2015/11/05@12:36:46.144+0100] P-13960      T-9348  I BROKER  2: (1153)  BROKER entdeckte toten Server 14344.
[2015/11/05@12:36:46.145+0100] P-13960      T-9348  I BROKER  2: (8839)  Keine SQL Server sind verfügbar.  Versuche später nochmal.
[2015/11/05@12:36:50.155+0100] P-13960      T-9348  I BROKER  2: (1153)  BROKER entdeckte toten Server 14344.
[2015/11/05@12:36:50.156+0100] P-13960      T-9348  I BROKER  2: (8839)  Keine SQL Server sind verfügbar.  Versuche später nochmal.
[2015/11/05@12:36:52.111+0100] P-13960      T-9348  I BROKER  2: (1153)  BROKER entdeckte toten Server 14344.
[2015/11/05@12:36:52.111+0100] P-13960      T-9348  I BROKER  2: (8839)  Keine SQL Server sind verfügbar.  Versuche später nochmal.
[2015/11/05@12:36:53.809+0100] P-13960      T-9348  I BROKER  2: (1153)  BROKER entdeckte toten Server 14344.
[2015/11/05@12:36:53.810+0100] P-13960      T-9348  I BROKER  2: (8839)  Keine SQL Server sind verfügbar.  Versuche später nochmal.
[2015/11/05@12:36:58.825+0100] P-13960      T-9348  I BROKER  2: (1153)  BROKER entdeckte toten Server 14344.
[2015/11/05@12:36:58.826+0100] P-13960      T-9348  I BROKER  2: (8839)  Keine SQL Server sind verfügbar.  Versuche später nochmal.
[2015/11/05@12:37:07.911+0100] P-13960      T-9348  I BROKER  2: (1153)  BROKER entdeckte toten Server 14344.
[2015/11/05@12:37:07.912+0100] P-13960      T-9348  I BROKER  2: (8839)  Keine SQL Server sind verfügbar.  Versuche später nochmal.
[2015/11/05@12:37:15.050+0100] P-13960      T-9348  I BROKER  2: (1153)  BROKER entdeckte toten Server 14344.
[2015/11/05@12:37:15.051+0100] P-13960      T-9348  I BROKER  2: (8839)  Keine SQL Server sind verfügbar.  Versuche später nochmal.
[2015/11/05@12:37:15.264+0100] P-13608      T-2864  I WDOG   28: (2526)  Abtrennen Client 128 des verendeten Servers 3.
[2015/11/05@12:37:15.265+0100] P-13608      T-2864  I WDOG  128: (5029)  SYSTEM ERROR: Freigabe multiplexed latch. latchId: 29697729

When we try to connect with our application we get the error

[error] [client 192.168.50.77] PHP Warning:  odbc_connect(): SQL error: [unixODBC][DataDirect][ODBC Progress OpenEdge Wire Protocol driver][OPENEDGE]Broker rejects connection., SQL state S1000 in SQLConnect in /home/user1001/www/htdocs/home/inc/datenbank.php on line 3

Before this error we often get this error message

[error] [client 192.168.1.4] PHP Warning:  odbc
_prepare(): SQL error: [unixODBC][DataDirect][ODBC Progress OpenEdge Wire
Protocol driver]Socket closed., SQL state 08S01 in SQLPrepare in

We are making sure that all SQL statements are correkt, that the connection to the database is closed immediately.

What we are not yet tried is to add this code after connection because the broker rejected connection before we can execute this:

$result = odbc_exec( $_SESSION['odbc_connect_01'], "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED" );

What can we do that no dead server is found or how we can resolve this error?

 

[Rob Fitzpatrick]

"Broker rejects connection" could be due to the _Connect table being full. Do you know how many filled _Connect records you have at the time, compared with the value of -n?

What is your OpenEdge version, including service pack?

 

[Brandlhuber Florian]

Can you tell me how to find the table _Connect or how to find out how many records are in there.
Where is the value -n is defined?

Our OpenEdge Version: 11.3.02.000 1333

 

[Rob Fitzpatrick]

_Connect is a Virtual System Table (VST): an in-memory data structure that can be queried like a real table. _Connect is the VST that contains information about each connected process, including brokers, servers, ABL clients, SQL clients, Progress utilities, etc.

The _Connect table has a finite number of records which is determined at database startup by the value of certain broker startup parameters. Specifically, the number of records in _Connect is (value of -Mn) + (value of -n) + 2. For example, if -Mn is 10 and -n is 40 then there are 52 records in _Connect. When a database client connects to the database, information about that client is written in an _Connect record and remains until the client disconnects or the database is shut down. When all _Connect records are in use then no more processes (servers, clients, etc.) can connect.

Where to find the values of your broker startup parameters is determined by how you start your database. In Unix, parameters and their values are typically in a shell script or in a parameter file (.pf file) that is referenced by a shell script that runs one or more proserve commands. The proserve command is a Progress shell script (in $DLC/bin) that starts database brokers (_mprosrv processes). You can also see startup parameter values in the database log (dbname.lg) in the same directory where the .db file is located. Look for the most recent database startup in the log file (a (333) message) and after that you will see a series of line items describing the startup parameters and their values.

If you have access to a Progress procedure editor and you have a compiler license installed, you can write something like this to see connnected users:

for each _connect where _connect-name <> ?:
  display
    _connect-usr
    _connect-name
    _connect-type
    _connect-clienttype
  .
end.

From SQL, you could do something like "select * from PUB."_Connect";". That would show all records but you could add a predicate as desired.

Alternatively, you can use the Progress database monitor (promon) to view connected users and some of the startup parameters. The syntax is promon <path to database>, e.g. promon /path/to/dbname.db.

Once in promon, you can see users (_Connect records) in menu option 1, sub-option 1. You can see startup parameters, including -Mn and -n, in menu option 6.

 

[Brandlhuber Florian]

Now i found the -Mn and -n parameters. ProAplha has a tool where we can edit these parameters.

How we solve the problem that the broker will not detect dead server?
Errormessage "BROKER entdeckte toten Server" ( "broker detects death of server" )
Where this error comes from?

With SQL-Statement we could not read because it was not found.

From proAlpha-Support we got the table BCR_Connections. Does anybody know if this is the same table as _Connect?

 

[Rob Fitzpatrick]

How we solve the problem that the broker will not detect dead server?
Errormessage "BROKER entdeckte toten Server" ( "broker detects death of server" )

It sounds very much like the broker is detecting the death of the server. That's what the (1153) message is telling you. A better question might be "why is the server dying?" Probably due to an OpenEdge bug. But I'd put that question to proAlpha Support.

From proAlpha-Support we got the table BCR_Connections. Does anybody know if this is the same table as _Connect?

If it has a different name it's a different table. If you're not comfortable creating ad hoc queries, the easiest way to see _Connect contents is to use screen 1,1 in promon.

With SQL-Statement we could not read because it was not found.

I don't know what you mean by this.