[sql_init] :: 500 (Internal Server Error)

[ottigerb]

Südhang had to update the CREATE OR REPLACE VIEW from several apps. We opened an issue for me here.

Issue

What we did/tried:

[sql_init]
CREATE OR REPLACE VIEW export_sci_view AS 
include(includes/export_production_neu.sql)

this export_production_neu.sql works perfectly inside http://optinomic.cust.local/api/#/admin/tools/sql.

Because: This code is executed when a module is installed and is so in a schema specially created for that module. We had to deactivate the module and tried to reactivate.

=> 500 (Internal Server Error)

enableApp:  ch.suedhang.apps.sci.production Stress-Coping-Inventar (SCI) 1.0

vendor.js:25734 POST http://demo.optinomic.org/api/module_activations?module_identifier=ch.suedh…s.sci.production&name_overwrite=Stress-Coping-Inventar%20(SCI)&version=1.0 500 (Internal Server Error)

sudo less /var/log/upstart/therapyserver-api.log tells me:

NOTICE:  role "ch_suedhang_apps_sci_production" does not exist, skipping
Error in action: SQLError (Just "SqlError {sqlState = \"42P16\", sqlExecStatus = FatalError, sqlErrorMsg = \"cannot drop columns from view\", sqlErrorDetail = \"\", sqlErrorHint = \"\"}")
NOTICE:  drop cascades to 2 other objects
DETAIL:  drop cascades to view ch_suedhang_apps_sci_production_test.sci_view
drop cascades to view ch_suedhang_apps_sci_production_test.sci_view_neu

Reverted the SQL - and app is able to reactivate.

Debugging

Test 1: New App

I created a new patient - app with the sam export_production_neu.sql . => I called the view a bit different then the "parent" & "production-app" as sci_view_neu => Works perfectly!

Test 2: New User App

Because this "risky" activate/deactivate thing - I thought it would be a good idea to have a ch.suedhang.apps.export.toolbox user-app where we can define our [sql_init] for all the apps - so we can safe activate/deactivate just this one and the production apps are not affecting if something goes wrong.

I was able to activate this new ch.suedhang.apps.export.toolbox app but nothing show's up in Export-Toolbox.

sudo less /var/log/upstart/therapyserver-api.log tells me:

NOTICE:  schema "ch_suedhang_apps_export_toolbox" does not exist, skipping
NOTICE:  role "ch_suedhang_apps_export_toolbox" does not exist, skipping
NOTICE:  drop cascades to view ch_suedhang_apps_export_toolbox.export_sci_view
NOTICE:  schema "ch_suedhang_apps_export_toolbox" does not exist, skipping
NOTICE:  role "ch_suedhang_apps_export_toolbox" does not exist, skipping

=> Is [sql_init] user-apps only?

Test 3: Fresh name

Then I tried to just rename the CREATE OR REPLACE VIEW
=> Works perfectly!

My 2 cents:

For me it looks like we do have a bug around schema / role: potentially not cleared correctly while deactivating app or having trouble to "REPLACE VIEW". Hmm... this is diving to deep into system => Thanks for your help! Or could this be Ubuntu 14 vs. 16 related?

[ottigerb]

Idea

I guess we tried to update the views directly inside http://optinomic.cust.local/api/#/admin/tools/export like:

CREATE OR REPLACE VIEW sci_view AS

SELECT

  -- START:  Optinoimc Default |  Needed for Export-Toolbox
  survey_response_view.patient_id as optinomic_patient_id,
  survey_response_view.stay_id as optinomic_stay_id,
  survey_response_view.event_id as optinomic_event_id,
  survey_response_view.survey_response_id as optinomic_survey_response_id,
  survey_response_view.filled as optinomic_survey_filled,
  ((cast(response AS json))->>'id') as optinomic_limesurvey_id,
  -- END:  Optinoimc Default |  Needed for Export-Toolbox

  CONCAT(patient.cis_pid, '00', RIGHT((stay.cis_fid/100)::text,2)) as MedStatFid,
  stay.cis_fid/100 as FID,
  ((cast(response AS json))->>'Erhebungszeitpunkt') as erhebungszeitpunkt,
  ((cast(response AS json))->>'andererZeitpunkt') as andererzeitpunkt,
  TO_DATE(((cast(response AS json))->>'Datum'), 'YYYY-MM-DD')  as datum,
  CASE WHEN (filled > '2017-01-15'::date) AND (((cast(response AS json))->>'Erhebungszeitpunkt') = '2') THEN ((cast(response AS json))->>'ASCIBelastung[ESCIB1]') ELSE ((cast(response AS json))->>'ESCIBelastung[ESCIB1]') END as sci_01,
  CASE WHEN (filled > '2017-01-15'::date) AND (((cast(response AS json))->>'Erhebungszeitpunkt') = '2') THEN ((cast(response AS json))->>'ASCIBelastung[ESCIB2]') ELSE ((cast(response AS json))->>'ESCIBelastung[ESCIB2]') END as sci_02,
  CASE WHEN (filled > '2017-01-15'::date) AND (((cast(response AS json))->>'Erhebungszeitpunkt') = '2') THEN ((cast(response AS json))->>'ASCIBelastung[ESCIB3]') ELSE ((cast(response AS json))->>'ESCIBelastung[ESCIB3]') END as sci_03,
  CASE WHEN (filled > '2017-01-15'::date) AND (((cast(response AS json))->>'Erhebungszeitpunkt') = '2') THEN ((cast(response AS json))->>'ASCIBelastung[ESCIB4]') ELSE ((cast(response AS json))->>'ESCIBelastung[ESCIB4]') END as sci_04,
  CASE WHEN (filled > '2017-01-15'::date) AND (((cast(response AS json))->>'Erhebungszeitpunkt') = '2') THEN ((cast(response AS json))->>'ASCIBelastung[ESCIB5]') ELSE ((cast(response AS json))->>'ESCIBelastung[ESCIB5]') END as sci_05,
  CASE WHEN (filled > '2017-01-15'::date) AND (((cast(response AS json))->>'Erhebungszeitpunkt') = '2') THEN ((cast(response AS json))->>'ASCIBelastung[ESCIB6]') ELSE ((cast(response AS json))->>'ESCIBelastung[ESCIB6]') END as sci_06,
  CASE WHEN (filled > '2017-01-15'::date) AND (((cast(response AS json))->>'Erhebungszeitpunkt') = '2') THEN ((cast(response AS json))->>'ASCIBelastung[ESCIB7]') ELSE ((cast(response AS json))->>'ESCIBelastung[ESCIB7]') END as sci_07,
  CASE WHEN (filled > '2017-01-15'::date) AND (((cast(response AS json))->>'Erhebungszeitpunkt') = '2') THEN ((cast(response AS json))->>'ASCISymptome[ESCIS1]') ELSE ((cast(response AS json))->>'ESCISymptome[ESCIS1]') END as sci_08,
  CASE WHEN (filled > '2017-01-15'::date) AND (((cast(response AS json))->>'Erhebungszeitpunkt') = '2') THEN ((cast(response AS json))->>'ASCISymptome[ESCIS2]') ELSE ((cast(response AS json))->>'ESCISymptome[ESCIS2]') END as sci_09,
  CASE WHEN (filled > '2017-01-15'::date) AND (((cast(response AS json))->>'Erhebungszeitpunkt') = '2') THEN ((cast(response AS json))->>'ASCISymptome[ESCIS3]') ELSE ((cast(response AS json))->>'ESCISymptome[ESCIS3]') END as sci_10,
  CASE WHEN (filled > '2017-01-15'::date) AND (((cast(response AS json))->>'Erhebungszeitpunkt') = '2') THEN ((cast(response AS json))->>'ASCISymptome[ESCIS4]') ELSE ((cast(response AS json))->>'ESCISymptome[ESCIS4]') END as sci_11,
  CASE WHEN (filled > '2017-01-15'::date) AND (((cast(response AS json))->>'Erhebungszeitpunkt') = '2') THEN ((cast(response AS json))->>'ASCISymptome[ESCIS5]') ELSE ((cast(response AS json))->>'ESCISymptome[ESCIS5]') END as sci_12,
  CASE WHEN (filled > '2017-01-15'::date) AND (((cast(response AS json))->>'Erhebungszeitpunkt') = '2') THEN ((cast(response AS json))->>'ASCISymptome[ESCIS6]') ELSE ((cast(response AS json))->>'ESCISymptome[ESCIS6]') END as sci_13,
  CASE WHEN (filled > '2017-01-15'::date) AND (((cast(response AS json))->>'Erhebungszeitpunkt') = '2') THEN ((cast(response AS json))->>'ASCISymptome[ESCIS7]') ELSE ((cast(response AS json))->>'ESCISymptome[ESCIS7]') END as sci_14,
  CASE WHEN (filled > '2017-01-15'::date) AND (((cast(response AS json))->>'Erhebungszeitpunkt') = '2') THEN ((cast(response AS json))->>'ASCISymptome[ESCIS8]') ELSE ((cast(response AS json))->>'ESCISymptome[ESCIS8]') END as sci_15,
  CASE WHEN (filled > '2017-01-15'::date) AND (((cast(response AS json))->>'Erhebungszeitpunkt') = '2') THEN ((cast(response AS json))->>'ASCISymptome[ESCIS9]') ELSE ((cast(response AS json))->>'ESCISymptome[ESCIS9]') END as sci_16,
  CASE WHEN (filled > '2017-01-15'::date) AND (((cast(response AS json))->>'Erhebungszeitpunkt') = '2') THEN ((cast(response AS json))->>'ASCISymptome[ESCI10]') ELSE ((cast(response AS json))->>'ESCISymptome[ESCI10]') END as sci_17,
  CASE WHEN (filled > '2017-01-15'::date) AND (((cast(response AS json))->>'Erhebungszeitpunkt') = '2') THEN ((cast(response AS json))->>'ASCISymptome[ESCI11]') ELSE ((cast(response AS json))->>'ESCISymptome[ESCI11]') END as sci_18,
  CASE WHEN (filled > '2017-01-15'::date) AND (((cast(response AS json))->>'Erhebungszeitpunkt') = '2') THEN ((cast(response AS json))->>'ASCISymptome[ESCI12]') ELSE ((cast(response AS json))->>'ESCISymptome[ESCI12]') END as sci_19,
  CASE WHEN (filled > '2017-01-15'::date) AND (((cast(response AS json))->>'Erhebungszeitpunkt') = '2') THEN ((cast(response AS json))->>'ASCISymptome[ESCI13]') ELSE ((cast(response AS json))->>'ESCISymptome[ESCI13]') END as sci_20,
  ((cast(response AS json))->>'ESCICoping[ESCIC1]') as sci_21,
  ((cast(response AS json))->>'ESCICoping[ESCIC2]') as sci_22,
  ((cast(response AS json))->>'ESCICoping[ESCIC3]') as sci_23,
  ((cast(response AS json))->>'ESCICoping[ESCIC4]') as sci_24,
  ((cast(response AS json))->>'ESCICoping[ESCIC5]') as sci_25,
  ((cast(response AS json))->>'ESCICoping[ESCIC6]') as sci_26,
  ((cast(response AS json))->>'ESCICoping[ESCIC7]') as sci_27,
  ((cast(response AS json))->>'ESCICoping[ESCIC8]') as sci_28,
  ((cast(response AS json))->>'ESCICoping[ESCIC90]') as sci_29,
  ((cast(response AS json))->>'ESCICoping[ESCIC10]') as sci_30,
  ((cast(response AS json))->>'ESCICoping[ESCIC11]') as sci_31,
  ((cast(response AS json))->>'ESCICoping[ESCIC12]') as sci_32,
  ((cast(response AS json))->>'ESCICoping[ESCIC13]') as sci_33,
  ((cast(response AS json))->>'ESCICoping[ESCIC14]') as sci_34,
  ((cast(response AS json))->>'ESCICoping[ESCIC15]') as sci_35,
  ((cast(response AS json))->>'ESCICoping[ESCIC16]') as sci_36,
  ((cast(response AS json))->>'ESCICoping[ESCIC17]') as sci_37,
  ((cast(response AS json))->>'ESCICoping[ESCIC18]') as sci_38,
  ((cast(response AS json))->>'ESCICoping[ESCIC19]') as sci_39,
  ((cast(response AS json))->>'ESCICoping[ESCIC20]') as sci_40
FROM "survey_response_view" 
LEFT JOIN patient ON(survey_response_view.patient_id = patient.id) 
LEFT JOIN stay ON(survey_response_view.stay_id = stay.id) 

WHERE module = 'ch.suedhang.apps.sci.production'
AND survey_response_view.patient_id != '1169'
AND survey_response_view.patient_id != '387'
AND survey_response_view.patient_id != '1';

Potentially we created with this a "global" view - not corretly assigned to the schema / role.

This code is executed when a module is installed and is so in a schema specially created for that module. It means accessing the main tables as to be done by prefixing public. to their name. When returning a view/table/function created in this schema, the API does it with a special user which only has read-only access to the other schemas. This is preventing a module from modifying the API tables but also the tables from other modules.

Possible - not?

So I try
DROP VIEW sci_view and then try to de/activate the app again.

[ottigerb]

DROP VIEW sci_view

DROP VIEW IF EXISTS sci_view

Both do have the following error in console:

vendor.js:25864 POST http://optinomic.cust.local/api/run_sql 500 (Internal Server Error)

Maybe I am not the owner of the view. Hmm?

therapyserver-api.log

NOTICE:  view "sci_view" does not exist, skipping
Spock Error while handeling ["run_sql"]: QueryError {qeMessage = "query resulted in a command response", qeQuery = "DROP VIEW  IF EXISTS   sci_view"}
NOTICE:  view "sci_view" does not exist, skipping
Spock Error while handeling ["run_sql"]: QueryError {qeMessage = "query resulted in a command response", qeQuery = "DROP VIEW  IF EXISTS  sci_view"}
NOTICE:  view "sci_view" does not exist, skipping
Spock Error while handeling ["run_sql"]: QueryError {qeMessage = "query resulted in a command response", qeQuery = "DROP VIEW IF EXISTS sci_view"}
(END)

[schoenenb]

Just to confirm your guess

I guess we tried to update the views directly inside http://optinomic.cust.local/api/#/admin/tools/export like:

The "global" view generator was me on 7th of april - as I think I was told in #15