Installation problem

Support
4

Hi Shase, Kovah

I had the exact same error when downloading and installing v1.3.1 today.

This is not a MySQL strict-mode problem. The referenced article mentions a different error. The update SQL script at /application/modules/setup/sql/006_1.2.0.sql is trying to do something unnatural, considering the database schema.

The fix suggested above adds default values to the ip_products table definition. But it adds a string value to an INT (integer) column, which is why Shase is getting the second error.

Problem
Version 1.2.0 adds the ip_products table, and then attempts to migrate existing data from ip_item_lookups into the new table. But some required columns are missing, which causes the new table to moan about not having all its required fields. A reasonable solution is to change the SQL query that migrates the data into ip_products to include defaults.

Fix
In the /application/modules/setup/sql/006_1.2.0.sql file, change this (line 51):

# Move lookup items to products
INSERT INTO ip_products (product_name, product_description, product_price)
  SELECT
    item_name,
    item_description,
    item_price
  FROM ip_item_lookups;

To this:

# Move lookup items to products
INSERT INTO ip_products (
  family_id,
  product_sku,
  product_name,
  product_description,
  product_price,
  purchase_price,
  tax_rate_id
  )
  SELECT
   0 as family_id, -- default to 0 (no family)
   concat('sku-',item_lookup_id) as product_sku, -- use ip_item_lookup primary key as new SKU
   item_name,
   item_description,
   item_price,
   0 as product_purchase_price, -- default purchase price to 0
   0 as tax_rate_id -- default tax rate ID
  FROM ip_item_lookups;

What this achieves is the following:

  • For new installations, no data will be migrated since the table ip_item_lookups will be empty.
  • For upgrades, existing data in table ip_item_lookups will be moved into table ip_products, but with some added columns needed to the new table.

I tested the fix with a new installation, as well as with existing data in the ip_item_lookups table.

Anyone who downloads v1.3.1 will experience this same error. It is non-trivial to resolve without SQL knowledge.Therefore, I suggest fixing this in the current release.

Hope that helps.

2 Likes