How to fix 0xC0000005 exception in Postgres 9.0
Hi!
Every time when user tries to log on from same virtual computer where
Postgreql resides, Postgres terminates with 0xC0000005 exception. Log is
below.
About 65 users are accessing this server over VPN. They can use server
normally.
Logging from localhost using pgadmin 4 with user postgres also works.
Only any attempt to log on from localhost with user dbadmin from psqlODBC
client causes this exception.
It has worked normally for many years but now suddenly stopped working for
localhost.
How to fix this ?
Server:
PostgreSQL 9.0.4, compiled by Visual C++ build 1500, 64-bit
OS:
Windows server 2008 R2
Version 6.1 Build 7601
Andrus.
Postgres log:
2020-02-20 15:44:51 EET LOG: server process (PID 3788) was terminated by
exception 0xC0000005
2020-02-20 15:44:51 EET HINT: See C include file "ntstatus.h" for a
description of the hexadecimal value.
2020-02-20 15:44:51 EET LOG: terminating any other active server
processes
2020-02-20 15:44:51 EET andrus mydatabase WARNING: terminating connection
because of crash of another server process
2020-02-20 15:44:51 EET andrus mydatabase DETAIL: The postmaster has
commanded this server process to roll back the current transaction and exit,
because another server process exited abnormally and possibly corrupted
shared memory.
2020-02-20 15:44:51 EET andrus mydatabase HINT: In a moment you should be
able to reconnect to the database and repeat your command.
2020-02-20 15:44:51 EET kati mydatabase WARNING: terminating connection
because of crash of another server process
2020-02-20 15:44:51 EET kati mydatabase DETAIL: The postmaster has
commanded this server process to roll back the current transaction and exit,
because another server process exited abnormally and possibly corrupted
shared memory.
2020-02-20 15:44:51 EET kati mydatabase HINT: In a moment you should be
able to reconnect to the database and repeat your command.
2020-02-20 15:44:51 EET julia mydatabase WARNING: terminating connection
because of crash of another server process
2020-02-20 15:44:51 EET julia mydatabase DETAIL: The postmaster has
commanded this server process to roll back the current transaction and exit,
because another server process exited abnormally and possibly corrupted
shared memory.
2020-02-20 15:44:51 EET julia mydatabase HINT: In a moment you should be
able to reconnect to the database and repeat your command.
2020-02-20 15:44:51 EET reinpuu mydatabase WARNING: terminating connection
because of crash of another server process
2020-02-20 15:44:51 EET reinpuu mydatabase DETAIL: The postmaster has
commanded this server process to roll back the current transaction and exit,
because another server process exited abnormally and possibly corrupted
shared memory.
2020-02-20 15:44:51 EET reinpuu mydatabase HINT: In a moment you should be
able to reconnect to the database and repeat your command.
2020-02-20 15:44:51 EET kokka mydatabase WARNING: terminating connection
because of crash of another server process
2020-02-20 15:44:51 EET kokka mydatabase DETAIL: The postmaster has
commanded this server process to roll back the current transaction and exit,
because another server process exited abnormally and possibly corrupted
shared memory.
2020-02-20 15:44:51 EET kokka mydatabase HINT: In a moment you should be
able to reconnect to the database and repeat your command.
2020-02-20 15:44:51 EET myll mydatabase WARNING: terminating connection
because of crash of another server process
2020-02-20 15:44:51 EET myll mydatabase DETAIL: The postmaster has
commanded this server process to roll back the current transaction and exit,
because another server process exited abnormally and possibly corrupted
shared memory.
2020-02-20 15:44:51 EET myll mydatabase HINT: In a moment you should be
able to reconnect to the database and repeat your command.
2020-02-20 15:44:51 EET WARNING: terminating connection because of crash
of another server process
2020-02-20 15:44:51 EET DETAIL: The postmaster has commanded this server
process to roll back the current transaction and exit, because another
server process exited abnormally and possibly corrupted shared memory.
2020-02-20 15:44:51 EET HINT: In a moment you should be able to reconnect
to the database and repeat your command.
2020-02-20 15:44:51 EET dbadmin mydatabase FATAL: the database system is in
recovery mode
2020-02-20 15:44:51 EET LOG: all server processes terminated;
reinitializing
2020-02-20 15:45:01 EET FATAL: pre-existing shared memory block is still
in use
2020-02-20 15:45:01 EET HINT: Check if there are any old server processes
still running, and terminate them.
On 2/20/20 12:35 PM, Andrus wrote:
Hi!
Every time when user tries to log on from same virtual computer where
Postgreql resides, Postgres terminates with 0xC0000005 exception. Log is
below.About 65 users are accessing this server over VPN. They can use server
normally.
Logging from localhost using pgadmin 4 with user postgres also works.Only any attempt to log on from localhost with user dbadmin from
psqlODBC client causes this exception.It has worked normally for many years but now suddenly stopped working
for localhost.How to fix this ?
Upgrade to a version of Postgres that is not 5 years past EOL?
Server:
PostgreSQL 9.0.4, compiled by Visual C++ build 1500, 64-bit
OS:
Windows server 2008 R2
Version 6.1 Build 7601Andrus.
Postgres log:
2020-02-20 15:44:51 EETļæ½ļæ½ LOG:ļæ½ server process (PID 3788) was terminated
by exception 0xC0000005
2020-02-20 15:44:51 EETļæ½ļæ½ HINT:ļæ½ See C include file "ntstatus.h" for a
description of the hexadecimal value.
2020-02-20 15:44:51 EETļæ½ļæ½ LOG:ļæ½ terminating any other active server
processes
2020-02-20 15:44:51 EET andrus mydatabase WARNING:ļæ½ terminating
connection because of crash of another server process
2020-02-20 15:44:51 EET andrus mydatabase DETAIL:ļæ½ The postmaster has
commanded this server process to roll back the current transaction and
exit, because another server process exited abnormally and possibly
corrupted shared memory.
2020-02-20 15:44:51 EET andrus mydatabase HINT:ļæ½ In a moment you should
be able to reconnect to the database and repeat your command.
2020-02-20 15:44:51 EET kati mydatabase WARNING:ļæ½ terminating connection
because of crash of another server process
2020-02-20 15:44:51 EET kati mydatabase DETAIL:ļæ½ The postmaster has
commanded this server process to roll back the current transaction and
exit, because another server process exited abnormally and possibly
corrupted shared memory.
2020-02-20 15:44:51 EET kati mydatabase HINT:ļæ½ In a moment you should be
able to reconnect to the database and repeat your command.
2020-02-20 15:44:51 EET julia mydatabase WARNING:ļæ½ terminating
connection because of crash of another server process
2020-02-20 15:44:51 EET julia mydatabase DETAIL:ļæ½ The postmaster has
commanded this server process to roll back the current transaction and
exit, because another server process exited abnormally and possibly
corrupted shared memory.
2020-02-20 15:44:51 EET julia mydatabase HINT:ļæ½ In a moment you should
be able to reconnect to the database and repeat your command.
2020-02-20 15:44:51 EET reinpuu mydatabase WARNING:ļæ½ terminating
connection because of crash of another server process
2020-02-20 15:44:51 EET reinpuu mydatabase DETAIL:ļæ½ The postmaster has
commanded this server process to roll back the current transaction and
exit, because another server process exited abnormally and possibly
corrupted shared memory.
2020-02-20 15:44:51 EET reinpuu mydatabase HINT:ļæ½ In a moment you should
be able to reconnect to the database and repeat your command.
2020-02-20 15:44:51 EET kokka mydatabase WARNING:ļæ½ terminating
connection because of crash of another server process
2020-02-20 15:44:51 EET kokka mydatabase DETAIL:ļæ½ The postmaster has
commanded this server process to roll back the current transaction and
exit, because another server process exited abnormally and possibly
corrupted shared memory.
2020-02-20 15:44:51 EET kokka mydatabase HINT:ļæ½ In a moment you should
be able to reconnect to the database and repeat your command.
2020-02-20 15:44:51 EET myll mydatabase WARNING:ļæ½ terminating connection
because of crash of another server process
2020-02-20 15:44:51 EET myll mydatabase DETAIL:ļæ½ The postmaster has
commanded this server process to roll back the current transaction and
exit, because another server process exited abnormally and possibly
corrupted shared memory.
2020-02-20 15:44:51 EET myll mydatabase HINT:ļæ½ In a moment you should be
able to reconnect to the database and repeat your command.
2020-02-20 15:44:51 EETļæ½ļæ½ WARNING:ļæ½ terminating connection because of
crash of another server process
2020-02-20 15:44:51 EETļæ½ļæ½ DETAIL:ļæ½ The postmaster has commanded this
server process to roll back the current transaction and exit, because
another server process exited abnormally and possibly corrupted shared
memory.
2020-02-20 15:44:51 EETļæ½ļæ½ HINT:ļæ½ In a moment you should be able to
reconnect to the database and repeat your command.
2020-02-20 15:44:51 EET dbadmin mydatabase FATAL:ļæ½ the database system
is in recovery mode
2020-02-20 15:44:51 EETļæ½ļæ½ LOG:ļæ½ all server processes terminated;
reinitializing
2020-02-20 15:45:01 EETļæ½ļæ½ FATAL:ļæ½ pre-existing shared memory block is
still in use
2020-02-20 15:45:01 EETļæ½ļæ½ HINT:ļæ½ Check if there are any old server
processes still running, and terminate them.
--
Adrian Klaver
adrian.klaver@aklaver.com
This error is really common and is caused by windows via UAC, SFC,
Firewarll, AV etc.. for it spontaneously to appear means system config
change happened or update occurred.
Common sources windows updates, anti-viruses changes, firewall changes.
If its not the above 4 figuring out what causing this is near impossible
as something is trying to access a shared memory resource that does not
have access rights to it or app that is accessing it is has a bug and
corrupting PostgreSQL memory.
Given this is localhost connection, start looking at firewall or AV.
On Thu, Feb 20, 2020 at 3:35 PM Andrus <kobruleht2@hot.ee> wrote:
Show quoted text
Hi!
Every time when user tries to log on from same virtual computer where
Postgreql resides, Postgres terminates with 0xC0000005 exception. Log is
below.About 65 users are accessing this server over VPN. They can use server
normally.
Logging from localhost using pgadmin 4 with user postgres also works.Only any attempt to log on from localhost with user dbadmin from psqlODBC
client causes this exception.It has worked normally for many years but now suddenly stopped working for
localhost.How to fix this ?
Server:
PostgreSQL 9.0.4, compiled by Visual C++ build 1500, 64-bit
OS:
Windows server 2008 R2
Version 6.1 Build 7601Andrus.
Postgres log:
2020-02-20 15:44:51 EET LOG: server process (PID 3788) was terminated
by
exception 0xC0000005
2020-02-20 15:44:51 EET HINT: See C include file "ntstatus.h" for a
description of the hexadecimal value.
2020-02-20 15:44:51 EET LOG: terminating any other active server
processes
2020-02-20 15:44:51 EET andrus mydatabase WARNING: terminating connection
because of crash of another server process
2020-02-20 15:44:51 EET andrus mydatabase DETAIL: The postmaster has
commanded this server process to roll back the current transaction and
exit,
because another server process exited abnormally and possibly corrupted
shared memory.
2020-02-20 15:44:51 EET andrus mydatabase HINT: In a moment you should be
able to reconnect to the database and repeat your command.
2020-02-20 15:44:51 EET kati mydatabase WARNING: terminating connection
because of crash of another server process
2020-02-20 15:44:51 EET kati mydatabase DETAIL: The postmaster has
commanded this server process to roll back the current transaction and
exit,
because another server process exited abnormally and possibly corrupted
shared memory.
2020-02-20 15:44:51 EET kati mydatabase HINT: In a moment you should be
able to reconnect to the database and repeat your command.
2020-02-20 15:44:51 EET julia mydatabase WARNING: terminating connection
because of crash of another server process
2020-02-20 15:44:51 EET julia mydatabase DETAIL: The postmaster has
commanded this server process to roll back the current transaction and
exit,
because another server process exited abnormally and possibly corrupted
shared memory.
2020-02-20 15:44:51 EET julia mydatabase HINT: In a moment you should be
able to reconnect to the database and repeat your command.
2020-02-20 15:44:51 EET reinpuu mydatabase WARNING: terminating
connection
because of crash of another server process
2020-02-20 15:44:51 EET reinpuu mydatabase DETAIL: The postmaster has
commanded this server process to roll back the current transaction and
exit,
because another server process exited abnormally and possibly corrupted
shared memory.
2020-02-20 15:44:51 EET reinpuu mydatabase HINT: In a moment you should
be
able to reconnect to the database and repeat your command.
2020-02-20 15:44:51 EET kokka mydatabase WARNING: terminating connection
because of crash of another server process
2020-02-20 15:44:51 EET kokka mydatabase DETAIL: The postmaster has
commanded this server process to roll back the current transaction and
exit,
because another server process exited abnormally and possibly corrupted
shared memory.
2020-02-20 15:44:51 EET kokka mydatabase HINT: In a moment you should be
able to reconnect to the database and repeat your command.
2020-02-20 15:44:51 EET myll mydatabase WARNING: terminating connection
because of crash of another server process
2020-02-20 15:44:51 EET myll mydatabase DETAIL: The postmaster has
commanded this server process to roll back the current transaction and
exit,
because another server process exited abnormally and possibly corrupted
shared memory.
2020-02-20 15:44:51 EET myll mydatabase HINT: In a moment you should be
able to reconnect to the database and repeat your command.
2020-02-20 15:44:51 EET WARNING: terminating connection because of
crash
of another server process
2020-02-20 15:44:51 EET DETAIL: The postmaster has commanded this
server
process to roll back the current transaction and exit, because another
server process exited abnormally and possibly corrupted shared memory.
2020-02-20 15:44:51 EET HINT: In a moment you should be able to
reconnect
to the database and repeat your command.
2020-02-20 15:44:51 EET dbadmin mydatabase FATAL: the database system is
in
recovery mode
2020-02-20 15:44:51 EET LOG: all server processes terminated;
reinitializing
2020-02-20 15:45:01 EET FATAL: pre-existing shared memory block is
still
in use
2020-02-20 15:45:01 EET HINT: Check if there are any old server
processes
still running, and terminate them.
Hi!
Given this is localhost connection, start looking at firewall or AV.
Windows firewall is turned off. It does not affect to local connections in
same computer.
I turned windows antivirus off but problem persists.
Andrus.
Hi!
Upgrade to a version of Postgres that is not 5 years past EOL?
Not possible at moment because requires lot of testing not to break existing
applications.
Planned in future.
Andrus.
On 2/20/20 1:27 PM, Andrus wrote:
Hi!
Upgrade to a version of Postgres that is not 5 years past EOL?
Not possible at moment because requires lot of testing not to break
existing applications.
Planned in future.
Alright.
Then what changes were done recently, in particular ODBC-related.
Andrus.
--
Adrian Klaver
adrian.klaver@aklaver.com
Hi!
Then what changes were done recently, in particular ODBC-related.
Same psqlODBC driver 09.00.0101 has worked 10 years.
Previously I logged into this server long time ago and then it worked.
Windows update history shows lot of updates all called
Security Intelligence Update for Microsoft Endpoint Protection - KB 2461484
with different version numbers.
Andrus.
Attachments:
image[1].pngimage/png; name="image[1].png"Download+0-1
On 2/20/20 2:29 PM, Andrus wrote:
Hi!
Then what changes were done recently, in particular ODBC-related.
Same psqlODBC driver 09.00.0101 has worked 10 years.
Previously I logged into this server long time ago and then it worked.
Windows update history shows lot of updates all called
Security Intelligence Update for Microsoft Endpoint Protection - KB 2461484
with different version numbers.
So did the issue show up after the above updates?
image
Andrus.
--
Adrian Klaver
adrian.klaver@aklaver.com
On 2020-Feb-20, Andrus wrote:
Upgrade to a version of Postgres that is not 5 years past EOL?
Not possible at moment because requires lot of testing not to break existing
applications.
Planned in future.
Applications are already broken, since they can't connect, right? Seems
like a great time to test them with an upgraded server.
--
�lvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On 2/20/20 2:42 PM, Alvaro Herrera wrote:
On 2020-Feb-20, Andrus wrote:
Upgrade to a version of Postgres that is not 5 years past EOL?
Not possible at moment because requires lot of testing not to break existing
applications.
Planned in future.Applications are already broken, since they can't connect, right? Seems
like a great time to test them with an upgraded server.
From here:
/messages/by-id/B75CDDBB971F43D0864066636BBAA24B@dell2
the breakage seems restricted to ODBC connection to localhost with user
dbadmin(other users maybe?). The driver is psqlODBC driver 09.00.0101,
which I believe is pre-libpq, so libpq connections seem to work.
--
Adrian Klaver
adrian.klaver@aklaver.com
On 2/20/20 4:35 PM, Adrian Klaver wrote:
On 2/20/20 2:29 PM, Andrus wrote:
Hi!
>Then what changes were done recently, in particular ODBC-related.
Same psqlODBC driver 09.00.0101 has worked 10 years.
Previously I logged into this server long time ago and then it worked.
Windows update history shows lot of updates all called
Security Intelligence Update for Microsoft Endpoint Protection - KB 2461484
with different version numbers.So did the issue show up after the above updates?
What is ntstatus.h? Would looking there for 0xC0000005 give a clue to the
problem (and thus solution)?
image
Andrus.
--
Angular momentum makes the world go 'round.
On 2/20/20 2:42 PM, Alvaro Herrera wrote:
On 2020-Feb-20, Andrus wrote:
Upgrade to a version of Postgres that is not 5 years past EOL?
Not possible at moment because requires lot of testing not to break existing
applications.
Planned in future.Applications are already broken, since they can't connect, right? Seems
like a great time to test them with an upgraded server.
Realized I should have been clearer. By pre-libpq I meant this:
https://odbc.postgresql.org/docs/release.html
psqlODBC 09.05.0100 Release
Changes:
Use libpq for all communication with the server
Previously, libpq was only used for authentication. Using it for
all communication lets us remove a lot of duplicated code. libpq is now
required for building or using libpq.
--
Adrian Klaver
adrian.klaver@aklaver.com
On 2/20/20 2:48 PM, Ron wrote:
On 2/20/20 4:35 PM, Adrian Klaver wrote:
On 2/20/20 2:29 PM, Andrus wrote:
Hi!
>Then what changes were done recently, in particular ODBC-related.
Same psqlODBC driver 09.00.0101 has worked 10 years.
Previously I logged into this server long time ago and then it worked.
Windows update history shows lot of updates all called
Security Intelligence Update for Microsoft Endpoint Protection - KB
2461484
with different version numbers.So did the issue show up after the above updates?
What is ntstatus.h? Would looking there for 0xC0000005 give a clue to
the problem (and thus solution)?
Good luck on gaining any useful information:)
image
Andrus.
--
Adrian Klaver
adrian.klaver@aklaver.com
On 2/20/20 4:50 PM, Adrian Klaver wrote:
On 2/20/20 2:48 PM, Ron wrote:
On 2/20/20 4:35 PM, Adrian Klaver wrote:
On 2/20/20 2:29 PM, Andrus wrote:
Hi!
>Then what changes were done recently, in particular ODBC-related.
Same psqlODBC driver 09.00.0101 has worked 10 years.
Previously I logged into this server long time ago and then it worked.
Windows update history shows lot of updates all called
Security Intelligence Update for Microsoft Endpoint Protection - KB
2461484
with different version numbers.So did the issue show up after the above updates?
What is ntstatus.h? Would looking there for 0xC0000005 give a clue to
the problem (and thus solution)?Good luck on gaining any useful information:)
/The instruction at 0x%08lx referenced memory at 0x%08lx. The memory could
not be %s./
If he recently patched Windows, that might be the cause.
--
Angular momentum makes the world go 'round.
On Thu, Feb 20, 2020 at 3:30 PM Andrus <kobruleht2@hot.ee> wrote:
Same psqlODBC driver 09.00.0101 has worked 10 years.
Previously I logged into this server long time ago and then it worked.
Windows update history shows lot of updates all called
You are playing with fire if you let some components goes years beyond
their expiration dates while others you keep current.
Maybe you can setup a new VM without some of the more recent Windows
patches and see if that works?
David J.
Attachments:
image[1].pngimage/png; name="image[1].png"Download+0-1
having chased this error several times over the last 20 years, if its not
windows update, AV update, firewall config change, UAC or a driver update
it is nearly impossible to to figure out what is causing it.
One can try to run sysinternal app such as process explorer or depends to
look over the files see if there are recent changes
https://docs.microsoft.com/en-us/sysinternals/downloads/process-explorer
Process explorer i have seen crash postgresql when it tries to monitor
the internal workings so it can be very dangerous to use,
I have learned if the root of the problem can not be quickly identified in
an hour or two, its faster to just start from scratch, format the server,
install windows, and recover from backups. That is how frustrating this
error can be to track ..
On Thu, Feb 20, 2020 at 6:17 PM Ron <ronljohnsonjr@gmail.com> wrote:
Show quoted text
On 2/20/20 4:50 PM, Adrian Klaver wrote:
On 2/20/20 2:48 PM, Ron wrote:
On 2/20/20 4:35 PM, Adrian Klaver wrote:
On 2/20/20 2:29 PM, Andrus wrote:
Hi!
Then what changes were done recently, in particular ODBC-related.
Same psqlODBC driver 09.00.0101 has worked 10 years.
Previously I logged into this server long time ago and then it worked.
Windows update history shows lot of updates all called
Security Intelligence Update for Microsoft Endpoint Protection - KB
2461484
with different version numbers.So did the issue show up after the above updates?
What is ntstatus.h? Would looking there for 0xC0000005 give a clue to the
problem (and thus solution)?Good luck on gaining any useful information:)
*The instruction at 0x%08lx referenced memory at 0x%08lx. The memory could
not be %s.*If he recently patched Windows, that might be the cause.
--
Angular momentum makes the world go 'round.
Hi!
Realized I should have been clearer. By pre-libpq I meant this:
https://odbc.postgresql.org/docs/release.html
psqlODBC 09.05.0100 Release
Changes:
Use libpq for all communication with the server
Previously, libpq was only used for authentication. Using it for all
communication lets us remove a lot of duplicated code. libpq is now
required for building or using libpq.
I upgraded psqlodbc driver to 12.1 version but problem persists.
After server is manually started, application works.
I added log_statement = 'all' . Log before crash is:
2020-02-21 18:46:40 EET mydbadmin mydb LOG: statement: SELECT
drop_table('temptulemus')
2020-02-21 18:46:40 EET mydbadmin mydb LOG: statement: create temp table
temptulemus as SELECT * FROM andmetp ;select * from temptulemus limit 0
2020-02-21 18:46:40 EET mydbadmin mydb LOG: statement: select n.nspname,
c.relname, a.attname, a.atttypid, t.typname, a.attnum, a.attlen,
a.atttypmod, a.attnotnull, c.relhasrules, c.relkind, c.oid,
pg_get_expr(d.adbin, d.adrelid), case t.typtype when 'd' then t.typbasetype
else 0 end, t.typtypmod, c.relhasoids, '', c.relhassubclass from
(((pg_catalog.pg_class c inner join pg_catalog.pg_namespace n on n.oid =
c.relnamespace and c.oid = 28203181) inner join pg_catalog.pg_attribute a on
(not a.attisdropped) and a.attnum > 0 and a.attrelid = c.oid) inner join
pg_catalog.pg_type t on t.oid = a.atttypid) left outer join pg_attrdef d on
a.atthasdef and d.adrelid = a.attrelid and d.adnum = a.attnum order by
n.nspname, c.relname, attnum
2020-02-21 18:46:40 EET mydbadmin mydb LOG: statement: select COUNT(*)::int
from temptulemus
2020-02-21 18:46:40 EET mydbadmin mydb LOG: statement: ;SELECT * FROM
temptulemus offset 0 limit 900
2020-02-21 18:46:40 EET LOG: server process (PID 6000) was terminated by
exception 0xC0000005
2020-02-21 18:46:40 EET HINT: See C include file "ntstatus.h" for a
description of the hexadecimal value.
2020-02-21 18:46:40 EET LOG: terminating any other active server
processes
So command which causes crash is
SELECT * FROM temptulemus offset 0 limit 900
As shown in lines before this is data from andmetp table. This table
contains text type column. This column may contain data like <script>
Maybe this causes postgres crash. Or maybe some unicode character in table
cause crash. This table does not contain binary data.
Database encoding is UTF-8.
There is also other server in Linux
PostgreSQL 9.6.2 on x86_64-pc-linux-gnu, compiled by gcc (Debian 4.9.2-10)
4.9.2, 64-bit
which drops connection when reading the same table. Itlooks like in this
case linux re-starts process automatically so application starts.
Table contents are similar in both servers.
Andrus.
is this happening from any client or just a specific client running ODBC?
are the clients running AV if so are the AV versions the same?
Given this is killing a Linux server, sounds like ODBC is sending back
garabage data to the server crashing it.
There are several settings in OBDC, to change how text columns are
processed, play around with those settings see if that helps it
https://odbc.postgresql.org/docs/config.html
As you have it narrowed down to a table, try querying only a few records
at a time to see if you can identify the specific Record(s) that may be the
issue.
SELECT * FROM temptulemus where temptulemus.unique_id >1 offset 0 limit 100
and try querying the columns that do not contain the suspect data that
could be causing this
On Fri, Feb 21, 2020 at 12:01 PM Andrus <kobruleht2@hot.ee> wrote:
Show quoted text
Hi!
Realized I should have been clearer. By pre-libpq I meant this:
https://odbc.postgresql.org/docs/release.html
psqlODBC 09.05.0100 Release
Changes:
Use libpq for all communication with the server
Previously, libpq was only used for authentication. Using it for all
communication lets us remove a lot of duplicated code. libpq is now
required for building or using libpq.I upgraded psqlodbc driver to 12.1 version but problem persists.
After server is manually started, application works.I added log_statement = 'all' . Log before crash is:
2020-02-21 18:46:40 EET mydbadmin mydb LOG: statement: SELECT
drop_table('temptulemus')
2020-02-21 18:46:40 EET mydbadmin mydb LOG: statement: create temp table
temptulemus as SELECT * FROM andmetp ;select * from temptulemus limit 0
2020-02-21 18:46:40 EET mydbadmin mydb LOG: statement: select n.nspname,
c.relname, a.attname, a.atttypid, t.typname, a.attnum, a.attlen,
a.atttypmod, a.attnotnull, c.relhasrules, c.relkind, c.oid,
pg_get_expr(d.adbin, d.adrelid), case t.typtype when 'd' then
t.typbasetype
else 0 end, t.typtypmod, c.relhasoids, '', c.relhassubclass from
(((pg_catalog.pg_class c inner join pg_catalog.pg_namespace n on n.oid =
c.relnamespace and c.oid = 28203181) inner join pg_catalog.pg_attribute a
on
(not a.attisdropped) and a.attnum > 0 and a.attrelid = c.oid) inner join
pg_catalog.pg_type t on t.oid = a.atttypid) left outer join pg_attrdef d
on
a.atthasdef and d.adrelid = a.attrelid and d.adnum = a.attnum order by
n.nspname, c.relname, attnum
2020-02-21 18:46:40 EET mydbadmin mydb LOG: statement: select
COUNT(*)::int
from temptulemus
2020-02-21 18:46:40 EET mydbadmin mydb LOG: statement: ;SELECT * FROM
temptulemus offset 0 limit 900
2020-02-21 18:46:40 EET LOG: server process (PID 6000) was terminated
by
exception 0xC0000005
2020-02-21 18:46:40 EET HINT: See C include file "ntstatus.h" for a
description of the hexadecimal value.
2020-02-21 18:46:40 EET LOG: terminating any other active server
processesSo command which causes crash is
SELECT * FROM temptulemus offset 0 limit 900
As shown in lines before this is data from andmetp table. This table
contains text type column. This column may contain data like <script>
Maybe this causes postgres crash. Or maybe some unicode character in
table
cause crash. This table does not contain binary data.
Database encoding is UTF-8.There is also other server in Linux
PostgreSQL 9.6.2 on x86_64-pc-linux-gnu, compiled by gcc (Debian 4.9.2-10)
4.9.2, 64-bitwhich drops connection when reading the same table. Itlooks like in this
case linux re-starts process automatically so application starts.
Table contents are similar in both servers.Andrus.
forgot to say publish the Linux logs it may have more details what is going
on
On Fri, Feb 21, 2020 at 12:27 PM Justin <zzzzz.graf@gmail.com> wrote:
Show quoted text
is this happening from any client or just a specific client running ODBC?
are the clients running AV if so are the AV versions the same?Given this is killing a Linux server, sounds like ODBC is sending back
garabage data to the server crashing it.There are several settings in OBDC, to change how text columns are
processed, play around with those settings see if that helps it
https://odbc.postgresql.org/docs/config.htmlAs you have it narrowed down to a table, try querying only a few records
at a time to see if you can identify the specific Record(s) that may be the
issue.
SELECT * FROM temptulemus where temptulemus.unique_id >1 offset 0 limit
100and try querying the columns that do not contain the suspect data that
could be causing thisOn Fri, Feb 21, 2020 at 12:01 PM Andrus <kobruleht2@hot.ee> wrote:
Hi!
Realized I should have been clearer. By pre-libpq I meant this:
https://odbc.postgresql.org/docs/release.html
psqlODBC 09.05.0100 Release
Changes:
Use libpq for all communication with the server
Previously, libpq was only used for authentication. Using it forall
communication lets us remove a lot of duplicated code. libpq is now
required for building or using libpq.I upgraded psqlodbc driver to 12.1 version but problem persists.
After server is manually started, application works.I added log_statement = 'all' . Log before crash is:
2020-02-21 18:46:40 EET mydbadmin mydb LOG: statement: SELECT
drop_table('temptulemus')
2020-02-21 18:46:40 EET mydbadmin mydb LOG: statement: create temp table
temptulemus as SELECT * FROM andmetp ;select * from temptulemus limit 0
2020-02-21 18:46:40 EET mydbadmin mydb LOG: statement: select n.nspname,
c.relname, a.attname, a.atttypid, t.typname, a.attnum, a.attlen,
a.atttypmod, a.attnotnull, c.relhasrules, c.relkind, c.oid,
pg_get_expr(d.adbin, d.adrelid), case t.typtype when 'd' then
t.typbasetype
else 0 end, t.typtypmod, c.relhasoids, '', c.relhassubclass from
(((pg_catalog.pg_class c inner join pg_catalog.pg_namespace n on n.oid =
c.relnamespace and c.oid = 28203181) inner join pg_catalog.pg_attribute a
on
(not a.attisdropped) and a.attnum > 0 and a.attrelid = c.oid) inner join
pg_catalog.pg_type t on t.oid = a.atttypid) left outer join pg_attrdef d
on
a.atthasdef and d.adrelid = a.attrelid and d.adnum = a.attnum order by
n.nspname, c.relname, attnum
2020-02-21 18:46:40 EET mydbadmin mydb LOG: statement: select
COUNT(*)::int
from temptulemus
2020-02-21 18:46:40 EET mydbadmin mydb LOG: statement: ;SELECT * FROM
temptulemus offset 0 limit 900
2020-02-21 18:46:40 EET LOG: server process (PID 6000) was terminated
by
exception 0xC0000005
2020-02-21 18:46:40 EET HINT: See C include file "ntstatus.h" for a
description of the hexadecimal value.
2020-02-21 18:46:40 EET LOG: terminating any other active server
processesSo command which causes crash is
SELECT * FROM temptulemus offset 0 limit 900
As shown in lines before this is data from andmetp table. This table
contains text type column. This column may contain data like <script>
Maybe this causes postgres crash. Or maybe some unicode character in
table
cause crash. This table does not contain binary data.
Database encoding is UTF-8.There is also other server in Linux
PostgreSQL 9.6.2 on x86_64-pc-linux-gnu, compiled by gcc (Debian
4.9.2-10)
4.9.2, 64-bitwhich drops connection when reading the same table. Itlooks like in this
case linux re-starts process automatically so application starts.
Table contents are similar in both servers.Andrus.
Hi!
forgot to say publish the Linux logs it may have more details what is going
on
Linux server has older application client which replicates all data one from
andmetp table using select * from andmetp
I tried several times. Linux log contains
2020-02-21 16:18:50 EET mydbadmin mydb STATEMENT: SELECT * FROM andmetp
2020-02-21 16:18:50 EET mydbadmin mydb FATAL: connection to client lost
2020-02-21 16:18:50 EET mydbadmin mydb STATEMENT: SELECT * FROM andmetp
2020-02-21 17:19:08 EET mydbadmin mydb LOG: could not send data to client:
Connection reset by peer
2020-02-21 17:19:08 EET mydbadmin mydb STATEMENT: SELECT * FROM andmetp
2020-02-21 17:19:08 EET mydbadmin mydb FATAL: connection to client lost
2020-02-21 17:19:08 EET mydbadmin mydb STATEMENT: SELECT * FROM andmetp
It looks like only connection was terminated. “dmesg -T” does not show
postgres crash. Maybe postgres main process is not killed in Linux.
is this happening from any client or just a specific client running ODBC?
This happene if Windows 2008 server if logged in same same server from RDP
client and in my development windows 10 workstation which logs to linux
server over VPN
are the clients running AV if so are the AV versions the same?
In windows 2008 server antivirus was turned off like I wrote. In my devel
workstation I use Microsoft antivirus coming with windows 10.
Given this is killing a Linux server, sounds like ODBC is sending back
garabage data to the server crashing it.
I can try publish odbc log if this helps.
Application replicateds some tables at startup to local disk.
For tables with 5000 or more rows odbc connection hangs if there is big TCP
packet loss.
So we changed replication command
select * from mytable
to commands
select drop_table(‘temptulemus’);
create temp table temptulemus as select * from mytable;
select * from mytable offset 0 limit 900;
select * from mytable offset 900 limit 900;
select * from mytable offset 1800 limit 900;
...
etc.
In this case data was also retrieved on poor connections.
Maybe there is some better solution for this.
There are several settings in OBDC, to change how text columns are
processed, play around with those settings see if that helps it
https://odbc.postgresql.org/docs/config.html
Application needs certain settings. Maybe it is better try to upgrade to
Postgres 12.2 first.
As you have it narrowed down to a table, try querying only a few records
at a time to see if you can identify the specific Record(s) that may be the
issue.
SELECT * FROM temptulemus where temptulemus.unique_id >1 offset 0 limit
100
and try querying the columns that do not contain the suspect data that
could be causing this
andmetp table contains 584 record in linux server. I tried script to read
data from this table every time one more row
for i=1 to 600
? i
StartTextMerge()
TEXT TEXTMERGE NOSHOW
select * from andmetp limit <<tran(i)>>
ENDTEXT
IF !TExec()
RETURN .f.
ENDIF
endfor
this worked without error.
Andrus.
