update.sql -> MySQL-error

[♥iTea 3]

Is Multi Stores compatible with MySQL 5? I get this error again and again when I run update.sql in phpMyAdmin 2.7.0-pl1. I used a fresh local osC-installation, Multi-Stores 1.7, php 4.4.1.

 

###########################
# //rmh M-S_multi-stores  #
###########################

CREATE TABLE administrators (
 administrators_id int(11) NOT NULL auto_increment,
 administrators_distributors_id int(11) NOT NULL DEFAULT '0',
 administrators_username varchar(20) NOT NULL DEFAULT '',
 administrators_password varchar(40) NOT NULL DEFAULT '',
 administrators_allowed_pages varchar(255) NOT NULL DEFAULT '',
 administrators_allowed_stores varchar(255) NOT NULL DEFAULT '',
 administrators_selected_stores_id int(11) NOT NULL DEFAULT '0',
 PRIMARY KEY  (administrators_id),
 UNIQUE KEY administrators_username (administrators_username)
);

INSERT INTO `administrators` VALUES (1, 0, 'admin', '21232f297a57a5a743894a0e4a801fc3', '*', '*', 1);

CREATE TABLE categories_to_stores (
 categories_id int NOT NULL,
 stores_id int NOT NULL,
 PRIMARY KEY (categories_id,stores_id)
);

CREATE TABLE distributors (
 distributors_id int(11) NOT NULL auto_increment,
 distributors_name varchar(32) NOT NULL DEFAULT '',
 distributors_email_address varchar(96) DEFAULT NULL,
 date_added datetime DEFAULT NULL,
 last_modified datetime DEFAULT NULL,
 PRIMARY KEY  (distributors_id),
 KEY IDX_MANUFACTURERS_NAME (distributors_name)
);

CREATE TABLE languages_to_stores (
 languages_id int NOT NULL,
 stores_id int NOT NULL,
 PRIMARY KEY (languages_id,stores_id)
);

INSERT INTO languages_to_stores VALUES (1, 1);
INSERT INTO languages_to_stores VALUES (2, 1);
INSERT INTO languages_to_stores VALUES (3, 1);

CREATE TABLE manufacturers_to_stores (
 manufacturers_id int NOT NULL,
 stores_id int NOT NULL,
 PRIMARY KEY (manufacturers_id,stores_id)
);

CREATE TABLE products_to_stores (
 products_id int NOT NULL,
 stores_id int NOT NULL,
 PRIMARY KEY (products_id,stores_id)
);

CREATE TABLE stores (
  stores_id int NOT NULL auto_increment,
  stores_name varchar(64) NOT NULL DEFAULT '',
  stores_image varchar(64),
  stores_url varchar(255) DEFAULT NULL,
  stores_config_table varchar(64) NOT NULL,
  stores_status tinyint(1) NOT NULL,
  date_added datetime,
  last_modified datetime,
  PRIMARY KEY (stores_id),
  UNIQUE (stores_config_table)
);

INSERT INTO stores (stores_id, stores_name, stores_image, stores_url, stores_config_table, stores_status, date_added) VALUES ('1', 'Default', 'oscommerce.gif', 'http://localhost/catalog/', 'configuration', '1', '2006-01-23');

ALTER TABLE categories ADD distributors_id int DEFAULT '0' NOT NULL;

ALTER TABLE customers_basket ADD customers_basket_stores_id int NOT NULL AFTER customers_basket_date_added;

ALTER TABLE customers_basket_attributes ADD customers_basket_stores_id int NOT NULL AFTER customers_id;

ALTER TABLE customers_info ADD customers_info_stores_id int AFTER customers_info_date_account_last_modified;

ALTER TABLE orders ADD orders_stores_id int DEFAULT '1' NOT NULL AFTER orders_date_finished;

ALTER TABLE orders_products ADD products_distributors_id int DEFAULT '0' NOT NULL AFTER products_quantity;

ALTER TABLE products ADD distributors_id int DEFAULT '0' NOT NULL AFTER manufacturers_id;

ALTER TABLE specials ADD stores_id int DEFAULT '0' NOT NULL AFTER status;

INSERT INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, date_added) VALUES ('Stores ID', 'STORES_ID', '1', 'The id of my store', '6', '0', '2006-01-23');

INSERT INTO configuration_group (configuration_group_id, configuration_group_title, configuration_group_description, sort_order, visible) VALUES ('16', 'Multi-Stores', 'Multi-Stores Options', '16', '1');

INSERT INTO configuration VALUES ('', 'Store Catalog Website URL', 'HTTP_CATALOG_SERVER', '', 'http://localhost/catalog', 16, 1, '2006-01-23', '2006-01-23', NULL, NULL);
INSERT INTO configuration VALUES ('', 'Store Catalog Website SSL URL', 'HTTPS_CATALOG_SERVER', '', '', 16, 2, '2006-01-23', '2006-01-23', NULL, NULL);
INSERT INTO configuration VALUES ('', 'Enable SSL Store Catalog', 'ENABLE_SSL_CATALOG', 'false', 'Enable SSL links for Store Catalog', 16, 3, '2006-01-23', '2006-01-23', NULL, 'tep_cfg_select_option(array(\'true\', \'false\'),');
INSERT INTO configuration VALUES ('', 'Store Catalog Website Path', 'DIR_WS_CATALOG', '', '/catalog', 16, 4, '2006-01-23', '2006-01-23', NULL, NULL);
INSERT INTO configuration VALUES ('', 'Store Catalog Filesystem Path', 'DIR_FS_CATALOG', '', 'F:\WWW\catalog', 16, 5, '2006-01-23', '2006-01-23', NULL, NULL);
INSERT INTO configuration VALUES ('', 'Store Catalog Website Images Path', 'DIR_WS_CATALOG_IMAGES', '', 'images/)', 16, 6, '2006-01-23', '2006-01-23', NULL, NULL);
INSERT INTO configuration VALUES ('', 'Store Catalog Website Languages Path', 'DIR_WS_CATALOG_LANGUAGES', '', 'includes/languages/', 16, 7, '2006-01-23', '2006-01-23', NULL, NULL);
INSERT INTO configuration VALUES ('', 'Store Catalog Filesystem Languages Path', 'DIR_FS_CATALOG_LANGUAGES', '', 'F:\WWW\catalog\includes\languages\', 16, 8, '2006-01-23', '2006-01-23', NULL, NULL);
INSERT INTO configuration VALUES ('', 'Store Catalog Filesystem Images Path', 'DIR_FS_CATALOG_IMAGES', '', 'F:\WWW\catalog\images\', 16, 9, '2006-01-23', '2006-01-23', NULL, NULL);
INSERT INTO configuration VALUES ('', 'Store Catalog Filesystem Modules Path', 'DIR_FS_CATALOG_MODULES', '', 'F:\WWW\catalog\includes\modules\', 16, 10, '2006-01-23', '2006-01-23', NULL, NULL);


###########################
# //rmh M-S_pricing	   #
###########################

CREATE TABLE customers_groups (
 customers_groups_id int NOT NULL auto_increment,
 customers_groups_name varchar(32) NOT NULL DEFAULT '',
 PRIMARY KEY  (customers_groups_id)
);

INSERT INTO customers_groups (customers_groups_id, customers_groups_name) VALUES ('1', 'Retail');
INSERT INTO customers_groups (customers_groups_id, customers_groups_name) VALUES ('2', 'Wholesale');

CREATE TABLE products_price_schedules (
 products_price_schedules_id int NOT NULL auto_increment,
 products_id int DEFAULT '0' NOT NULL,
 customers_groups_id int DEFAULT '0' NOT NULL,
 products_groups_price decimal(15,4) DEFAULT '0.0000' NOT NULL,
 products_groups_price_qty int(11) DEFAULT '0' NOT NULL,
 stores_id int DEFAULT '0' NOT NULL,
 PRIMARY KEY  (products_price_schedules_id)
);

ALTER TABLE customers ADD customers_groups_id int DEFAULT '1' NOT NULL AFTER customers_id;
ALTER TABLE customers ADD customers_tax_exempt char(1) DEFAULT '0' NOT NULL AFTER customers_newsletter;

ALTER TABLE orders ADD customers_tax_exempt char(1) DEFAULT '0' NOT NULL AFTER customers_address_format_id;

ALTER TABLE products ADD products_qty_blocks int DEFAULT '1' NOT NULL AFTER products_price;

INSERT INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, use_function, set_function, date_added) VALUES ('Default Customer Group', 'DEFAULT_CUSTOMER_GROUP', '1', 'The Customer Group a new member is assigned to', '5', '7', 'tep_cfg_get_customer_group', 'tep_cfg_pull_down_cg_list(', '2006-01-23');
INSERT INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, use_function, set_function, date_added) VALUES ('Visitor Pricing Group', 'VISITOR_PRICING_GROUP', '0', 'The Customer Group pricing for guests (Hide Prices = Must login to see pricing & default = Default osCommerce)', '5', '8', 'tep_cfg_get_customer_group', 'tep_cfg_pull_down_vg_list(', '2006-01-23');


###########################
# //rmh M-S_fixes		 #
###########################

INSERT INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, date_added) VALUES ('New Products', 'MIN_DISPLAY_NEWPRODUCTS', '1', 'Minimum number of products to display in the \'New Products\' box', '2', '18', '2006-01-23');

ALTER TABLE whos_online CHANGE last_page_url last_page_url VARCHAR(255) NOT NULL default '';

 

This is what phpMyAdmin reports:

 

SQL-query: 

INSERT INTO configuration
VALUES ('', 'Store Catalog Website URL', 'HTTP_CATALOG_SERVER', '', 'http://localhost/catalog', 16, 1, '2006-01-23', '2006-01-23', NULL , NULL);

MySQL retourneerde:  

#1264 - Out of range value adjusted for column 'configuration_id' at row 1

 

Can my MySQL-settings be wrong? It must be something smal I'm not seeing...

[Cosmic 0]

Yes I get this same error too. I am a complete osCommerce 'noob' at the moment though, so I might also be overlooking something simple ? Hope someone more knowledgeable answers your post soon.

 

Kind Regards

 

Cosmic.

[aragorn108 0]

Could successfully install the Multi-Stores contrib (1730) on a fresh original osC 2.2ms2 (not the 051113 update) installation using MySQL 5.0.18, PHP 5.0.3, Apache 2.0.53, phpMyAdmin 2.7.0-pl2. No problems executing the update.sql script. But when clicking a category in the catalog, products would not display with an SQL error. After moving back to MySQL 4.4.10a products display nicely.

 

While testing the MS contrib, seemingly found a bug :o : in Admin, when changing customer info, Customer Group setting is not remembered. Hope to fix the bug soon, though new to PHP, and little knowledgeable about osC, but with background as C++/Java developer.

 

Cheers :rolleyes: ,

aragorn108

[aragorn108 0]

Please see this post for suggested fix to Customer Group problem.

 

Kindly,

aragorn108