Magento upgrade takes too long and never completes

I recently upgraded a clients magento from V1.4 to V1.7.2.0 and i followed this steps:- Following are the main points to upgrade the website from Magento v1.4.0.0 to v1.7.2.0:-

1. Collect the live database backup in a zipped format, without the following tables:-

   • “log_customer”
   • “log_quote”
   • “log_summary”
   • “log_url”
   • “log_url_info”
   • “log_visitor”
   • “log_visitor_info”

   “log_visitor_online”

2. Unzip the zipped backup database, in your own file system, in any folder.

3. Start the local WAMP / XAMPP, and create a test database “test_something” or any other name, using the web app “phpMyAdmin”.

4. Open the command prompt window and then type “mysql” to start the MySQL Command Prompt.

5. Import the unzipped database into the test database, using the command prompt, so that it will be a lot faster without any errors.

6. After the successful import, run the SQL mentioned in the file “DB Changes.txt” from phpMyAdmin.

7. Extract a fresh Magento v1.7.2.0 in local WAMP / XAMPP, and start installing this Magento using the test database with the old livesite data.

8. After successful Magento installation, export & dump the new upgraded database in a zipped format using command prompt, so that it will be a lot faster without any errors.

9. Extract a fresh zipped Magento or upload a fresh unzipped Magento of v1.7.2.0 into the live server’s file system, without installing anything.

10. Upload this zipped database into the live server’s file system, and then open the PuTTY for the live server.

11. Upload a copy of the “app/etc/local.xml” Magento file of local WAMP / XAMPP, to replace the live Magento’s “app/etc/local.xml” file. Remember to change all the DB credentials of this file, as per the new live server, before uploading it to the live server.

12. Remember not to browse the Magento from the web browser for the live web server, till the point #14 gets completed.

13. Using PuTTY commands, extract the zipped database, and then import it into the new database of the live website.

14. After the successful import, search “core_config_data” database table with the “path” column value as “%base_url%”. Replace all the values of the “value” column with the full URL of the live Site “http://www.livesite.com/”, without any mention of “index.php”.

15. Upload the theme and its related files to the new server’s file system.

16. Upload the extension/module check there compatibility.

17. Make sure to configure the required modules in the System Configuration are from the Admin panel.

    The DB Changes.txt are as follows:- CREATE TABLE IF NOT EXISTS log_customer ( log_id int(10) unsigned NOT NULL AUTO_INCREMENT, visitor_id bigint(20) unsigned DEFAULT NULL, customer_id int(11) NOT NULL DEFAULT '0', login_at datetime NOT NULL DEFAULT '0000-00-00 00:00:00', logout_at datetime DEFAULT NULL, store_id smallint(5) unsigned NOT NULL, PRIMARY KEY (log_id), KEY IDX_VISITOR (visitor_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Customers log information';

    ---

    --

    -- Table structure for table log_quote

    CREATE TABLE IF NOT EXISTS log_quote ( quote_id int(10) unsigned NOT NULL DEFAULT '0', visitor_id bigint(20) unsigned DEFAULT NULL, created_at datetime NOT NULL DEFAULT '0000-00-00 00:00:00', deleted_at datetime DEFAULT NULL, PRIMARY KEY (quote_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Quote log data';

    ---

    --

    -- Table structure for table log_summary

    CREATE TABLE IF NOT EXISTS log_summary ( summary_id bigint(20) unsigned NOT NULL AUTO_INCREMENT, store_id smallint(5) unsigned NOT NULL, type_id smallint(5) unsigned DEFAULT NULL, visitor_count int(11) NOT NULL DEFAULT '0', customer_count int(11) NOT NULL DEFAULT '0', add_date datetime NOT NULL DEFAULT '0000-00-00 00:00:00', PRIMARY KEY (summary_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Summary log information';

    ---

    --

    -- Table structure for table log_url

    CREATE TABLE IF NOT EXISTS log_url ( url_id bigint(20) unsigned NOT NULL DEFAULT '0', visitor_id bigint(20) unsigned DEFAULT NULL, visit_time datetime NOT NULL DEFAULT '0000-00-00 00:00:00', PRIMARY KEY (url_id), KEY IDX_VISITOR (visitor_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='URL visiting history';

    ---

    --

    -- Table structure for table log_url_info

    CREATE TABLE IF NOT EXISTS log_url_info ( url_id bigint(20) unsigned NOT NULL AUTO_INCREMENT, url varchar(255) NOT NULL DEFAULT '', referer varchar(255) DEFAULT NULL, PRIMARY KEY (url_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Detale information about url visit';

    ---

    --

    -- Table structure for table log_visitor

    CREATE TABLE IF NOT EXISTS log_visitor ( visitor_id bigint(20) unsigned NOT NULL AUTO_INCREMENT, session_id char(64) NOT NULL DEFAULT '', first_visit_at datetime DEFAULT NULL, last_visit_at datetime NOT NULL DEFAULT '0000-00-00 00:00:00', last_url_id bigint(20) unsigned NOT NULL DEFAULT '0', store_id smallint(5) unsigned NOT NULL, PRIMARY KEY (visitor_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='System visitors log';

    ---

    --

    -- Table structure for table log_visitor_info

    CREATE TABLE IF NOT EXISTS log_visitor_info ( visitor_id bigint(20) unsigned NOT NULL DEFAULT '0', http_referer varchar(255) DEFAULT NULL, http_user_agent varchar(255) DEFAULT NULL, http_accept_charset varchar(255) DEFAULT NULL, http_accept_language varchar(255) DEFAULT NULL, server_addr bigint(20) DEFAULT NULL, remote_addr bigint(20) DEFAULT NULL, PRIMARY KEY (visitor_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Additional information by visitor';

    ---

    --

    -- Table structure for table log_visitor_online

    CREATE TABLE IF NOT EXISTS log_visitor_online ( visitor_id bigint(20) unsigned NOT NULL AUTO_INCREMENT, visitor_type char(1) NOT NULL, remote_addr bigint(20) NOT NULL, first_visit_at datetime DEFAULT NULL, last_visit_at datetime DEFAULT NULL, customer_id int(10) unsigned DEFAULT NULL, last_url varchar(255) DEFAULT NULL, PRIMARY KEY (visitor_id), KEY IDX_VISITOR_TYPE (visitor_type), KEY IDX_VISIT_TIME (first_visit_at,last_visit_at), KEY IDX_CUSTOMER (customer_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

    TRUNCATE report_event;

    TRUNCATE report_viewed_product_index;

    TRUNCATE report_compared_product_index;

    TRUNCATE dataflow_batch_export;

    ALTER TABLE orders CHANGE url parent_id VARCHAR( 255 ) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL;

I On the query logging by changing the following lines in lib\Varien\Db\Adapter\Pdo\Mysql.php file

protected $_debug               = true;
protected $_logAllQueries       = true;
protected $_debugFile           = 'var/debug/pdo_mysql.log';

Then by analyzed the pdo_myql.log file I came to know that a query is executing with error and thus the magento installer run it again and again.

Error was.

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry ''11199-1' for key 'UNQ_INCREMENT_ID'

So I deleted the entry in the database table and in pdo_mysql.log now other queries are displaying and the up-gradation is complete.

Working many days on magento upgrade, I am summarizing the steps to successfully upgrading magento from 1.4.0.1 to 1.7.0.2. These errors displayed will be different for other projects because every project have different data.

1. Create a fresh database for the new version. (I am using SQLyog because it is good for big database importing and exporting).

2. Extract and Install fresh version 1.7.0.1 in www or htdocs. My project name is magento171.

3. Create new database because we will need the fresh db in repair step.

4. Import the old database data into the new database.

5. Change the new database name in etc/local.xml in new installed version of magento.

6. In etc/local.xml find and change SET NAMES utf8 to SET NAMES utf8; SET FOREIGN_KEY_CHECKS=0; SET UNIQUE_CHECKS=0; .

7. Copy old project file in new version of magento. I have the theme in blank folder. Copy default if you have template files in default folder.

   • app\design\frontend\default\blank

   • app\code\local

   • skin\frontend\default/blank

   • app\etc\modules (copy the files which are not in the new version).

8. On the MySql queries logging by changing the following lines in lib\Varien\Db\Adapter\Pdo\ Mysql.php

    protected $_debug               = true;
    protected $_logAllQueries       = true;
    protected $_debugFile           = 'var/debug/pdo_mysql.log';
   

9. Search and change CREATE TABLE to CREATE TABLE IF NOT EXISTS In code/core/mage/.

10. Change the following entries in the table core_config_data (use your project folder name).

      • web/unsecure/base_url | http://localhost/magento171/
      • web/secure/base_url   | http://localhost/magento171/
    

11. Rename /errors/local.xml.sample to /errors/local.xml to enable error_reporting .

12. Clear the magento cache by delete all the data in var\cache.

13. Go to browser and type the your project path. http://localhost/magento171/ and keep eyes on the browser and on the var/debug/pdo_mysql.log file.

14. The first error occurred to me is: Error in file: "D:\xampp\htdocs\magento171\app\code\core\Mage\Sales\sql\sales_setup\mysql4-upgrade-1.3.99-1.4.0.0.php"

    • SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '7' for key 'PRIMARY'

    Fix: I found from pdo_mysql.log file that the issue is in the sales_flat_order table, which means many duplicate entries for primary key are there so I truncate all the sales tables. This is actually the error in my old DB. In new version increment_id is UNIQUE. We can't skip primary key checks, so I truncated all the tables related to sales. If you have same issue then truncate all tables related to that feature like if duplicate in customer then truncate all customer table or if in catalog then truncate catalog tables. But remember truncate should be done at the time when the error is occured because if truncated before the installation begins the installer will not read the existing data and finally you will miss some records like missing some orders or invoices.

    SET FOREIGN_KEY_CHECKS = 0;
    TRUNCATE `sales_flat_creditmemo`;
    TRUNCATE `sales_flat_creditmemo_comment`;
    TRUNCATE `sales_flat_creditmemo_grid`;
    TRUNCATE `sales_flat_creditmemo_item`;
    TRUNCATE `sales_flat_invoice`;
    TRUNCATE `sales_flat_invoice_comment`;
    TRUNCATE `sales_flat_invoice_grid`;
    TRUNCATE `sales_flat_invoice_item`;
    TRUNCATE `sales_flat_order`;
    TRUNCATE `sales_flat_order_address`;
    TRUNCATE `sales_flat_order_grid`;
    TRUNCATE `sales_flat_order_item`;
    TRUNCATE `sales_flat_order_payment`;
    TRUNCATE `sales_flat_order_status_history`;
    TRUNCATE `sales_flat_quote`;
    TRUNCATE `sales_flat_quote_address`;
    TRUNCATE `sales_flat_quote_address_item`;
    TRUNCATE `sales_flat_quote_item`;
    TRUNCATE `sales_flat_quote_item_option`;
    TRUNCATE `sales_flat_quote_payment`;
    TRUNCATE `sales_flat_quote_shipping_rate`;
    TRUNCATE `sales_flat_shipment`;
    TRUNCATE `sales_flat_shipment_comment`;
    TRUNCATE `sales_flat_shipment_grid`;
    TRUNCATE `sales_flat_shipment_item`;
    TRUNCATE `sales_flat_shipment_track`;
    SET FOREIGN_KEY_CHECKS = 1;
    

15. The installation takes too long, so I inspect the pdo_mysql.log file and the following error is displaying again and again. Error displayed: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry ''11199-1' for key 'UNQ_INCREMENT_ID'' Fix: So I delete the first entry in the table.

16. Database Rapair Step: It is necessary to Repair the new database with the fresh database using magento-db-repair-tool-1.1 (http://www.magentocommerce.com/wiki/1_-_installation_and_configuration/db-repair-tool). It the end the report will show all the fixes.

17. Now you can simply shift the website to the live server.

I think that the most comprehensive description of the Magento 1.4 to 1.7.x upgrading is this way, offered by turnkey website:

Before proceeding with this part of Magento upgrade, it is important to see to what version Magento upgrade scripts will upgrade your store. Enter this command to check this:

1
./mage list-upgrades

If you will see this result:

Updates for community:
Mage_All_Latest: 1.4.2.1 => 1.7.0.2
Lib_Js_Mage: 1.4.2.0 => 1.7.0.2
Lib_Varien: 1.4.2.0 => 1.7.0.2

It means that your Magento will be upgraded to version 1.7.0.2. If it is not what you need you can change upgrade channel to “beta” and upgrade your Magento to RC (beta) version.

1 – Enter this command to change the upgrade channel to stable (remember, “stable” channel will upgrade your Magento to latest 1.7.x stable version):

1
./mage config-set preferred_state stable

After this the “./mage list-upgrades” command will show you this result:

Updates for community:

  Mage_All_Latest: 1.4.2.1 => 1.7.0.2.
    Lib_Js_Mage: 1.4.2.0 => 1.7.0.2.
    Lib_Varien: 1.4.2.0 => 1.7.0.2.
    Lib_Phpseclib: 1.4.2.0 => 1.7.0.2.
    Mage_Core_Adminhtml: 1.4.2.0 => 1.7.0.2.
    Mage_Core_Modules: 1.4.2.0 => 1.7.0.2.

2 – After channel selection you can upgrade your Magento to Magento 1.7.0.2) using this command:

1
./mage upgrade-all --force

If “./mage upgrade-all –force” will not work, you can try to execute this command:

1
./mage install connect20.magentocommerce.com/community Mage_All_Latest --force

You will see upgraded packages on your screen:

…

    Package upgraded: community/Mage_Locale_en_US 1.7.0.2
    Package upgraded: community/Lib_Mage 1.7.0.2
    Package upgraded: community/Lib_ZF 1.11.1.0
    Package upgraded: community/Lib_Js_Prototype 1.7.0.2.
    Package upgraded: community/Lib_ZF_Locale 1.11.1.0

Now the upgrade is complete and you can execute database upgrade visiting your Magento store in your browser, this process will take several minutes, so be patient. If everything was upgraded correctly, you will see upgraded store in your browser. Before database upgrade it is recommended to increase time and memory limits of your PHP engine.

If it is not possible to increase it, you can try to execute database upgrade via SSH, e.g.:

1

    php -f ./index.php

When database upgrade will be finished, you can check version of your store in the footer of Magento administration panel.

Check your apache logs. However, it sounds like it's getting caught somewhere in the upgrade process. Make sure all your files are the new version, backup your database, and make sure your database connection information is correct.

You may also want to double check the size of your database. If your database is huge, magento may be timing out.