Thesql complex procedure debugging

MySQLmysql-8.0

I have below stored procedure which i would like to debug but its not throwing any errors into error table.This stored procedure is run by event manager on every minute.

Here is the process :

I have four tables: A, B,C,D

Whenever any data will be inserted into table A same time using trigger data will be inserted into table b. Here table b has all those columns which are available in table A.table D contain only latest date on which procedure has been ran.

Below is the procedure which is inserting data into table C

DROP PROCEDURE IF EXISTS `abc`; 
DELIMITER $$
CREATE PROCEDURE `abc`(IN `last_execution_time` DATETIME, IN `end_date` DATETIME)
BEGIN
    DECLARE errorCode VARCHAR(5) DEFAULT '00000';
    DECLARE errorMessage TEXT DEFAULT '';
    DECLARE cdrs_uniqueid text;
    DECLARE cdrs_staging_uniqueid text;
    DECLARE cdrs_staging_count varchar(5);
    DECLARE reseller_cdrs_count varchar(5);
    DECLARE reseller_cdrs_uniqueid text;
    DECLARE cdrs_count varchar(5);
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    BEGIN
        GET DIAGNOSTICS CONDITION 1
                errorCode = RETURNED_SQLSTATE, errorMessage = MESSAGE_TEXT;
        END;
        select count(`uniqueid`),group_concat(`uniqueid`) from  b where `end_stamp`>=last_execution_time AND `end_stamp` < end_date into cdrs_staging_count,cdrs_staging_uniqueid;
        select count(`uniqueid`),group_concat(`uniqueid`) from cdrs where `end_stamp`>= last_execution_time AND `end_stamp` < end_date into cdrs_count,cdrs_uniqueid;
        select count(`uniqueid`),group_concat(`uniqueid`) from reseller_cdrs where `end_stamp`>= last_execution_time AND `end_stamp` < end_date into reseller_cdrs_count,reseller_cdrs_uniqueid;
        INSERT INTO `c` (`account_id`, `reseller_id`, `type`, `country_id`, `billseconds`, `mcd`, `total_calls`, `debit`, `cost`, `total_answered_call`, `total_fail_call`, `unique_date`, `calldate`) (SELECT `accountid`, `reseller_id`, `type`, `country_id`, SUM(`billseconds`) AS `billseconds`, MAX(`billseconds`) AS `mcd`, COUNT(*) AS `total_calls`, SUM(`debit`) AS `debit`, SUM(`cost`) AS `cost`, COUNT(CASE WHEN `billseconds` > 0 THEN 1 END) AS `total_answered_call`, COUNT(CASE WHEN `billseconds`=0 THEN 1 END) AS `total_fail_call`, DATE_FORMAT(`callstart`, "%Y%m%d") AS `unique_date`, DATE_FORMAT(`callstart`, "%Y-%m-%d") AS `calldate` FROM `b` WHERE `end_stamp`>=`last_execution_time` AND `end_stamp` < `end_date` GROUP BY `accountid`, `country_id`, `reseller_id`, `unique_date`) ON DUPLICATE KEY UPDATE `billseconds`=(`billseconds` + VALUES(`billseconds`)), `debit`=(`debit` + VALUES(`debit`)), `cost`=(`cost` + VALUES(`cost`)), `total_answered_call`=(`total_answered_call` + VALUES(`total_answered_call`)), `total_fail_call`=(`total_fail_call` + VALUES(`total_fail_call`)), `calldate`=`calldate`, `mcd`=GREATEST(VALUES(`mcd`), `mcd`), `total_calls`=(`total_calls` + VALUES(`total_calls`));
        insert into debug_info(`query`,`type`,`operation`) values(concat('INSERT INTO `cdrs_day_by_summary` (`account_id`, `reseller_id`, `type`, `country_id`, `billseconds`, `mcd`, `total_calls`, `debit`, `cost`, `total_answered_call`, `total_fail_call`, `unique_date`, `calldate`) (SELECT `accountid`, `reseller_id`, `type`, `country_id`, SUM(`billseconds`) AS `billseconds`, MAX(`billseconds`) AS `mcd`, COUNT(*) AS `total_calls`, SUM(`debit`) AS `debit`, SUM(`cost`) AS `cost`, COUNT(CASE WHEN `billseconds` > 0 THEN 1 END) AS `total_answered_call`, COUNT(CASE WHEN `billseconds`=0 THEN 1 END) AS `total_fail_call`, DATE_FORMAT(`callstart`, "%Y%m%d") AS `unique_date`, DATE_FORMAT(`callstart`, "%Y-%m-%d") AS `calldate` FROM `cdrs_staging` WHERE `end_stamp`>= "',last_execution_time ,'" AND `end_stamp` < "',end_date,'" GROUP BY `accountid`, `country_id`, `reseller_id`, `unique_date`) ON DUPLICATE KEY UPDATE `billseconds`=(`billseconds` + VALUES(`billseconds`)), `debit`=(`debit` + VALUES(`debit`)), `cost`=(`cost` + VALUES(`cost`)), `total_answered_call`=(`total_answered_call` + VALUES(`total_answered_call`)), `total_fail_call`=(`total_fail_call` + VALUES(`total_fail_call`)), `calldate`=`calldate`, `mcd`=GREATEST(VALUES(`mcd`), `mcd`), `total_calls`=(`total_calls` + VALUES(`total_calls`))'),'cdrs_day_by_summary','insert');

        -- BELOW Entries are not inserted into debug_info table as well as not showing any errors into errors table.

        insert into debug_info(`query`,`type`,`operation`) values (concat("WHERE `end_stamp` >= '",last_execution_time,"' AND `end_stamp` < '",end_date,"' UNIQUEID : cdrs : ",cdrs_uniqueid," : reseller_cdrs : ",reseller_cdrs_uniqueid," : cdrs_staging : ",cdrs_staging_uniqueid," : COUNT : cdrs_staging : ",cdrs_staging_count," cdrs count : ",cdrs_count," : reseller_cdrs : ",reseller_cdrs),'cdrs_staging,cdrs','select');
        UPDATE `d` SET `last_execution_date`=`end_date` WHERE `name`='get_cdr_records';
          -- BELOW Entry also not inserted into debug_info table as well as not showing any errors into errors table.

        insert into debug_info(`query`,`type`) values(concat('update `reports_process_list` SET `last_execution_date`="',end_date,'" where `name`="get_cdr_records"'),'reports_process_list','update');
        IF errorCode != '00000' THEN
            INSERT INTO `errors` (code, message, query_type, record_id, on_db, on_table) VALUES (errorCode, errorMessage, 'insert', NEW.uniqueid, 'astpp', 'f');
        END IF;
    END$$
DELIMITER ;

Anyone have suggestion to debug this procedure or modification into above procedure to get inserted information into debug_info table.

Best Answer

If the insert / update was successful in your local system and not in another server, then there is no point in discussing that it had run in your local system. You have to check using the server in which it fails.

If the insert / update was successful in the same server but in a mysql client and fails through stored procedure, you shall try this.

get diagnostics condition number

has to be run for each of your insert / update and then you can check value of errorCode.

Please refer document for

GET DIAGNOSTICS CONDITION

And also for

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION

Related Solutions

Mysql – Complex thesql selection

I tried to produce the same ordering and having some trouble but here are two different queries you can start with:

SELECT
    booki_id,bet_id,bettype,
    group_concat(odds order by bet) betodds,line
FROM
    bettable
GROUP BY
    booki_id,bet_id,bettype,line
;

SELECT * FROM
(
    SELECT
        booki_id,bet_id,bettype,
        group_concat(odds order by bet) betodds,line
    FROM
        bettable
    GROUP BY
        booki_id,bet_id,bettype,line
) AA
ORDER BY betodds
;

I also created sample data to test it with, and here it is:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.5.12 MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

MySQL (Current test) :: use test
Database changed
MySQL (Current test) :: DROP TABLE IF EXISTS bettable;
Query OK, 0 rows affected (0.03 sec)

MySQL (Current test) :: CREATE TABLE bettable
    -> (
    ->     id       INT NOT NULL AUTO_INCREMENT,
    ->     booki_id INT,
    ->     bet_id   INT,
    ->     bet      INT,
    ->     bettype  VARCHAR(10),
    ->     line     FLOAT,
    ->     odds     FLOAT,
    ->     PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.05 sec)

MySQL (Current test) :: INSERT INTO bettable (booki_id,bet_id,bet,bettype,line,odds) VALUES
    -> (123,321,1,'3way',0.0,2.3),
    -> (123,321,2,'3way',0.0,3.4),
    -> (123,322,1,'3way',0.0,1.1),
    -> (123,322,2,'3way',0.0,7.4),
    -> (123,323,1,'3way',0.0,1.3),
    -> (123,323,2,'3way',0.0,9.4);
Query OK, 6 rows affected (0.02 sec)
Records: 6  Duplicates: 0  Warnings: 0

MySQL (Current test) :: SELECT
    ->     booki_id,bet_id,bettype,
    ->     group_concat(odds order by bet) betodds,line
    -> FROM
    ->     bettable
    -> GROUP BY
    ->     booki_id,bet_id,bettype,line
    -> ;
+----------+--------+---------+---------+------+
| booki_id | bet_id | bettype | betodds | line |
+----------+--------+---------+---------+------+
|      123 |    321 | 3way    | 2.3,3.4 |    0 |
|      123 |    322 | 3way    | 1.1,7.4 |    0 |
|      123 |    323 | 3way    | 1.3,9.4 |    0 |
+----------+--------+---------+---------+------+
3 rows in set (0.01 sec)

MySQL (Current test) :: SELECT * FROM
    -> (
    ->     SELECT
    ->         booki_id,bet_id,bettype,
    ->         group_concat(odds order by bet) betodds,line
    ->     FROM
    ->         bettable
    ->     GROUP BY
    ->         booki_id,bet_id,bettype,line
    -> ) AA
    -> ORDER BY betodds
    -> ;
+----------+--------+---------+---------+------+
| booki_id | bet_id | bettype | betodds | line |
+----------+--------+---------+---------+------+
|      123 |    322 | 3way    | 1.1,7.4 |    0 |
|      123 |    323 | 3way    | 1.3,9.4 |    0 |
|      123 |    321 | 3way    | 2.3,3.4 |    0 |
+----------+--------+---------+---------+------+
3 rows in set (0.02 sec)

MySQL (Current test) ::

You will have to play with the order of the whole rows from here.

Give it a Try !!!

Debugging Stored Procedure in Visual Studio – Object Reference Not Set Error

Do you have DELIMITER statements around the CREATE...?

Does VS have any way to debug Stored Routines? (I suspect not.)

The failure to do it this way could cause trouble later:

START TRANSACTION;
SELECT ... from users ... FOR UPDATE;
various SETs
UPDATE ...;
COMMIT;

Another consideration... An "audit trail" may be better achieved via a TRIGGER wherein you can compare NEW. to OLD..

Best Answer

Related Solutions

Mysql – Complex thesql selection

Debugging Stored Procedure in Visual Studio – Object Reference Not Set Error

Related Question