0039601: can't execute DB updates after killing a connection

Openbravo Issue Tracking System - Openbravo ERP

View Issue Details

Project

Category

View Status

Date Submitted

Last Update

0039601

Openbravo ERP

A. Platform

public

2018-11-07 15:03

2018-12-11 20:22

Reporter

alostale

Assigned To

alostale

Priority

immediate

Severity

major

Reproducibility

random

Status

closed

Resolution

fixed

Platform

OS Version

Product Version

Target Version

Fixed in Version

3.0PR19Q1

Merge Request Status

Review Assigned To

caristu

OBNetwork customer

Web browser

Modules

Core

Support ticket

Regression level

Production - Confirmed Stable

Regression date

2016-08-10

Regression introduced in release

3.0PR16Q4

Regression introduced by commit

https://code.openbravo.com/erp/devel/pi/rev/88ea14de1a84e176f457750118a6d4823588d7d2 [^]

Triggers an Emergency Pack

Summary

0039601: can't execute DB updates after killing a connection

Description

With Audit trail active, killing DB connections can cause random failures while trying to update information.

Technically the problem is:
1. Pool keeps PooledConnections each of them associated with a physical DB connection
2. When one of those physical DB connections is killed and a PooledConnection is going to be used (borrowed from the pool), Tomcat transparently creates a new physical connection and associates it with the existing PooledConnection instance.
3. Openbravo, in order not to recreate ad_context_info temporary table on each borrow, does it when a new connection is created and marks it as already initialized. This flag is set in the PooledConnection, incorrectly assuming a PooledConnection instance is always tight to the same Connection instance
4. Because of 2nd step, a new uninitialized Connection is associated with an existing PooledConnection marked as initialized, so queries expecting the temporary table to be present will fail.

This situation remains until the connection is finally returned to the pool.

Steps To Reproduce

In a Professional instance:
0. Configure DB pool to a fixed 5 connections:
  db.pool.initialSize=5
  db.pool.minIdle=5
  db.pool.maxActive=5
1. As System Admin open Table window
2. Select any table and mark it as fully audited
3. Restart Tomcat
4. Kill the 5 connections that are in pool: in psql execute select pg_terminate_backend(pid), pid from pg_stat_activity where application_name ='openbravo';
5. Log in
6. Open Process Request window
7. Create a new record and save
  -> Saving failed. ERROR: relation "ad_context_info" does not exist

Proposed Solution

Additional Information

Tags

No tags attached.

Relationships

depends on	backport	0039603	3.0PR18Q4	closed	alostale	can't update information after killing a connection
depends on	backport	0039604	3.0PR18Q3.3	closed	alostale	can't update information after killing a connection
caused by	defect	0033441	3.0PR16Q4	closed	NaroaIriarte	query to check if ad_context_info exists executed once per request