Upgrade database 2.2 > 2.3.1

[NEWPRob 1]

I've got a copy of my old database, but thanks to the recent hacking of a ton of osc sites mine got wiped out! So, rather than reinstall a hackable 2.2 I'm upgrading to 2.3.1

 

So, like half the other folks that I've read about, I installed 2.3.1 and imported my old database. Some things work and some don't. I gather I have to do this differently. Has anyone done this successfully? I'd rather not have to enter every product by hand again, not to mention losing my customer list and order history.

 

I found the thread below, but don't want to waste my time screwing things up even more. Should I simply import the stuff that isn't red or do I have to run some crazy sql commands like in the 'upgrade' guide that came with 2.3.1?

 

http://forums.oscommerce.com/topic/367429-download-230/page__view__findpost__p__1548738

[♥FIMBLE 82]

its not as bad as you might think to do this, it is not a straight swap from 2.2 to 2.3.

You will need to use a tool like phpmyadmin or similar to compare the tables prior to moving them over, also it depends on how many addons were installed with DB settings, as these will not be present in your 2.3 DB structure

Nic

[burt 5,435]

Agreed with Nic, it really is not as hard as some others are making out. There are some new configuration items, some new tables, some new columns in existing tables, and a lot of varchars have been increased in size. It should not take anyone half a day to get it working even if it's the first time ever played with databases.

[NEWPRob 1]

So, is there a "how to" on doing this? I think it may take more than half a day doing it blindly.

 

I have the .sql copy of my original database and will reinstall 2.3.1 to get a clean copy of the new one. I downloaded one program to compare databases but it only wants to connect to a server, any suggestions on what to use? I can log into phpmyadmin, can I compare from there?

 

Thanks for the help, I finally have a day off so I hope to tackle this today and get it working.

[♥multimixer 325]

You can use any file comparison program to do this. There are 2 plain text files to compare and merge. You still need to have an idea of what is what, to know what to keep from the old DB

[NEWPRob 1]

I don't have anything like that, can ya post a link to something? I originally copied over the basic stuff I figured I'd need like the customer_ orders_ and product_ stuff. I can export and import stuff but must admit I haven't read any sql manuals. Am I just keeping the new database as is and importing the data from the old? Trying to get an idea of what I have to do before I start doing it.

[burt 5,435]

You should be updating your OLD database by adding/amending the NEW (or CHANGED) stuff from a clean 2.3.1 db.

 

If you try to import the OLD stuff INTO a NEW db, you will get errors.

 

Simply update your OLD db to 2.3.1 standards.

[NEWPRob 1]

You should be updating your OLD database by adding/amending the NEW (or CHANGED) stuff from a clean 2.3.1 db.

 

If you try to import the OLD stuff INTO a NEW db, you will get errors.

 

Simply update your OLD db to 2.3.1 standards.

 

Step 1 in the right direction!

 

Got the OLD database online, and just installed a fresh copy of 2.3.1 so I got a NEW copy to check against. Downloaded "Compare Suite Light" to compare the two sql

[NEWPRob 1]

"Documents have similarity of less than 40%"

 

I don't care too much about the mods I had on 2.2, I'll install new ones . . what should I be focusing on?

[♥bruyndoncx 260]

you should focus on

1) new tables in 2.3.1

2) fields that have a different datatype or size in 2.3.1

3) new fields in 2.3.1

 

any tables and or fields that where added by contributions won't really hurt when you run the 2.3.1, it is extra fat in the database, but won't stop your site from functioning.

 

you might consider keeping the data asis and see if you want to add the functionality again later on when the contribution has been adapted to the 2.3.1 coding standards

[NEWPRob 1]

you should focus on

1) new tables in 2.3.1

2) fields that have a different datatype or size in 2.3.1

3) new fields in 2.3.1

 

any tables and or fields that where added by contributions won't really hurt when you run the 2.3.1, it is extra fat in the database, but won't stop your site from functioning.

 

you might consider keeping the data asis and see if you want to add the functionality again later on when the contribution has been adapted to the 2.3.1 coding standards

 

Ok, just wanna make sure I got this right before I go do it . .

 

Compare NEW to OLD. Change OLD to match NEW, tables, fields and datatypes. Export data from OLD, import to NEW.

 

Then I should have the NEW database with the correct settings and the OLD data?

 

For example, I change OLD:

Field Type Collation Attributes Null Default Extra Action

address_format_id varchar(11) No auto_increment

 

To:

 

Field Type Collation Attributes Null Default Extra Action

address_format_id varchar(255) No auto_increment

 

Export and import it into NEW

 

I've upgraded forums a hundred times without this much trouble . . what were they thinkin' ?

[Xpajun 119]

Ok, just wanna make sure I got this right before I go do it . .

 

Compare NEW to OLD. Change OLD to match NEW, tables, fields and datatypes. Export data from OLD, import to NEW.

 

Then I should have the NEW database with the correct settings and the OLD data?

 

For example, I change OLD:

Field Type Collation Attributes Null Default Extra Action

address_format_id varchar(11) No auto_increment

 

To:

 

Field Type Collation Attributes Null Default Extra Action

address_format_id varchar(255) No auto_increment

 

Export and import it into NEW

 

I've upgraded forums a hundred times without this much trouble . . what were they thinkin' ?

 

 

The information in the post you refer to shows you how to import your 2.2 data into a 2.3 database without the need to alter any database except for the table "product review"

 

A lot of people have done it successfully that way - why chance messing up your database by rewriting it when it has already been rewitten for you

[burt 5,435]

You should not be importing data into 2.3.1 db.

 

You should be updating old (2.1, 2.2 etc) db to 2.3 settings.

[Xpajun 119]

You should not be importing data into 2.3.1 db.

 

You should be updating old (2.1, 2.2 etc) db to 2.3 settings.

 

???

 

 

So you are saying that all data that is entered into a 2.3 database should be entered in a 2.2 db first and then converted to a 2.3 db?

 

If you have worked with databases (and even most tutors tend to shy away from them for some reason) you should know that a database should be planned from the beginning to suit the project it is being used for and then have the data inserted, and never altered once the data is inserted - although that is more than always kicked out the window now due to the need to make modifications.

 

There is no reason why 2.2 data can not be inserted into a 2.3 database providing the data conforms to the new database (which apart from the products review table 2.2 data does) - after all if you were moving your store data from a - say - magento store to osCommerce you wouldn't alter the database to fit osC you'd alter the data...

[burt 5,435]

No, I am saying that if you have a DB already (let's call it 2.2), then it is a few minutes work to update that DB to the same tables, fields and datatypes as 2.3.1, and add in any extras that 2.3.1 demands (such as the CONFIG stuff for action_recorder and so on).

 

If you have a 2.3.1 already, and you want your 2.2 stuff into it, then you have to do far more work this way, than what is done in the previous sentence. Let's not bring Magento into it, here we are talking only of 2.2 > 2.3.1

 

Juls - I have now updated 60 to 70 stores to 2.3.1 - it literally takes moments to change an existing DB to 2.3.1 standard. Again, so it's clear to anyone reading;

 

You want to upgrade your shop from 2.2 to 2.3.1. Update your existing 2.2 db to the same as 2.3.1, and install the 2.3.1 files. Done. 10 minutes work.

[burt 5,435]

I can see that my explanation is not all that. Lack of English language skills lol

 

 

Here I install a rc2a (assume this is a 3 years old shop as an example).

I then install a new 2.3.1 in a folder. Link the 2.3.1 to the rc2a db.

The new 2.3.1 kinda works, but produces errors, the columns are missing and all the rest of it.

I then update the rc2a db with the extras needed for 2.3.1 usability. Bang! It all works.

Less than 60 seconds.

 

Obviously this is an easy example. A real site that has been running for years would need far more work in the addons, adding the languages that the old shop is running (2.3.1 ships only with english). Adding product images (usually these are found as part of the products table in rc2a) - these would need stripping out and placing in the new products_image table and so on.

 

Anyway, perhaps the video more better illustrates what I am attempting to convey?