Author Topic: Migrating your data from OpenCart Version to Version 2.1.0.x  (Read 25501 times)


  • Administrator
  • Hero Member
  • *****
  • Posts: 2664
Migrating your data from OpenCart Version to Version 2.1.0.x
« Reply #1 on: December 22, 2015, 06:24:23 PM »

You should NEVER operate on a live environment unless you ABSOLUTELY have to! No Professional would have you do any different.
Using these procedures will require some working knowledge of SQL, phpMyAdmin and the ability to follow direction verbatim.
If you do not have these skills you should PAY someone to do it for you.
There are people on the website who you can pay to convert your site for you.
We can do it for you also; but we charge much more for that service than those who do it on OpenCart.

NOTE: The changes in OpenCart v2.1.0.2 are insignificant and do not have ANY affect the this upgrade process.
Steps 5 & 6 are about replacing the database in the new install with the one you want to covert.
If upgrading from v2.0.x to 2.1.x you can most likely skip steps 3,4 & 9

It has taken some effort but we have put together enough information from our efforts and sources on the OpenCart forum
(The original upgrade.php and Pekka Mansikka's OpenCart Upgrade & Migration Tool 1.4.x - 2.x). We have developed some
steps needed to help migrate your data to the OpenCart 2.1.0.x version database. In the migration you WILL loose any modules you had installed along with any MODS, Layouts and settings.
You will need to re-input all layouts for your stores. Download OUR file, unzip it then upload the file to your new installation.
If you want; you can use our VQMOD XML file to add the upgrade.php file to your installation along with all the current changes we have made up until now located at bottom of this post (Recommended).

Here are the steps needed to help you migrate. We use phpMyAdmin for all Import/Export functions.
Do not take ANY shortcuts or this procedure WILL NOT WORK PROPERLY and you will get MANY errors.
This has been tested on OpenCart Database v1.5.4 and up and will work on any earlier version for database migration.

Also from my understanding you should update your php to a version higher than 5.3 for best results using OpenCart v2.x.
If you need to upgrade from v2.1.0.x to v2.2.0.0

  • Export your current database to an SQL file included with all DROP/CREATE/INSERT statements

  • Upload the OpenCart 2.1.0.x software to a new folder location on your server and complete a NEW install with a NEW Database (we will use this database later).
    To install the new version (ex., your config.php files must be empty (they have to exist with a zero length!!!).
    This process will also create the proper config.php files that you will need (root & admin) for the upgrade process to work.
    If the config.php files are not zero bytes in length you will start an upgrade (We don't want to do this just yet).

  • Copy all your images from your original OpenCart installation (data/image) to the new installation (image/catalog). This includes all folders & files.
    You may need to download them first then upload to the new installation (All image files in one folder can be a problem later).

    NOTE: OpenCart's config file points to the image root location and you will never see your images if you upload to that location.

  • Export the oc_setting, oc_extension, oc_layout, oc_layout_route & oc_layout_module tables from the database of the new installation to SQL files (included with DROP/CREATE/INSERT statements).
    You will need these files to replace the same tables in the updated database after migration.

  • Drop all tables in the database if you can. If you cannot drop all the tables; delete the newly created database (from the new install) then using the same database name, username and password recreate a new empty database for the upgrade.
    Double check that the database host names stays the same or you will need to change it in your config.php files (root & admin).
    If you are handy you can export all tables from the database to an SQL file  only the table structures then sort that file and extract all the Drop Statements to be able to drop all tables.

  • Import into the new database using the database table SQL file that you created earlier from the original database.
    Now you have all your data, new fields and tables from your old database in the new one and are ready for the upgrade.

  • In the OpenCart 2.1.0.x upload folder, copy the opencart.sql file from the install folder to the catalog folder.
    It is incorrectly coded in the upgrade.php file and the DIR_APPLICATION parameter in the config.php points to the catalog folder not the original install/model folder.
    If you have uploaded our VQMOD XML file then this is already done for you.

  • Download the file at bottom of post. (it contains the modified upgrade.php file). Upload the modified upgrade.php file to the install/model folder in your new installation to replace the original one.
    If you are using our VQMOD XML file then this is already done for you.
    Run the OpenCart 2.1.0.x installation again (ex. with the modified upgrade.php file in place).
    The installation should go into Upgrade mode (using the newly created config.php files and the updated upgrade.php file).

  • After the upgrade is compete, import the oc_settings, oc_extension, oc_layout, oc_layout_route & oc_layout_module sql files that you created earlier into your converted (new) database to overwrite the existing tables.

  • Go to Extensions->Modules->Account. Choose Edit then enable this module then save it.

  • Log into the admin panel, go to Design->Layouts->Default (or whatever your stores default layout is). Create the default layout for your store.

    • Change the route to common/home
    • Add the layout modules for your default page. Set your store to use this layout (default).

    You should now be up and running. You may need to look for updated MODS for the ones that you had previously installed in version 1.5.x.
    This is a clean system with your data migrated ONLY! Re-check ALL Settings & enable all modules.
    Test, Test then re-test again before going live.

    Other Helpful Information

    Updated 02/18/2016 - v2.1.0.x

    To install just copy the upload folders to the OpenCart installed location. It will replace the upgrade.php file and add the opencart.sql file to the proper location for the upgrade to work properly.
    This script can be used to migrate from any lower database version to any higher one.
    What you would need to do for this to happen is to replace the opencart.sql file with a database export of the higher version (structures with DROP / CREATE statements ONLY).
    When you run the upgrade it will add/change any fields and indexes of tables from the lower version to match the higher version and also add any missing tables that exist in the lower version or only exist in the higher version.
    If you are upgrading to a lesser version, you may need to remove or modify some v2.1.0.x specific truncate/insert/update statements in the upgrade.php file (RSUpdateDBTables function).
    For best upgrade results and OpenCart operations use our VQMOD XML file (at bottom of page) that contains the upgrade.php as well as many fixes that you will need for your system to run smoothly.

    Updated 02/18/2016 - v2.1.0.x Layout

    This is a VQMOD XML file to correct issues with stores not showing the correct chosen layout when set in the admin panel. Also fixes some banner and route issues
    a detailed explanation can be found here

    Updated 02/01/2016 -

    This is a modified version of the database engine that will log all SQL calls so that you can track what OpenCart is attempting to do in real time and perhaps give you some insight on how to correct the issue.
    We also make a VQMOD XML version of this for debugging -
    The location and  name of the log file will be - system/storage/logs/RSSQLData.txt.

    SQL log with back trace will look similar to this:

Code: [Select]
SELECT DISTINCT * FROM oc_country WHERE country_id = '0'
        File: /home/content/57/10005557/html/opencarttest/vqmod/vqcache/vq2-system_library_db_mysqli.php Func: LogSQL  Line: 19
        File: /home/content/57/10005557/html/opencarttest/system/library/db.php Func: query  Line: 16
        File: /home/content/57/10005557/html/opencarttest/admin/model/localisation/country.php Func: query  Line: 22
        File: /home/content/57/10005557/html/opencarttest/admin/controller/localisation/country.php Func: getCountry  Line: 458
        File: /home/content/57/10005557/html/opencarttest/vqmod/vqcache/vq2-system_engine_action.php Func: call_user_func  Line: 46
        File: /home/content/57/10005557/html/opencarttest/system/engine/front.php Func: execute  Line: 34
        File: /home/content/57/10005557/html/opencarttest/system/engine/front.php Func: execute  Line: 29
        File: /home/content/57/10005557/html/opencarttest/admin/index.php Func: dispatch  Line: 178

Start from the last line then read backwards to trace the sequence in which the actually module called the SQL statement.
REMEMBER, When moving to production; replace this file with the original mysqli.php file from the original install.

Updated 04/24/2016 - v2.1.0.x

This VQMOD XML file is the VQMod install that contains all the changes below that we have made to OpenCart v2.1.0.x to correct issues.
Use this procedure if you do not want to make physical changes to your OpenCart files (Recommended).
Contents has the correct .htaccess file for SEO URL's to work (rename htaccess.txt to .htaccess).
Using this method contains the proper upgrade.php file and all changes listed here to correct other upgrade issues also. Best when used with VQMOD Manager

  • Fix: column_Left, column_right,content_top & content_bottom so that the store layout will display properly - 20151219
  • Add: Missing header text and allow it to be used - 20151219
  • Fix: Broken banner template for add/edit - Gaspar - 20160115
  • Fix: Updated OpenBayPro code that was left over from v1.5.x - 20160110
  • Fix: Force URL's in store to be terminated properly with a trailing slash or any store without trailing slash will default to main store. Force HTTPS protocol to HTTPS if SSL is selected in store settings - 20160116
  • Add: Place suhosin parameters to php.ini for when product[key] gets too long (root & admin) when updating quantity in checkout/cart - artcore - 20160115
  • Fix: Place opencart.sql file into proper folder for upgrade.php script to find - 20151216
  • Fix: Replace upgrade.php file with modified upgrade.php file that will work properly - 20160116
  • Chg: Force banner displays to be random each time page is shown - 20160116 - v1.0.5
  • Fix: Broken SQL statement in catalog/model/design/layout.php that prevented banners from displaying - 20160117 - v1.0.5
  • Fix: Force layouts that have an empty route to be assigned common/home in the database to avoid confusion when attempting to use the layout - 20160117 - v1.06
  • Add: Rearrange Featured Products - Tri Le - 20150902 - v1.07
  • Fix: OpenBayPro - Undefined method loader - 20160126 - pm-netti - v1.0.7
  • Fix: Modify any third party themes that have error that does not allow removing items from checkout cart - 20160130 - v1.08
  • Fix: Added missing labels and code for Backup & Restore Tool - 20160131 - v1.09
  • Fix: When selecting "Print Shipping List" from the Orders section; if no product in the order were to be shipped the browser screen would be blank. Changed it to show the order but with DO NOT SHIP in the heading. This will avoid confusing the user into believing that something is broken - v1.0.10
  • Add: Full database dump in Backup & Restore is now available in SQL format that has DROP / CREATE / INSERT statements so that you can backup/restore your whole database properly - 20160204 - v1.11
  • Fix: Reward Points not subtracting the order points from the customers rewards points - 20160204 - vyshemirsky - v1.11
  • Add: Ability to add products to OpenCart shopping cart from your website to aid in a better customer experience - 20160206 - v1.12
  • Chg: Changed Customer Online Report URL & Referrer columns to maximize available text space - 20160206 - v1.12
  • Fix: Fix theme views - viethemes - 20160207 - v1.12
  • Fix: Remove trailing slash from any routes in layout_route table (when layouts are saved) - 201602013 - v.1.13
  • Fix: Affiliate Commission Radio Button selection Fix - 20160217 - ocmta - v1.14
  • Fix: Change payment method button loading hang when "Terms and Conditions" not ticked - 20160220 - Unknown Author - v1.15
  • Fix: Google Captcha error issue on Guest Checkout- 20160227 - nuke123 - v1.16
  • Fix: Added customer_activity table to be de-serialized - v1.17
  • Fix: Fixed Ounce calculations to reflex proper amount in USPS shipping XML - v1.18

If you like the work that we have done, please donate to make helpful new work possible.

Donate with Credit Card
Donate with PayPal