Importing DB Critical Error

8 replies [Last post]

Sat, 01/23/2010 - 17:57

magpie

Offline

Joined: 09/28/2009

Juice: 18

I exported my Drupal DB and then while importing the DB into a new website I get the following error. It is complaining about
uc_products so I hope this is an appropriate place to post this problem. The rest of the tables seem to be loading, but
the site is totally messed after the import. mydomain.com/user comes up as page not found.

Drupal 6 / UC2 / importing with PhpMyadmin

Error
SQL query:

CREATE ALGORITHM = UNDEFINEDDEFINER = `cdman` @ `%` SQL SECURITY DEFINER VIEW `cdman`.`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 (

`cdman`.`uc_order_products` `op1`
JOIN `cdman`.`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 ;

MySQL said:

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

Sat, 01/23/2010 - 23:22

#1 View page

torgosPizza

Offline

Joined: 08/14/2007

Juice: 4110

Re: Importing DB Critical Error

Well the issue is right at the bottom: "Access denied." This is not an Ubercart issue, but a database/hosting one... the "SUPER" privilege is something that a server admin would have, and depending on your hosting provider, may or may not be something they are willing to give you. You may also be having other tables that are suffering for this (or, also likely is the chance that the rest of the import stops when it hits this irrevocable error, hence the "page not found" you are getting at /user).

To be honest I've never used "CREATE ALGORITHM" in MySql, but here's the link to its documentation: http://dev.mysql.com/doc/refman/5.0/en/create-view.html

Anyway, you may need to contact your host and ask them to give you the proper permissions on that database, otherwise you may have other DB imports fail as well. If your host can't give you the SUPER privilege, or at the very least help you with importing your DB, it may be time to find another host.

--
Help directly fund development: Donate via PayPal!

Sun, 01/24/2010 - 00:57

#2 View page

magpie

Offline

Joined: 09/28/2009

Juice: 18

Thanks for your reply, but

Thanks for your reply, but I'd like to suggest that if you are going to take the time to reply then
could you please not sound so condescending?

I don't just blindly post without looking at the code and try my hardest to work things out myself.
The offending table structure indicates UC_ORDER_PRODUCTS having problems, so I thought someone else may have
experienced this issue within this forum. Is this wrong?

In the end, I fixed the issue by using the backup/migrate option within Drupal and ran the import
function again using MyPhPAdmin and it works now! So problem solved.

There was obviously some bug in my table structure of some component of Ubercart when exporting
via MyPHPAdmin export script. I don't know why it would not work but fixed using the export / migration tool.

BTW, I never ran 'Create Algorithm' I assume that is a function of the import script. The access denied was
an obvious thing to look at. Since I AM the superuser that error turned out to be erroneous.

Sun, 01/24/2010 - 01:02

#3 View page

torgosPizza

Offline

Joined: 08/14/2007

Juice: 4110

Re: Thanks for your reply, but

Sorry, didn't mean to sound condescending.

Usually errors like that are obvious, but if you tried other things - and apologies if I missed them in your earlier post - then that is a bit strange. I mentioned 'create algorithm' not as a suggestion that you were the one that ran it, I understand that it's part of the import... whether it's part of a script you were running or just part of how the sql dump file was being imported.

I think an error like this could be caused by corruption, brought on my gzip compression or something similar. I myself have seen this happen, where I dump an sql file right to gzip during the export process. That can sometimes result in corrupted database schema, especially on a large database dump. Sorry I didn't think of that first, I wrongly assumed it was a privileges issue, but as you described, this wasn't the case.

It kind of makes sense, then, that backup-migrate module solution worked... it probably does things in such a way that the results are consistent, and it knows what to expect on both sides - exporting and importing. Good to know that it worked for you, though. You had better luck than me using that module

--
Help directly fund development: Donate via PayPal!

Fri, 01/29/2010 - 15:52

#4 View page

Lyle

Offline

Joined: 08/07/2007

Juice: 6854

Re: Importing DB Critical Error

In your export of your database, ignore or exclude the "uc_order_products_pair_vw", as well as anything else that ends in "_vw". These are database VIEWS, which are provided by the uc_order_views module, and they generally cause more trouble than they're worth. You should be able to uninstall, export, and then reinstall the Ubercart Views Marketing module without messing anything up.

Fri, 11/12/2010 - 10:39

#5 View page

llservice

Offline

Joined: 11/12/2010

Juice: 10

Same/Similar Issue- Help Needed

I have done what I think is exactly what you stated:
Exclude all tables ending _vw,
Import DB without above
uninstalled, (a few times) and re-installed.

However when I enable the views_marketing module, I get an SQL Syntax Error as follows, (although after reviewing below against the proper syntax found while searching, I see no syntax errors):

<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< op2.`nid` GROUP BY `nid`, `pair_nid` ORDER BY `nid`, `order_count` DESC, `pair_sum_qty` DESC in /home/jsafetys/public_html/sites/all/modules/uc_views/uc_views_marketing/uc_views_marketing.install on line 11.
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<= 1 ORDER BY uc_order_products_qty_vw_sum_qty DESC LIMIT 0, 10 in /home/jsafetys/public_html/modules/acquia/views/includes/view.inc on line 771.
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

Mon, 08/02/2010 - 23:25

#6 View page

ppcc

Offline

Joined: 08/02/2010

Juice: 6

Re: Importing DB Critical Error

removing the views from the database backup worked for me. Great tip!

Mon, 11/29/2010 - 22:11

#7 View page

tua

Offline

Joined: 11/29/2010

Juice: 3

Error # 1227 - Access denied. You need the PROCESS privilege

I first want to create a password localhost / phpmyadmin, then I tried to get into Reload privileges ..... after that the user 'root' @ '127 .0.01 ': edit privileges then I do change the password by providing a new password, after press GO . the results
Error
SQL query:

SHOW processlist

MySQL said:

# 1227 - Access denied. You need the PROCESS privilege for this operation

phpmyadmin was not using the default password.
My question:
How to restore back to default with no password and the process of becoming normal?
Please help, thank you.

Attachment	Size
Photo tua.GIF	1.98 KB

Thu, 02/21/2013 - 14:40

#8 View page

dexter5

Offline

Joined: 02/21/2013

Juice: 3

Simple fix for me

I was receiving the error, ' you need the SUPER Privliege, but my MySQL user had privelege to create views.

Here's how i got around it:
1. Export the database from phpMyAdmin EXCLUDING any Views.
2. Export the Views seperately.
3. Open the Views .sql files in a text editor
4. Delete the portion that reads: DEFINER=`your_username`@`localhost`
5. Import the .sql files through phpMyAdmin

Ubercart

Navigation

User login