Upgrading older ver. to 2.2ms1 SOLVED (with pain)

[syscon]

Upgrading from older version of oSCommerce to ver. 2.2ms1 (long post).

Here are some notes during my recent successful upgrade (not without a pain though).

The problem with the upgrade is different structure of database old one and new one and it is almost impossible to fix it manually. So use the program that came with the oscommerce to compare two databases.

 

The program is a perl script called "mysqldiff.pl" in directory:

/oscommerce-2.2ms1/extras/mysql_diff/

 

The instructions are detailed, using Linux

 

First make a backup of your osCommerce database:

Administration - Tools - Database backup

Make a backup using NO Compression - pure SQL, as this is a text file.

Go to that directory and copy that file to a different folder and rename it to backup_copy.sql

Now, backup the whole directory of your ecommerce server (just in case something goes wrong).

 

1.) Install new copy of osCommerce ver.2.2ms1 or newer. (I've used ver. 2.2ms1), including the sample of database that came with oSC "oscommerce.sql". We need the structure of this database and some information that come with it.

 

2.) Upload your database "backup_copy.sql" into mysql. You need to create empty database first call it changed_copy, use phpMyAdmin.

Upload the database:

mysql changed_copy -uuser -password < backup_copy.sql

 

3.) Now we need to compare our database "backup_copy.sql" to "oscommerce.sql". Use program that perl program that came with the oscommerce.

The syntax is:

./mysqldiff.pl backup_copy.sql oscommerce.sql > alter.txt

 

This will output file called "alter.txt", print this file.

Open browser, Mozilla will let you open many tabs, it will make your work easier (you can copy from one window to another (you reduce chance of making a mistake), in addition you can copmare the the database so you will know where to make changes.

Go through the list and make changes.

 

Now backup the database you have changed "changed_copy", use syntax

mysqldump -uuser -ppassword --opt changed_copy > working_copy.sql

 

Compare again the database you have changed to the original one:

./mysqldiff.pl changed_copy.sql oscommerce.sql > alter2.txt

 

Open alter.txt to make sure it is empty. If it is, it means you have done all the changes, Good Job!

 

Open the "working_copy.sql" database, it is a plain text file (use text editor, not a word processing program).

 

Remove all unnecessary information from this database, retain only the information you have input or collected from customers. For example: address_book, orders etc. You do not need information like configuration, zone table etc.

Remove all that information from the database.

 

Here is an example what you should keep. The instruction tells the database to drop table that is in database and replace with yours.

------------

DROP TABLE IF EXISTS address_book;

CREATE TABLE address_book (

customers_id int(11) NOT NULL default '0',

address_book_id int(11) NOT NULL default '1',

entry_gender char(1) NOT NULL default '',

entry_company varchar(32) default NULL,

entry_firstname varchar(32) NOT NULL default '',

entry_lastname varchar(32) NOT NULL default '',

entry_street_address varchar(64) NOT NULL default '',

entry_suburb varchar(32) default NULL,

entry_postcode varchar(10) NOT NULL default '',

entry_city varchar(32) NOT NULL default '',

entry_state varchar(32) default NULL,

entry_country_id int(11) NOT NULL default '0',

entry_zone_id int(11) NOT NULL default '0',

PRIMARY KEY (address_book_id,customers_id)

) TYPE=MyISAM;

 

--

-- Dumping data for table 'address_book'

--

 

/*!40000 ALTER TABLE address_book DISABLE KEYS */;

LOCK TABLES address_book WRITE;

INSERT INTO address_book VALUES xxxx removed xxxxxxxxxxxxxxxxxxxxxxx

xxxxxxxxxxxxxxxxxxxxxxxxxxxx

...

...

UNLOCK TABLES;

/*!40000 ALTER TABLE address_book ENABLE KEYS */;

------------

 

Save it as: new_upload.sql. If you deleted something that you are not supposed to, go back and re-do it.

 

The reason you need to remove all unnecessary table is that the oscommerce.sql you uploaded during installation contain all this information and some new entries into configuration menu that will be overwritten by your tables if you don't remove them.

For example I was missing two menu option from Administration - Configuration option information in my old database did not contain those entries. So when I uploaded my database, my old configuration was overwriting the existing one (new one), that is why some people are seing some strange problems when they try to check it out.

For example if you upgraded to osc ver. 2.2ms1 and your Administration - Configuration menu has only 11 entries, it means you are missing two entries, there should be 13 entries (there is one hidden, so total would be 14 including hidden one.

That is why you need to strip your database from all configurations so it will not overwrite the new one.

 

Good luck.

Joseph