Upgrading Magento is always scary - kind of like flashing your BIOS on a PC. It is always important to backup your entire file system and magento database prior to upgrading to the most recent version to avoid catastrophic results.

This tutorial is for those of you who are at whits end and you just want to curl up and die in a hole because you attempted to upgrade magento and you encountered errors like:

Error in file: "app/code/core/Mage/Sales/sql/sales_setup/mysql4-upgrade-" - Column "base_shipping_hidden_tax_amount" does not exists on table "sales_flat_creditmemo"

Error in file: "/app/code/core/Mage/Customer/sql/customer_setup/mysql4-upgrade-" - SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '0-918' for key 'UNQ_BY_CUSTOMER'

Not only are these errors annoying but magento has a very unhelpful error system that often gives cryptic errors or it stores them in random files in the /var/reports directory.

Before you begin any of this, be sure to back up your entire file system.

Fixing Integrity constraint violation: 1062 Duplicate entry '0-918' for key 'UNQ_BY_CUSTOMER'

This fix actually isn't too bad as you might think. Even if you have no coding experience or experience connecting to your server via FTP, this will be easy.

If you are not familiar with connecting to a server via FTP, we have a lovely tutorial explaining how to do this. If you are familiar then please continue reading!

Navigate to the root of your magento installation and find the app directory. Once there, enter the app directory and then enter the etc directory. You will see a few files (see image).

Open the config.xml file in your favorite text editor (we recommend using notepad++ for ease of use for those of you who do not have an IDE). Around line 41 you will see the following:

<initStatements>SET NAMES utf8</initStatements>

Comment out this line like the following:

!-- <initStatements>SET NAMES utf8</initStatements> -->

And then add this line below the commented out code

<initstatements>SET NAMES utf8; SET FOREIGN_KEY_CHECKS=0; SET UNIQUE_CHECKS=0;</initstatements>

Now that this issue is resolved, try to load your magento site again. Your site is working again! But there may be a greater issue here so keep reading...

Fixing Column "base_shipping_hidden_tax_amount" does not exists on table "sales_flat_creditmemo" or other MySQL Related Errors

If you have the error above or you have other general MySQL errors in magento then there still is hope.

Step 1

Navigate to the /var/cache directory of your magento installation and delete all of the cache.
Navigate to the /var/sessions directory of your magento installation and delete all of the sessions.

Step 2

Go to this the magento website or download the magento database repair tool directly. Unzip this file and you will see a php file.

Place the php file in the root directory of your magento installation and continue to the next step.

Step 3

Install a fresh copy of Magento CE or you can simply download this sample database for reference if you would like to avoid having to install a fresh copy of magento.

Import the fresh database via phpMyAdmin (or by using your favorite database managment tool) and wait until it is fully completed with NO errors! Remote databases are OK.

Note: if you are accessing your database via ssh then see here:
To export an entire database:
mysqldump -u username -p database_name > database_name.sql

To import an entire database:
mysql -u username -p database_name < database_name.sql

Step 4

Now that you have a copy of the database you can upgrade with and that we can reference, it is time to finally repair this database and get it working! We need to download the Magento Database Repair Tool.

Step 5

Extract the file anywhere within your file system (the document root directory will suffice) and then type in your web browser: http://yourdomain.com/magento-db-repair-tool-1.0.php and you will see this page:
Magento Database Repair Tool

Note: this script doesn't care where the databases are located. You can connect to a remote database or a database that is on your localhost. Just ensure you enable access to your database remotely via your hosting control panel.

Type in the required information (be sure to include any and all prefixes you have) and then click continue. This script will match the corrupted database to the good database and repair any incorrect SQL table structures and repair any important data in those tables. Things like usernames, password, products will NOT be affected so do not worry!

When this process is complete you will see the following:
Magento Database Repair Complete

Final Step

Try entering in the URL of your magento installation to ensure everything worked correctly. If it did not then restore your file system and database and make sure you went through the motions correctly. Be sure to document and research any errors on the magento forums and see if anyone else has encountered/solved these errors!

But remember that line of code we replaced?

<initStatements>SET NAMES utf8</initStatements>

Be sure to replace our edited line of XML with the original as this is a security risk!

Hope this helps all those out there that are struggling with this!

Be Sociable, Share!