[TRAFODION-1212] LP Bug: 1449732 - Drop schema cascade returns error 1069

• Type: Bug
• Status: Open
• Priority: Critical
• Resolution: Unresolved
• Fix Version/s: 2.4
• Component/s: sql-cmu
• External issue URL: https://bugs.launchpad.net/bugs/1449732
• Assignee: Suresh Subbiah
• Reporter: Weishiun Tsai
• Created: 22/Jul/15 18:20
• Updated: 16/Apr/20 02:19

Description

The frequency of ‘drop schema cascade’ returning error 1069 is still pretty high, even after several attempts to address this issue. This is causing a lot of headache for the QA regression testing. After each regression testing run, there are always several schemas that couldn’t be dropped and needed to be manually cleaned up.

Multiple issues may lead to this problem. This just happens to be one scenario that is quite reproducible now. In this particular scenario, the schema contains a TMUDF library qaTmudfLib and 2 TMUDF functions qa_tmudf1 and qa_tmudf2. qa_tmudf1 is a valid function, while qa_tmudf2 has a bogus external name and a call to it is expected to see an error.

After invoking both, a drop schema cascade almost always returns error 1069.

This is seen on the r1.1.0rc3 (v0427) build installed on a workstation and it is fairly reproducible with this build. To reproduce it:

(1) Download the attached tar file and untar it to get the 3 files in there. Put the files in any directory <mydir>.
(2) Make sure that you have run ./sqenv.sh of your Trafodion instance first as building UDF needs $MY_SQROOT for the header files.
(3) Run build.sh
(4) Change the line “create library qaTmudfLib file '<mydir>/qaTMUdfTest.so';” in mytest.sql and fill in <mydir>
(5) From sqlci, obey mytest.sql

Here is the execution output:

>>log mytest.log clear;
>>drop schema mytest cascade;

• • • ERROR[1003] Schema TRAFODION.MYTEST does not exist.

— SQL operation failed with errors.
>>create schema mytest;

— SQL operation complete.
>>set schema mytest;

— SQL operation complete.
>>
>>create library qaTmudfLib file '<mydir>/qaTMUdfTest.so';

— SQL operation complete.
>>
>>create table mytable (a int, b int);

— SQL operation complete.
>>insert into mytable values (1,1),(2,2);

— 2 row(s) inserted.
>>
>>create table_mapping function qa_tmudf1()
+>external name 'QA_TMUDF'
+>language cpp
+>library qaTmudfLib;

— SQL operation complete.
>>
>>select * from UDF(qa_tmudf1(TABLE(select * from mytable)));

A B
----------- -----------

1 1
2 2

— 2 row(s) selected.
>>
>>create table_mapping function qa_tmudf2()
+>external name 'DONTEXIST'
+>language cpp
+>library qaTmudfLib;

— SQL operation complete.
>>
>>select * from UDF(qa_tmudf2(TABLE(select * from mytable)));

• • • ERROR[11246] An error occurred locating function 'DONTEXIST' in library 'qaTMUdfTest.so'.

• • • ERROR[8822] The statement was not prepared.

>>
>>drop schema mytest cascade;

• • • ERROR[1069] Schema TRAFODION.MYTEST could not be dropped.

— SQL operation failed with errors.