1101 BLOB/TEXT column can't have a default value

[tkroh]

Installation of ZenCart 1.3.6 on WinXPSP2, IIS 5.1, PHP 5.2.0, MySQL 5.0 resulted in:

Installation In Progress...

~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
1101 BLOB/TEXT column 'products_options_values' can't have a default value
in:
[CREATE TABLE orders_products_attributes ( orders_products_attributes_id int(11) NOT NULL auto_increment, orders_id int(11) NOT NULL default '0', orders_products_id int(11) NOT NULL default '0', products_options varchar(32) NOT NULL default '', products_options_values BLOB NOT NULL default '', options_values_price decimal(15,4) NOT NULL default '0.0000', price_prefix char(1) NOT NULL default '', product_attribute_is_free tinyint(1) NOT NULL default '0', products_attributes_weight float NOT NULL default '0', products_attributes_weight_prefix char(1) NOT NULL default '', attributes_discounted tinyint(1) NOT NULL default '1', attributes_price_base_included tinyint(1) NOT NULL default '1', attributes_price_onetime decimal(15,4) NOT NULL default '0.0000', attributes_price_factor decimal(15,4) NOT NULL default '0.0000', attributes_price_factor_offset decimal(15,4) NOT NULL default '0.0000', attributes_price_factor_onetime decimal(15,4) NOT NULL default '0.0000', attributes_price_factor_onetime_offset decimal(15,4) NOT NULL default '0.0000', attributes_qty_prices text, attributes_qty_prices_onetime text, attributes_price_words decimal(15,4) NOT NULL default '0.0000', attributes_price_words_free int(4) NOT NULL default '0', attributes_price_letters decimal(15,4) NOT NULL default '0.0000', attributes_price_letters_free int(4) NOT NULL default '0', products_options_id int(11) NOT NULL default '0', products_options_values_id int(11) NOT NULL default '0', products_prid tinytext NOT NULL, PRIMARY KEY (orders_products_attributes_id), KEY idx_orders_id_prod_id_zen (orders_id,orders_products_id) ) TYPE=MyISAM;]

According to MySQL docs:

Error: 1101 SQLSTATE: 42000 (ER_BLOB_CANT_HAVE_DEFAULT)
Message: BLOB/TEXT column '%s' can't have a default value

Other fields which have the same bug:

query_builder : query_description
query_builder : query_string
query_builder : query_keys_list

Removing

 default ''

from the relevant field definitions in ./zc_install/sql/mysql_zencart.sql seems to solve the problem.

[tkroh]

Removing default '' from the relevant field definitions in ./zc_install/sql/mysql_zencart.sql seems to solve the problem.

Forgot to add:
...but this causes problems because parts of the system don't set values for those NOT NULL fields (they assume a default value exists).

I don't know if this has always been like this, or if it's a new restriction with MySQL 5.

[DrByte]

You're right Travis.

There were a few adjustments required to fix this fully, most of which are discussed here:
http://www.zen-cart.com/forum/showthread.php?t=50340

It's also fixed in v1.3.7, announced here:
http://www.zen-cart.com/forum/showthread.php?t=54424

[tkroh]

Well, don't I feel silly. I did search to see if this had previously been covered, but apparently my searching skills aren't quite up to snuff. :)