Reference

Magento – SQLSTATE[HY000]: General error: 1025 Error on rename of – report_compared_product_index and report_viewed_product_index

Posted by | Reference | No Comments

SQL error when attempting to upgrade Magento across 1.4.2 –> 1.7

/app/code/core/Mage/Catalog/sql/catalog_setup/mysql4-upgrade-1.5.9.9-1.6.0.0.php

Run the following SQL to drop the following two tables and recreate as key violations from previous versions across to 1.6 causes errors.


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `report_compared_product_index`
--
DROP TABLE IF EXISTS `report_compared_product_index`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `report_compared_product_index` (
`index_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`visitor_id` int(10) unsigned DEFAULT NULL,
`customer_id` int(10) unsigned DEFAULT NULL,
`product_id` int(10) unsigned NOT NULL,
`store_id` smallint(5) unsigned DEFAULT NULL,
`added_at` datetime NOT NULL,
PRIMARY KEY (`index_id`),
UNIQUE KEY `UNQ_BY_VISITOR` (`visitor_id`,`product_id`),
UNIQUE KEY `UNQ_BY_CUSTOMER` (`customer_id`,`product_id`),
KEY `IDX_STORE` (`store_id`),
KEY `IDX_SORT_ADDED_AT` (`added_at`),
KEY `product_id` (`product_id`),
CONSTRAINT `FK_REPORT_COMPARED_PRODUCT_INDEX_CUSTOMER` FOREIGN KEY (`customer_id`)
REFERENCES `customer_entity` (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `FK_REPORT_COMPARED_PRODUCT_INDEX_PRODUCT` FOREIGN KEY (`product_id`)
REFERENCES `catalog_product_entity` (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `FK_REPORT_COMPARED_PRODUCT_INDEX_STORE` FOREIGN KEY (`store_id`)
REFERENCES `core_store` (`store_id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `report_viewed_product_index`
--
DROP TABLE IF EXISTS `report_viewed_product_index`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `report_viewed_product_index` (
`index_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`visitor_id` int(10) unsigned DEFAULT NULL,
`customer_id` int(10) unsigned DEFAULT NULL,
`product_id` int(10) unsigned NOT NULL,
`store_id` smallint(5) unsigned DEFAULT NULL,
`added_at` datetime NOT NULL,
PRIMARY KEY (`index_id`),
UNIQUE KEY `UNQ_BY_VISITOR` (`visitor_id`,`product_id`),
UNIQUE KEY `UNQ_BY_CUSTOMER` (`customer_id`,`product_id`),
KEY `IDX_STORE` (`store_id`),
KEY `IDX_SORT_ADDED_AT` (`added_at`),
KEY `product_id` (`product_id`),
CONSTRAINT `FK_REPORT_VIEWED_PRODUCT_INDEX_CUSTOMER` FOREIGN KEY (`customer_id`)
REFERENCES `customer_entity` (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `FK_REPORT_VIEWED_PRODUCT_INDEX_PRODUCT` FOREIGN KEY (`product_id`)
REFERENCES `catalog_product_entity` (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `FK_REPORT_VIEWED_PRODUCT_INDEX_STORE` FOREIGN KEY (`store_id`)
REFERENCES `core_store` (`store_id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1348756 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

Magento SSH maintenance

Posted by | Reference | No Comments

What’s this all about? Well there’s various forum posts and great tips out there for maintaining your Magento installs. I’ve collated some of these together below for my own purposes and ease of access:


rm -rf downloader/pearlib/cache/*
rm -rf downloader/pearlib/download/*
rm -rf var/cache/*
rm -rf var/session/*
rm -rf var/report/*
rm -rf var/tmp/*


TRUNCATE log_customer;
TRUNCATE log_quote;
TRUNCATE log_summary;
TRUNCATE log_summary_type;
TRUNCATE log_url;
TRUNCATE log_url_info;
TRUNCATE log_visitor;
TRUNCATE log_visitor_info;
TRUNCATE log_visitor_online;
TRUNCATE report_event;
TRUNCATE catalogsearch_fulltext;
TRUNCATE catalogsearch_query;
TRUNCATE catalog_compare_item;


chmod -R 777 lib/PEAR
./pear mage-setup
./pear upgrade -f magento-core/Mage_All_Latest-stable
chmod 550 ./mage
./mage mage-setup .
./mage sync
./mage list-upgrades
./mage config-set preferred_state stable
./mage upgrade-all --force


find . -type f -exec chmod 644 {} \;
find . -type d -exec chmod 755 {} \;

php -f index.php

Magento Upgrade 1.3.x to 1.4.x Custom Theme Issues Fix

Posted by | Reference | No Comments

From reading through the blogosphere and all the varying Magento forums its obvious lots of people are having issues in upgrading from Magento 1.3.x to 1.4.x due to custom theme problems and errors. I was no different! Who would have thought these things could be so difficult!?

After several attempts at upgrading Magento to 1.4.x failing dismally I set about toying with a development site until I could figure out what was what?! Its detailed everywhere that your 1.3.x theme will have to be re-written to work with 1.4.x but I’ve managed to get a simple enough process working for me in upgrading Magento 1.3.x to 1.4.x and keeping my custom 1.3.x theme working in 1.4.x without any re-development at all! So here goes:

  1. Log into the Magento Backend Admin Panel and remove products that you have placed in more than one category. Just have your products in one category otherwise these cause errors after the upgrade.
  2. Turn off SEO url rewrites (System >> Configuration >> Web >> Search Engines Optimization)
  3. Turn off cache management (System >> Cache Management)

The following from CrucialWebHost’s help site is perfect:
A little cleanup first:

rm -rf downloader/pearlib/cache/* downloader/pearlib/download/*

Set the permissions on the pear file so we can run it:

chmod 550 pear
I then cleaned out the cache myself manually to be sure:
rm -Rf var/cache/*
and again from the CrucialWebHost:
Initialize the PEAR channel just in case:

./pear mage-setup .

And then we can upgrade:

./pear upgrade-all

If it tells you that there's Nothing to upgrade, you'll need to do this instead:

./pear install magento-core/Mage_All_Latest

Lastly, we need to clear out any temporary and cached data. These commands will be done via SSH in the staging directory.

rm -rf downloader/pearlib/cache/* downloader/pearlib/download/*
rm -rf var/cache/ var/session/ var/report/ media/tmp/

And then reset our file and directory permissions:

find . -type f -exec chmod 644 {} \;
find . -type d -exec chmod 755 {} \;
chmod 550 pear

After this point delete everything in the following to clean out the backend cache (you need to do this or you will get errors when saving new configurations and probably logging into magento admin after upgrade too)
rm -Rf app/code/core/Zend/Cache/*
Log into your new 1.4.x Magento Backend Admin Panel from index.php/admin/

  1. ReIndex Data on everything (System >> Index Management)
  2. Turn on SEO rewrites  (System >> Configuration >> Web >> Search Engines Optimization)
  3. Turn on Cache Management (System >> Cache Management)

From this process you have theoretically upgraded your Magento installation to the latest 1.4.x version of Magento and have your custom theme working fine. Or at least I have, I can’t guarantee that this process will work for everyone out there?!

Good luck.

Error text: Nameserver doesn’t exists in the registry. 123-reg

Posted by | Reference | No Comments

Had an interesting experience with 123-reg and trying to apportion nameservers to domains always flagging back:

Setting nameservers to ns1.domain.tld and ns2.domain.tld
Please wait…
Nameservers for domain.net were not changed to
ns1.domain.tld and ns2.domain.tld
Error text: Nameserver ns1.domain.tld and ns2.domain.tld doesn’t exists in the registry

A quick scoot around their help centre didn’t flag back any answers for anything as complicated. So resorting to google I figured it was 123-reg hadn’t parsed through my details to all their associated registrars. Not immediately obvious from the error message.

The answer was to phone up 123-reg support and detail they needed to make sure my domain and nameservers were registered with all their associated registrar registries.

3rd time lucky on phone and 5 days later I managed to get ‘Ricky’ who knew exactly what I was talking about straight away and didn’t pawn me off with talk of DNS propogation etc. etc.  He ensured my .uk.net domain and nameservers were registered properly with Central NIC and got back to me very promptly. Thank you Ricky.

So all is well and hopefully this might help anyone else in the same situation.


First published on yaduk on Feb 2, 2010