Upgrade from 1.6 pr21_to_pr22 missing

[jlgdeveloper]

Hi All

 

I am upgrading an Oscommerce installation to the latest.

 

I assume the version is 1.X as the files generally have 1.x in their comments: index.php,v 1.1 2003/06/11

 

I have downloaded the latest release from here (44): http://www.oscommerce.com/solutions/downloads

I have followed the instructions from here: http://www.oscommerce.info/kb/221

 

Here are the issues:

 

A>

Instruction: Next, copy the old database over to the new database by using a tool such as phpMyAdmin or similar utility.

 

I have taken this to mean that I should restore the old database over the new...which is what I done. Is that incorrect?

 

B>

The download does not support the following instructions:

Run Update File

In the "extras" folder in the osCommerce download and there is a folder named pr21_to_pr22, which holds the file ms1_to_ms2.php. Copy this file to your catalog folder or root folder on your server space where the new osCommerce files are. It can be renamed if desired, such as to 'upgrade.php'.

 

My extras folder only contains the following: button_template_admin.psd button_template.psd tep_database-pr2.2-CVS.pdf update-20060817.txt upgrade-22rc1.html

 

I did read the update file, but it is clearly related to folks who already are at 2.2.

 

I found and extracted RC1, but no joy there either.

 

 

A topic on the forum suggested:

The easiest method might be to do a fresh install, and to "then import the product and customer data".

 

Can I export the product and customer data from the old and then pull it into the new? Would you have a link for direction on this? My customer as mentioned an xl file method...csv text file..whatever.

 

Or, how would I transfer the relevant data from the old database to the new without bolloxing up the new?

 

So in summary, I would be grateful for solid directions on how to proceed.

 

Jonathan

[jlgdeveloper]

Bump.

Hi All

 

I am upgrading an Oscommerce installation to the latest.

 

I assume the version is 1.X as the files generally have 1.x in their comments: index.php,v 1.1 2003/06/11

 

I have downloaded the latest release from here (44): http://www.oscommerce.com/solutions/downloads

I have followed the instructions from here: http://www.oscommerce.info/kb/221

 

Here are the issues:

 

A>

Instruction: Next, copy the old database over to the new database by using a tool such as phpMyAdmin or similar utility.

 

I have taken this to mean that I should restore the old database over the new...which is what I done. Is that incorrect?

 

B>

The download does not support the following instructions:

Run Update File

In the "extras" folder in the osCommerce download and there is a folder named pr21_to_pr22, which holds the file ms1_to_ms2.php. Copy this file to your catalog folder or root folder on your server space where the new osCommerce files are. It can be renamed if desired, such as to 'upgrade.php'.

 

My extras folder only contains the following: button_template_admin.psd button_template.psd tep_database-pr2.2-CVS.pdf update-20060817.txt upgrade-22rc1.html

 

I did read the update file, but it is clearly related to folks who already are at 2.2.

 

I found and extracted RC1, but no joy there either.

A topic on the forum suggested:

The easiest method might be to do a fresh install, and to "then import the product and customer data".

 

Can I export the product and customer data from the old and then pull it into the new? Would you have a link for direction on this? My customer as mentioned an xl file method...csv text file..whatever.

 

Or, how would I transfer the relevant data from the old database to the new without bolloxing up the new?

 

So in summary, I would be grateful for solid directions on how to proceed.

 

Jonathan

[♥FWR Media]

Does the site have cPanel Jonathan?

 

Also .. do you have a local pc/test server?

[jlgdeveloper]

Does the site have cPanel Jonathan?

 

Also .. do you have a local pc/test server?

 

Hi Robert

 

No, none of the above as I am migrating it from one server to the other, and until all sorted out, upgraded and secured, the existing installation will be the primary.

 

It is my server, no hosting software yet.

 

I am considering just manually migrating the data, headers etc. Besides writing code to do it manually, I am looking at easy-populate (which the customer wants anyway).

 

Jonathan

[♥FWR Media]

Hi Robert

 

No, none of the above as I am migrating it from one server to the other, and until all sorted out, upgraded and secured, the existing installation will be the primary.

 

It is my server, no hosting software yet.

 

I am considering just manually migrating the data, headers etc. Besides writing code to do it manually, I am looking at easy-populate (which the customer wants anyway).

 

Jonathan

 

I would ..

 

1) Upload a fresh RC2a to the new host

 

2) Upload a FULL backup (not osC backup a MySQL backup including DROP TABLES) of the old site over the top of the new database (correcting errors to achieve this). This will leave the new tables of RC2a like administrators.

 

3) Run the MySQL upgrade for MS1 to MS2.2

 

4) Run the MySQL upgrade from MS2.2 to RC1

 

5) Run the MySQL upgrade from RC1 to RC2

 

6) Run the MySQL upgrade from RC2 to RC2a

 

Probably harder than it looks as you may well get errors to handle but if you can achieve this you will get the old site up and running on a brand new RC2a with database intact. You can then "just" remodify files.

 

If the client had contributions that modified tables by adding fields you will then have to identify and deal with these.

 

Just a thought.

 

Rob

[jlgdeveloper]

I would ..

 

1) Upload a fresh RC2a to the new host

 

2) Upload a FULL backup (not osC backup a MySQL backup including DROP TABLES) of the old site over the top of the new database (correcting errors to achieve this). This will leave the new tables of RC2a like administrators.

 

3) Run the MySQL upgrade for MS1 to MS2.2

 

4) Run the MySQL upgrade from MS2.2 to RC1

 

5) Run the MySQL upgrade from RC1 to RC2

 

6) Run the MySQL upgrade from RC2 to RC2a

 

Probably harder than it looks as you may well get errors to handle but if you can achieve this you will get the old site up and running on a brand new RC2a with database intact. You can then "just" remodify files.

 

Just a thought.

 

Rob

 

Hi Rob

 

I appreciate the advice. I will be following it! I just discovered that oyt of 7000 od products, there are 2716 similar product_model id's in the database, so easy-populate would not work ...yet.

 

My biggest challenge then will be to find the mysql updates, which I am beginning right now.

 

I'll post up as I progress.

 

Jonathan

[♥FWR Media]

Correction

 

Upload a fresh RC2a to the new host

 

Should have read ..

 

Upload a fresh RC2a to the new host .. AND INSTALL

 

Sorry.

[jlgdeveloper]

Correction

Should have read ..

 

Upload a fresh RC2a to the new host .. AND INSTALL

 

Sorry.

 

No issues there, I did start with a brand new install of RC2a. I blew away it's new database, and reverted back completely to the Version 1.6 database.

 

Now to one by one locate and execute the sql upgrades.

 

Thanks

[jlgdeveloper]

Ok, so here is the process I am following:

 

3) Run the MySQL upgrade for MS1 to MS2.2

 

I found the ms2.2 (MS = mile stone) file on sourceforge here: http://sourceforge.net/project/showfiles.php?group_id=31957

In the extra's folder I did indeed find a folder called pr21_to_pr22

In that folder is a file called ms1_to_ms2.php

I placed that folder into my website, and executed the ms1_to_ms2.php file.

I had to modify it each time it came to an error, and manually make a few changes to the database when it crashed.

 

Basically read the error message, read the code, figure out the complaint and move the to the next re-run of the script, commenting out all that had been completed successfully to date.

 

 

4) Run the MySQL upgrade from MS2.2 to RC1

 

I found the RC1 download here: http://www.oscommerce.com/redirect.php/go,41

In this file: extras/update-20060817.txt...........

 

...................................at the very end of this file, I found a note to perform the following database changes:

The following SQL queries need to be performed:

 

ALTER TABLE whos_online MODIFY COLUMN last_page_url VARCHAR(255) NOT NULL;

 

ALTER TABLE customers MODIFY COLUMN customers_default_address_id INTEGER;

 

ALTER TABLE customers_basket MODIFY COLUMN final_price DECIMAL(15,4);

 

 

 

5) Run the MySQL upgrade from RC1 to RC2

 

The upgrade procedure is found here: http://www.oscommerce.com/ext/upgrade-22rc2.html

 

The changes here to the database follow. Do them one at a time:

alter table banners add index idx_banners_group (banners_group); alter table banners_history add index idx_banners_history_banners_id (banners_id); alter table currencies add index idx_currencies_code (code); alter table customers add index idx_customers_email_address (customers_email_address); alter table customers_basket add index idx_customers_basket_customers_id (customers_id); alter table customers_basket_attributes add index idx_customers_basket_att_customers_id (customers_id); alter table orders add index idx_orders_customers_id (customers_id); alter table orders_products add index idx_orders_products_orders_id (orders_id); alter table orders_products add index idx_orders_products_products_id (products_id); alter table orders_status_history add index idx_orders_status_history_orders_id (orders_id); alter table orders_products_attributes add index idx_orders_products_att_orders_id (orders_id); alter table orders_products_download add index idx_orders_products_download_orders_id (orders_id); alter table products add index idx_products_model (products_model); alter table products_attributes add index idx_products_attributes_products_id (products_id); alter table reviews add index idx_reviews_products_id (products_id); alter table reviews add index idx_reviews_customers_id (customers_id); alter table specials add index idx_specials_products_id (products_id); alter table zones add index idx_zones_to_geo_zones_country_id (zone_country_id); alter table orders_status add public_flag int DEFAULT '1'; alter table orders_status add downloads_flag int DEFAULT '0'; alter table orders modify payment_method varchar(255) NOT NULL; alter table whos_online modify last_page_url text NOT NULL;

 

6) Run the MySQL upgrade from RC2 to RC2a

 

Could not find any sql statements here...strangely, the same files were here as are in the MS2.2 -> RC1 upgrade ........ has to be in error

 

 

At this point I am still figuring if this has done the trick, and next, how to re-do the files that provide the site integration, header etc

[jlgdeveloper]

Ok, I found that the administrators table was missing, so I added it:

 

CREATE TABLE administrators (
 id int NOT NULL auto_increment,
 user_name varchar(32) binary NOT NULL,
 user_password varchar(40) NOT NULL,
 PRIMARY KEY (id)
);

 

Do not add a login as the code encrypts it and that will not work (for the site /admin). The initial page in the admin will recognize that there is no registered administrator and will prompt you for a new login and password.

 

Looking at all the admin pages will point out quite a few site issues that need attention.

 

I managed to figure out all the customizations that integrated the catalog with the customers site.

 

New issue:

 

The catalog in general is about two times slower than the original site.

 

I double checked all the indexes:

 

I found this anomaly:

Index: idx_zones_to_geo_zones_country_id is supposed to be a part of the zones to geo zones table, but was part of the zones table. The name does not matter, but the missing index on zones to geo zones might.....so I added that.

 

DROP TABLE IF EXISTS zones;
CREATE TABLE zones (
 zone_id int NOT NULL auto_increment,
 zone_country_id int NOT NULL,
 zone_code varchar(32) NOT NULL,
 zone_name varchar(32) NOT NULL,
 PRIMARY KEY (zone_id),
 KEY idx_zones_country_id (zone_country_id)
);

DROP TABLE IF EXISTS zones_to_geo_zones;
CREATE TABLE zones_to_geo_zones (
  association_id int NOT NULL auto_increment,
  zone_country_id int NOT NULL,
  zone_id int NULL,
  geo_zone_id int NULL,
  last_modified datetime NULL,
  date_added datetime NOT NULL,
  PRIMARY KEY (association_id),
  KEY idx_zones_to_geo_zones_country_id (zone_country_id)
);

So in summary, the near last sql alter statements on the RC1 to RC2 upgrade should read:

 

Actual: alter table zones add index idx_zones_to_geo_zones_country_id (zone_country_id);

 

New:

alter table zones add index idx_zones_country_id (zone_country_id);

alter table zones_to_geo_zonesadd index idx_zones_to_geo_zones_country_id (zone_country_id);

 

That is not the reason for the slow performance.

 

Background:

The new server has 8 processors, is blazingly fast on other sites and this sites html and this sites admin.

I instituted mysql "slow query logging at over one second duration, and it did not report any sql queries as being that slow.

I have noticed that the cpu usage hits 95% or so during a page refresh of say the primary catalog page, with mysql being the majority of that cpu usage.

 

I installed mysqldiff, and pulled a diff report:

 

#
# MySQLDiff 1.5.0
#
# http://www.mysqldiff.org
# (c) 2001-2004, Lippe-Net Online-Service
#
# Create time: 24.02.2008 10:49
#
# --------------------------------------------------------
# Source info
# Host: localhost
# Database: xxxxx_catalog
# --------------------------------------------------------
# Target info
# Host: localhost
# Database: xxxxx_test
# --------------------------------------------------------
#

SET FOREIGN_KEY_CHECKS = 0;

#
# DDL START
#
ALTER TABLE address_book
ALTER customers_id SET DEFAULT '';


ALTER TABLE banners
ALTER date_added SET DEFAULT '';


ALTER TABLE banners_history
ALTER banners_id SET DEFAULT '',
ALTER banners_history_date SET DEFAULT '';


ALTER TABLE configuration
MODIFY configuration_title varchar(255) NOT NULL DEFAULT '' COMMENT '' COLLATE latin1_swedish_ci,
MODIFY configuration_key varchar(255) NOT NULL DEFAULT '' COMMENT '' COLLATE latin1_swedish_ci,
ALTER configuration_group_id SET DEFAULT '',
ALTER date_added SET DEFAULT '';
#
#  Fieldformats of
#	configuration.configuration_title changed from varchar(64) NOT NULL DEFAULT '' COMMENT '' COLLATE latin1_swedish_ci to varchar(255) NOT NULL DEFAULT '' COMMENT '' COLLATE latin1_swedish_ci.
#	configuration.configuration_key changed from varchar(64) NOT NULL DEFAULT '' COMMENT '' COLLATE latin1_swedish_ci to varchar(255) NOT NULL DEFAULT '' COMMENT '' COLLATE latin1_swedish_ci.
#  Possibly data modifications needed!
#

ALTER TABLE countries
ALTER address_format_id SET DEFAULT '';


ALTER TABLE customers_basket
ALTER customers_id SET DEFAULT '',
ALTER customers_basket_quantity SET DEFAULT '',
MODIFY final_price decimal(15,4) NULL DEFAULT NULL COMMENT '',
MODIFY customers_basket_date_added char(8) NULL DEFAULT NULL COMMENT '' COLLATE latin1_swedish_ci;
#
#  Fieldformats of
#	customers_basket.final_price changed from decimal(15,4) NOT NULL DEFAULT '0.0000' COMMENT '' to decimal(15,4) NULL DEFAULT NULL COMMENT ''.
#	customers_basket.customers_basket_date_added changed from varchar(8) NULL DEFAULT NULL COMMENT '' COLLATE latin1_swedish_ci to char(8) NULL DEFAULT NULL COMMENT '' COLLATE latin1_swedish_ci.
#  Possibly data modifications needed!
#

ALTER TABLE customers_basket_attributes
ALTER customers_id SET DEFAULT '',
ALTER products_options_id SET DEFAULT '',
ALTER products_options_value_id SET DEFAULT '';


ALTER TABLE customers_info
ALTER customers_info_id SET DEFAULT '';


ALTER TABLE geo_zones
ALTER date_added SET DEFAULT '';


ALTER TABLE manufacturers_info
ALTER manufacturers_id SET DEFAULT '',
ALTER languages_id SET DEFAULT '';


ALTER TABLE newsletters
ALTER date_added SET DEFAULT '';


ALTER TABLE orders
ALTER customers_id SET DEFAULT '',
ALTER customers_address_format_id SET DEFAULT '',
ALTER delivery_address_format_id SET DEFAULT '',
ALTER billing_address_format_id SET DEFAULT '',
ALTER orders_status SET DEFAULT '';


ALTER TABLE orders_products
ALTER orders_id SET DEFAULT '',
ALTER products_id SET DEFAULT '',
ALTER products_price SET DEFAULT '',
ALTER final_price SET DEFAULT '',
ALTER products_tax SET DEFAULT '',
ALTER products_quantity SET DEFAULT '';


ALTER TABLE orders_products_attributes
ALTER orders_id SET DEFAULT '',
ALTER orders_products_id SET DEFAULT '',
ALTER options_values_price SET DEFAULT '';


ALTER TABLE orders_status_history
ALTER orders_id SET DEFAULT '',
ALTER orders_status_id SET DEFAULT '',
ALTER date_added SET DEFAULT '';


ALTER TABLE orders_total
ALTER orders_id SET DEFAULT '',
ALTER value SET DEFAULT '',
ALTER sort_order SET DEFAULT '';


ALTER TABLE products
ALTER products_quantity SET DEFAULT '',
ALTER products_price SET DEFAULT '',
ALTER products_date_added SET DEFAULT '',
ALTER products_weight SET DEFAULT '',
ALTER products_status SET DEFAULT '',
ALTER products_tax_class_id SET DEFAULT '';


ALTER TABLE products_attributes
ALTER products_id SET DEFAULT '',
ALTER options_id SET DEFAULT '',
ALTER options_values_id SET DEFAULT '',
ALTER options_values_price SET DEFAULT '';


ALTER TABLE products_attributes_download
ALTER products_attributes_id SET DEFAULT '';


ALTER TABLE products_notifications
ALTER products_id SET DEFAULT '',
ALTER customers_id SET DEFAULT '',
ALTER date_added SET DEFAULT '';


ALTER TABLE products_options_values_to_products_options
ALTER products_options_id SET DEFAULT '',
ALTER products_options_values_id SET DEFAULT '';


ALTER TABLE products_to_categories
ALTER products_id SET DEFAULT '',
ALTER categories_id SET DEFAULT '';


ALTER TABLE reviews
ALTER products_id SET DEFAULT '';


ALTER TABLE reviews_description
ALTER reviews_id SET DEFAULT '',
ALTER languages_id SET DEFAULT '';


ALTER TABLE sessions
ALTER expiry SET DEFAULT '';


ALTER TABLE specials
ALTER products_id SET DEFAULT '',
ALTER specials_new_products_price SET DEFAULT '';


ALTER TABLE tax_class
ALTER date_added SET DEFAULT '';


ALTER TABLE tax_rates
ALTER tax_zone_id SET DEFAULT '',
ALTER tax_class_id SET DEFAULT '',
ALTER tax_rate SET DEFAULT '',
ALTER date_added SET DEFAULT '';


ALTER TABLE zones
ALTER zone_country_id SET DEFAULT '';


ALTER TABLE zones_to_geo_zones
ALTER zone_country_id SET DEFAULT '',
ALTER date_added SET DEFAULT '';


#
# DDL END
#

SET FOREIGN_KEY_CHECKS = 1;

Thoughts

 

Mostly, this refelects missing default values in the "test" database that was created from scratch using the RC2a release.

To me the defaults are intergral to new data, so I do not want to remove them. It has got to be an error!

The presence of the defaults cannot in themselves be a reason for the slow response.

 

The only changes possibly worth doing are these, excluding removing the defaults (did the first 2, and the last one):

 

#

# Fieldformats of

# configuration.configuration_title changed from varchar(64) NOT NULL DEFAULT '' COMMENT '' COLLATE latin1_swedish_ci to varchar(255) NOT NULL DEFAULT '' COMMENT '' COLLATE latin1_swedish_ci.

# configuration.configuration_key changed from varchar(64) NOT NULL DEFAULT '' COMMENT '' COLLATE latin1_swedish_ci to varchar(255) NOT NULL DEFAULT '' COMMENT '' COLLATE latin1_swedish_ci.

# Possibly data modifications needed!

#

#

# Fieldformats of

# customers_basket.final_price changed from decimal(15,4) NOT NULL DEFAULT '0.0000' COMMENT '' to decimal(15,4) NULL DEFAULT NULL COMMENT ''.

# customers_basket.customers_basket_date_added changed from varchar(8) NULL DEFAULT NULL COMMENT '' COLLATE latin1_swedish_ci to char(8) NULL DEFAULT NULL COMMENT '' COLLATE latin1_swedish_ci.

# Possibly data modifications needed!

#

 

I visited other database oriented websites that do some pulling, and found that the mysql daemon only hit 3-5% of the machine cpu.

 

This site has 7132 products with products and products descriptions having index lengths of 277 and 431 kb.

 

Next, I repaired the products and products description tables.................................no joy.

 

Any ideas folks?

[jlgdeveloper]

Ok, the results are in.

 

I have figured it out.

 

I got a hint here:

 

http://www.oscbooks.com/oscommerce-ebooks/..._oscommerce.php

 

Basically, turn cashing on in the admin!

 

................

 

If I did it again, I might be tempted to create the new database, and then do a diff against the existing one.

 

I would have to be careful to exclude all those incorrect defaults.

 

Have a good day folks!

 

Thanks to Robert in the UK for all your help.

 

Jonathan

[♥FWR Media]

Ok, the results are in.

 

I have figured it out.

 

I got a hint here:

 

http://www.oscbooks.com/oscommerce-ebooks/..._oscommerce.php

 

Basically, turn cashing on in the admin!

 

................

 

If I did it again, I might be tempted to create the new database, and then do a diff against the existing one.

 

I would have to be careful to exclude all those incorrect defaults.

 

Have a good day folks!

 

Thanks to Robert in the UK for all your help.

 

Jonathan

 

Glad you got it going .. nice work :thumbsup: