utPLSQL-cli having connection issues

[shakye]

The tests takes a long time when the connection parameter is passed (username/pass@localhost:1521/sid) .This happens rarely but since I'm working with Jenkins the Testing build step takes about an hour and fails.

[pesse]

Hm, never experienced such behaviour.
Can you give some more information? Cli-Version, utPLSQL version, Java-Version etc?

[shakye]

I'm using
java - 8 (1.8.0.181)
utPLSQL - 3.1.2
Cli - 3.1.0

[pesse]

Did I understand that correctly that you don't experience this problem every time but only sometimes?
Can you give me the exact call you're running?
I have an impression that it's not connecting but receiving results from database - I see something similar from time to time in our travis builds.

[shakye]

Yes, I don't experience this every time .
utplsql run usr/pass@%computername%:1521/pdb_'+ BUILD_NUMBER +' -p=schema.package -f=ut_junit_reporter -o=result.xml --failure-exit-code=0
I'm running this script as a batch script and some build steps take 1 hour and fails ,the reporter XML file is also empty.

[pesse]

Would you be willing to help me debug that issue?
I think it has to do with the changes around reporters in 3.1.0. It would be great if you could check some things inside the database on such a failed build.

[shakye]

Sure thing!

[pesse]

Okay, thank you very much! I will come back to you this week with some instructions/questions.

[shakye]

Hi @pesse ,
I debugged the source code and came across the exact issue.
The await termination of the executor service takes 1 hour and doesn't return any output to the XML file.
Do you have any update on this?

[jgebal]

@shakye

• Do you always execute the same tests in Jenkins?
• Do you have any concurrency - many runs running at the same time? That should not be the issue but might give a clue.
• Do you always build from scratch?
• Are you able to make this problem reproducible, so that we can reproduce it locally?
• What DB version are you using?
• Can you reproduce this without CLI but from SQLPlus with command exec ut.run('schema.package'); ?

[SebGt]

@pesse, As discussed on Slack, I add my case as comment:

After updating my UTPLSQL version from 3.0.3 to 3.1.3, now I'm using UTPLSQL-CLI (java client) to run.
I use the following command (from Jenkins):
V:\Tools\UnitTests\Jenkins\utPLSQL-cli\bin\utplsql run vista_test/vitol_test@VISDAY_GVA.WORLD -f=ut_xunit_reporter -o=tpkg_html_result.xml -f=ut_coverage_html_reporter -o=tpkg_html_coverage.html -p=vista_test.tpkg_html -include=vista_bo.pkg_html
This command works well a first time but if I execute it a second time immediately after the first one, it never ends.
DB is Oracle 11g. (11.2.0.4.0)

When I run several time the following pl/sql code on the same DB it works always:
begin
ut.run(ut_varchar2_list('test.tpkg_tools','WORK'),
ut_coverage_html_reporter(),
a_coverage_schemes => ut_varchar2_list('WORK'),
a_include_objects => ut_varchar2_list('WORK.pkg_tools')
);
end;

Here are the client and api info:
C:\Users$devtools>V:\Tools\UnitTests\Jenkins\utPLSQL-cli\bin\utplsql info scott/tiger@DEV_DB.WORLD
cli 3.1.2-SNAPSHOT.local
utPLSQL-java-api 3.1.2.271
utPLSQL 3.1.3.2402

During my tries, I executed pl/sql run in the same time than the java client is stuck (start java client, wait, run several time in pl sql session the pl sql run) and pl sql runs were completed successfully and very fast always. So don't think about an issue on DB side.
I also have the issue with below case:
Run unit tests set1, it completes ok
After try to run immediately a second set (on other pl/sql programs) and this second was stuck.

[SebGt]

After few tries, in summary I do:
Run Jenkins job -> Complete ok
Run Jenkins job -> Stuck -> Stop Jeknkins run
Run Jenkins job -> Complete ok

[pesse]

More Input from slack (@SebGt):
With my current test, I have this query SELECT TEXT, ITEM_TYPE FROM TABLE((:B1 ).GET_LINES(:B2 , :B3 )) when job is stuck
Table ut_output_buffer_tmp is empty (edited)

When run is successfull, I have up to 3 sessions running.
Details of session are :
Program: all "JDBC Thin Client",
Module: "JDBC Thin Client" for 2 sessions and "JDBC Thin Client" after "utPLSQL" after "JDBC Thin Client" for the last one
Action: (only for session with utPLSQL as Module) test program name. for the other sessions it is always null

When run is stuck third session never displays Module "utPLSQL" (edited)

When run is struck, the last query of the active session is:

select owner || '.' || type_name,
      case
         when sys_connect_by_path(owner || '.' || type_name, ',') like '%TEST.UT_OUTPUT_REPORTER_BASE%' then
          'Y'
         else
          'N'
      end is_output_reporter
 from dba_types t
where instantiable = 'YES'
connect by supertype_name = prior type_name
      and supertype_owner = prior owner
start with type_name = 'UT_REPORTER_BASE'
       and owner = 'TEST'

I checked with my DBAs, Session is active but it does nothing.

[pesse]

Hey @SebGt, can you try whether https://bintray.com/utplsql/utPLSQL-cli/download_file?file_path=utPLSQL-cli-develop-201901102355.zip fixes the problems?

[SebGt]

hello @pesse, thanks, I'm trying.
I'll give you a feedback today.

[SebGt]

@pesse, sorry, I still have the stuck execs.
Only change is I can exec 2 times successfully before to be stuck. previously it was 1 success / 1 stuck.

[pesse]

Can you tell me what's the latest SQL when being stuck now?
Is it still stuck forever or does it fail?

[SebGt]

I'm checking

[SebGt]

The last query is SELECT TEXT, ITEM_TYPE FROM TABLE((:B1 ).GET_LINES(:B2 , :B3 ))
Run is stuck since 10 minutes and does not fail.

[SebGt]

I checked with DBA on enterprise manager, session has no activity.
After execution of the query SELECT TEXT, ITEM_TYPE FROM TABLE((:B1 ).GET_LINES(:B2 , :B3 )) (it was fast), session does nothing.

[SebGt]

I made tests on Oracle18c and I was not able to reproduce the issue. I ran around 30 times several unit tests and I had no stuck case.
It seems to be related to 11g issue.
To be sure I will made specific Jenkins jobs to be executed on 18c during several days next week and I will monitor that and I give you a feedback EOW.

[pesse]

This SQL is issued by ut_output_table_buffer.get_lines_cursor
What I don't get is how cli is different to normal ut_runner - they use the same objects to access the output.
@jgebal any ideas?

[jgebal]

I think we've established that the issue with session getting stuck is related to combination of dba_typesand connect by in 11g

[SebGt]

Test done today with dev version provided by @pesse. I still have stuck cases.
Last query was : SELECT COLUMN_VALUE AS TEXT FROM TABLE((:B1 ).GET_LINES(:B2 , :B3 ))

[pesse]

This should be dealt with by the meanwhile implemented watchdog.