Ubercart Views breaks database backups

uc_order_products_pair_vw is a view defined by uc_views, so maybe this issue belongs in its queue.

Now, what are you doing again? I've never heard of CREATE ALGORITHM before.

How are you importing the database?

Is this related to #448038: mysql view isn't standard for Drupal?

IM GETTING THE SAME ERROR. Doesnt seem to be impacting the site, but im worried it may down the line. Please help.

I am doing a regular phpmyadmin import into a blank SQL database.

Bluehost believe it may be the localhost database trying to add additional rows in the new SQL database.

What to do?

How are you making the import? Are you using the backup and migrate module like in this issue: #448038: mysql view isn't standard for Drupal?

Are you able to unistall uc_views and then reinstall? (Please note, not just disable but uninstall)

This error occurs when you use backup_migrate to backup and restore your db.

I have written a patch for that module to support db-views as well as tables. I hope they will accept and apply it (read more about it in the link in my previous post).

UPDATE

So even though I got the error, it doesnt seem to be impacting the site performance.

Bluehost said the error might be do to an additional command to add an empty database row that is not being added, or an extra space in the database

No the issue isn't related to performance. It is because the backup and migrate module treats the database views as tables, and thus is unable to to recreate them.

What the module does is for each table in the database to generate SQL for:
1. deleting the table if it exists
2. recreate the table
3. insert backed up data (unless otherwise configured)

Step 2 is failing for views while step 3 has no meaning for views. So I made a patch to correct that.

Meanwhile, if you are only using the backup and migrate module to back up data you can work around this problem by simply adding the views to the list of tables to exclude from the backup. The views from uc_views module are all named uc_[something]_vw.

I am having the same issue.

I am not using backup_migrate module.

I simply exported the db from my local phpmyadmin, and tried to import it on my website's phpmyadmin.

I am using ubercart though.

Thanks,
Mukesh

You must somehow have backed up your db views as though they are tables - no idea how though.

The easiest way to recover I guess is disable the module, uninstall and then install it again.

If you are using the 6.x-3.x-dev version it should be enough to just do this with the uc_views_marketing module.

Same problem. Just dumped all tables via phpMyAdmin at one server and trying to import it at another. Extremely confusing.

I faced the same problem, here is quick fix:

1) Check database user permissions: it should be at least "CREATE VIEW", I granted FULL access
2) Check and edit your backup: delete "ALGORITHM = UNDEFINED DEFINER = `root`@`localhost` SQL SECURITY DEFINER" and just leave CREATE VIEW bla-bla-bla. The ALGORITHM statement supported by MySql 5 and newer.

Now it must work as expected

I had the same problem and my solution was to uninstall the following modules:

- Views exporter
- Views Showcase
- Views Slideshow: SingleFrame
- Views Slideshow: ThumbnailHover
- Views UI

i don't really know which one was causing the problem.

Hi everyone,

Here found the solution:
http://www.hostingrails.com/MySQL-Error-1227-Access-Denied-you-need-SUPE...

Steps:
1. Edit your exported SQL file and search for word ALGORITHM or simply move to the end of file as more chances of these queries are at the end of file.
2. In those CREATE queries set DEFINER username with your cPanel username. E.g. DEFINER='root'@'localhost' will become DEFINER='cpanel_username'@'localhost'
3. Save and upload to import in your cPanel's PhpMyAdmin

Thanks & Regards,
Tajinder Singh

I installed the Backup and Migrate module on the site I was transferring, downloaded the .sql file without including certain cache and watchdog tables. After I had the .sql file, I went to the destiniation DB via phpMyAdmin and imported it directly into the new table. Worked fine for me.

I tried doing all the manual sql changes explained above but wasn't able to get it to work. I also tried using bigdump.php and it also throw an error.

Sorry, forgot to mention that this only works (or we can say worked for me) in case of below error and in case you have ubercart views module installed:

#1227 - Access denied; you need the SUPER privilege for this operation

Thanks for another solution :)

This issue has nothing to do in particula with the ' Backup/Migrate' module and thus can not be fixed by patching Backup/Migrate. This also happens with Phpmyadmin or any normal database dump mechanism (like the shell 'mysqldump'), and it always results in broken (= not restorable) backups.

The suggestions from #14 might work, if you are able to do search & replace operations on the database dump, *each time* you're restoring a invalid database dump. Those database dumps are invalid because the source database name is hardcoded multiple times for

`uc_order_products_pair_vw`
`uc_order_products_qty_vw`
`uc_order_products_user_vw`

Anyway, even with s&r of all occurences as suggested in #14 I still got this error:

#1227 - Access denied; you need the SUPER privilege for this operation

In shared hosting you definitely won't have this db permission, so this either needs to be fixed properly (e.g. with a "backup mode" which removes *_vw temporarily from the database), or CLEARLY STARTED on the project page: "THIS MODULE BREAKS YOUR DATABASE BACKUPS"!

I have discovered that is because of this little scrap of offending code:
DEFINER = `root`@`localhost` SQL SECURITY DEFINER

Remove that piece of the code, and then it will work fine. That was just an unnecessary comment that came from the database dump, anyway.

The MySQL backups that I had (via backup_migrate.module) had neither ALGORITHM nor DEFINER anywhere in their output. The only way I was able to get the backup to reinstall was to remove the references to the three Ubercart Views altogether.

I agree with asb in #17 that this needs to be fixed or UC Views needs to state that the module will jeopardize your database backups. This is a pretty big deal.

Confirming that this affects both phpMyAdmin and backup_migrate backups -- just tried it on my own install. This isn't Backup and Migrate–specific.

The uc_order_products_pair_vw, uc_order_products_qty_vw and uc_order_products_user_vw should not be included in backup sets. They are dynamically created from existing data.

If you need to reinstate them after restoring a backup, simply ensure they do not already exist, then uninstall and reinstall UC Views Marketing.

I do neither consider this "fixed" nor as a "support request". It is a critical bug that makes backups useless without giving notice about this; so the absolute minimum would be a HUGE WARNING on the project page, in the README.txt and in the documentation.

Yeah, I gotta agree. If something should not be included in backups or it will break your site that's worth something in the README and INSTALL texts at the very least.

Suggesting we mark this as a critical documentation issue for the reasons noted above.

Once it is at least actually documented we can squabble over whether this is a bug that should be fixed.

Hi,
Iam really confused now. How to fix this issue? Can anybody make step-b step tutorial? It is quite important issue.
I tried to export database excluding
`uc_order_products_pair_vw`
`uc_order_products_qty_vw`
`uc_order_products_user_vw`
Not working.

I tried to manually remove ALGORITHM = UNDEFINED DEFINER = `root`@`localhost` SQL SECURITY DEFINER
Again no luck.

Thank you for answer.

@loparr: Have you tried to drop the database views tables before the database dump? Since many 3rd party tools, including Phpmyadmin, seem to have limited support for these database views, simply "excluding" them (e.g. like normal cache_* tables) might not work as you would expect.

In a case of emergency, you still can try to salvage the database dump as described in #14.

i got a big error after the backup migrate, views not working.
turn off javascript did work, but its not really workable.

I found the following fixed it for me:
go to your database. and remove:

uc_order_products_pair_vw
uc_order_products_qty_vw
uc_order_products_user_vw
Then recreate tables(views) with following command(s)

CREATE TABLE IF NOT EXISTS `uc_order_products_pair_vw` (
`nid` int(10) unsigned
,`pair_nid` int(10) unsigned
,`pair_sum_qty` decimal(27,0)
,`order_count` bigint(21)
);
-- --------------------------------------------------------

CREATE TABLE IF NOT EXISTS `uc_order_products_qty_vw` (
`nid` int(10) unsigned
,`order_count` bigint(21)
,`avg_qty` decimal(9,4)
,`sum_qty` decimal(27,0)
,`max_qty` smallint(5) unsigned
,`min_qty` smallint(5) unsigned
);

CREATE TABLE IF NOT EXISTS `uc_order_products_user_vw` (
`nid` int(10) unsigned
,`uid` int(10) unsigned
,`order_count` bigint(21)
,`avg_qty` decimal(9,4)
,`sum_qty` decimal(27,0)
,`max_qty` smallint(5) unsigned
,`min_qty` smallint(5) unsigned
);

@pindaman Yes, that is what the module maintainer is saying one has to do if this module is installed (see #21).

The issue here is that something like this needs to be documented, at the very least.

subscribing

This has nothing to do with the technology you use to backup nor restore your database. This is a uc_view design issue related to MySQL specification.

When the MySQL View (not the drupal) is created, it inherits the rights of the MySQL user who created the view, for instance developper@localhost. If you dump the database and restore it on another MySQL server where developper@localhost does not exist or has not the MySQL rights to access you tables, you will get the problem. Typically, if you backup on your development server and restore on the operational server.

The problem is due to the way the MySQL View is created, in uc_views code. MySQL allows, basically, two options :

1. create the view with the rights of the user who creates it (SQL SECURITY DEFINER), the default option
2. create the view with the rights of who uses it (SQL SECURITY INVOKER)

I suppose uc_views uses the first option, explicitly or not (it's the default). It's a design choice, not a bug. I wouldn't say it's the best choice (see further) but it seems to be the developer's one. If it really is a design choice then there is a lack of documentation. The warning about "uc_view using mysql views", in the README.txt file, if far from being enough. But what should be added ? At least a warning about this backup-restore issue. It's not easy to get into details since the way to overcome the problem depends on the mysql interface you use and the rights you have on the server. If we accept this design choice, it's more a MySQL than a uc_views issue : "how to change SQL SECURITY from DEFINER to INVOKER of some (or all) views of a database."

Anyway, I would suggest to modify uc_views code so that the views are created with "SQL SECURITY INVOKER" setting. This is what most MySQL basic users want. The users who really want to execute the views requests with the rights of a specific user, different from the invoker, know what they do and most probably know how to deal with views properties on their system. There might be a compatibility issue due to the "late" introduction of this option in MySQL. But since it's been introduced in version 5.0.16 (released on 21 Nov 2005), this seems acceptable…

Meanwhile, how to solve the problem ? The best is to refer to MySQL lists and forum (like this post). On a Linux system, here is a quick and dirty way to solve the problem :

1. backup the database through a mysql dump (Virtualmin server backup default)
2. remove the lines which contain the SQL SECURITY settings
3. restore the database on you target system.

Step 2 can be accomplished using sed :
$ sed '/^\/\*\!50013/d' backup.sql > backup_without_50013.sql

French readers can have a look at this article.

@blekc Personally I agree that it would be better if the module were modified to use the second option, as you suggest.

At the moment, though, I'm still hoping we can address the CRITICAL DOCUMENTATION BUG that exists by not documenting what the current setup means for a given user. Once we document what it's currently doing, I agree we should talk about what it SHOULD be doing. Perhaps the module maintainer will choose to stick with how it is -- and perhaps s/he will convince us all of why that's the right choice :) But the immediate need is to document to people what's going on, and how to account for it in your site setup and backup operations.

Please, please module maintainer (longwave?) -- add information to the README.txt and INSTALL.txt files, and a notice on the project page, about removing the Ubercart Views tables from your SQL backup prior to restoring/importing the SQL. This is really critical!

If someone can write some text ready for me to paste into the project page and README.TXT, that would be much appreciated, as would a patch to the .install file to change the way the view is created; I didn't write this code in the first place and I don't have enough time to do this myself at the moment.

Thank you, I just got this problem.

You said "go to your database. and remove:"

Do I have to do that after having imported the database ?

Because I do not want to break the shop original database that I want to duplicate.

Thanks again.

Maybe my host Dreamhost has the solution:

http://wiki.dreamhost.com/Restore_SQL_backup

Gotchas

Note that if you restore an SQL backup to a database under another user and have any views in your database, you'll get an error similar to this:
ERROR 1227 (42000) at line 694: Access denied; you need the SUPER privilege for this operation
You'll need to change the DEFINER mentioned in the .sql file at the line specified in the error.

Finally ss said on #18 http://drupal.org/node/445854#comment-4511054

I just deleted DEFINER = `root`@`localhost` SQL SECURITY DEFINER

And it works.

I had to delete it because I couldn't change it as Dreamhost wrote.

Is there any risk that something breaks on my new install after importing the database ?

Thanks for your replies.