Realtime reporter produces no output #985

PhilippSalvisberg · 2019-07-28T09:13:51Z

Describe the bug

Running the realtime reporter from the SQL Developer extension produces sometimes no output due to a ORA-00001: unique constraint (UT3_LATEST_RELEASE.UT_OUTPUT_BUFFER_INFO_TMP_PK) violated

Provide version info

19.0.0.0.0
19.0.0


PL/SQL procedure successfully completed.


UT_VERSION                                                  
------------------------------------------------------------
v3.1.8.3169-develop


BANNER                                                                           BANNER_FULL                                                                                                                                                      BANNER_LEGACY                                                                        CON_ID
-------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ----------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production           Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production                                                                                           Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production                    0
                                                                                 Version 19.4.0.0.0                                                                                                                                                                                                                                          



PARAMETER                      VALUE                                                           
------------------------------ ----------------------------------------------------------------
NLS_LANGUAGE                   ENGLISH                                                         
NLS_TERRITORY                  SWITZERLAND                                                     
NLS_CURRENCY                   SFr.                                                            
NLS_ISO_CURRENCY               SWITZERLAND                                                     
NLS_NUMERIC_CHARACTERS         .'                                                              
NLS_CALENDAR                   GREGORIAN                                                       
NLS_DATE_FORMAT                DD.MM.YYYY HH24:MI:SS                                           
NLS_DATE_LANGUAGE              ENGLISH                                                         
NLS_SORT                       BINARY                                                          
NLS_TIME_FORMAT                HH24:MI:SSXFF                                                   
NLS_TIMESTAMP_FORMAT           DD.MM.RR HH24:MI:SSXFF                                          

PARAMETER                      VALUE                                                           
------------------------------ ----------------------------------------------------------------
NLS_TIME_TZ_FORMAT             HH24:MI:SSXFF TZR                                               
NLS_TIMESTAMP_TZ_FORMAT        DD.MM.RR HH24:MI:SSXFF TZR                                      
NLS_DUAL_CURRENCY              SF                                                              
NLS_COMP                       BINARY                                                          
NLS_LENGTH_SEMANTICS           BYTE                                                            
NLS_NCHAR_CONV_EXCP            FALSE                                                           

17 rows selected. 


PORT_STRING                                                 
------------------------------------------------------------
x86_64/Linux 2.4.xx

Information about client software

utPLSQL for SQL Developer 1.0.0 (or current development version)

To Reproduce

Run a small test in SQL Developer's realtime reporter
Rerun test
Repeat step 2 until the test is not executed anmore, you get an initializing... message for

In the SQL Developer logs I find the following:

2019-07-28 11:00:51 SEVERE org.utplsql.sqldev.runner.UtplsqlRunner: Error while producing events for reporter id 1c8f3d5e77a64bbdb523d52c91c47e72: PreparedStatementCallback; SQL [DECLARE
   l_reporter ut_realtime_reporter := ut_realtime_reporter();
BEGIN
   l_reporter.set_reporter_id(?);
   l_reporter.output_buffer.init();
   sys.dbms_output.enable(NULL);
   ut_runner.run(
      a_paths     => ut_varchar2_list(
                        'PLSCOPE.test_dd_util.test_get_view_source'
                     ),
      a_reporters => ut_reporters(l_reporter)
   );
   sys.dbms_output.disable;
END;
]; ORA-00001: unique constraint (UT3_LATEST_RELEASE.UT_OUTPUT_BUFFER_INFO_TMP_PK) violated
ORA-06512: at "UT3_LATEST_RELEASE.UT_OUTPUT_BUFFER_BASE", line 32
ORA-06512: at "UT3_LATEST_RELEASE.UT_OUTPUT_REPORTER_BASE", line 34
ORA-06512: at line 4
; nested exception is java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (UT3_LATEST_RELEASE.UT_OUTPUT_BUFFER_INFO_TMP_PK) violated
ORA-06512: at "UT3_LATEST_RELEASE.UT_OUTPUT_BUFFER_BASE", line 32
ORA-06512: at "UT3_LATEST_RELEASE.UT_OUTPUT_REPORTER_BASE", line 34
ORA-06512: at line 4

as a consequence of this failure the consumer thread does not find result rows.

Expected behavior

Repeat test runs without technical issues.

PhilippSalvisberg · 2019-07-28T13:59:22Z

This is not a framework bug. It's caused by the SQL Developer extension. The consumer thread must be started before the producer thread to avoid this behavior.

PhilippSalvisberg · 2020-03-13T10:32:05Z

I'm re-opening this issue based on the discussion in JetBrain's DBE-9671 and in Slack.

In the meantime I consider this a utPLSQL framework bug based on the following code in ut_output_buffer_base

    select count(*) into l_exists from ut_output_buffer_info_tmp where output_id = self.output_id;
    if ( l_exists > 0 ) then
      update ut_output_buffer_info_tmp set start_date = self.start_date where output_id = self.output_id;
    else
      insert into ut_output_buffer_info_tmp(output_id, start_date) values (self.output_id, self.start_date);
    end if;
    commit;

If the producer and the consumer session run the select statement at the same time, both will try an insert and one session will fail, which results in the behaviour described above.

The workaround until now is to start either the consumer or the producer with a short delay (e.g. 100ms). As long as the query on the ut_output_buffer_info_tmp table is faster than the delay this will "solve" the issue.

However, we can do better and make it work without enforcing the consumers to apply a delay when starting producers and consumers. One way to solve it is to lock the table ut_output_buffer_info_tmp exclusively. Something like

lock table ut_output_buffer_info_tmp in exclusive mode wait 10

Since all transactions on this table are very short, 10 should be more than enough.

PhilippSalvisberg added the bug label Jul 28, 2019

PhilippSalvisberg mentioned this issue Jul 28, 2019

Realtime reporter produces no output utPLSQL/utPLSQL-SQLDeveloper#80

Closed

PhilippSalvisberg closed this Jul 28, 2019

PhilippSalvisberg reopened this Mar 13, 2020

utPLSQL / utPLSQL

Realtime reporter produces no output #985

Realtime reporter produces no output #985

PhilippSalvisberg commented Jul 28, 2019

PhilippSalvisberg commented Jul 28, 2019

PhilippSalvisberg commented Mar 13, 2020 •

edited

utPLSQL / utPLSQL

Join GitHub today

GitHub is where the world builds software

Realtime reporter produces no output #985

Realtime reporter produces no output #985

Comments

PhilippSalvisberg commented Jul 28, 2019

PhilippSalvisberg commented Jul 28, 2019

PhilippSalvisberg commented Mar 13, 2020 • edited

Essential cookies

Always active

Analytics cookies

PhilippSalvisberg commented Mar 13, 2020 •

edited