Views not correctly exported to dump file
Posted by:
JK Heydt
Date: April 11, 2022 06:46AM
When I create a Self-Contained File using Data Export to a dump file, views are defined like this in the dump file insead of the original format below. If
I use a batch file to do nightly backups using mysqldump.exe
--- DUMP FILE SECTION FOR VIEW---
CREATE
ALGORITHM = UNDEFINED
DEFINER = `root`@`localhost`
SQL SECURITY DEFINER
VIEW `hto_fcd`.`vw_charges` AS
SELECT
1 AS `BillingAcctNo`,
1 AS `CompanyNo`,
1 AS `StandingCd`,
1 AS `CustomerTypeCd`,
1 AS `InvoiceGroupCd`,
1 AS `RecordNo`,
1 AS `ChargeBatchNo`,
1 AS `AcctNo`,
1 AS `HaulCd`,
1 AS `PlanNo`,
1 AS `PrechargeNo`,
1 AS `TicketStopNo`,
1 AS `BillingDesc`,
1 AS `AutoChargeNo`,
1 AS `EffectiveDate`,
1 AS `Units`,
1 AS `UnitRate`,
1 AS `TotalCharge`,
1 AS `ClearedBit`,
1 AS `InvoiceNo`,
1 AS `HideBit`
--- END DUMP FILE ---
--- ORIGINAL VIEW FORMAT ---
CREATE
ALGORITHM = UNDEFINED
DEFINER = `root`@`localhost`
SQL SECURITY DEFINER
VIEW `vw_charges` AS
SELECT
`a`.`BillingAcctNo` AS `BillingAcctNo`,
`account`.`CompanyNo` AS `CompanyNo`,
`account`.`StandingCd` AS `StandingCd`,
`account`.`CustomerTypeCd` AS `CustomerTypeCd`,
`account`.`InvoiceGroupCd` AS `InvoiceGroupCd`,
`charge`.`RecordNo` AS `RecordNo`,
`charge`.`ChargeBatchNo` AS `ChargeBatchNo`,
`charge`.`AcctNo` AS `AcctNo`,
`charge`.`HaulCd` AS `HaulCd`,
`charge`.`PlanNo` AS `PlanNo`,
`charge`.`PrechargeNo` AS `PrechargeNo`,
`charge`.`TicketStopNo` AS `TicketStopNo`,
`charge`.`BillingDesc` AS `BillingDesc`,
`charge`.`AutoChargeNo` AS `AutoChargeNo`,
`charge`.`EffectiveDate` AS `EffectiveDate`,
`charge`.`Units` AS `Units`,
`charge`.`UnitRate` AS `UnitRate`,
`charge`.`TotalCharge` AS `TotalCharge`,
`charge`.`ClearedBit` AS `ClearedBit`,
`charge`.`InvoiceNo` AS `InvoiceNo`,
`charge`.`HideBit` AS `HideBit`
FROM
(((((`charge`
JOIN `pre_charge` ON (((`pre_charge`.`RecordNo` = `charge`.`PrechargeNo`)
AND (`pre_charge`.`HaulCd` = `charge`.`HaulCd`)
AND (`pre_charge`.`HideBit` = 0))))
LEFT JOIN `haul_plan` ON (((`haul_plan`.`RecordNo` = `charge`.`PlanNo`)
AND (`charge`.`HaulCd` = 'HAUL'))))
LEFT JOIN `route_plan` ON (((`route_plan`.`RecordNo` = `charge`.`PlanNo`)
AND (`charge`.`HaulCd` = 'ROUTE'))))
JOIN (SELECT
`account`.`RecordNo` AS `RecordNo`,
(CASE
WHEN
((`account`.`ParentAcctNo` = 0)
OR (`account`.`BillToSelfBit` = 1))
THEN
`account`.`RecordNo`
ELSE `account`.`ParentAcctNo`
END) AS `BillingAcctNo`
FROM
`account`) `a` ON ((`a`.`RecordNo` = `charge`.`AcctNo`)))
LEFT JOIN `account` ON ((`account`.`RecordNo` = `a`.`BillingAcctNo`)))
WHERE
((`charge`.`HideBit` = 0)
AND (IFNULL(`haul_plan`.`HideBit`, 0) = 0)
AND (IFNULL(`route_plan`.`HideBit`, 0) = 0))
--- END ORIGINAL FORMAT --
I can copy and paste between databases but I have dozens of views. Is there a way to export properly in a dump file?