volatile table is not dropped after hpdci session ends

Bug #1246183 reported by Weishiun Tsai on 2013-10-30
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Trafodion
In Progress
High
Sandhya Sundaresan

Bug Description

A volatile table is not dropped after the hpdci session has ended. In the following example, the volatile table persists after several hpdci disconnects and reconnects. This problem is not seen from sqlci, so I am assuming that the problem is in how mxosvr handles volatile tables.

-bash-4.1$ hpdci.seascape2-sqtopl7.sh

Welcome to HP Database Command Interface 3.0
(c) Copyright 2010-2012 Hewlett-Packard Development Company, LP.

Connected to Data Source: TDM_Default_DataSource

SQL>set schema seabase.mytest;

--- SQL operation complete.

SQL>create volatile table abc (a int not null not droppable primary key);

--- SQL operation complete.

SQL>showddl abc;

CREATE VOLATILE TABLE ABC
  (
    A INT NO DEFAULT NOT NULL NOT DROPPABLE
  , PRIMARY KEY (A ASC)
  )
;

--- SQL operation complete.

SQL>exit;
-bash-4.1$ hpdci.seascape2-sqtopl7.sh

Welcome to HP Database Command Interface 3.0
(c) Copyright 2010-2012 Hewlett-Packard Development Company, LP.

Connected to Data Source: TDM_Default_DataSource

SQL>set schema seabase.mytest;

--- SQL operation complete.

SQL>showddl abc;

CREATE VOLATILE TABLE ABC
  (
    A INT NO DEFAULT NOT NULL NOT DROPPABLE
  , PRIMARY KEY (A ASC)
  )
;

--- SQL operation complete.

SQL>exit;
-bash-4.1$ hpdci.seascape2-sqtopl7.sh

Welcome to HP Database Command Interface 3.0
(c) Copyright 2010-2012 Hewlett-Packard Development Company, LP.

Connected to Data Source: TDM_Default_DataSource

SQL>set schema seabase.mytest;

--- SQL operation complete.

SQL>showddl abc;

CREATE VOLATILE TABLE ABC
  (
    A INT NO DEFAULT NOT NULL NOT DROPPABLE
  , PRIMARY KEY (A ASC)
  )
;

--- SQL operation complete.

SQL>exit;
-bash-4.1$ hpdci.seascape2-sqtopl7.sh

Welcome to HP Database Command Interface 3.0
(c) Copyright 2010-2012 Hewlett-Packard Development Company, LP.

Connected to Data Source: TDM_Default_DataSource

SQL>set schema seabase.mytest;

--- SQL operation complete.

SQL>showddl abc;

CREATE VOLATILE TABLE ABC
  (
    A INT NO DEFAULT NOT NULL NOT DROPPABLE
  , PRIMARY KEY (A ASC)
  )
;

--- SQL operation complete.

Changed in trafodion:
assignee: nobody → Anoop Sharma (anoop-sharma)
Weishiun Tsai (wei-shiun-tsai) wrote :

Verified on 11/6/2013, this problem has been fixed.

-bash-4.1$ hpdci.seascape2-sqtopl7.sh

Welcome to HP Database Command Interface 3.0
(c) Copyright 2010-2012 Hewlett-Packard Development Company, LP.

Connected to Data Source: TDM_Default_DataSource

SQL>set schema seabase.mytest;

--- SQL operation complete.

SQL>create volatile table abc (a int not null not droppable primary key);

--- SQL operation complete.

SQL>showddl abc;

CREATE VOLATILE TABLE ABC
  (
    A INT NO DEFAULT NOT NULL NOT DROPPABLE
  , PRIMARY KEY (A ASC)
  )
;

--- SQL operation complete.

SQL>exit;
-bash-4.1$ hpdci.seascape2-sqtopl7.sh

Welcome to HP Database Command Interface 3.0
(c) Copyright 2010-2012 Hewlett-Packard Development Company, LP.

Connected to Data Source: TDM_Default_DataSource

SQL>set schema seabase.mytest;

--- SQL operation complete.

SQL>showddl abc;

*** ERROR[4082] Object SEABASE.MYTEST.ABC does not exist or is inaccessible. [2013-11-06 17:34:20]

SQL>exit;
-bash-4.1$ hpdci.seascape2-sqtopl7.sh

Welcome to HP Database Command Interface 3.0
(c) Copyright 2010-2012 Hewlett-Packard Development Company, LP.

Connected to Data Source: TDM_Default_DataSource

SQL>set schema seabase.mytest;

--- SQL operation complete.

SQL>showddl abc;

*** ERROR[4082] Object SEABASE.MYTEST.ABC does not exist or is inaccessible. [2013-11-06 17:34:39]

SQL>exit;

Changed in trafodion:
status: New → Fix Released
Weishiun Tsai (wei-shiun-tsai) wrote :

Repoened this bug. This problem has reapeared on the 11/11/2013 build, as shown here:

-bash-4.1$ hpdci.sqws138.sh

Welcome to HP Database Command Interface 3.0
(c) Copyright 2010-2012 Hewlett-Packard Development Company, LP.

Connected to Data Source: TDM_Default_DataSource

SQL>set schema seabase.mytest;

--- SQL operation complete.

SQL>create volatile table abc (a int not null not droppable primary key);

--- SQL operation complete.

SQL>showddl abc;

CREATE VOLATILE TABLE ABC
  (
    A INT NO DEFAULT NOT NULL NOT DROPPABLE
  , PRIMARY KEY (A ASC)
  )
;

--- SQL operation complete.

SQL>exit;
-bash-4.1$ hpdci.sqws138.sh

Welcome to HP Database Command Interface 3.0
(c) Copyright 2010-2012 Hewlett-Packard Development Company, LP.

Connected to Data Source: TDM_Default_DataSource

SQL>set schema seabase.mytest;

--- SQL operation complete.

SQL>showddl abc;

CREATE VOLATILE TABLE ABC
  (
    A INT NO DEFAULT NOT NULL NOT DROPPABLE
  , PRIMARY KEY (A ASC)
  )
;

--- SQL operation complete.

SQL>exit;
-bash-4.1$ hpdci.sqws138.sh

Welcome to HP Database Command Interface 3.0
(c) Copyright 2010-2012 Hewlett-Packard Development Company, LP.

Connected to Data Source: TDM_Default_DataSource

SQL>set schema seabase.mytest;

--- SQL operation complete.

SQL>showddl abc;

CREATE VOLATILE TABLE ABC
  (
    A INT NO DEFAULT NOT NULL NOT DROPPABLE
  , PRIMARY KEY (A ASC)
  )
;

--- SQL operation complete.

SQL>exit;
-bash-4.1$

Changed in trafodion:
status: Fix Released → New
Changed in trafodion:
assignee: Anoop Sharma (anoop-sharma) → Selvaganesan (selva-ganesan)
tags: added: client-ci
removed: dcs

Can you please test this issue with the latest release and confirm if the problem still exists.

Changed in trafodion:
status: New → Opinion
Weishiun Tsai (wei-shiun-tsai) wrote :

Tried it on the GIT 0603_0930 build again. This problem still exists.

-bash-4.1$ trafci.sh -h <blocked out> -u dontcare -p dontcare

Welcome to Trafodion Command Interface
Copyright(C) 2013-2014 Hewlett-Packard Development Company, L.P.

Host Name/IP Address: <blocked out>
User Name: dontcare

Connected to Trafodion

SQL>set schema mytest;

--- SQL operation complete.

SQL>create volatile table myvoltab (a int not null not droppable primary key);

--- SQL operation complete.

SQL>showddl myvoltab;

CREATE VOLATILE TABLE MYVOLTAB
  (
    A INT NO DEFAULT NOT NULL NOT DROPPABLE
  , PRIMARY KEY (A ASC)
  )
;

--- SQL operation complete.

SQL>exit;
-bash-4.1$ trafci.sh -h <blocked out> -u dontcare -p dontcare

Welcome to Trafodion Command Interface
Copyright(C) 2013-2014 Hewlett-Packard Development Company, L.P.

Host Name/IP Address: <blocked out>
User Name: dontcare

Connected to Trafodion

SQL>set schema mytest;

--- SQL operation complete.

SQL>showddl myvoltab;

CREATE VOLATILE TABLE MYVOLTAB
  (
    A INT NO DEFAULT NOT NULL NOT DROPPABLE
  , PRIMARY KEY (A ASC)
  )
;

--- SQL operation complete.

SQL>

Changed in trafodion:
status: Opinion → New
information type: Proprietary → Public
tags: added: sql-exe
removed: client-ci
Changed in trafodion:
assignee: Selvaganesan Govindarajan (selva-ganesan) → Pavani Puppala (pavani-puppala)
Changed in trafodion:
assignee: Pavani Puppala (pavani-puppala) → Selvaganesan Govindarajan (selva-ganesan)
Changed in trafodion:
milestone: none → r0.9
Changed in trafodion:
assignee: Selvaganesan Govindarajan (selva-ganesan) → Sandhya Sundaresan (sandhya-sundaresan)

This issue is not seen when Trf is running slowly. The symptom is that some volatile tables fail to get cleaned up during session exit. It is difficult to reproduce this. The lolic to drop and cleanup volatile tables is in place . There may be an underlying intermittent error that may prevent this . If a new session is started and then exited, any leftover volatile tables from the previous session will be dropped.
Still trying to reproduce in order to capture the intermittent issue.

Changed in trafodion:
status: New → In Progress
Changed in trafodion:
milestone: r0.9 → r1.1
Changed in trafodion:
milestone: r1.1 → r1.2
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers