SQL Import Error [#666190]

Hi,

I've recently had problems when backing-up/restoring my database via phpMyAdmin. When I try to import my website via phpMyAdmin it crashes out with the following error...

SQL reports...
--------------------------------------
Error

SQL query:

--
-- Dumping data for table `uc_order_products_pair_vw`
--
INSERT INTO `uc_order_products_pair_vw` ( `nid` , `pair_nid` , `pair_sum_qty` , `order_count` )
VALUES ( 74, 77, '1', 1 ) ;

MySQL said: Documentation
#1471 - The target table uc_order_products_pair_vw of the INSERT is not insertable-into
-----------------------------------------

Any help or insights would be appreciated! Thank you.

Victoria

Comments

Comment #1

joecanti commented 23 December 2009 at 01:07

theres a video on learnbythedrop.com which deals with transferring databases - he goes into quite a lot of detail about which settings work for him inside phpmyadmin when exporting/importing - might be useful to you - its called moving a localhost site to the web and its half way down here: http://learnbythedrop.com/video?page=1

There are some modules that might help you out also - backup and migrate.... or if that fails you could use 'node export' to export all your products from one place to another.

hope this helps, Joe

Comment #2

victoria_b commented 23 December 2009 at 01:40

Hi Joe,

Thanks for the info. I've learnt a lot from LearnByTheDrop. It's a great site.

To add to my initial query...

I've been exporting/importing my DB via phpMyAdmin now for nearly a year - had no problems. I used to use Backup and Migrate until I realised that it wasn't saving the uc_views tables. I'm aware that a patch was written and submitted for the Backup and Migrate module to cover this - do you know if it was ever accepted and rolled into a later version?

Anyhow, I switched to backing up via phpMyAdmin as it was correctly saving my complete DB including the uc_views tables. However a short time ago my host upgraded their WHM/cPanel and since then when I export my DB it's 2Mb less in size and when I try to import the DB back the original error I posted above occurs.

I'm positive that it's not a Drupal / uc_views module error as everything was working pre host WHM/cPanel upgrade - I'm just trying to find out as much about any possible problems their upgrade "may" have caused my DB.

Without the ability to successfully export and import I can't reliably backup my site.

Again, thanks for the quick info!

Victoria

Comment #3

joecanti commented 23 December 2009 at 01:53

Hi Victoria,
Im not sure whether that has been implemented into backup and migrate...but if phpmyadmin is having problems I'd guess that backup and migrate would too.

I'm not too hot in this area, but 2mb seems like a lot to lose off a database - and if its when your host upgraded then im guessing its that. I would get in contact with them and ask them if the MySQL was updated as well - maybe they've been having a few customers with the same problems...if they updated everything at just the right time you may be trying to put an older database into a newer MYSQL version? Or maybe they've added a database download size limit during their upgrades after which things get cut off...

I guess there may even be a chance they could put it back to how it was, but perhaps not if its on a shared host...

failing that there are some third party back up options like site vault - ive never used it but im looking into options like that as an automated scheduled backup for all my sites...

Also check with them how often they back things up - would be nice to know that if anything went wrong you had another option...

good luck! Joe

Comment #4

madsph commented 23 December 2009 at 07:05

This is due to the fact that you are treating databse views as though they were tables.
You should not ever INSERT INTO `uc_order_products_pair_vw` because a view does not contain any data - it simply represents a view on data gathered from various tables.

Back up and migrate have the same problem, I submitted a patch to them several months back, but I don't think they have applied it yet.

So the simplest way forward for you is to disable the uc_views_marketing module and then uninstall it. Then take you db backup as normal. On the site you migrate to, install uc_views_marketing again, and the db views will be rebuild.

Comment #5

victoria_b commented 24 December 2009 at 00:14

Hi madsph,

Thanks for the reply and clarification.

If I may, I have a couple of questions before I consider this thread closed.

1) If I have to manually disable, backup my DB and then reinstall the module this doesn't seem to be a practical solution to a cron DB backup. I take it the patch to Backup and Migrate should allow a me to do an automated backup of the entire database but will it include or exclude the DB views so not to crash out?

2) Would you know why I was able to previously backup and successfully restore my DB when the views were installed?

Thank you.