MySQL Forums
Forum List  »  Data Recovery

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?

Options: ReplyQuote


Subject
Views
Written By
Posted
Views not correctly exported to dump file
158
April 11, 2022 06:46AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.