2

When I execute the following:

mysqldump -u root -n mydatabase > mydatabase.sql

The tables are all dumped without the database name, but the views oddly get the database name prepended:

/*!50001 DROP TABLE IF EXISTS `vfiscalperiodcombobox`*/;
/*!50001 DROP VIEW IF EXISTS `vfiscalperiodcombobox`*/;
/*!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      = utf8 */;
/*!50001 SET character_set_results     = utf8 */;
/*!50001 SET collation_connection      = utf8_general_ci */;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `mydatabase`.`vfiscalperiodcombobox` AS select `mydatabase`.`fiscalperiod`.`name` AS `text`,`mydatabase`.`fiscalperiod`.`id` AS `value` from `mydatabase`.`fiscalperiod` order by `mydatabase`.`fiscalperiod`.`start` */;
/*!50001 SET character_set_client      = @saved_cs_client */;
/*!50001 SET character_set_results     = @saved_cs_results */;
/*!50001 SET collation_connection      = @saved_col_connection */;

Because of this I cannot import this file into another database (without manually removing the database names from the file).

This happens with the following MariaDB version on Windows:

mysql  Ver 15.1 Distrib 10.1.9-MariaDB, for Win32 (AMD64)

It does NOT happen with the following version on Debian:

mysql  Ver 15.1 Distrib 10.0.28-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

Is this simply a bug? Or am I doing something wrong? Or is there a flag with which I can force mysqldump to never prepend the database name?

1
  • Did you ever figure out a solution to this? I'm having the same problem.
    – Jeff
    Apr 17, 2020 at 13:02

2 Answers 2

1

The user who asked on How to prevent MariaDB mysqldump from prepending database name in CREATE VIEW?, reported this issue on mariadb's jira

It seems to have been fixed on Versions 10.3.35, 10.4.25, 10.5.16, 10.6.8, 10.7.4

Another workaround - also seen on that jira ticket - is to monckeypatch the SQL dump. Just to exemplify:

mysqldump --no-data DATABASE_NAME \
| sed "s/\`DATABASE_NAME\`\.//g" > DATABASE_NAME.sql 
0

They are not bug, but a feature of mysql. Just make sure you are using root or admin account.

2
  • 1
    Could you add a link to the piece of MySQL documentation where this is further explained?
    – joanolo
    Feb 13, 2017 at 8:00
  • Why is this "feature" implemented in one version but not the other? Jun 5, 2019 at 4:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.