Can't generate 'create view' sql

#1

Posted by ronan on April 28, 2009 at 9:15pm

Status:

active

» postponed (maintainer needs more info)

Backup and migrate doesn't support MySQL views. Are there any Drupal modules that use them? Drupal is supposed to work with MySQL 4.1 so this should not be an issue with Drupal data, but if there are any contributed modules using them then I'll have to either support views or warn the user about not supporting them.

If you are backup up data from tools other than Drupal you would be safest to use a more complete tool like phpMyAdmin.

Login or register to post comments

#2

Posted by yaoweizhen on April 29, 2009 at 4:27am

I used this contrib module http://drupal.org/project/uc_views
I have created issue for this module http://drupal.org/node/448038

Login or register to post comments

#3

Posted by madsph on May 25, 2009 at 10:26am

Status:

postponed (maintainer needs more info)

» closed (fixed)

Since many of the uc_views users are reporting this problem, I have created a patch for backup_migrate that allows db views to be backed up and restored.

the patch is made against the head branch.

hope you can use it.

By the way I noticed that you pass $exclude_tables and $nodata_tables as parameters for _backup_migrate_dump_tables and _backup_migrate_get_dump_sql but you don't seem to use the values since they are overwritten within the functions. Therefor I have avoided changing the signature of those functions, even though I have a $exclude_views equivalent to $exclude_tables.

Login or register to post comments

#4

Posted by madsph on May 25, 2009 at 10:27am

Status:

closed (fixed)

» patch (to be ported)

And the patch attachement :-S

Attachment	Size
backup_migrate-447522.patch	5.59 KB

Login or register to post comments

#5

Posted by ronan on May 27, 2009 at 1:54am

Thanks for the patch. I'll review and port this as soon as possible. I may not add it to the 1.x branch as I'm trying to make that branch obsolete and am approaching a stable release for the 2.x branch.

Login or register to post comments

#6

Posted by madsph on June 18, 2009 at 10:08am

Today I had the chance to try out my patch in an installation without db-views and I realized that I am instantiating the default values of the lists containing the views and the view names wrong.

So rather than setting
$out = "";
it really should be
$out = array();
in both

function _backup_migrate_get_views()
  $out = "";
  ...

and

function _backup_migrate_get_view_names()
  $out = "";
  ...

Sorry for not testing properly the first time.

Login or register to post comments

#7

Posted by SocialNicheGuru on July 23, 2009 at 4:26pm

I LOVE this module. It has saved my behind more than once.

I ran into this problem recently where a view table is not created. If you use ubercart at all, I think you will run into this issue.

The patch doesn't seem to work it all fails.

A

Login or register to post comments

#8

Posted by madsph on July 27, 2009 at 6:56am

How does the patch fail?

Login or register to post comments

#9

Posted by victoria_b on December 25, 2009 at 3:41am

Ronan,

As a user of uc_views may I enquire what the status of this patch is? Was it still not working the last time it was submitted or has it been accepted and rolled into the 2.x branch of Backup and Migrate?

Thank you.

Login or register to post comments

#10

Posted by daveparrish on January 10, 2010 at 7:29am

Subscribe

Login or register to post comments

#11

Posted by SocialNicheGuru on April 15, 2010 at 10:18pm

Version:

6.x-1.2

» 6.x-2.x-dev

This failed on the new 6.x.dev version.

it takes me forever to upload a database when I forget that I need to upload uc views, uc_order, and uc_products separately.

Login or register to post comments

#12

Posted by lyricnz on October 28, 2010 at 9:48am

Please add support for views to backup_migrate. More and more modules are using views these days, and this prevents backup_migrate working on those installs (generates broken SQL).

DROP TABLE IF EXISTS `uc_order_products_pair_vw`;
;
INSERT INTO `uc_order_products_pair_vw` VALUES (....

Login or register to post comments

#13

Posted by Korchkidu on December 14, 2010 at 4:49pm

Hi,

Views support in backup and migrate is also critical for us. We added a new module that uses views and now, all our backup and deployment process is broken... It is unfortunate but we will have to do without backup and migrate (which is a great module anyway^^) until this bug is fixed...

Best regards.

Login or register to post comments

#14

Posted by Aron Novak on December 17, 2010 at 10:12am

Component:	Miscellaneous	» Code
Assigned to:	Anonymous	» Aron Novak
Status:	patch (to be ported)	» needs review

Here is the ported version of #4 to the 2.x-dev.

Attachment	Size
447522-view-support.patch	2.46 KB

Login or register to post comments

#15

Posted by Aron Novak on December 17, 2010 at 10:13am

The only important comment is that the users should know that the mysql user has to have enough permissions to actually view the statement for creating the view.

Login or register to post comments

#16

Posted by Bartezz on January 7, 2011 at 4:35pm

Having the same issues using uc_views.module
Subscribing...

Login or register to post comments

#17

Posted by Bartezz on January 10, 2011 at 8:55am

Status:

needs review

» reviewed & tested by the community

Seems like the patch in #14 works, just applied, created a quick backup (of a ~20MB database) which has 3 mysql views (uc_order_products_pair_vw, uc_order_products_qty_vw, uc_order_products_user_vw) and restored the database to this backup without issues.

Cheers

Login or register to post comments

#18

Posted by Aron Novak on January 11, 2011 at 1:35pm

Status:

reviewed & tested by the community

» needs review

I needed to rewrite the patch a little bit as for some more strict shared hosting, CREATE ALGORITHM, DEFINER, etc are causing issues.
Now i switch to ANSI mode for exporting the VIEW what produces simply CREATE VIEW command. I did not want to "parse" the output of the mysql SHOW CREATE VIEW query. Let me know how it works.

Attachment	Size
447522-view-support-1.patch	2.83 KB

Login or register to post comments

#19

Posted by Aron Novak on January 11, 2011 at 3:02pm

The previous patch makes backup and migrate generating dumps with sql syntax error.

Attachment	Size
447522-view-support-2.patch	2.85 KB

Login or register to post comments

#20

Posted by Korchkidu on January 11, 2011 at 3:47pm

Status:

needs review

» reviewed & tested by the community

Reviewed and tested. Works great!

Login or register to post comments

#21

Posted by Aron Novak on January 25, 2011 at 5:30pm

I'm happy to work more on this to get committed if there is a reason for not committing it like that. If there is no reason, then please commit it.

Login or register to post comments

#22

Posted by ronan on January 27, 2011 at 4:42am

Status:

reviewed & tested by the community

» patch (to be ported)

Patch looks good. Could you port it to 7 as I'd like to keep feature parity between the 2 versions.

Login or register to post comments

#23

Posted by Fannon on January 27, 2011 at 1:33pm

Tested #19
I'm not sure if this solved the Problem: The Backup creation took now WAY longer than usual: in 125 716.79 ms.
Without patch it is: 4 288.43 ms.

And i don't find the DROP TABLE IF EXISTS `uc_order_products_qty_vw`; command in the exported SQL anymore, which previously was exported incorrect.

But haven't imported the SQL.

Greets,
Simon

Login or register to post comments

#24

Posted by Bartezz on January 31, 2011 at 1:50pm

Tested #19 and it works great on shared. Had some problems with #14 (/* SQL Error (1227): Access denied; you need the SUPER privilege for this operation */). Can't really say I've noticed that much difference in processing time as #23 mentioned.

Thanx for the patch, hope it'll get committed soon!

Login or register to post comments

#25

Posted by Aron Novak on February 3, 2011 at 4:31pm

Version:	6.x-2.x-dev	» 7.x-2.x-dev
Status:	patch (to be ported)	» needs review

Here it is for 7.x as well.

Attachment	Size
447522_view_support-25.patch	2.83 KB

Login or register to post comments

#26

Posted by ronan on February 3, 2011 at 5:26pm

Awesome, thanks! I'll try and get this committed ASAP.

Login or register to post comments

#27

Posted by Aron Novak on February 28, 2011 at 1:22pm

Would be cool if someone could review it.

Login or register to post comments

#28

Posted by Bartezz on February 28, 2011 at 11:06pm

Not running anything on D7 yet, sorry...

Login or register to post comments

#29

Posted by fmesco on April 8, 2011 at 12:42am

Sorry this is a late reply to this issue, but I'm running into this problem and am looking for a patch for Drupal 6. There's mention of D6 in this thread but the patches all look like they are for D7. Could you please clarify for me? Thanks.

Login or register to post comments

#30

Posted by Bartezz on April 8, 2011 at 9:32am

@fmesco try #19 worked perfectly for my D6 setup...

Cheers

Login or register to post comments

#31

Posted by Chemtox on April 13, 2011 at 12:21am

subisub

Login or register to post comments

#32

Posted by cristian.stoica on April 20, 2011 at 2:20pm

@Aron thanks for the patch at #19, also confirming that is working great for 6.20 and latest dev of BM.

Login or register to post comments

#33

Posted by millenniumtree on April 20, 2011 at 4:54pm

The CiviCRM "module" also creates a MySQL view.

If you're moving a CiviCRM site to another server or restoring to a blank database, you'll get an error similar to this:
Table 'civicrm.civicrm_domain_view' doesn't exist

If you're just trying to restore the backup to an existing database, the error will be different.
This could be quite an eye-opener the first time your site goes down and you need to restore from a scheduled backup!!

Before Patch #19 (Drupal 6.20)

[web@gami public_html]$ drush bam-restore db manual SITE-2011-04-20T11-14-55.mysql

WD php: Query was empty       [error]
query: ; in /home/somewhere/public_html/includes/database.mysql.inc on line 136.
WD php: Duplicate entry '1' for key 1       [error]
query: INSERT INTO `civicrm_domain_view` VALUES ('1','SITE','etc', 'etc'...

After Patch #19

[web@gami public_html]$ drush bam-restore db manual SITE-2011-04-20T11-34-23.mysql
Default Database restored from Manual Backups Directory file SITE-2011-04-20T11-34-23.mysql in 172074.33 ms. 58731 SQL commands executed.       [success]

Yay! Thanks Aron.

This doesn't fix EXISTING backups, of course. If you need to load an existing backup, you'll have to manually remove the lines that reference that table/view and try again, then re-create the MySQL views (if you're restoring to an empty database).

Login or register to post comments

#34

Posted by Aron Novak on April 21, 2011 at 6:26am

Status:

needs review

» reviewed & tested by the community

Thanks cristian.stoica and millenniumtree for testing.
After #32 and #33, maybe it's RTBC, isn't it?

Login or register to post comments

#35

Posted by Starminder on May 14, 2011 at 2:46pm

Status:

reviewed & tested by the community

» active

For #25 patch, it's one of my first patches, I didn't get any errors there. But, when I tried to do a 'quick manual' backup, I got this after the patch:

Fatal error: Call to undefined function db_fetch_array() in /home/hoslot5/public_html/sites/all/modules/backup_migrate/includes/destinations.db.mysql.inc on line 208

I've got 3 uc related views leftover that I can't seem to delete, that are now keeping me from being able to backup also.

Edit: Getting this on clean sites - reverting

Login or register to post comments

#36

Posted by drupalworlds on September 18, 2011 at 1:21pm

Version:

7.x-2.x-dev

» 6.x-2.4

I found that after enabling the backup and migrate module, the module folder was crashed.
So I returned the files back to a same named folder as I suppose it was caused by my mistake.
After then, however, there was some problem for the backup of the uc_view tables.

Exactly, as the view tables could not be backed up properly.
//--------------------------------------------------------------------------------
DROP TABLE IF EXISTS `uc_order_products_pair_vw`;
;
DROP TABLE IF EXISTS `uc_order_products_qty_vw`;
;
INSERT INTO `uc_order_products_qty_vw` VALUES ('39','23','1.0000','23','1','1'),('44','1','1.0000','1','1','1'),('45','6','3.5000','21','7','1'),('46','1','1.0000','1','1','1');
DROP TABLE IF EXISTS `uc_order_products_user_vw`;
;
INSERT INTO `uc_order_products_user_vw` VALUES ('39','1','16','1.0000','16','1','1'),('44','1','1','1.0000','1','1','1'),('45','1','6','3.5000','21','7','1'),('46','1','1','1.0000','1','1','1');
//-----------------------------------------------------------------------------------------------

When the folder was crashed NORMALLY, it was correctly backed up as follows:
//---------------------------------------------------------------------------------------------
DROP VIEW IF EXISTS `uc_order_products_pair_vw`;
SET sql_mode = 'ANSI';
CREATE VIEW "uc_order_products_pair_vw" AS select "op1"."nid" AS "nid","op2"."nid" AS "pair_nid",sum("op2"."qty") AS "pair_sum_qty",count("op2"."nid") AS "order_count" from ("uc_order_products" "op1" join "uc_order_products" "op2") where (("op1"."order_id" = "op2"."order_id") and ("op1"."nid" <> "op2"."nid")) group by "op1"."nid","op2"."nid" order by "op1"."nid",count("op2"."nid") desc,sum("op2"."qty") desc;
SET sql_mode = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
DROP VIEW IF EXISTS `uc_order_products_qty_vw`;
SET sql_mode = 'ANSI';
CREATE VIEW "uc_order_products_qty_vw" AS select "op"."nid" AS "nid",count("op"."nid") AS "order_count",avg("op"."qty") AS "avg_qty",sum("op"."qty") AS "sum_qty",max("op"."qty") AS "max_qty",min("op"."qty") AS "min_qty" from "uc_order_products" "op" group by "op"."nid" order by "op"."nid";
SET sql_mode = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
DROP VIEW IF EXISTS `uc_order_products_user_vw`;
SET sql_mode = 'ANSI';
CREATE VIEW "uc_order_products_user_vw" AS select "op"."nid" AS "nid","o"."uid" AS "uid",count("o"."order_id") AS "order_count",avg("op"."qty") AS "avg_qty",sum("op"."qty") AS "sum_qty",max("op"."qty") AS "max_qty",min("op"."qty") AS "min_qty" from ("uc_order_products" "op" join "uc_orders" "o") where ("op"."order_id" = "o"."order_id") group by "o"."uid","op"."nid" order by "o"."uid","op"."nid";
SET sql_mode = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
//----------------------------------------------------------------------------------------------------

Therefore, you should leave them crashed !

Login or register to post comments

Can't generate 'create view' sql

Jump to:

Issue Summary

Comments

#1

#2

#3

#4

#5

#6

#7

#8

#9

#10

#11

#12

#13

#14

#15

#16

#17

#18

#19

#20

#21

#22

#23

#24

#25

#26

#27

#28

#29

#30

#31

#32

#33

#34

#35

#36

Project:	Backup and Migrate
Version:	6.x-2.4
Component:	Code
Category:	bug report
Priority:	normal
Assigned:	Aron Novak
Status:	active