Solved

I have MySQL Proc that does not work yet it's code outside the structure does!?

Posted on 2016-08-17
14
122 Views
Last Modified: 2016-08-24
I'm trying to populate a 'census' table with info about imports.  A couple fields I'm looking for are the int'l rate plan code and an indicator if the current plan is the best (=0), needs to downgrade (-1) or upgrade (+1).

The code words fine outside as a batch job just by changing some DELCAREd vars to local vars.
When inside it fails to update the 'census' table.

I did discover that a two table update (of one table) will certainly fail if you JOIN using USING().  It requires an ON expression.  But that's beside the point / not my issue now.

Code of PROC and same code below it outside.  The latter variant works, does update `zModem_Config`.
DROP PROCEDURE `Census_BestPlanCalc` ;


DELIMITER &&
CREATE PROCEDURE `Census_BestPlanCalc`()
BEGIN
  
  DECLARE var_latest_inv                               INTEGER UNSIGNED ;
  DECLARE posting_period                               VARCHAR(50)      ;
  
  # ===================================================================================
  
  DROP TABLE IF EXISTS `Modem_Usages` ;             # Temp table
  DROP TABLE IF EXISTS `Calcd_Charge` ;             # Temp table
  DROP TABLE IF EXISTS `Enable_Best_Calc` ;         # Temp table
  DROP TABLE IF EXISTS `CurrentBest_Comparison` ;
  
  # --------------------------------------
  SELECT invoice_id, posting_period INTO var_latest_inv, posting_period
  FROM   Invoice
  WHERE  date_charges_import_completed = (SELECT MAX(date_charges_import_completed) FROM Invoice );
  
  # --------------------------------------
  CREATE TABLE `Modem_Usages` AS
  SELECT modem_config_id
       , intl_rate_plan_id
       , intl_data_usage
  FROM   ModemConfig mc
  JOIN   InvoicedModem im
  USING  (wireless_no)
  WHERE  im.invoice_id = var_latest_inv ;
  
  # --------------------------------------
  CREATE TABLE `Calcd_Charge` AS
  SELECT mu.modem_config_id
       , mu.intl_rate_plan_id
       , mu.intl_data_usage
       , irp.intlplan_code
       , irp.intlplan_limit_MB AS `current_threshold`
       , CASE WHEN mu.intl_data_usage/1000 <= intlplan_limit_MB THEN intlplan_rate_charge 
                                                        ELSE intlplan_rate_charge + (intl_data_usage/1000 - intlplan_limit_MB) * intlplan_overage_rate_per_MB END AS `calcd_charge`
  FROM IntlRatePlan   irp
  JOIN `Modem_Usages` mu
  USING (intl_rate_plan_id)  ;
  
  # --------------------------------------
  
  CREATE TABLE `Enable_Best_Calc` AS
  SELECT  mu.modem_config_id
       ,  mu.intl_rate_plan_id
       , irp.intlplan_code
       ,   i.intlplan_limit_MB  AS 'OtherMBs'
       ,   i.intlplan_code      AS 'AvailPlans'
       ,  CASE WHEN mu.intl_data_usage/1000 <= i.intlplan_limit_MB THEN i.intlplan_rate_charge 
                                                        ELSE i.intlplan_rate_charge + (mu.intl_data_usage/1000 - i.intlplan_limit_MB) * i.intlplan_overage_rate_per_MB END AS `calcd_charge`
  FROM   `Modem_Usages`  mu
  JOIN   `IntlRatePlan` irp
  USING  (intl_rate_plan_id)
  JOIN   `IntlRatePlan` i
  WHERE  (i.intlplan_limit_MB <> irp.intlplan_limit_MB AND SUBSTRING(i.intlplan_code,1,4) = SUBSTRING(irp.intlplan_code,1,4)
    OR    i.intlplan_limit_MB =  irp.intlplan_limit_MB AND i.intlplan_code = irp.intlplan_code)
   AND   i.date_no_more_irp_contracts > CURRENT_DATE() 
   AND   i.intlplan_code NOT IN ('Unassigned', 'Suspended', 'NoRatePlan')
   ORDER BY 1, 4;
  
  # --------------------------------------

  
  CREATE TABLE `CurrentBest_Comparison` AS
  SELECT  a.modem_config_id
        , ROUND(a.calcd_charge) AS current_charge
        , a.OtherMBs            AS current_limit
        , b.LowestCharge
        , c.AvailPlans        AS preferred_plan_code
        , c.OtherMBs          AS preferred_limit
        , SIGN(CAST(c.OtherMBs AS  SIGNED) - CAST(a.OtherMBs AS  SIGNED)) as preferred_plan_tristate
  FROM    `Enable_Best_Calc` a
  JOIN    (SELECT modem_config_id,  MIN(ROUND(calcd_charge,4)) AS `LowestCharge` FROM `Enable_Best_Calc` GROUP BY 1) b
    ON    a.modem_config_id = b.modem_config_id 
  JOIN    `Enable_Best_Calc` c
    ON    a.modem_config_id = c.modem_config_id  AND ROUND(c.calcd_charge,4) = ROUND(b.LowestCharge,4)
  WHERE   a.intlplan_code = a.AvailPlans ;
  
  # ======================================
  
  UPDATE `zModem_Census`          a
  JOIN   `CurrentBest_Comparison` b
    ON   a.modem_config_id = b.modem_config_id
  SET    a.preferred_plan_code     = b.preferred_plan_code
       , a.preferred_plan_tristate = b.preferred_plan_tristate
  WHERE  a.posting_period = posting_period ;
  
  COMMIT ;
  
  
  DROP TABLE IF EXISTS `Modem_Usages` ;             # Temp table
  DROP TABLE IF EXISTS `Calcd_Charge` ;             # Temp table
  DROP TABLE IF EXISTS `Enable_Best_Calc` ;         # Temp table
  #DROP TABLE IF EXISTS `CurrentBest_Comparison` ;   # Temp table
  
END&&

DELIMITER ;

select * from `CurrentBest_Comparison` ;

  DROP TABLE IF EXISTS `Modem_Usages` ;             # Temp table
  DROP TABLE IF EXISTS `Calcd_Charge` ;             # Temp table
  DROP TABLE IF EXISTS `Enable_Best_Calc` ;         # Temp table
  DROP TABLE IF EXISTS `CurrentBest_Comparison` ;
  
  # --------------------------------------
  SELECT invoice_id, posting_period INTO @var_latest_inv, @posting_period
  FROM   Invoice
  WHERE  date_charges_import_completed = (SELECT MAX(date_charges_import_completed) FROM Invoice );
  
  # --------------------------------------
  CREATE TABLE `Modem_Usages` AS
  SELECT modem_config_id
       , intl_rate_plan_id
       , intl_data_usage
  FROM   ModemConfig mc
  JOIN   InvoicedModem im
  USING  (wireless_no)
  WHERE  im.invoice_id = @var_latest_inv ;
  
  # --------------------------------------
  CREATE TABLE `Calcd_Charge` AS
  SELECT mu.modem_config_id
       , mu.intl_rate_plan_id
       , mu.intl_data_usage
       , irp.intlplan_code
       , irp.intlplan_limit_MB AS `current_threshold`
       , CASE WHEN mu.intl_data_usage/1000 <= intlplan_limit_MB THEN intlplan_rate_charge 
                                                        ELSE intlplan_rate_charge + (intl_data_usage/1000 - intlplan_limit_MB) * intlplan_overage_rate_per_MB END AS `calcd_charge`
  FROM IntlRatePlan   irp
  JOIN `Modem_Usages` mu
  USING (intl_rate_plan_id)  ;
  
  # --------------------------------------
  
  CREATE TABLE `Enable_Best_Calc` AS
  SELECT  mu.modem_config_id
       ,  mu.intl_rate_plan_id
       , irp.intlplan_code
       ,   i.intlplan_limit_MB  AS 'OtherMBs'
       ,   i.intlplan_code      AS 'AvailPlans'
       ,  CASE WHEN mu.intl_data_usage/1000 <= i.intlplan_limit_MB THEN i.intlplan_rate_charge 
                                                        ELSE i.intlplan_rate_charge + (mu.intl_data_usage/1000 - i.intlplan_limit_MB) * i.intlplan_overage_rate_per_MB END AS `calcd_charge`
  FROM   `Modem_Usages`  mu
  JOIN   `IntlRatePlan` irp
  USING  (intl_rate_plan_id)
  JOIN   `IntlRatePlan` i
  WHERE  (i.intlplan_limit_MB <> irp.intlplan_limit_MB AND SUBSTRING(i.intlplan_code,1,4) = SUBSTRING(irp.intlplan_code,1,4)
    OR    i.intlplan_limit_MB =  irp.intlplan_limit_MB AND i.intlplan_code = irp.intlplan_code)
   AND   i.date_no_more_irp_contracts > CURRENT_DATE() 
   AND   i.intlplan_code NOT IN ('Unassigned', 'Suspended', 'NoRatePlan')
   ORDER BY 1, 4;
  
  # --------------------------------------

  
  CREATE TABLE `CurrentBest_Comparison` AS
  SELECT  a.modem_config_id
        , ROUND(a.calcd_charge) AS current_charge
        , a.OtherMBs            AS current_limit
        , b.LowestCharge
        , c.AvailPlans        AS preferred_plan_code
        , c.OtherMBs          AS preferred_limit
        , SIGN(CAST(c.OtherMBs AS  SIGNED) - CAST(a.OtherMBs AS  SIGNED)) as preferred_plan_tristate
  FROM    `Enable_Best_Calc` a
  JOIN    (SELECT modem_config_id,  MIN(ROUND(calcd_charge,4)) AS `LowestCharge` FROM `Enable_Best_Calc` GROUP BY 1) b
    ON    a.modem_config_id = b.modem_config_id 
  JOIN    `Enable_Best_Calc` c
    ON    a.modem_config_id = c.modem_config_id  AND ROUND(c.calcd_charge,4) = ROUND(b.LowestCharge,4)
  WHERE   a.intlplan_code = a.AvailPlans ;
  
  # ======================================
  
  UPDATE `zModem_Census`          a
  JOIN   `CurrentBest_Comparison` b
    ON   a.modem_config_id = b.modem_config_id
  SET    a.preferred_plan_code     = b.preferred_plan_code
       , a.preferred_plan_tristate = b.preferred_plan_tristate
  WHERE  a.posting_period = @posting_period ;
  
  COMMIT ;

Open in new window

Any suggestions?

Thanks!
0
Comment
Question by:Ralph
  • 8
  • 5
14 Comments
 

Author Comment

by:Ralph
ID: 41760438
A little more info:
CREATE TABLE `zModem_Census` (
  `modem_census_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `date_of_census` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `posting_period` varchar(50) DEFAULT NULL,
  `modem_config_id` int(10) unsigned NOT NULL,
  `grandfathered_into_system` tinyint(3) unsigned DEFAULT '0',
  `upon_import` tinyint(3) unsigned DEFAULT '0',
  `have_all_manuf_fields` tinyint(3) unsigned DEFAULT '0',
  `have_all_shipping_fields` tinyint(3) unsigned DEFAULT '0',
  `have_billable_status_set` tinyint(3) unsigned DEFAULT '0',
  `have_utilization_set` tinyint(3) unsigned DEFAULT '0',
  `have_plan_rates_set` tinyint(3) unsigned DEFAULT '0',
  `intlplancode_before_invoice` varchar(50) DEFAULT NULL,
  `intlplancode_after_invoice` varchar(50) DEFAULT NULL,
  `domplancode_before_invoice` varchar(50) DEFAULT NULL,
  `domplancode_after_invoice` varchar(50) DEFAULT NULL,
  `preferred_plan_code` varchar(50) DEFAULT NULL,
  `preferred_plan_tristate` tinyint(4) DEFAULT '0',
  `import_changed_manuf_data` tinyint(3) unsigned DEFAULT '0',
  `import_changed_shipping_data` tinyint(3) unsigned DEFAULT '0',
  `import_changed_plans_data` tinyint(3) unsigned DEFAULT '0',
  `import_changed_billing_status` tinyint(3) unsigned DEFAULT '0',
  `have_visited_since_change` tinyint(3) unsigned DEFAULT '0',
  `posting_period_of_first_invoice` varchar(50) DEFAULT '1',
  `posting_period_of_last_invoice` varchar(50) DEFAULT '1',
  PRIMARY KEY (`modem_census_id`),
  KEY `fk_ModemCensus_ModemConfig` (`modem_config_id`),
  CONSTRAINT `fk_ModemCensus_ModemConfig` FOREIGN KEY (`modem_config_id`) REFERENCES `ModemConfig` (`modem_config_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=51780 DEFAULT CHARSET=latin1 COMMENT='Workflow or trigger populate metrics';

Open in new window

The pen-ultimate table.
0
 
LVL 33

Expert Comment

by:ste5an
ID: 41760939
Just for curiosity: Where do you use ZModem these days?
0
 

Author Comment

by:Ralph
ID: 41761316
Ahhh, this is not to be confused with the Zmodem protocol!
This provides post-import metrics on modem billing plans.

At home I was hoping I'd just left off some commits in the Proc, but alas, they are there for the update.
This is BAFFLING me.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 41762047
hmmm. you are CREATING the procedure, but are you also EXECUTING it?
I see only the CREATE part ...
0
 

Author Comment

by:Ralph
ID: 41763245
Hi Guy,

Ah, I don't know what you expect to see.
I call it and zModem_Census is not updated.

Why would it work as a batch job but not in a Proc?

Ralph
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 41764105
how do you exactly execute the procedure?
if your batch job is only creating the procedure, it will do that.
you should create it once, and run it with call procname
http://dev.mysql.com/doc/refman/5.7/en/call.html
0
 

Author Comment

by:Ralph
ID: 41764615
At the end of PROC ImportCellModemCharges_ATnT I have:
# _____________________________________________________
  IF take_notice THEN
    INSERT INTO Import_Details (`identifier`, `level`, `thing`, `what`) VALUES (param_invoice_identifier, 'High', 'CALLing Census_GenerateMetrics()', 'Census_GenerateMetrics() calls Census_BestPlanCalc() and Census_HaveFieldsSet().' ) ;
    COMMIT ;
    IF debug THEN SELECT 'CALLing Census_GenerateMetrics(posting_period), which calls Census_BestPlanCalc() and Census_HaveFieldsSet().', SUBSTRING(param_invoice_identifier,1,8) AS 'posting_period' ; END IF ;
  END IF ; # ___________________________________________________________________________#
  
  
  CALL Generate_Metrics(SUBSTRING(param_invoice_identifier,1,8))  ;
  

Open in new window

Inside Generate_Metrics() I have:
CREATE PROCEDURE Generate_Metrics(IN posting_period1 VARCHAR(50))
BEGIN
  
  DECLARE can_up_plan_now, can_dn_plan_now, nbr_modems INTEGER  ;
  DECLARE posting_period2, posting_period3             VARCHAR(50) ;
  DECLARE var_date_of_census1, var_date_of_census2, var_date_of_census3   TIMESTAMP ;
  DECLARE modems_upgradeable1, modems_upgradeable2, modems_upgradeable3   INTEGER  ;
  DECLARE visited_up1, visited_up2, visited_up3                           INTEGER  ;
  DECLARE modems_dngradeable1, modems_dngradeable2, modems_dngradeable3   INTEGER  ;
  DECLARE visited_dn1, visited_dn2, visited_dn3                           INTEGER  ;
  
  
  # =========================================================================
  
  DROP TABLE IF EXISTS census_up_grade_1mo ;  # Temp table
  DROP TABLE IF EXISTS census_up_grade_2mo ;  # Temp table
  DROP TABLE IF EXISTS census_up_grade_3mo ;  # Temp table
  DROP TABLE IF EXISTS census_dn_grade_1mo ;  # Temp table
  DROP TABLE IF EXISTS census_dn_grade_2mo ;  # Temp table
  DROP TABLE IF EXISTS census_dn_grade_3mo ;  # Temp table
  DROP TABLE IF EXISTS yUpgradeable_3mo ;
  DROP TABLE IF EXISTS yDowngradeable_3mo ;
  DROP TABLE IF EXISTS yImport_Changes ;
  DROP TABLE IF EXISTS yMissing_Fields ;
  DROP TABLE IF EXISTS yMissing_Plan_Data ;
  
  # =========================================================================
  # This call to Cencus_BestPlanCalc MUST come before Census_HaveFieldsSet
  
  CALL Census_BestPlanCalc()  ;
  
  CALL Census_HaveFieldsSet() ;
  ...
...

Open in new window

The code runs, as the table `CurrentBest_Comparison`gets created every time I do an import of charges.
I also run it manually with a CALL to the procedure, and still not change to `zModem_Census`; UNLESSI run just the code with 'local' variables outside of the PROC.
SELECT invoice_id, posting_period INTO @var_latest_inv, @var_posting_period
  FROM   Invoice
  WHERE  date_charges_import_completed = (SELECT MAX(date_charges_import_completed) FROM Invoice );

Open in new window

Odd...
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 41764805
seems so far all nice.
so, the only explanation that I would go for is that still there is something going on like "wrong database selected "...
from what you explain, the code is correct, and you execute all correctly.
if this last short into the blue is not the issue, I would try to get some other pair of eyes locally on your server (gigs?) to find out the issue
0
 

Author Comment

by:Ralph
ID: 41764833
I was thinking I might have to look into the explain plan. To find out what explain plan was used in a procedure though, I don't know how to do that.
Something new to find out.
0
 

Author Comment

by:Ralph
ID: 41766069
Nope, can't EXPLAIN an UPDATE command.  Also, apologies for "it's" rather than "its" in the title.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 41766378
the explain plan (if possible) would only tell you how the query was executed, but not inside which db or server...
0
 

Author Comment

by:Ralph
ID: 41767452
Unfortunately always same DB same server.  That's certain.

-- -- -- -- --
More Info:

Seems the UPDATE is flaky, it won't always work outside of the PROC either.

18:26:34      UPDATE  `zModem_Census`          a        ,  `CurrentBest_Comparison` b   SET     a.preferred_plan_code     = b.preferred_plan_code        ,  a.preferred_plan_tristate = b.preferred_plan_tristate   WHERE   a.posting_period  = 'Tue Aug 23 2016 @ 18:22:50'     AND   a.modem_config_id = b.modem_config_id      
0 row(s) affected Rows matched: 1566  Changed: 0  Warnings: 0      0.187 sec

When it does work... see preferred_plan_code and preferred_plan_tristate.
This is from the zModem_Census table.
When it does work
In my second post on this issue on 2016-08-17 at 18:31:04ID: 41760438, the image there is of the last working table before the update.
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 41768281
>0 row(s) affected Rows matched: 1566  Changed: 0  Warnings: 0      0.187 sec
this message tells you can if finds 1566 rows that would be updated, but 0 rows that actually get a value being CHANGED
at least, that is what I interprete into some posts around.

which means that maybe you have already the same data in both tables for the 2 columns being updated.
can you test that using a SELECT to check if the values are really different?
1
 

Author Closing Comment

by:Ralph
ID: 41769572
I Just ran it again, this time (I'm using MySQL Workbench) I 'Reconnect(ed) to server' first, so maybe it's that and maybe since I'm now not only doing this calculation upon a posting_period, (when a bill is imported), but also using other criteria, so MAYBE it's the other code changes, but [b]IT WORKED[/b]!

I really don't like these 'now you see it, now you don't' scenarios.
Nor do I like MySQL Workbench using cached data rather than letting me know it lost its connection.

Thanks for bearing with me on this Guy.
And if I'm unlucky and this issue rears its head again, BEWARE!

Ciao for now,
Ralph
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…

861 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now