Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

update.sql -> MySQL-error


iTea

Recommended Posts

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...

Link to comment
Share on other sites

  • 2 weeks later...

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Unfortunately, your content contains terms that we do not allow. Please edit your content to remove the highlighted words below.
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...