0

I have a mysql database on a shared web host. Some of the tables are rather large, and our nightly dump process was getting killed. So I made a script that queried the database for all the table names , and dumped them individually, concatenating them all into a single file. 

mysql -uusername -ppassword -hhostname dbname < <(echo 'SHOW TABLES') | xargs -I TableName sh -c 'nice -19 mysqldump --opt -uusername -ppassword -hhostname dbname TableName >> /path/to/dump.sql'

I have the problem now where one of the view definitions (as I understand, MySQL has idiosyncratic view creation statements in its dump file) causes the error 

ERROR 1146 (42S02) at line 5182: Table 'dbname.vView' doesn't exist

I looked up line 5182 in the dump file and it's this first line, which begins the creation of the view:

/*!50001 DROP TABLE `vView`*/;
/*!50001 DROP VIEW IF EXISTS `vView`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */

Yet for whatever reason, other views in the dump file have

/*!50001 DROP TABLE IF EXISTS `vOtherView`*/;

And they get created okay.

Following this question, I tried mysql -f when importing the dump, but mysql still halted on the error.

How can I get the dump file to have a properly formatted view creation statement (outside of doing some post-processing of the dump file)? Or how can I get mysql to forge ahead regardless?

Community
  • 1
  • 1
user151841
  • 17,377
  • 29
  • 109
  • 171

1 Answers1

1

Never understood what the problem was, but my workaround was to first dump just the table definitions with --no-data, and then use the xargs to individually dump each table's data with --no-create-table.

user151841
  • 17,377
  • 29
  • 109
  • 171