Export Views

[DMealy]

When using “Export Database as SQL Statements” on a 5.017 MySQL database, the views show up in the SQL as CREATE TABLE statements, not as CREATE VIEWS. How do I get the views/procs to export? (other than to use the MySQL Query Browser, which generates the correct SQL).

The same problem occurs using the Scheduled Backup Wizard.

There are a couple of other odd things – the views, stored procedures and functions are not listed in the selection list in 'Export Database…', so you can't choose which views/procs to export. And the check boxes for the 5.x Objects are only available when all tables in the database are selected – grayed-out otherwise.

[peterlaursen]

Which version of SQLyog are you using? You must use 5.0 or higher.

Please read through the COMPLETE .sql-file !! First a Table is created, then a View, next the Table is dropped. Maybe Ritesh can explain the exact reasons for this coding. But it works perfectly here! Try yourself! edit the file .. replace the DB-name in 'use' statement and import it. Does it not import correctly?

But this

Quote:

And the check boxes for the 5.x Objects are only available when all tables in the database are selected – grayed-out otherwise.

— is as it must be! SP's, Functions and Views 'belong to' the Database, Triggers 'belong to' a Table. However .. yes .. if all tables used for defining a VIEW are there then it COULD be done that those VIEWs that are sufficiently defined were exported. But it is not like that as of now. Would be quite complicated coding and would violate the MySQL OBJECTS concept to some degree. OBJECT (except for TRIGGERS) 'belong to' a Database – not a set of Tables!

[peterlaursen]

a further explanation:

You define a VIEW as

Code:

CREATE VIEW .. as SELECT …

the SELECT statenent may use UNIONs and JOINs etc. and thus several (dozens, hundreds!) of tables may be involved in defining a VIEW. When you are exporting only a subset of a database it is not at all uncomplicated to test whether the tables needed for building a VIEW are there!

So VIEWS, SP's and FUNCTIONS are exported with the complete database only (if you choose).

[DMealy]

Thanks for your reply – I resolved the problem.

The 'Export' was generating an error when trying to create a table out of a particular view (the view used the same column aliased with different names: …a.column1 AS OneThing, a.column1 AS Another…) The exporter found two columns with the same name (it doesn't use the alias, evidently) when creating a table and stops. Changed the view, and the export now works.

[peterlaursen]

hmmm .. there might be some ALIAS issue with VIEWs.

Let Ritesh research into it next week.

[Ritesh]

I have asked my developer to work on it. I will reply back by Monday.

[peterlaursen]

I must admit that I too think that this looks pretty strange!

I have a music database as one big TABLE 'mp3_files'.

And VIEWs 'title', 'album', 'genre' and 'artist'.

Now let us look at 'artist'. The .sql-file does all this with it.

Is it not highly inefficient? There may be dozens/hundreds of view in a complicated DB-setup! Those that use VIEWs often do so EXTENSIVELY!

Code:

DROP TABLE IF EXISTS `artist`;

DROP VIEW IF EXISTS `artist`;

CREATE TABLE `artist` ……

drop view if exists `artist`;

drop table if exists `artist`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `artist` AS select ….

Why is it necessary to create the tables at all? It is a waste of server resources (and of time), I think!

[peterlaursen]

@ritesh:

When is monday?

Monday February 19th 2007 ? 😛

I checked SQLyog 5.1 beta1. Seems to work fine now. No table is created and then dropped. However a small cosmetics:

The

Code:

drop view if exists …

is repeated twice for each view.

[Ritesh]

The extra CREATE TABLE statement are correct. This is done because a VIEW might reference another VIEW which does not yet exist. We have to create temporary table to overcome this issue.

Even MySQLDUMP follows the same.

While fixing this we introduced another bug in v5.1 BETA 1 where there are no extra CREATE TABLE. This is wrong.

The extra CREATE VIEWS that you see are temporary and will be deleted while you import the full dump.

The issue reported by DMealy is actually no BUG and it is correct.

[Author]

Posts