[ic] Matrix options and set_row possible bug?

Jon Jensen jon at endpoint.com
Thu Jun 22 12:03:11 EDT 2006

On Wed, 21 Jun 2006, Peter wrote:

> Ok, I've been having problems with trying to use Matrix options on my IC 5.2 
> core/4.9 foundation based catalogs (with a fair bit of customization). 
> Anyways, I'll list the symptoms of the problem first, then show you what else 
> I've discovered in my attempts at solving the problem...
> I can change a given item to Matrix Options but can't generate the variants 
> with the "Create all possible combinations" button in the UI. Also items that 
> are set to matrix options cannot be added to a shopping cart (this last 
> symptom may just be because there are no variants for the item and so IC 
> can't find a varyant to add to the cart).
> Checking the error log I get the following when I click on the "Create all 
> possible combinations" button:
> jqJXiIfA: - [21/June/2006:18:38:03 -0700] mr-s 
> /cgi-bin/mr-s/admin/item_option_old.html Safe: DBD::Pg::st execute failed: 
> ERROR:  null value in
> column "sku" violates not-null constraint
> ...followed by a long block of code which is the [perl] block contained in 
> the [if cgi explode] block in the file 
> include/Options/old/item_option_matrix.
> If I then click on the "Create all possible combinations" button again I get 
> no errors at all and the item gets set back to simple options.
> So I turned debugging on, added in/uncommented a number of Debug() and 
> ::logDebug() lines and eventually narrowed the problem down to the 
> Vend::Table::DBI::set_row sub.  I uncommented all the ::logDebug() lines in 
> the set_row sub and got the following output in my debug log (when I click on 
> "Create all possible combinations"):
> Vend::Table::DBI:debug: set_row fields='HT148-NoChoice-NoChoice,,,,,,,,,'
> Vend::Table::DBI:debug: set_row query=INSERT INTO options VALUES (?, ?, ?, ?, 
> ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
> Vend::Table::DBI:debug: set_row fields='HT148-NoChoice-NoChoice,,,,,,,,,'
> Safe: DBD::Pg::st execute failed: ERROR:  null value in column "sku" violates 
> not-null constraint
> ...same long block of code as before here.
> From what I can tell, there is just one field in the @fields array which 
> marks all the rest as undefined and DBI interprets the undefined value as 
> NULL, so DBI is setting all those ? placeholders (barring the first) to NULL 
> in an insert statement.  This clashes with the NOT NULL field constraints in 
> the options table.
> I realise that I can probably work around this by removing the NOT NULL 
> constraints from the fields, but I'd really rather fix this problem properly. 
> Unfortunately it looks like this may be a problem (bug?) in the set_row sub 
> and it will require a significant rewrite to fix it.  I simply do not know 
> the code well enough to be able to rewrite it without worrying about breaking 
> some other compatibility elsewhere.


It is a known deficiency in some Interchange database routines that when a 
new row is created, it's first created with only the primary key and the 
remaining fields NULL, and then updated to populate the fields. Obviously 
quite bad if you're using a well-designed database that doesn't allow NULL 
all over the place.

I can't remember if that has been fixed in newer versions of Interchange 
yet or not, but it's worth taking a look at the latest set_row and any 
dependent routines in CVS.

I will also note that it's pretty pointless to try to keep a tight data 
model in the options table when you're using the old 4.8-style options, 
because the table is not even remotely normalized: There are essentially 
three different kinds of rows crammed into the same table, and it just 
isn't a sane data model.

So you'd might as well just consider dropping the NOT NULL constraints on 
options, as the table is fairly messy anyway. If you want to do things a 
better way, look at the newer options introduced in (I think) Interchange 
5.2, using a separate variants table.


Jon Jensen
End Point Corporation

More information about the interchange-users mailing list