3

I'm using Workbench 5.2 from my production DB machine (Ubuntu Linux). I've been trying to export. First try.. I had the entire schema selected including all tables and views in the right hand window.

When I imported this data into my test machine all the views came in as tables. Obviously not what I want because they don't return any data now.... and it's now a table.

But this is where I'm confused. I can see how to export just the tables. But how do I get the views exported and into the test machine?

1 Answer 1

1

There's probably an error occurring somewhere in the process, because that would cause exactly what you're seeing.

Here's why:

When you define a view, the views or tables referenced by that view must already exist:

mysql> CREATE VIEW broken_view AS SELECT some_column FROM nonexistent_table;
ERROR 1146 (42S02): Table 'test.nonexistent_table' doesn't exist

For this reason, mysqldump generates a dump file that includes statements defining all of the views as equivalent(ish) tables, then goes back one by one and drops the tables and creates the views. This avoids the problem that would occur if any view referenced another view, or referenced a table that hadn't been restored yet (tables are restored in alphabetical order).

The error could be while generating the dump file -- if any of your existing views are invalid -- for example, they reference tables or columns that don't exist, or reference DEFINER users that don't exist or don't have proper permissions, or if you don't have sufficient privileges -- their definitions can't be extracted, because SHOW CREATE VIEW only works on valid views.

If, by way of illustration, you create a view against a table, then drop the table, you'll find that you can no longer SHOW CREATE VIEW against that view.

If your views reference tables in a different schema that aren't on the destination server, you'd have a similar problem, because their definitions won't be accepted.

Being someone with a strong distaste for almost all graphical tools, I can't stand Workbench, but I believe it uses mysqldump under the hood, so this is likely to be the nature of the problem.

The export file should be human-readable. If, at the end of the file, there are view definitions, then a problem is occurring when you're restoring the file. If not, then you've got a problem preventing the export from finishing, and Workbench may not be showing you the error... but an incomplete export (or import) would be exactly the explanation for views being defined as tables on the destination server.

1
  • You know I did notice an error when I was importing. I don't remember the number but it was saying it didn't know root@%. So as I test I decided to manually copy some of the views and create new ones. I was able to create a new view DEFINER = root@% but when one view referenced this view I got the error again. If I changed the % to 'localhost' then it worked fine and I was able to create the view.
    – Matt Winer
    May 30, 2013 at 21:01

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.