Magento upgrade takes too long and never completes

Question

I am upgrading magento from 1.4.0.1 to 1.7.0.2. Initially there were some errors; after fixing those, now the upgrade is running for 5 hours and never completes. No error is displaying. Any idea why it is happening?

Answer 1

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;

Answer 2

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.