?
Solved

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

Posted on 2016-08-17
14
Medium Priority
?
161 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 35

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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 143

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 143

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
 
LVL 143

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 143

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 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 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

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

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

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

719 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