Bug: Export Table Data Also Exports View Data

[PeterVermont]

Views were not listed among the choices of tables to be exported (which is fine for me). However, in addition to the requested tables SQLYog also exported all of my views. Some of my views are very expensive to calculate and all of the data is redundant with the table data so it is not useful to export view data.

I believe I am using 5.23 beta (can't check now since it is busy dumping my view!) but this behavior was also true with 5.22a.

Thank you for providing such a great product!

Peter Andrews

[peterlaursen]

We recently introduced a new export option.  there are now 2:

the 'DUMP' option

the 'export data' option

Try using the DUMP option.  And read the changlogs/notes for 5.22:  

— Added 'export data as SQL' as an option. Note: this feature is NOT intended for backup purposes as only INSERT (DATA) statements and a very simple CREATE statement will be created.

I do not think it should export views unless you explicitly export a a view!  We will dixuss on Monday! 

[Marcwolf]

I have the same issue.

I have now spent about 8 hours TRYING to backup a database from one locations and to import it into another location.

The speed that SQLYog can do the migrate takes too long.. Far faster to dump the DB, zip the SQL file, and then reimport at other end.

However I found that VIEWS are being exported as DATA!!!.. Not a Stored SQL Statement that gathers information and displays it.

Wonderful Bug.. I would have believed that even a first year DBA would have understood the nature of a view compared to a table.

If there a Fix for this Yet!!! (Using SQLYog Ent 6.14)

Most Annoyed!~!!

Dave (Developer who works on an hourly pay rate and cannot charge customer for this!!!!)

[peterlaursen]

I do not not think these are the same issues!

@PV:

1) There is an option to export VIEWs or not (a checkbox). In 6.2 there will be a tree where you can 'pick' individual VIEWs.

@Marc:

You are using the term 'migrating', so I think you are using the Migration Tool? It is by design and documented as well that the Migration Tool will migrate VIEWs on theto TABLEs at the . Migration Tool is primarily designed for transferring data from non-MySQL databases to MySQL and a lot of users requested the option to migrate VIEWs onto TABLES on (it was not originally included in version 5.0 but added later following those requests). The code describing a VIEW is not the same on all those systems (what every one-year DBA should know too! 🙂 ) and we clearly documented that we do not attempt to 'translate' code from one database system to another. To transfer data from one MySQL server to another MySQL server there are several other options than the Migration Tool . For instance you can use structure sync and next sync data.

[peterlaursen]

but this faq

http://webyog.com/faq/content/17/78/en/wha…ation-tool.html

.. seems not to have been updated with the information on VIEWs!

[Marcwolf]

Ok..

Lets take this a step at a time.

If I open up SQLYog and select a DB. Right click and then select Backup Database as SQL Dump

Select Structure and Data

Select Create DB, Foreign Key, Drop Table, and Bulk Insert optin

Because this is a MySQL5 DB and I want to make sure that I get the Views and SP structures as well I make sure that ALL

5.x Objects are set.

Select ALL Tables.

Export to a SQL file.

Now – I understand that I should beable to then take this SQL file to another machine, or the same machine and run it against an existing database with the Restore from SQL Dump.

The end result will (or should) be that the DB will be loaded with the new structure as well as the data.

This is not happening. I am getting an error becase the SQL Dump is treating a VIEW like a TABLE and exporting the VIEW ike a TABLE i.e. with INSERT statements.

Regardless of whether I should be using the Migration Tool, The Structure Syncronisation tool, or the Data Syncronisation tool… The act of DUMP and the RESTORING a Database should result in a CLEAN Import..

With this issue it now means that many of our MySQL5 backups are invalid and this causes us great concern for ourselves and our customers.

Dave

[Mahesh]

HI,

We dont have any similar issue reported earlier ,

This is strange behaviour ! And we are not able to reproduce the situation either,

Can you please give the information listed below ,

1. Attach Exported Dump

2. What Error message it shows while import ? and

3. What type of connection you used (http or ssh) ?

Thanks

[Marcwolf]

This is the database.

Please look at Sales_Order_Srch

You will see that it is referenced both as a table and a vierw.. It is infact only a view!!!!

When importing I get the “Server has Gone Away” error.

I am using a standard http (or tcpip) connection. This occurs when the DB is either on localhost or remote.

Take Care

Dave

p.s. MySQL Version 5.0.45

Mahesh wrote on Jan 9 2008, 05:00 AM:

HI,

We dont have any similar issue reported earlier ,

This is strange behaviour ! And we are not able to reproduce the situation either,

Can you please give the information listed below ,

1. Attach Exported Dump

2. What Error message it shows while import ? and

3. What type of connection you used (http or ssh) ?

Thanks

[Manoj]

First about the error:-

You have a very large BULK INSRT. 99.9 % certain that a single insert statement exceeds the max_allowed_packet setting in server configuration!

You can solve this by following any of these two options,

1)While exporting you can choose not to use BULK inserts as well as in SQLyog “tools .. preferences ('general' tab)” you can define the BULK size. BULK INSERTS means that more than one row of data are included with every INSERT statement.

A FAQ here:http://webyog.com/faq/content/24/101/en/ab…-and-bulks.html

2)In the MySQL configuration file (my.cnf/my,ini) you enter a line in the [mysqld] section reading like

max_allowed_packet=100M

(or whatever size you want. Default is as low as 1M with recent MySQL versions. A BLOB can be up to 64M in size).

Now about views as table:-

Every view is first created as a table while importing and will be removed when the actual view is created(No DATA is imported, only the structure!), you can see this in the SQL dump. This is required because a view can refer to another view which is not yet created!.

[peterlaursen]

Just let me add that the 'workflow' of creating a table in the first place that is later replaced with a view is also used by the (autoritative) 'mysqldump' program. It is the ONLY way to ensure that VIEWs based on VIEWs will always import!

[Author]

Posts