Magento: SQL Failed to Run Successfully

Overview

When you install the ShipperHQ extension in most cases it will run SQL as part of this installation.  If your frontend is displaying an error report after install it is likely either this SQL has not run successfully, or you have not copied files across correctly.
Most commonly the reasons for SQL not running are:
  1. Unique Index Too Long
  2. SQL has already been run on this server and is causing a conflict by attempting to run twice (this is very rare and only happens when database has been manually modified)
  3. The frontend was updated during installation, resulting in attempt to run SQL install/upgrade before files were present

You may see errors such as:

SQLSTATE[42S02]: Base Table Or View Not Found

Before doing anything please check:

  1. You have installed all files correctly (including Common/Logger)
  2. the report in var/report, which will give you a good indication of the problem

If you find that you have an attribute missing, or tables not found you are likely to have issue 3 above, which can be resolved by rewinding the core_resource.

Magento 1

Manually rewind core_resource

Take the following steps to resolve the issue. We assume here you are competent with phpmyadmin, if not then get a developer who is to assist, as doing this wrong can break your Magento installation.

  1. Take full db backup
  2. Find the database table ‘core_resource’
  3. Browse the table, looking for the entry causing the issue (e.g. shipperhq_shipper_setup)
  4. Remove that entry alone
  5. Ensure cache is switched off
  6. Refresh the frontend
  7. Check to see that install script has now run and created the missing tables and attributes

Script delete/rewind core_resource (Recommended)

Alternatively, we have a script which allows you to delete/rewide the core_resource entry. Please note, you will need the latest WSA Common installed for the script to run successfully.

Delete core_resource

This should only be done if its the first time you have installed this extension.

Disclaimer – Do not use this script unless you are confident that you know what you are doing. WebShopApps/ShipperHQ cannot be held responsible for the outcome.

  1. Take a full db backup
  2. Add the script file (setResource.php) to the base of your Magento install.
  3. Navigate to your base Magento url followed by ‘/setResource.php’ (eg. www.magento.com/setResource.php).
  4. Select the extension from the dropdown. This is in multiple parts (e.g.: “ShipperHQ Base”) so you may need to run each of them.
    If you cannot see your extension then you can add the core_resource code manually (eg. shipperhq_shipper_setup).
  5. Click on the ‘Show Current Resource Version’ button to continue.
  6. Now click on the ‘delete’ button to delete the row in core_resource that corresponds to your extension.
  7. Refresh the frontend.
  8. Ensure that the script has now run correctly – check that the attributes have been created.
  9. If there is an error pertaining to a different table go back to the setResource page and select the next module from the drop-down field.
  10. Remove the script file after use.

Rewind Resource

​Additionally the setResource contains a rewind script, which can be used to roll back to an earlier version and will force Magento to run the upgrade script for the SQL.

Do not use this script unless you are-confident that you know what you are doing. WebShopApps/ShipperHQ cannot be held responsible for the outcome.

  1. Take a full db backup
  2. ​Add the script file (setResource.php) to the base of your Magento install
  3. ​​Navigate to your base Magento url followed by ‘/setResource.php’ (eg. www.magento.com/setResource.php)
  4. Select the extension from the dropdown. If you cannot see the extension then you can add the core_resource code manually (eg. shipperhq_shipper_setup).
    If you cannot see the extension in the drop down list you need to look in app/code/community/shipperhq/module name/etc/config.xml. In this file you will want to search for resourceModel and note the first name in the middle and add and append _setup after the name. So for example <resourceModel>shipperhq_shipper_resource</resourceModel> you would be shipperhq_shipper_setup. You would then enter this into the other field
  5. Click on the ‘Show Current Resource Version’ button to continue
  6. Note the module version and check to see if it is set to the version before e.g current module version is 1.0.6 you would roll-back to 1.0.5
  7. Click rewind
  8. Refresh the frontend
  9. Ensure that the script has now run correctly – check that the attributes have been created
  10. Remove the script file after use

Magento 2

Take the following steps to resolve the issue. We assume here you are competent with phpmyadmin, if not then get a developer who is to assist, as doing this wrong can break your Magento installation.

  1. Take full db backup
  2. Find the database table ‘setup_module’
  3. Browse the table, looking for the entry causing the issue (e.g. shipperhq_shipper)
  4. Remove that entry alone
  5. From the command line run: php bin/magento setup:upgrade
  6. Refresh indexes, the compiler (if enabled) and cache (if enabled)
  7. Refresh the frontend
  8. Check to see that install script has now run and created the missing tables and attributes

Further Help

If problems remain:

  1. Identify the actual error. If just a report number is given then go find the report from var/reports directory under you Magento installation file system
  2. Disable extension, further information here
  3. Contact ShipperHQ Support with your order details, error report and problem seen for us to diagnose the issue