8

My database backup through mysqldump get created with wrong source SQL query for all database views. All source columns are always "1". I can export view definition using mysql workbench and the same db user just fine.

Here is example of view definition produced by mysqldump 

DROP TABLE IF EXISTS `my_table_name`;
/*!50001 DROP VIEW IF EXISTS `my_table_name`*/;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
/*!50001 CREATE VIEW `my_table_name` AS SELECT 
 1 AS `id`,
 1 AS `col1`,
 1 AS `col2`,
 1 AS `col3`,
 1 AS `col4`,
 1 AS `col5`*/;
SET character_set_client = @saved_cs_client;

The same thing happens on Percona Server 5.6.12 and 5.6.25

user2237890
  • 91
  • 3

2 Answers2

0

The generated view is wrong because it is a temporary view with a value of '1' in all columns.

When mysqldump, the view is generated as a temporary view and replaced when creating the view normally.

Maybe you didn't generate a view for the same reason as me, so only a temporary view seems to have been created.

In my opinion mysql 5.7.31 didn`t automatically convert the DB name and userID into the source of the view during mysqldump. I recommend you. Make a separate schema and data file when backing up the DB to copy.

mysqldump -u USERID -pPASSWORD DBNAME --no-data --triggers --routines > filePath\filename.sql
mysqldump -u USERID -pPASSWORD DBNAME --no-create-info > filePath\filename.sql

Modify name and userID on the schema backup file.

After that, recovering the two backup files will recover the view normally.

Emma
  • 27,428
  • 11
  • 44
  • 69
wkawu
  • 1
  • 1
0

I had the same issue. When I was doing the initial dump I was getting an unrelated error about some procedure:

mysqldump: Couldn't execute 'SHOW CREATE PROCEDURE `unrelated_table_name`': Undeclared variable: some_variable name (1327)

I assumed the mysqldump command completed, and was just showing errors. However this error prevented the rest of the mysqldump script to complete.

Long story short, I removed this procedure and now the mysqldump command was able to complete. Now at the end of the dump file I had the correct view drop and create sql.

I hope this helps.

Joel
  • 181
  • 1
  • 2
  • 8