DCS - schema setting seems to be retained from previous session

[Aruna Sadashiva]

Noticed this on sq151, the default schema for new connections is not SEABASE, when connecting to 2 specific servers, it is PHOENIX. Schema setting is being retained from previous session.

Build Version:
Trafodion Platform : Release 0.7.0
Trafodion Connectivity Services : Version 1.0.0 Release 0.7.0 (Build debug [37599], date 15Feb14)
Trafodion JDBC Type 4 Driver : Traf_JDBC_Type4_Build_37599
Trafodion Command Interface : TrafCI_Build_37599

[Arvind Narain (arvind-narain)]

For simulation configure one mxosrvr via DCS, set schema via connection string, disconnect and then connect without specifying a schema in the connection string.

[Judy Zhao (hongxia-zhao)]

SEABASE is the default schema .It is not retained from previous session. Following is the experiment steps.So what is the issue?

SQL>set schema mgbtest;

--- SQL operation complete.

SQL>select * from ord;

ORDERID USERID PRODUCTID UNIT_PRICE QTY_ORDERED
---------- -------- ---------- ---------- -----------
         1 a 9001 21 1
         2 b 9002 21 1
         3 c 9003 21 1
         4 d 9004 21 1
         5 e 9005 21 1

--- 5 row(s) selected.

SQL>exit
-bash-4.1$ trafci

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

Host Name/IP Address: localhost:38972
User Name: zz

Connected to Trafodion

SQL>select * from ord;

*** ERROR[4082] Object TRAFODION.SEABASE.ORD does not exist or is inaccessible. [2015-03-18 09:46:48]

SQL>

[Anuradha (anuradha-hegde)]

Judy, This issue is seen when you use ODBC Connect string. Can you test this with ODBC client instead of t4 application?

Thanks

[jiegao (jie-gao)]

uhmm.. my test steps just indicate that the schema will not be retained.

below are my test steps.

using tracfi to create a new schema "aa"

modified connect_test.cpp as below.

while( ( returnCode = SQLExecDirect( hstmt , (SQLCHAR*)"set schema aa;", SQL_NTS ) ) == SQL_STILL_EXECUTING );

compile connect_test.cpp and execute it.

then modified connect_test.cpp again as below

//while( ( returnCode = SQLExecDirect( hstmt , (SQLCHAR*)"set schema aa;", SQL_NTS ) ) == SQL_STILL_EXECUTING );
while( ( returnCode = SQLExecDirect( hstmt , (SQLCHAR*)"create table checkone1( C01 CHAR( 20 ) CHARACTER SET ISO88591 NOT NULL)PRIMARY KEY ( C01 ); ", SQL_NTS ) ) == SQL_STILL_EXECUTING );

if schema aa is retained, then table checkone1 should be created in schema aa, right?

check from trafci side.

[jiegao@g4t3018 /opt/home/jiegao/project/Rowsets]$ trafci

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

Host Name/IP Address: localhost:15972
User Name: zz

Connected to Trafodion

SQL>set schema aa;

--- SQL operation complete.

SQL>select * from checkone1;

*** ERROR[4082] Object TRAFODION.AA.CHECKONE1 does not exist or is inaccessible. [2015-04-21 16:42:56]

SQL>set schema SEABASE;

--- SQL operation complete.

SQL>select * from checkone1;

--- 0 row(s) selected.

so the experiment result shows that table checkone1 is created in default schema seabase.

[jiegao (jie-gao)]

i tried the following ways to reproduce this issue but all failed.

1). use odbc connection string to have a simple test, see #4

2).
On workstation, check the number of mxosrvr, the result is 4.

Open 4 sessions, use trafci to connect to trafodion. When 4 mxosrvr are occupied, the 5th trafci shows “*** ERROR[1] The message id: as_connect_message_error With parameters: server handle not available”.

Use sql commend “set schema aa;” in the last opened trafci and exit, then start trafci again. Since the first 3 trafci processes are still open and activating the connection, the new opened trafci should be linked to that specific mxosrvr which was set schema as aa right? But the result actually shows this mxosrvr fetches the default schema.

3).
use kill -19 mxosrvr_pid to block another 3 processes and test it. the result keep the same as 2).

4). based on item 3). use odbc connection string to set a new schema and a new table, and then exit it abnormally. use jdbc -- trafci to test the result. the output shows that the previous schema will not be retain either.

5). on cluster environment, open, check schema and exit trafci frequently for more than 10 times. no such issue happen.

6). confirm with Daniel that he had never met such problem.

In conclusion, this is a very rare-reproduced issue, at least i can never reproduce it on ws, i am afraid i can not fix this bug based on current information.

[Anuradha (anuradha-hegde)]

As mentioned in the description earlier please test this with single connection and restart dcs
 (in $DCS_INSTALL_DIR/conf/servers file, set it to only 1)

 Use Windows ODBC driver you will be able to simulate the problem easily.

[jiegao (jie-gao)]

I tried as your suggestion, but i still cannot reproduce it. the detailed steps as follows.

1). set conf/server to 1 and restart dcs.

[jiegao@g4t3018 /opt/home/jiegao/project/core/sqf/sql/scripts]$ sqcheck
Checking if processes are up.
Checking attempt: 1; user specified max: 2. Execution time in seconds: 0.

The SQ environment is up!

Process Configured Actual Down
------- ---------- ------ ----
DTM 2 2
RMS 4 4
MXOSRVR 0 1 -1

2). on windows side, use windows odbc connection string to call the back end server.
i use the following sql:
 SQLCHAR sql[40]="insert into checkone1 values('bbb')";
 SQLCHAR sql1[40]="set schema aa";
 SQLCHAR sql2[40]="insert into checkone values('bbb')";
suppose we have schema aa and table checkone and checkone1.

3) check the result by trafci.

SQL>show schema;

SCHEMA SEABASE

--> result shows that the schema hasn't retained.

[jiegao (jie-gao)]

so my idea is , this bug may be only reproduced after a certain sql command. so could you give me the suspicious sql command you have run for regression test?