Installation problem

Support
#1

Hi everyone,

I have a problem about installation. I got below message during installation.

A Database Error Occurred
Error Number: 1364
Field 'family_id' doesn't have a default value

# 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;

Filename: /home/midc/public_html/musteri/modules/setup/models/mdl_setup.php
Line Number: 102

How can I solve this problem ? Thank you…

1 Like
#2

First of all: read this article about the MySQL strict mode which leads to this error.

Then replace

CREATE TABLE IF NOT EXISTS `ip_products` (
  `product_id` int(11) NOT NULL AUTO_INCREMENT,
  `family_id` int(11) NOT NULL,
  `product_sku` varchar(15) NOT NULL,
  `product_name` varchar(50) NOT NULL,
  `product_description` longtext NOT NULL,
  `product_price` float(10,2) NOT NULL,
  `purchase_price` float(10,2) NOT NULL,
  `tax_rate_id` int(11) NOT NULL,
  PRIMARY KEY (`product_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

with

CREATE TABLE IF NOT EXISTS `ip_products` (
  `product_id` int(11) NOT NULL AUTO_INCREMENT,
  `family_id` int(11) NOT NULL DEFAULT '',
  `product_sku` varchar(15) NOT NULL DEFAULT '',
  `product_name` varchar(50) NOT NULL,
  `product_description` longtext NOT NULL,
  `product_price` float(10,2) NOT NULL,
  `purchase_price` float(10,2) NOT NULL DEFAULT '',
  `tax_rate_id` int(11) NOT NULL DEFAULT '',
  PRIMARY KEY (`product_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

and re-run the setup.
This will solve this problem even if it’s not recommended at the moment.

InvoicePlane IIS Install Guid
#3

Hi,

I turned off MySQL strict mode and I replaced sql code in 006_1.2.0.sql
but I got error message again.

A Database Error Occurred
Error Number: 1067
Invalid default value for 'family_id'

# Module "products" CREATE TABLE IF NOT EXISTS `ip_products` ( `product_id` int(11) NOT NULL AUTO_INCREMENT, `family_id` int(11) NOT NULL DEFAULT '', `product_sku` varchar(15) NOT NULL DEFAULT '', `product_name` varchar(50) NOT NULL, `product_description` longtext NOT NULL, `product_price` float(10,2) NOT NULL, `purchase_price` float(10,2) NOT NULL DEFAULT '', `tax_rate_id` int(11) NOT NULL DEFAULT '', PRIMARY KEY (`product_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Filename: /home/midc/public_html/musteri/modules/setup/models/mdl_setup.php
Line Number: 102
#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
#5

[size=10]Last Update 30.04.2015[/size]

#6