Ralph
asked on
MySQL Procedure compile completes w/o error but also w/o ANY notification
I even have SQL_MODE = 'STRICT_TRANS_TABLES' set, hoping I'd find some cause.
I can't find any reference to this in my search so far.
Any suggestions on what to look for?
I'm using MySQL Workbench 6.3 on Windows 7. Seems a bit more buggy than the 6.2 version I used to use.
Thanks!
I can't find any reference to this in my search so far.
DROP PROCEDURE ImportCellModemPlans_ATnT ;
SET @OLD_SQL_MODE = @@SQL_MODE , SQL_MODE = 'STRICT_TRANS_TABLES' ;
DELIMITER &&
CREATE PROCEDURE `ImportCellModemPlans_ATnT`(IN param_invoice_id INTEGER, IN param_invoice_identifier VARCHAR(50), IN take_notice TINYINT )
BEGIN
... 1300 lines later...
END &&
DELIMITER ;
SET SQL_MODE = @OLD_SQL_MODE ;
Any suggestions on what to look for?
I'm using MySQL Workbench 6.3 on Windows 7. Seems a bit more buggy than the 6.2 version I used to use.
Thanks!
There is no enough information on this. What notification do you expect?
ASKER
I'm using MySQL Workbench, but holds true for command line too.
Normally when I compile a PROC, (I include DROP theproc in line w/ its definition), I get:
With this one large proc I get:
Normally when I compile a PROC, (I include DROP theproc in line w/ its definition), I get:
3 159 16:28:40 DROP PROCEDURE `Census_ImportPlanChange` 0 row(s) affected 0.000 sec
3 160 16:28:40 CREATE PROCEDURE `Census_ImportPlanChange`(IN Pre_OR_Post VARCHAR(50), IN posting_period VARCHAR(50))
BEGIN
# -------------------------------------------------------------------------------------
DROP TABLE IF EXISTS yPlan_Data_Change ;
# =====================================================================================
IF UPPER(Pre_OR_Post) = 'PRE' THEN
UPDATE zModem_Census AS z
JOIN ModemConfig AS mc
ON mc.modem_config_id = z.modem_config_id
AND z.posting_period = posting_period
SET z.intlplancode_before_invoice = mc.intlplan_code
, z.domplancode_before_invoice = mc.domplan_code ;
COMMIT ;
END IF ;
# -------------------------------------------------------------------------------------
IF UPPER(Pre_OR_Post) = 'POST' THEN
UPDATE zModem_Census AS z
JOIN ModemConfig AS mc
ON mc.modem_config_id = z.modem_config_id
AND z.posting_period = posting_period
SET z.intlplancode_after_invoice = mc.intlplan_code
, z.domplancode_after_invoice = mc.domplan_code ;
COMMIT ;
# ===================================================================================
UPDATE zModem_Census a
JOIN zModem_Census b
ON a.posting_period = b.posting_period
AND a.modem_config_id = b.modem_config_id
SET a.import_changed_plans_data = (b.intlplancode_before_invoice != b.intlplancode_after_invoice) OR (b.domplancode_before_invoice != b.domplancode_after_invoice)
WHERE a.posting_period = posting_period ;
COMMIT ;
END IF ;
END 0 row(s) affected 0.000 sec
With this one large proc I get:
3 155 16:28:27 DROP PROCEDURE ImportCellModemPlans_ATnT 0 row(s) affected 0.000 sec
3 155 16:28:27
ASKER
BTW, just changed that PROC so I hadn't yet cleaned it up and removed superfluous statement.
Try this script:
DELIMITER $$
DROP PROCEDURE IF EXISTS `Census_ImportPlanChange` $$
CREATE DEFINER=`root`@`%` PROCEDURE `Census_ImportPlanChange`(
IN Pre_OR_Post VARCHAR(50),
IN posting_period VARCHAR(50)
)
BEGIN
IF UPPER(Pre_OR_Post) = 'PRE' THEN
UPDATE zModem_Census AS z
JOIN ModemConfig AS mc
ON mc.modem_config_id = z.modem_config_id
AND z.posting_period = posting_period
SET z.intlplancode_before_invoice = mc.intlplan_code
, z.domplancode_before_invoice = mc.domplan_code ;
COMMIT ;
END IF ;
IF UPPER(Pre_OR_Post) = 'POST' THEN
UPDATE zModem_Census AS z
JOIN ModemConfig AS mc
ON mc.modem_config_id = z.modem_config_id
AND z.posting_period = posting_period
SET z.intlplancode_after_invoice = mc.intlplan_code
, z.domplancode_after_invoice = mc.domplan_code ;
COMMIT ;
UPDATE zModem_Census a
JOIN zModem_Census b
ON a.posting_period = b.posting_period
AND a.modem_config_id = b.modem_config_id
SET a.import_changed_plans_data = (b.intlplancode_before_invoice != b.intlplancode_after_invoice) OR (b.domplancode_before_invoice != b.domplancode_after_invoice)
WHERE a.posting_period = posting_period ;
COMMIT ;
END IF ;
END $$
DELIMITER ;
Maybe you can delete the definer part.
ASKER
Hi Zberteoc,
I don't have root perms on this remote server, so I can't try that.
Putting in my credentials gets me the same as w/o. No feedback at all that compilation was successful.
It IS successful though.
At EO Compile I put show errors and show warnings commands.
No warnings, no errors.
This didn't used to happen. It started rather recently when I made the large Proc even larger to its current size of 1353 lines.
It's importing data into 4 tables with a lot of logic to assure no redundancies, and a lot of code for detailed logging and optional debugging select to console evidence of progress.
I guess I can live with the status quo as long as it still compiles w/o errors...
Thanks!
I don't have root perms on this remote server, so I can't try that.
Putting in my credentials gets me the same as w/o. No feedback at all that compilation was successful.
It IS successful though.
At EO Compile I put show errors and show warnings commands.
No warnings, no errors.
This didn't used to happen. It started rather recently when I made the large Proc even larger to its current size of 1353 lines.
It's importing data into 4 tables with a lot of logic to assure no redundancies, and a lot of code for detailed logging and optional debugging select to console evidence of progress.
I guess I can live with the status quo as long as it still compiles w/o errors...
Thanks!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.