Get error when backup database has a view table "create view"

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.

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.

And the patch attachement :-S

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.

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.

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

How does the patch fail?

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.

Subscribe

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.

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 (....

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.

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

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.

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

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

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.

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

Reviewed and tested. Works great!

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.

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

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

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!

Here it is for 7.x as well.

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

Would be cool if someone could review it.

Not running anything on D7 yet, sorry...

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.

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

Cheers

subisub

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

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).

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

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

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 !

I tried to backup my database that have a view table but I got the error when backup.
If I delete the view table, It works fine.
Anyone please fix this feature, thanks.

Anyone please fix this feature...

Maybe a good start is trying to fix it yourself? I would look at the patch that was submitted here http://drupal.org/node/447522#comment-3932404 and see if you can work those changes into D7?

Cheers

Here is a fresh patch for the 7.x The patch in #25 is merely a copy-paste from the 6.x patch, it's not a surprise that it does not work.
This patch is useful when you don"t use the mysqldump to make the dumps, that way it's bogus and i don't see an evident way to fix it, so i added it as a node to the checkbox text.

Here are the new patches for 7.x-2.x and 6.x-2.x, please review.
At the 7.x, compared to #39, i fixed a typo in the checkbox text and in a comment.
At the 6.x, compared to #19, I added the ability to exclude views from the UI.

Hi Aron -

Very nice, with a needed tweak and a suggested one. (v7.x patch in #40).

Needed: _get_view_create_sql() in destinations.db.mysql.inc mistakenly fails to lowercase the key to $create['create view']. The lowercase key is caused by the _get_tables() function. EDIT --> I may be hallucinating, but my production version seems to return the capitalized, vs. the local. I've just added a test for each.

Suggested: in _get_view_create_sql() I suggest the revision seen below. This is not a patch because I'm totally unsure that this code is generally compatible. I do know it seems to fix my issue with a civicrm database beautifully. My revisions take aim at a thoroughly annoying problem with the SHOW CREATE VIEW [...]: as queried through backup_migrate on my machine, the result does not include the critical algorithm and definer definitions.

I have also included code to fix the absolute PLAGUE that DEFINER can inflict when you move to a new database with a new user. CURRENT_USER seems to have an almost magical quality in this respect. I'm unsure if I've missed a critical objection to this approach. But it seems solid to me.

Final note: The mysql user I'm using has every privilege there is, so while it is in my nature to side with those who say things like "It's a permission issue because it IS a permission issue! Get a real host and learn something new, don't try to weasel around a gate you don't understand!", that doesn't seem to be the real issue here. Which is too bad.

Cheers.

  /**
   * Get the sql for the structure of the given table.
   */
  function _get_view_create_sql($view) {
    $out = "";
    // Switch SQL mode to get rid of "CREATE ALGORITHM..." what requires more permissions + troubles with the DEFINER user
    $sql_mode = $this->query("SELECT @@SESSION.sql_mode")->fetchField();
    $this->query("SET sql_mode = 'ANSI'");
    $result = $this->query("SHOW CREATE VIEW `" . $view['name'] . "`", array(), array('fetch' => PDO::FETCH_ASSOC));
    $this->query("SET SQL_mode = :mode", array(':mode' => $sql_mode));
    foreach ($result as $create) {
      $out .= "DROP VIEW IF EXISTS `". $view['name'] ."`;\n";
      $out .= "SET sql_mode = 'ANSI';\n";
      $create_view = '';
      if (!empty($create['create view'])) {
        $create_view = $create['create view'];      
      }
      elseif (!empty($create['Create View'])) {
        $create_view = $create['Create View'];      
      }
      if (strstr($create_view, "CREATE VIEW")) {
        // Missing critical arguments due to permission boondoggle.
        // The definitions added here are believed to be the most compatible compromise.
        // Note that changing the DEFINER to CURRENT_USER is quite useful when migrating db environments.
        $create_view = str_replace("CREATE VIEW", "CREATE ALGORITHM=UNDEFINED DEFINER=CURRENT_USER SQL SECURITY DEFINER VIEW", $create_view);
      }
      else {
        // Changing the DEFINER to CURRENT_USER is quite useful when migrating db environments.
       $matches = array();
       preg_match("/\bDEFINER=(.*) \b/i", $string, $matches);
       $create_view = str_replace($matches[1], 'CURRENT_USER', $create_view);
      }
      $out .= strtr($create_view, "\n", " ") . ";\n";
      $out .= "SET sql_mode = '$sql_mode';\n";
    }
    return $out;
  }

Tested #40 patch for 6.x and it seems to work.

These patches seem to work great. Thanks Aron!