mysqldump gives corrupt create views

Question

When I do a mysqldump in MySql the views are not being dumped correctly.

When I do :

mysqldump --opt --user=root --routines --max_allowed_packet=1024M my_database > my_file.sql

I get this in my my_file.sql:

    CREATE VIEW my_view AS 
    SELECT
    1 AS id,
    1 AS full_name

In my MySql server the original create view statement looks like this :

    CREATE VIEW my_view AS 
    SELECT
    t1.id AS id,
    CONCAT(t2.name,' ',t1.name) AS full_name
    FROM t1 
    LEFT JOIN t2 ON t1.t2_id = t2.id
    ORDER BY 
    t1.name,
    t2.name

I've tried all the differenet options for mysqldump but nithing makes it create the views properly in the dumped sql file.

I can't imagine any reason why it would do this. Are you sure that's the actual view in the database you're dumping? — Barmar, Mar 28 '23 at 15:25
It looks like: [mysqldump generates incorrect create view queries](https://dba.stackexchange.com/questions/210423/mysqldump-generates-incorrect-create-view-queries) or [Mysqldump broken CREATE VIEW export](https://stackoverflow.com/questions/34659617/mysqldump-broken-create-view-export) — Luuk, Mar 28 '23 at 15:53

score 1 · Answer 1 · answered Mar 28 '23 at 16:16

This is normal mysqldump behavior. The view with 1 AS <column> for everything is a temporary view. The real view is lower in the dump output.

Demo: I created a table t and a view v in my test schema:

mysql> use test

mysql> create table t ( id int primary key );

mysql> create view v as select * from t;

Then I dump the test schema and check the output. I have omitted a bunch of the mysqldump output for this example.

-- MySQL dump 10.13  Distrib 8.0.32, for macos13 (arm64)

--
-- Table structure for table `t`
--

DROP TABLE IF EXISTS `t`;
CREATE TABLE `t` (
  `id` int NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Temporary view structure for view `v`
--

DROP TABLE IF EXISTS `v`;
/*!50001 DROP VIEW IF EXISTS `v`*/;
/*!50001 CREATE VIEW `v` AS SELECT 
 1 AS `id`*/;

--
-- Final view structure for view `v`
--

/*!50001 DROP VIEW IF EXISTS `v`*/;

/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `v` AS select `t`.`id` AS `id` from `t` */;


-- Dump completed on 2023-03-28  9:12:32

You can see the comments indicating first the temporary view, then the real view.

mysqldump gives corrupt create views

1 Answers1