Problem when debugging a SP

soerenG · Post by **soerenG** » Mon 28 Sep 2015 21:49

Hi,
just making my first experience with the debugger. After deploying the cr_debug schema successfully and compiling a SP for debug, running it in debug mode creates an error "FUNCTION attach does not exist". However, running it without debug mode seems to work.

What might be wrong?

Regards,
Soeren

Mysql version is 5.5.44, dbForge is 6.3.358

alexa · Post by **alexa** » Tue 29 Sep 2015 12:29

Please check if there is the 'attach' function in the 'cr_debug' schema. If the function is missing, the problem should be connected with deploying the debug engine. Otherwise, the problem may be connected with permissions.

soerenG · Post by **soerenG** » Tue 29 Sep 2015 21:08

alexa wrote:Please check if there is the 'attach' function in the 'cr_debug' schema. If the function is missing, the problem should be connected with deploying the debug engine. Otherwise, the problem may be connected with permissions.

Please find attached a screenshot of the functions deployed. I deleted also and re-deployed the debug engine, the attach is still missing and no message comes up when the deployment takes place...?

Regards,
Soeren

alexa · Post by **alexa** » Wed 30 Sep 2015 11:38

Please note that you are searching the procedure in the 'Functions' node rather than in the 'Procedures' one.

Also, could you please provide us some information on the 5.5.44 server. Is it MariaDB?

soerenG · Post by **soerenG** » Wed 30 Sep 2015 18:49

alexa wrote:Please note that you are searching the procedure in the 'Functions' node rather than in the 'Procedures' one.

Also, could you please provide us some information on the 5.5.44 server. Is it MariaDB?

You mentioned in the first post a missing function, please find at the end the screenshot of the procedures. I was also able to spot some code in the procedure.

The server is a stock mysql 5.5.44 (no MariaDB!) which comes with "Debian jessie" on a x64 Linux system.

Regards,
Soeren

alexa · Post by **alexa** » Thu 01 Oct 2015 09:24

Thank you for the reply.

Could you please execute the following query:

Code: Select all

show create procedure <your_SP>

Here, please replace <your_SP> with the corresponding procedure after being compiled.

Then, please extract the text from the 'Create procedure' field by right-clicking the value in the 'Create procedure' field and selecting 'Data Viewer and Editor' from the popup menu.

You can analyze what's wrong on your own and tell us your conclusion or send us the script for investigation.

You can send a reply straight to our support system at supportATdevartDOTcom

soerenG · Post by **soerenG** » Mon 05 Oct 2015 20:38

Hi please find attached the code extracted as requested - not sure how I can find out on myself what is wrong because I don't know what all the injected debugging code is supposed to do.

Regards,
Soeren

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Code: Select all

CREATE DEFINER=`p2p_lending`@`%` PROCEDURE `sp_PNL_calc_monthly_PNL_Bondora22`(IN `START_CURRENT_PRD` varchar(20), IN `END_CURRENT_PRD` varchar(20), IN `CLIENT_ID` integer)
BEGIN
DECLARE cr_stack_depth INTEGER DEFAULT cr_debug.ENTER_MODULE2('sp_PNL_calc_monthly_PNL_Bondora', 'p2p_lending', 7, 100631)/*[cr_debug.1]*/;

  -- Lokale Variablen
  DECLARE pnl_monthly_id_LC bigint unsigned;
  DECLARE loan_identifier_LC varchar(50);
  DECLARE crs_finished integer;
  DECLARE all_fees_LC double;
  DECLARE def_sum_amort double;
  DECLARE amort_PNL, start_principal_LC, end_principal_LC, start_effective_LC, end_effective_LC, sell_principal_LC,
      sell_effective_LC, buy_principal_LC, buy_effective_LC, amortisation_LC double;
  DECLARE most_recent_sell_date_LC datetime; 

  -- Cursor fuer Durchlauf ueber die PnL-Records der aktuellen Periode. Da die Amortising-PnL-Berechnung relativ kompliziert ist
  -- fuehren wir die Berechnung strukturiert per Record durch, statt in ein einzelnes Update-Statement zu quetschen.
  DECLARE pnl_cursor CURSOR FOR
    SELECT pnl.pnl_monthly_id, pnl.period_start_principal, pnl.period_end_principal, pnl.period_start_effective,
        pnl.period_end_effective, pnl.most_recent_sell_date, period_sell_principal, period_sell_effective,
        pnl.period_buy_principal, period_buy_effective, period_amortisation
      FROM pnl_monthly_detail pnl
     WHERE pnl.period_start = START_CURRENT_PRD
       AND pnl.period_end   = END_CURRENT_PRD
       AND pnl.client_id    = CLIENT_ID
       AND pnl.platform     = 'BONDORA'
     ORDER BY pnl.pnl_monthly_id ;
  
  -- ""Not found"" handler fuer Cursors
  DECLARE CONTINUE HANDLER FOR NOT FOUND BEGIN/*[cr_debug.3 5]*/
DECLARE cr_stack_depth INTEGER DEFAULT cr_debug.ENTER_HANDLER('sp_PNL_calc_monthly_PNL_Bondora_Handler', 'sp_PNL_calc_monthly_PNL_Bondora', 'p2p_lending', 7, 100631)/*[cr_debug.2]*/;
CALL cr_debug.UPDATE_WATCH3('pnl_monthly_id_LC', pnl_monthly_id_LC, 'bigint unsigned', cr_stack_depth)/*[cr_debug.2]*/;
CALL cr_debug.UPDATE_WATCH3('loan_identifier_LC', loan_identifier_LC, 'varchar(50)', cr_stack_depth)/*[cr_debug.2]*/;
CALL cr_debug.UPDATE_WATCH3('crs_finished', crs_finished, 'integer', cr_stack_depth)/*[cr_debug.2]*/;
CALL cr_debug.UPDATE_WATCH3('all_fees_LC', all_fees_LC, 'double', cr_stack_depth)/*[cr_debug.2]*/;
CALL cr_debug.UPDATE_WATCH3('def_sum_amort', def_sum_amort, 'double', cr_stack_depth)/*[cr_debug.2]*/;
CALL cr_debug.UPDATE_WATCH3('amort_PNL', amort_PNL, 'double', cr_stack_depth)/*[cr_debug.2]*/;
CALL cr_debug.UPDATE_WATCH3('start_principal_LC', start_principal_LC, 'double', cr_stack_depth)/*[cr_debug.2]*/;
CALL cr_debug.UPDATE_WATCH3('end_principal_LC', end_principal_LC, 'double', cr_stack_depth)/*[cr_debug.2]*/;
CALL cr_debug.UPDATE_WATCH3('start_effective_LC', start_effective_LC, 'double', cr_stack_depth)/*[cr_debug.2]*/;
CALL cr_debug.UPDATE_WATCH3('end_effective_LC', end_effective_LC, 'double', cr_stack_depth)/*[cr_debug.2]*/;
CALL cr_debug.UPDATE_WATCH3('sell_principal_LC', sell_principal_LC, 'double', cr_stack_depth)/*[cr_debug.2]*/;
CALL cr_debug.UPDATE_WATCH3('sell_effective_LC', sell_effective_LC, 'double', cr_stack_depth)/*[cr_debug.2]*/;
CALL cr_debug.UPDATE_WATCH3('buy_principal_LC', buy_principal_LC, 'double', cr_stack_depth)/*[cr_debug.2]*/;
CALL cr_debug.UPDATE_WATCH3('buy_effective_LC', buy_effective_LC, 'double', cr_stack_depth)/*[cr_debug.2]*/;
CALL cr_debug.UPDATE_WATCH3('amortisation_LC', amortisation_LC, 'double', cr_stack_depth)/*[cr_debug.2]*/;
CALL cr_debug.UPDATE_WATCH3('most_recent_sell_date_LC', most_recent_sell_date_LC, 'datetime', cr_stack_depth)/*[cr_debug.2]*/;
CALL cr_debug.UPDATE_WATCH3('`START_CURRENT_PRD`', `START_CURRENT_PRD`, 'varchar(20)', cr_stack_depth)/*[cr_debug.2]*/;
CALL cr_debug.UPDATE_WATCH3('`END_CURRENT_PRD`', `END_CURRENT_PRD`, 'varchar(20)', cr_stack_depth)/*[cr_debug.2]*/;
CALL cr_debug.UPDATE_WATCH3('`CLIENT_ID`', `CLIENT_ID`, 'integer', cr_stack_depth)/*[cr_debug.2]*/;
CALL cr_debug.TRACE(28, 28, 41, 62, cr_stack_depth)/*[cr_debug.2]*/;
SET crs_finished = 1;
CALL cr_debug.UPDATE_WATCH3('crs_finished', crs_finished, '', cr_stack_depth)/*[cr_debug.1]*/;
CALL cr_debug.LEAVE_MODULE(cr_stack_depth - 1)/*[cr_debug.1]*/;
/*[cr_debug.4 4]*/END; 

  --
  -- Schritt 1: Daten aus Repayments fuer Periode aggregieren; das ist ein einfaches Summieren
  -- daher kommen wir ohne kompliziertes Cursor-Gedoens aus.
  --
  CALL cr_debug.UPDATE_WATCH3('`START_CURRENT_PRD`', `START_CURRENT_PRD`, 'varchar(20)', cr_stack_depth)/*[cr_debug.2]*/;
CALL cr_debug.UPDATE_WATCH3('`END_CURRENT_PRD`', `END_CURRENT_PRD`, 'varchar(20)', cr_stack_depth)/*[cr_debug.2]*/;
CALL cr_debug.UPDATE_WATCH3('`CLIENT_ID`', `CLIENT_ID`, 'integer', cr_stack_depth)/*[cr_debug.2]*/;
CALL cr_debug.UPDATE_WATCH3('pnl_monthly_id_LC', pnl_monthly_id_LC, 'bigint unsigned', cr_stack_depth)/*[cr_debug.2]*/;
CALL cr_debug.UPDATE_WATCH3('loan_identifier_LC', loan_identifier_LC, 'varchar(50)', cr_stack_depth)/*[cr_debug.2]*/;
CALL cr_debug.UPDATE_WATCH3('crs_finished', crs_finished, 'integer', cr_stack_depth)/*[cr_debug.2]*/;
CALL cr_debug.UPDATE_WATCH3('all_fees_LC', all_fees_LC, 'double', cr_stack_depth)/*[cr_debug.2]*/;
CALL cr_debug.UPDATE_WATCH3('def_sum_amort', def_sum_amort, 'double', cr_stack_depth)/*[cr_debug.2]*/;
CALL cr_debug.UPDATE_WATCH3('amort_PNL', amort_PNL, 'double', cr_stack_depth)/*[cr_debug.2]*/;
CALL cr_debug.UPDATE_WATCH3('start_principal_LC', start_principal_LC, 'double', cr_stack_depth)/*[cr_debug.2]*/;
CALL cr_debug.UPDATE_WATCH3('end_principal_LC', end_principal_LC, 'double', cr_stack_depth)/*[cr_debug.2]*/;
CALL cr_debug.UPDATE_WATCH3('start_effective_LC', start_effective_LC, 'double', cr_stack_depth)/*[cr_debug.2]*/;
CALL cr_debug.UPDATE_WATCH3('end_effective_LC', end_effective_LC, 'double', cr_stack_depth)/*[cr_debug.2]*/;
CALL cr_debug.UPDATE_WATCH3('sell_principal_LC', sell_principal_LC, 'double', cr_stack_depth)/*[cr_debug.2]*/;
CALL cr_debug.UPDATE_WATCH3('sell_effective_LC', sell_effective_LC, 'double', cr_stack_depth)/*[cr_debug.2]*/;
CALL cr_debug.UPDATE_WATCH3('buy_principal_LC', buy_principal_LC, 'double', cr_stack_depth)/*[cr_debug.2]*/;
CALL cr_debug.UPDATE_WATCH3('buy_effective_LC', buy_effective_LC, 'double', cr_stack_depth)/*[cr_debug.2]*/;
CALL cr_debug.UPDATE_WATCH3('amortisation_LC', amortisation_LC, 'double', cr_stack_depth)/*[cr_debug.2]*/;
CALL cr_debug.UPDATE_WATCH3('most_recent_sell_date_LC', most_recent_sell_date_LC, 'datetime', cr_stack_depth)/*[cr_debug.2]*/;
CALL cr_debug.TRACE(2, 2, 0, 5, cr_stack_depth)/*[cr_debug.2]*/;
CALL cr_debug.TRACE(34, 34, 2, 66, cr_stack_depth)/*[cr_debug.2]*/;
CALL log_doLog('info', 'START sp_PNL_calc_monthly_PNL_Bondora');
 CALL cr_debug.TRACE(35, 58, 1, 2, cr_stack_depth)/*[cr_debug.2]*/;
UPDATE pnl_monthly_detail pnl
 JOIN
 (
    -- Interest, late fees and Tilgung summieren und direkt als Summe pro note zuweisen
  SELECT note_id, SUM(interest) AS sum_ir, SUM(late_fees) AS sum_late,
           SUM(principal) AS sum_am
   FROM p2p_lending.bondora_repayments pay
  WHERE pay.client_id = CLIENT_ID
    AND pay.payment_date BETWEEN START_CURRENT_PRD AND END_CURRENT_PRD
  GROUP BY note_id
 ) income

   -- Join auf die note_id / client_id fuer Bondora ist immer eindeutig
  ON pnl.loan_identifier = income.note_id

 SET pnl.period_income_interest = sum_ir,
     pnl.period_income_late_interest = sum_late,
      pnl.period_amortisation = sum_am

  -- WICHTIG: PnL-Records immer ueber period & client eingrenzen
 WHERE pnl.period_start BETWEEN START_CURRENT_PRD AND END_CURRENT_PRD
   AND pnl.client_id = CLIENT_ID
    AND pnl.platform = 'BONDORA'
 ;
CALL cr_debug.UPDATE_SYSTEM_CALLS(104)/*[cr_debug.1]*/;

  CALL cr_debug.TRACE(60, 60, 2, 101, cr_stack_depth)/*[cr_debug.2]*/;
CALL log_doLog('info', CONCAT('Anzahl Loan-Records mit interest/late_fees update: ', cr_debug.get_ROW_COUNT()));

  --
  -- Schritt 2: Alle Loans mit ** start_principal = 0 ** wurden offenbar innerhalb der aktuellen Periode
  -- erworben. Wir holen diese Daten daher aus der dem Loan-Set
  --
  CALL cr_debug.TRACE(66, 114, 2, 33, cr_stack_depth)/*[cr_debug.2]*/;
UPDATE pnl_monthly_detail pnl
  JOIN (

        -- Inner Join: Loan-Daten von jeweils erstem Tag in den Loan-Records ermitteln
        SELECT
         IF(bls.BoughtFromResale = 1, bls.BoughtFromResale_Date, bls.LoanDate) AS most_recent_buy_date,
         bls.InvestmentPrincipal AS period_start_principal,
         IF(bls.BoughtFromResale = 1, bls.PurchasePrice, bls.InvestmentPrincipal) AS period_start_effective,
         bls.note_id
        FROM bondora_loan_set bls

        JOIN (
              -- Inner Join: Minimales/Erstes asof_date ermitteln, zu dem ein Loan in den Daten auftaucht
             SELECT MIN(asof_date) AS min_asof_date, note_id
               FROM bondora_loan_set
               WHERE client_id = CLIENT_ID
                 AND asof_date BETWEEN START_CURRENT_PRD AND END_CURRENT_PRD
               GROUP BY note_id
          ) min_asof

           ON min_asof.note_id = bls.note_id
          AND bls.asof_date = min_asof_date

         WHERE bls.client_id = CLIENT_ID
       ) loan

 ON pnl.loan_identifier = loan.note_id

 SET pnl.most_recent_buy_date = loan.most_recent_buy_date,
      pnl.first_inventory_date = loan.most_recent_buy_date,
      pnl.period_buy_principal = loan.period_start_principal,
      pnl.period_buy_effective = loan.period_start_effective,

      -- ACHTUNG: An dieser Stelle setzten wir den effective Value als Einstandswert als vorlaeufigen
      -- Endwer. Die Korrektur auf den echten effective Wert nach Tilgungen, Verkauf relativ zum
      -- Principal-Wert erfolgt dann weiter unten. Den end_principal setzen wir ebenfalls nicht, der
      -- wird nachfolgend separat ermittelt.
      pnl.period_end_effective = loan.period_start_effective,
   
   -- TODO: Ungeklaert. Fuer einige Loans (bspw. '19b9e93c-52a1-45f8-84c4-a45e017f286b') wird im naechsten Schritt kein
   -- End-Principal gesetzt, Ursache ist nicht klar. Diese Loans werden mit dem nachfolgenden ""set"" fuer den Moment als
   -- Workaround gefixt.
   pnl.period_end_principal = loan.period_start_principal

 WHERE pnl.period_start BETWEEN START_CURRENT_PRD AND END_CURRENT_PRD
   AND pnl.client_id = CLIENT_ID
   AND pnl.period_start_principal = 0
    AND pnl.first_inventory_date IS NULL
    AND pnl.platform = 'BONDORA';
CALL cr_debug.UPDATE_SYSTEM_CALLS(104)/*[cr_debug.1]*/;

  CALL cr_debug.TRACE(116, 116, 2, 101, cr_stack_depth)/*[cr_debug.2]*/;
CALL log_doLog('info', CONCAT('Anzahl Loans mit Start im aktuellen Monat gesetzt: ', cr_debug.get_ROW_COUNT()));

  --
  -- Schritt 3: Principal zum Ende der Periode setzen, auch hier aus Loans ermitteln; Das Quellfeld
  -- ist ""OutstandingPricncipal""
  --
 CALL cr_debug.TRACE(122, 153, 1, 34, cr_stack_depth)/*[cr_debug.2]*/;
UPDATE pnl_monthly_detail pnl

   JOIN (

   -- Ermittle OutstandingPrincipal am jeweiligen max(asof_date) per note
   SELECT ls2.note_id, ls2.OutstandingPrincipal, ls2.client_id, asof_max.max_asof_date
     FROM bondora_loan_set ls2

     JOIN (

     -- Ermittle jeweils den max(asof_date) per note_id aus der Grundmenge
     SELECT MAX(ls.asof_date) AS max_asof_date, ls.note_id, ls.client_id
       FROM bondora_loan_set ls
      WHERE asof_date BETWEEN START_CURRENT_PRD AND END_CURRENT_PRD
        AND client_id = CLIENT_ID
      GROUP BY note_id
     ) asof_max

    ON ls2.note_id = asof_max.note_id
   AND ls2.asof_date = asof_max.max_asof_date
   AND ls2.client_id = asof_max.client_id
   ) outstanding

  ON pnl.loan_identifier = outstanding.note_id
 AND outstanding.client_id = pnl.client_id
 AND outstanding.max_asof_date BETWEEN pnl.period_start AND pnl.period_end

  SET pnl.period_end_principal = outstanding.OutstandingPrincipal

 WHERE pnl.period_start BETWEEN START_CURRENT_PRD AND END_CURRENT_PRD
   AND pnl.client_id  = CLIENT_ID
    AND pnl.platform = 'BONDORA' ;
CALL cr_debug.UPDATE_SYSTEM_CALLS(104)/*[cr_debug.1]*/;

  CALL cr_debug.TRACE(155, 155, 2, 115, cr_stack_depth)/*[cr_debug.2]*/;
CALL log_doLog('info', CONCAT('Anzahl Loans ""Oustanding Principal"" am Ende der Periode gesetzt: ', cr_debug.get_ROW_COUNT()));

  --
  -- Setzen der ""period sales""-Felder. Als Quelle dienen auch hier die Loan-Daten, da nur
  -- diese die vollstaendigen Verkaufs-Infos beinhalten.
  --
  CALL cr_debug.TRACE(161, 180, 2, 33, cr_stack_depth)/*[cr_debug.2]*/;
UPDATE pnl_monthly_detail pnl
    JOIN (
          SELECT MIN(asof_date) AS min_asof_date, note_id, SoldInResale_Price, SoldInResale_Principal, SoldInResale_Date
            FROM bondora_loan_set
           WHERE client_id = CLIENT_ID
             AND asof_date BETWEEN START_CURRENT_PRD AND END_CURRENT_PRD
             AND SoldInResale_Date >= START_CURRENT_PRD
             AND SoldInResale_Date < (END_CURRENT_PRD + INTERVAL 1 DAY)
           GROUP BY note_id
        ) sold_loans
    ON pnl.loan_identifier = sold_loans.note_id

    SET pnl.most_recent_sell_date = SoldInResale_Date,
        pnl.period_sell_principal = SoldInResale_Principal,
        pnl.period_sell_effective = SoldInResale_Price

  WHERE pnl.period_start = START_CURRENT_PRD
    AND pnl.period_end = END_CURRENT_PRD
    AND pnl.client_id = CLIENT_ID
    AND pnl.platform = 'BONDORA';
CALL cr_debug.UPDATE_SYSTEM_CALLS(104)/*[cr_debug.1]*/;

 
  --
  -- Nachdem Nominal am Anfang und Ende bekannt sind, koennen wir den effctive value am Ende ausrechnen.
  -- ausrechnen.
  -- ACHTUNG: Wenn der Kredit in der laufenden Periode loslaeuft, ist effective_start = 0 und statt dessen
  -- (siehe auch oben) der effective_end zu nehmen!
  --
  CALL cr_debug.TRACE(189, 195, 2, 35, cr_stack_depth)/*[cr_debug.2]*/;
UPDATE pnl_monthly_detail pnl
     SET pnl.period_end_effective = (1 + ((pnl.period_end_principal - (pnl.period_start_principal+pnl.period_buy_principal) ) / (pnl.period_start_principal+pnl.period_buy_principal) )) * IF(pnl.period_start_effective != 0, pnl.period_start_effective, pnl.period_end_effective)
   WHERE pnl.period_start = START_CURRENT_PRD
     AND pnl.period_end = END_CURRENT_PRD
     AND pnl.CLIENT_ID = CLIENT_ID
     AND (pnl.period_start_principal+pnl.period_buy_principal) <> 0
     AND pnl.platform = 'BONDORA' ;
CALL cr_debug.UPDATE_SYSTEM_CALLS(104)/*[cr_debug.1]*/;

  CALL cr_debug.TRACE(197, 197, 2, 92, cr_stack_depth)/*[cr_debug.2]*/;
CALL log_doLog('info', CONCAT('Anzahl Updates fuer period_end_effective: ', cr_debug.get_ROW_COUNT()));

  CALL cr_debug.TRACE(199, 199, 2, 18, cr_stack_depth)/*[cr_debug.2]*/;
OPEN pnl_cursor;
  
  -- Start Loop
  pnl_record_loop: LOOP
  
    CALL cr_debug.TRACE(204, 204, 4, 22, cr_stack_depth)/*[cr_debug.2]*/;
SET amort_PNL = 0;
CALL cr_debug.UPDATE_WATCH3('amort_PNL', amort_PNL, '', cr_stack_depth)/*[cr_debug.1]*/;
    
    CALL cr_debug.TRACE(206, 208, 4, 122, cr_stack_depth)/*[cr_debug.2]*/;
FETCH pnl_cursor
     INTO pnl_monthly_id_LC, start_principal_LC, end_principal_LC, start_effective_LC, end_effective_LC,
      most_recent_sell_date_LC, sell_principal_LC, sell_effective_LC, buy_principal_LC, buy_effective_LC, amortisation_LC;
CALL cr_debug.UPDATE_WATCH3('pnl_monthly_id_LC', pnl_monthly_id_LC, '', cr_stack_depth)/*[cr_debug.1]*/;
CALL cr_debug.UPDATE_WATCH3('start_principal_LC', start_principal_LC, '', cr_stack_depth)/*[cr_debug.1]*/;
CALL cr_debug.UPDATE_WATCH3('end_principal_LC', end_principal_LC, '', cr_stack_depth)/*[cr_debug.1]*/;
CALL cr_debug.UPDATE_WATCH3('start_effective_LC', start_effective_LC, '', cr_stack_depth)/*[cr_debug.1]*/;
CALL cr_debug.UPDATE_WATCH3('end_effective_LC', end_effective_LC, '', cr_stack_depth)/*[cr_debug.1]*/;
CALL cr_debug.UPDATE_WATCH3('most_recent_sell_date_LC', most_recent_sell_date_LC, '', cr_stack_depth)/*[cr_debug.1]*/;
CALL cr_debug.UPDATE_WATCH3('sell_principal_LC', sell_principal_LC, '', cr_stack_depth)/*[cr_debug.1]*/;
CALL cr_debug.UPDATE_WATCH3('sell_effective_LC', sell_effective_LC, '', cr_stack_depth)/*[cr_debug.1]*/;
CALL cr_debug.UPDATE_WATCH3('buy_principal_LC', buy_principal_LC, '', cr_stack_depth)/*[cr_debug.1]*/;
CALL cr_debug.UPDATE_WATCH3('buy_effective_LC', buy_effective_LC, '', cr_stack_depth)/*[cr_debug.1]*/;
CALL cr_debug.UPDATE_WATCH3('amortisation_LC', amortisation_LC, '', cr_stack_depth)/*[cr_debug.1]*/;

    -- End of select?
    CALL cr_debug.TRACE(211, 213, 4, 11, cr_stack_depth)/*[cr_debug.2]*/;
IF crs_finished = 1 THEN
      CALL cr_debug.TRACE(212, 212, 6, 28, cr_stack_depth)/*[cr_debug.2]*/;
LEAVE pnl_record_loop;
    END IF;

    -- Fall 1: Principal Start & Ende = 0; dann wurde innerhalb der Periode der Loan erworben und wieder getilgt
    -- Ist auch das SaleDate gesetzt, wurde er verkauft und die PnL ist die Summe aller Cash-Flows:
    -- + Verkaufspreis - Einkaufspreis + Tilgungen
    CALL cr_debug.TRACE(218, 232, 4, 11, cr_stack_depth)/*[cr_debug.2]*/;
IF (start_principal_LC = 0) && (end_principal_LC = 0) && (most_recent_sell_date_LC IS NOT NULL) THEN
      CALL cr_debug.TRACE(219, 219, 6, 77, cr_stack_depth)/*[cr_debug.2]*/;
SET amort_PNL = sell_effective_LC - buy_effective_LC + amortisation_LC;
CALL cr_debug.UPDATE_WATCH3('amort_PNL', amort_PNL, '', cr_stack_depth)/*[cr_debug.1]*/;
      -- CALL log_doLog('debug', CONCAT('  Fall #1 fuer ', pnl_monthly_id_LC, '  =  ', amort_PNL));

    -- Fall 2: Nur Amortisation mit Endbestand, kein Verkauf: PNL = Aenderung_Principal * (principal_current - effective_current)
    ELSEIF (amortisation_LC != 0) && (end_principal_LC > 0) && (most_recent_sell_date_LC IS NULL) THEN
      CALL cr_debug.TRACE(224, 224, 6, 168, cr_stack_depth)/*[cr_debug.2]*/;
SET amort_PNL = (amortisation_LC / (start_principal_LC + buy_principal_LC)) * ((start_principal_LC + buy_principal_LC) - (start_effective_LC + buy_effective_LC));
CALL cr_debug.UPDATE_WATCH3('amort_PNL', amort_PNL, '', cr_stack_depth)/*[cr_debug.1]*/;
      -- CALL log_doLog('debug', CONCAT('  Fall #2 fuer ', pnl_monthly_id_LC, '  =  ', amort_PNL));
           
    -- Fall 3: Nur Sale mit Anfangsbestand
    ELSEIF (amortisation_LC = 0) && (end_principal_LC = 0) && (most_recent_sell_date_LC IS NOT NULL) THEN
      CALL cr_debug.TRACE(229, 229, 6, 61, cr_stack_depth)/*[cr_debug.2]*/;
SET amort_PNL = sell_effective_LC - start_effective_LC;
CALL cr_debug.UPDATE_WATCH3('amort_PNL', amort_PNL, '', cr_stack_depth)/*[cr_debug.1]*/;
     -- CALL log_doLog('debug', CONCAT('  Fall #3 fuer ', pnl_monthly_id_LC, '  =  ', amort_PNL));
        
    END IF;
    
    -- Schliesslich Update des aktuellen PnL-Satzes mit der errechneten amortisation PnL
    CALL cr_debug.TRACE(235, 237, 4, 50, cr_stack_depth)/*[cr_debug.2]*/;
UPDATE pnl_monthly_detail pmd
       SET pmd.period_income_amortisation = amort_PNL
     WHERE pmd.pnl_monthly_id = pnl_monthly_id_LC;
CALL cr_debug.UPDATE_SYSTEM_CALLS(104)/*[cr_debug.1]*/;
  
  END LOOP;

  -- Final den Cursor schliessen
  CALL cr_debug.TRACE(242, 242, 2, 19, cr_stack_depth)/*[cr_debug.2]*/;
CLOSE pnl_cursor;
  
  -- Final die Fees im Pseude-Loan ""BONDORA"" einsammeln
  CALL cr_debug.TRACE(245, 251, 2, 22, cr_stack_depth)/*[cr_debug.2]*/;
SELECT (
    SELECT ROUND(-1 * ABS(SUM(amount)), 2)
      FROM bondora_account_records acc
     WHERE acc.TransferDate BETWEEN START_CURRENT_PRD AND END_CURRENT_PRD
       AND acc.type IN (10, 11, 12)
         AND acc.client_id = CLIENT_ID
   ) INTO all_fees_LC;
CALL cr_debug.UPDATE_SYSTEM_CALLS(101)/*[cr_debug.1]*/;
CALL cr_debug.UPDATE_WATCH3('all_fees_LC', all_fees_LC, '', cr_stack_depth)/*[cr_debug.1]*/;
 
 CALL cr_debug.TRACE(253, 260, 1, 43, cr_stack_depth)/*[cr_debug.2]*/;
UPDATE pnl_monthly_detail pnl
      SET period_income_fees = all_fees_LC
    
 WHERE pnl.period_start = START_CURRENT_PRD
      AND pnl.period_end   = END_CURRENT_PRD
      AND pnl.client_id    = CLIENT_ID
      AND pnl.platform        = 'BONDORA'
      AND pnl.loan_identifier = 'BONDORA' ;
CALL cr_debug.UPDATE_SYSTEM_CALLS(104)/*[cr_debug.1]*/;

  CALL cr_debug.TRACE(262, 262, 2, 80, cr_stack_depth)/*[cr_debug.2]*/;
CALL log_doLog('info', CONCAT('Summe Bondora Fees ermittelt: ', all_fees_LC));
CALL cr_debug.UPDATE_WATCH3('all_fees_LC', all_fees_LC, '', cr_stack_depth)/*[cr_debug.1]*/;


  -- ------------------------------------------------------------------------------------
  -- Ab hier Setzen der Default-Felder. Dies wird zwar nicht fuer die Steuerberechnung
  -- benoetigt, aber fuer die Performanceberechnung
  --

  -- Default-Status und -Datum setzen
 CALL cr_debug.TRACE(271, 288, 1, 34, cr_stack_depth)/*[cr_debug.2]*/;
UPDATE pnl_monthly_detail pnl
   JOIN (
  -- Ermittle alle Loans mit jeweiligen Datum, die in der aktuellen Periode in den Default gewechselt sind
  SELECT DISTINCT loan.note_id, loan.Default_StartDate, client_id, loan.EAD1
    FROM bondora_loan_set loan
   WHERE loan.client_id = CLIENT_ID
     AND loan.Default_StartDate BETWEEN START_CURRENT_PRD AND END_CURRENT_PRD
     AND loan.AD = 1
     AND loan.asof_date BETWEEN START_CURRENT_PRD AND END_CURRENT_PRD
  ) defaults
    ON pnl.loan_identifier = defaults.note_id
    AND pnl.client_id = defaults.client_id
    SET pnl.is_defaulted_in_period = 1,
        pnl.default_date = defaults.Default_StartDate,
        pnl.exposure_at_default = defaults.EAD1
  WHERE pnl.period_start BETWEEN START_CURRENT_PRD AND END_CURRENT_PRD
   AND pnl.client_id = CLIENT_ID
    AND pnl.platform = 'BONDORA' ;
CALL cr_debug.UPDATE_SYSTEM_CALLS(104)/*[cr_debug.1]*/;

  CALL cr_debug.TRACE(290, 290, 2, 98, cr_stack_depth)/*[cr_debug.2]*/;
CALL log_doLog('info', CONCAT('Anzahl Loans mit Defaults in laufender Periode: ', cr_debug.get_ROW_COUNT()));

  -- Tilgungsleistungen in laufender Periode ab Default-Tag setzen
  CALL cr_debug.TRACE(293, 309, 2, 61, cr_stack_depth)/*[cr_debug.2]*/;
UPDATE pnl_monthly_detail pnl

    -- Join erfolgt ueber die note_id und summiert alle Zahlungen zwischen Default-Datum und Monatsende
    JOIN (
            SELECT SUM(br.principal) AS payments, br.note_id, br.payment_date
              FROM bondora_repayments br
             WHERE br.payment_date BETWEEN START_CURRENT_PRD AND END_CURRENT_PRD
             GROUP BY br.note_id, br.payment_date
        ) amort_after_default
      ON pnl.loan_identifier = amort_after_default.note_id
     SET pnl.period_amortisation_after_default = amort_after_default.payments
   WHERE pnl.period_start = START_CURRENT_PRD
     AND pnl.period_end = END_CURRENT_PRD
     AND pnl.client_id = CLIENT_ID
     AND pnl.default_date IS NOT NULL
     AND pnl.platform = 'BONDORA'
     AND amort_after_default.payment_date > pnl.default_date;
CALL cr_debug.UPDATE_SYSTEM_CALLS(104)/*[cr_debug.1]*/;

  CALL cr_debug.TRACE(311, 317, 2, 23, cr_stack_depth)/*[cr_debug.2]*/;
SELECT SUM(period_amortisation_after_default)
    FROM pnl_monthly_detail pnl
   WHERE pnl.period_start = START_CURRENT_PRD
     AND pnl.period_end = END_CURRENT_PRD
     AND pnl.client_id = CLIENT_ID
     AND pnl.platform = 'BONDORA'
    INTO def_sum_amort;
CALL cr_debug.UPDATE_SYSTEM_CALLS(101)/*[cr_debug.1]*/;
CALL cr_debug.UPDATE_WATCH3('def_sum_amort', def_sum_amort, '', cr_stack_depth)/*[cr_debug.1]*/;

  CALL cr_debug.TRACE(319, 319, 2, 112, cr_stack_depth)/*[cr_debug.2]*/;
CALL log_doLog('info', CONCAT('Anzahl Loans mit Tilgungen nach Default in laufender Periode: ', cr_debug.get_ROW_COUNT()));
  CALL cr_debug.TRACE(320, 320, 2, 97, cr_stack_depth)/*[cr_debug.2]*/;
CALL log_doLog('info', CONCAT('Summe Loan-Zahlungen nach Default: ', ROUND(def_sum_amort, 2)));
CALL cr_debug.UPDATE_WATCH3('def_sum_amort', def_sum_amort, '', cr_stack_depth)/*[cr_debug.1]*/;
  CALL cr_debug.TRACE(321, 321, 2, 64, cr_stack_depth)/*[cr_debug.2]*/;
CALL log_doLog('info', 'END sp_PNL_calc_monthly_PNL_Bondora');

CALL cr_debug.TRACE(323, 323, 0, 3, cr_stack_depth)/*[cr_debug.2]*/;
CALL cr_debug.LEAVE_MODULE(cr_stack_depth - 1)/*[cr_debug.2]*/;
END
;

alexa · Post by **alexa** » Thu 08 Oct 2015 08:11

Hello Soeren,

Thank you for the reply.

Will it be possible for you to try another versions of the MySQL servers?

alexa · Post by **alexa** » Fri 09 Oct 2015 10:21

Could you please let us know at what row of the stored procedure the error "FUNCTION attach does not exist" pops up?

Please also provide us a screenshot of the error.

alexa · Post by **alexa** » Tue 13 Oct 2015 08:07

Could you please perform the following:

1. Select 'Tools -> Options' from the main menu. The 'Options' window opens.
2. Navigate to the 'Environment -> Output' branch.
3. Select the 'Write queries sent by the program to the SQL Log' and 'Log application errors' options.
4. Unselect the 'Delete application log after closing' option. Click 'OK'.
5. Reproduce the issue.
6. Send us the *.log file with the latest date from the directory

Code: Select all

%SystemDrive%\Users\%UserName%\AppData\Roaming\Devart\dbForge Studio for MySQL\

alexa · Post by **alexa** » Wed 20 Jan 2016 09:24

It appears you have no access to the mysql.proc object. Please grant the access to the specified object.

Devart Forums

Problem when debugging a SP

Problem when debugging a SP

Re: Problem when debugging a SP

Re: Problem when debugging a SP

Re: Problem when debugging a SP

Re: Problem when debugging a SP

Re: Problem when debugging a SP

Re: Problem when debugging a SP

Re: Problem when debugging a SP

Re: Problem when debugging a SP

Re: Problem when debugging a SP

Re: Problem when debugging a SP