BLOB/TEXT column 'products_options_values' can't have a default value ??

Hi there , thank you for making such a great software avaliable for free . however, i encountered an error during database installation.
i am using iis6 , mysql 5

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;]
any solution ? Thanks in advance

Hmmm ... hadn't ran into that one yet.

You'll have to edit the /zc_install/sql/mysql_zencart.sql file and search for "BLOB " and remove the default '' following it (leaving the , )

There are 2 BLOB references with default values, and 3 TEXT references with default values.

• customers_basket_attributes (BLOB)
• orders_products_attributes (BLOB)
• query_builder (TEXT x 3)

These will be remedied in the next release.

1101 BLOB/TEXT column 'query_description' can't have a default value
in:
[CREATE TABLE query_builder ( query_id int(11) NOT NULL auto_increment, query_category varchar(40) NOT NULL default '', query_name varchar(80) NOT NULL default '', query_description TEXT NOT NULL default '', query_string TEXT NOT NULL default '', query_keys_list TEXT NOT NULL default '', PRIMARY KEY (query_id), UNIQUE KEY query_name (query_name) ) TYPE=MyISAM COMMENT='Stores queries for re-use in Admin email and report modules';]

Hmm new error ?
i would appreciate if u could send me all the fixed sql files

Updated SQL file attached.

Hey thanks ! after using ur attached sql files
i am still getting
1364 Field 'query_keys_list' doesn't have a default value
in:
[INSERT INTO query_builder ( query_id , query_category , query_name , query_description , query_string ) VALUES ( '1', 'email', 'All Customers', 'Returns all customers name and email address for sending mass emails (ie: for newsletters, coupons, GV's, messages, etc).', 'select customers_email_address, customers_firstname, customers_lastname from TABLE_CUSTOMERS order by customers_lastname, customers_firstname, customers_email_address');]

hmm is it because of mysql5 version lol ?

I've updated the attachment in my previous post.

quantumfusion:

hmm is it because of mysql5 version lol ?

Yes, the initial problem you reported is related to MySQL5.

Woot thanks a lot !! still a newbie to this software lol

I was also facing same problem.

thanx for the help.
zencart is working now.

Good work! I was going to pass on my SQL alterations but that has already been done.

xplorer:

I was also facing same problem.

thanx for the help.
zencart is working now.

And another soul rescued here, thanks! :smile:

Just want to share, I had the same going from 1.3.0.2 --> 1.3.5 in the route to 1.3.7

It started with
1101 BLOB/TEXT column 'query_description' can't have a default value
in:
[ALTER TABLE zen_dog_query_builder CHANGE COLUMN query_description query_description TEXT NOT NULL default '';]

when upgrading dB from 1.3.0.2 to 1.3.5.

After a number of delete database and reimport, I found that removing default '' in the following lines worked.

ALTER TABLE query_builder CHANGE COLUMN query_description query_description TEXT NOT NULL default '';

ALTER TABLE query_builder CHANGE COLUMN query_string query_string TEXT NOT NULL default '';

ALTER TABLE query_builder CHANGE COLUMN query_keys_list query_keys_list TEXT NOT NULL default '';

DrByte:

Hmmm ... hadn't ran into that one yet.

You'll have to edit the /zc_install/sql/mysql_zencart.sql file and search for "BLOB " and remove the default '' following it (leaving the , )

There are 2 BLOB references with default values, and 3 TEXT references with default values.

• customers_basket_attributes (BLOB)
• orders_products_attributes (BLOB)
• query_builder (TEXT x 3)

These will be remedied in the next release.

This issue still exists in the 1302-to-135.sql file for the query_builder fields.

Mark

mriffey:

This issue still exists in the 1302-to-135.sql file for the query_builder fields.

Mark

Correct - it's fixed in the upcoming v1.3.8 fileset.

Updating 1.37 to 1.38 (mysql_upgrade_zencart_137_to_138.sql) getting the same error.

ERROR 1364 (HY000) at line 62: Field 'query_keys_list' doesn't have a default value

smap:

Updating 1.37 to 1.38 (mysql_upgrade_zencart_137_to_138.sql) getting the same error.

ERROR 1364 (HY000) at line 62: Field 'query_keys_list' doesn't have a default value

Hmm, sorry upon 2nd look I'm not sure this is related.

My live/server version of MySQL is 4.1.22. My dev/local version of MySQL is 5.0.27 (where the upgrade/error was occurring). I reconfigured the dev instance and unchecked 'strict mode' and this error went away.