Magento upgrade steps, issues and solutions

This is raw copy from my notes during magento store upgrade.

=======================================
Restrict access to /index.php with IP Address whitelist.

This is what I have:

if (file_exists($maintenanceFile)) {
    // START IP BLOCKER
    // Remove file 'maintenance.flag' when you decide to go live
    $ip_whitelist = array('x.x.x.x', '127.0.0.1', 'x.x.x.x');
    if(!in_array($_SERVER['REMOTE_ADDR'], $ip_whitelist)) {
        $protocol = "HTTP/1.0";
        if ( "HTTP/1.1" == $_SERVER["SERVER_PROTOCOL"] )
          $protocol = "HTTP/1.1";
        header( "$protocol 503 Service Unavailable", true, 503 );
        header( "Retry-After: 3600" );
        header('Location: /index_landingpage.php'); // our own custom landing page.
        exit;
    }
    // END IP BLOCKER
    include_once dirname(__FILE__) . '/errors/503.php';
    exit;
}

Copy index.php to upgrade.php and remove that IP restriction, so you can use this file while running upgrade.

Remove cache and session files. Other folders can be kept clean too.

rm -rf var/cache/mage--*
rm -rf var/session/*
rm -rf var/log/*
rm -rf var/report/*
rm -rf var/locks/*

It is also recommended to cleanup database by truncating log tables which takes huge space. Run below shell command to clean these tables

1. dataflow_batch_export
2. dataflow_batch_import
3. log_customer
4. log_quote
5. log_summary
6. log_summary_type
7. log_url
8. log_url_info
9. log_visitor
10. log_visitor_info
11. log_visitor_online
12. report_viewed_product_index
13. report_compared_product_index
14. report_event

php -f shell/log.php clean

Then BACKUP your DATABASE.

mysqldump -u magento_user -p magento_db | gzip > magento_db.20140123.sql.gz

Overwrite your magento files with latest core magento files, hopefully you haven't modified any core files. :)

/*
 * Run commands shown below directly on the database
 * This is required so you don't get error:
 * SQLSTATE[HY000]: General error: 1025 Error on rename of 
 * './magento/sales_flat_order' to './magento/#sql2-66f-20a5a' (errno: 152)
 * See: @link http://www.magentocommerce.com/boards/viewreply/348454/
 */
ALTER TABLE sales_flat_order_item DROP INDEX IDX_ORDER , ADD INDEX IDX_SALES_FLAT_ORDER_ITEM_ORDER_ID ( order_id );
ALTER TABLE sales_flat_order_item DROP INDEX IDX_STORE_ID , ADD INDEX IDX_SALES_FLAT_ORDER_ITEM_STORE_ID ( store_id );
ALTER TABLE sales_flat_order_item DROP INDEX IDX_PRODUCT_ID;

Run this command to find tables with engine type MyISAM.

SELECT  CONCAT('ALTER TABLE ', table_name, ' ENGINE=InnoDB;') AS sql_statements
FROM    information_schema.tables AS tb
WHERE   table_schema = 'magento'
AND     `ENGINE` = 'MyISAM'
AND     `TABLE_TYPE` = 'BASE TABLE'
ORDER BY table_name;

Above SQL query will provide you sql statements that you need to run to fix tables. Run all those statements from the result except:

ALTER TABLE catalogsearch_fulltext ENGINE=InnoDB;

That one should stay as MyISAM.

+----------------------------------------------------+
| sql_statements                                     |
+----------------------------------------------------+
ALTER TABLE adjcartalert_dailystat ENGINE=InnoDB;
ALTER TABLE adjcartalert_quotestat ENGINE=InnoDB;
ALTER TABLE aitsys_news ENGINE=InnoDB;
ALTER TABLE aitsys_notification ENGINE=InnoDB;
ALTER TABLE aitsys_performer ENGINE=InnoDB;
ALTER TABLE aitsys_status ENGINE=InnoDB;
--ALTER TABLE catalogsearch_fulltext ENGINE=InnoDB;
ALTER TABLE importexport_importdata ENGINE=InnoDB;
ALTER TABLE oauth_nonce ENGINE=InnoDB;
ALTER TABLE sendfriend_log ENGINE=InnoDB;
ALTER TABLE storepickup_holiday ENGINE=InnoDB;
ALTER TABLE storepickup_location ENGINE=InnoDB;
ALTER TABLE storepickup_order ENGINE=InnoDB;
+----------------------------------------------------+

Delete any previous extensions entries from core_resource table that will interfere upgrade process. e.g. for me I needed to remove 'storepickup_setup' and temando.

mysql>
Delete from core_resource where code = 'storepickup_setup';
Delete from core_resource where code = 'temando_setup';

And Delete tables that are not required: ========================================

drop table temando_hscode;
drop table temando_box;
drop table temando_carrier;
drop table temando_manifest;
drop table temando_package;
drop table temando_quote;
drop table temando_rule;
drop table temando_shipment;
drop table temando_taxes;
drop table temando_warehouse;
drop table temando_zone;
drop table tmdpickup_hour;
drop table tmdpickup_location;
drop table tmdpickup_pickup;
drop table tmdpickup_zone;
drop table storepickup_holiday;
drop table storepickup_location;
drop table storepickup_order;
drop table storepickup_store;

If you are running this on staging site and using live database, set base_url that of staging.

select * from core_config_data where path like '%base_url%';
update core_config_data set value = 'http://magento-staging.technooze.com/' where config_id in (3,4);
select * from core_config_data where path like '%base_url%'; // to confirm update

if you get error: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '0-xxx' for key 'UNQ_BY_CUSTOMER'. Truncate tables below if you haven't already done so.

Truncate table dataflow_batch_export;
Truncate table dataflow_batch_import;
Truncate table log_customer;
Truncate table log_quote;
Truncate table log_summary;
Truncate table log_summary_type;
Truncate table log_url;
Truncate table log_url_info;
Truncate table log_visitor;
Truncate table log_visitor_info;
Truncate table log_visitor_online;
Truncate table report_viewed_product_index;
Truncate table report_compared_product_index;
Truncate table report_event;

Run this on mysql command line:

SET NAMES utf8;
SET FOREIGN_KEY_CHECKS=0;
SET UNIQUE_CHECKS=0;

To fix issue: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'magento.directory_country_region_name' doesn't exist ==============

SHOW CREATE TABLE `directory_country_region`;

It currently shows type as '`region_id` int(10)'. so you must have same type on these files:

/app/code/core/Mage/Directory/sql/directory_setup/mysql4-install-0.8.0.php
/app/code/core/Mage/Directory/sql/directory_setup/mysql4-install-0.7.0.php

And run this manually if it still fails:

CREATE TABLE `directory_country_region_name` (
  `locale` varchar(8) NOT NULL default '',
  `region_id` int(10) unsigned NOT NULL default '0',
  `name` varchar(64) NOT NULL default '',
  PRIMARY KEY  (`locale`,`region_id`),
  KEY `FK_DIRECTORY_REGION_NAME_REGION` (`region_id`),
  CONSTRAINT `FK_DIRECTORY_REGION_NAME_REGION` FOREIGN KEY (`region_id`) REFERENCES `directory_country_region` (`region_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Regions names';

To fix error: ERROR 1005 (HY000): Can't create table 'magento.admin_user' (errno: 150)

On sqldump at: CREATE TABLE `admin_user` ( change: `user_id` mediumint(9) unsigned NOT NULL AUTO_INCREMENT, to: `user_id` int(11) unsigned NOT NULL AUTO_INCREMENT, because foreign key has int(11).

to fix: Base table or view not found: 1146 Table 'magento.directory_country_region' doesn't exist file: /app/code/core/Mage/Directory/sql/directory_setup/mysql4-install-0.8.0.php at line 122: change from: `country_id` varchar(4) NOT NULL default '0', to: `country_id` varchar(2) NOT NULL default '0', because foreign key also has varchar(2) and we really need 2 char value only

   /*
    * if you get error: SQLSTATE[42S01]: Base table or view already exists:
    * 1050 Table 'catalog_product_entity_group_price, 
    * catalog_product_index_group_price, sales_order_aggregated_updated, 
    * coupon_aggregated_updated' already exists this means you have tables 
    * created during failed upgrade. If you get these errors just drop 
    * those tables or drop / create database and 
    * import fresh backup sqldump.
    */
//e.g. of drop tables 
DROP TABLE catalog_product_entity_group_price;
DROP TABLE catalog_product_index_group_price;
DROP TABLE sales_order_aggregated_updated;
DROP TABLE coupon_aggregated_updated;

Now BACKUP your modified DATABASE as clean backup before running upgrade.

mysqldump -u magento_user -p magento_db | gzip > magento_db.20140123.CLEAN.sql.gz

Then start upgrading through command line or you can access through browser. /upgrade.php. I like using command line to run upgrade.

php upgrade.php

Upgrade process went through without a single error or warning!!!

Then you can restore below on mysql:

SET FOREIGN_KEY_CHECKS=1;
SET UNIQUE_CHECKS=1;

Re-index all:

/shell$ php indexer.php reindexall

If your upgrade fails just use the clean backup database after you fix any warning or error that stopped you from upgrading.

mysql> 
drop database magento_db;
create database magento_db;
use magento_db;
magento_db.20140123.CLEAN.sql; /*make sure you have unzipped copy*/

Other issues I found after upgrade:

If you have issues printing invoice from admin, update this file: trunk/lib/Zend/Pdf/FileParserDataSource.php

change: abstract public function __construct(); to: abstract public function __construct($filePath);

If you find forgot password is sending you empty / blank password in the email. That means you are using old email template. Instead of sending plain password, magento now sends link to reset password. So you need to update your template.

Look here: System > Configuration > Customer > Customer Configuration > Password Options > Forgot Email Template. If you are using System > Transactional Emails then either set to use Default template from locale or update your email template as app/locale/en_US/template/email/account_password_reset_confirmation.html

If you add product to cart first time, it doesn't get added and it shows you empty cart. The solution for me was to exclude product view page from varnish cache.

If you are upgrading to magento version greater than CE 1.8.1 you may get error when visiting admin > System > Configuration:

Fatal error: Class 'Mage_Googlecheckout_Helper_Data' not found in app/Mage.php on line 547

Solution for this error is, go to app/code/core/Mage/GoogleCheckout/etc/ folder and delete all xml files except config.xml.

Let me know if you want to suggest more steps than above guide to upgrade magento faster, smooth and bug free.