CODEWITHSUNDEEP
mysqlsqlwindowmariadb
Andrej Piatnica
Andrej Piatnica

Reputation: 41

mysqldump does not keep view format / structure

When restoring DB backup on 10.3.13-MariaDB all works fine except of views. Well, these are exported too and works fine after restore, however formatting is corrupted - whole sql view is on single line. When checking dump file it shows:

/*!50001 DROP TABLE IF EXISTS `vw_sample_view`*/; 
/*!50001 DROP VIEW IF EXISTS `vw_sample_view`*/;
/*!50001 SET @saved_cs_client          = @@character_set_client */;
/*!50001 SET @saved_cs_results         = @@character_set_results */;
/*!50001 SET @saved_col_connection     = @@collation_connection */;
/*!50001 SET character_set_client      = utf8mb4 */;
/*!50001 SET character_set_results     = utf8mb4 */;
/*!50001 SET collation_connection      = utf8mb4_general_ci */;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`sample_user`@`%` SQL SECURITY DEFINER */
/*!50001 VIEW `vw_sample_view` AS select `cm`.`name` AS `market`,`u`.`name` AS `user`,`u`.`logged_at` AS `logged_at` from (`users` `u` join `core_market` `cm` on(`cm`.`id` = `u`.`id_market`)) where `u`.`id_market` is not null order by `u`.`logged_at` desc */;
/*!50001 SET character_set_client      = @saved_cs_client */;
/*!50001 SET character_set_results     = @saved_cs_results */;
/*!50001 SET collation_connection      = @saved_col_connection */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

Some of my sql views are fairly complex (and are used in other selects) and this format makes them impossible to edit without spending time formatting them first. Keeping them on separate place is not the workaround I would like to go with.

A similar issue has been posted already - Backing Up Views with Mysql Dump but the solution uses xargs which works for Linux only (and not sure this is solution for my problem). I am using Windows (I know there is something like Gow, but would like to skip 3rd party installations)

I have tried mysqldump with / without --routines flag, also tried unlikely flags such as --opt, --compact and many others - all with the same result. Used different users to make sure it's not an security issue (both root and sample_user).

As an SQL client I use HeidiSQL and export from it creates exactly what I need:

-- Dumping structure for view my_db.vw_sample_view
-- Removing temporary table and create final VIEW structure
DROP TABLE IF EXISTS `vw_sample_view`;
CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `vw_sample_view` AS SELECT
    cm.name AS market,
    u.name AS `user`,
    logged_at
FROM users u
INNER JOIN core_market cm ON cm.id = u.id_market
WHERE id_market IS NOT NULL
ORDER BY logged_at DESC ;

No idea whether this HeidiSQL uses mysqldump on background (and I am just missing proper parameter) but that's exactly output I want to get.

Update My goal is to have a scheduled backup on daily basis (which will not ruin SQL views). Heidi works perfect for one-time manual backups, however it can't be scheduled for reccurrent tasks (https://www.heidisql.com/forum.php?t=23870)

Upvotes: 2

Views: 781

Answers (1)

slaakso
slaakso

Reputation: 9050

The corrected answer:

MySQL/MariaDB stored the original source for the view creation to the ".frm" file (under "source"). Looks like HeidiSQL reads this first and if the server is not configured to allow this, it will use the modified one liner from the INFORMATION_SCHEMA.

There is no option to make mysqldump to use the .frm->source version.

Upvotes: 1

Related Questions