Transferring Customers/Orders/Products, Etc. from 2.2 RC1 to 2.3.3

[Kerrigan]

Ok, so I am finally deciding to do away with my old store (2.2 RC1) and do a fresh install of 2.3.3. I am setting up the fresh install of 2.3.3 in a new directory, as a trial, to make sure everything works properly before making the hard switch. How do I transfer all of the old orders, all of the customers and all of the products, smoothly? I don't know much about mysql, so please speak in beginners langauge, if you give instructions.

 

I backed up the database on my 2.2 RC1 store and tried to restore it on 2.3.3, but it's not working. I've never done this before, so I'm not sure what to do. I did do quite a few add-ons to the old store. I'm not sure if that matters or not, but just thought I would mention it. If there is a better or easier way to upgrade, instead of a fresh install, then please let me know. Thanks for any help that you can give!

[Kerrigan]

Is anyone able to help me...at all?

[MrPhil]

Have patience, Grasshopper. Everyone here giving support is volunteering their time, from timezones all around the world.

 

Now, how exactly did you back up your old store and try to restore it to the new one? Did you get any specific errors in the process, or does your new store just not work right? What you need to do is get the new store all properly configured and working with the sample merchandise set. Then you will replace the new database with the old one (regular restore, with tables recreated). Then you will run the add-on that migrates you to RC2/RC2a, to 2.3, to 2.3.1, to 2.3.2, and finally 2.3.3. I don't recall the number, but a quick search should find it. At that point you should have a new vanilla store with your old data (don't forget to move your product images over, too). There shouldn't be any changes to the database from your old store's add-ons that will break the new store, but you never know. Some add-ons do modify the database, but if done correctly, it should not affect vanilla code (i.e., function is added to the base, but base function should not have been changed).

[Kerrigan]

Have patience, Grasshopper. Everyone here giving support is volunteering their time, from timezones all around the world.

 

Now, how exactly did you back up your old store and try to restore it to the new one? Did you get any specific errors in the process, or does your new store just not work right? What you need to do is get the new store all properly configured and working with the sample merchandise set. Then you will replace the new database with the old one (regular restore, with tables recreated). Then you will run the add-on that migrates you to RC2/RC2a, to 2.3, to 2.3.1, to 2.3.2, and finally 2.3.3. I don't recall the number, but a quick search should find it. At that point you should have a new vanilla store with your old data (don't forget to move your product images over, too). There shouldn't be any changes to the database from your old store's add-ons that will break the new store, but you never know. Some add-ons do modify the database, but if done correctly, it should not affect vanilla code (i.e., function is added to the base, but base function should not have been changed).

 

Thanks MrPhil. Sorry for not being more patient.

 

After doing some reading around the forum, regarding upgrading from 2.2 RC1 to 2.3.3, it seemed to be very difficult. I saw many people advising to just do a fresh install of 2.3.3 and then bring all of the information (customer info, past orders, catalog, etc.) over to the new store. That's where I'm at now. The old store is still up and running and I am still using it.

 

The new store is up, with a new template, but I just need to transfer over all of the information from the old store. I backed up the old store, via the Database Backup Manager, as pure SQL and downloaded it. I tried to do a restore, by uploading that backup via the Database Backup Manager at the new store, but nothing happened. At the bottom left of my Chrome Browser, I saw the upload get to 100%, but then nothing happened.

 

There were no error messages, at all. The new store was a fresh install and everything seems to be working right. I deleted the "Install" folder and changed the setting on the configuration files, back to 444. I could manually add products to the store, as well as add-ons, if I want to. I guess I just don't know if I am transferring the information from the old store, properly or not.

 

Am I doing something wrong? Am I missing a step? What do you mean by "with tables created"? Is this the add-on that you are referring to, that will help me migrate, this one: http://addons.oscommerce.com/info/8731 Thanks again, for your help!

[MrPhil]

Does the .sql file you are trying to restore include DROP of existing tables and CREATE of new ones? It should -- so that you completely wipe the newly installed store's database before recreating the old store's database. If all it is doing is emptying (truncating) the new tables, or worse, simply INSERTing the data, you will have to either get another backup that includes DROP/CREATE, or do that manually. Then, before running the new store IMPORT the add-on's SQL statements to modify the database to the 2.3.3 format (or do it manually). Don't forget to erase the sample store product images and bring in your own.

 

Add-on: http://addons.oscommerce.com/info/8731

 

The new store is up, with a new template

If you obtained a "template", make sure it didn't wipe out your 2.3.3 code and replace it with 2.2 code (many "templates" do that -- they are complete replacements). It should be a 2.3.x-compatible template that doesn't do much more than "reskin" your site. You should confirm this.

[Kerrigan]

Does the .sql file you are trying to restore include DROP of existing tables and CREATE of new ones?

 

How do I do that? Will that effect my old store, if I do that? I need to keep the old store up and running, until I get the new one up and running.

 

If all it is doing is emptying (truncating) the new tables, or worse, simply INSERTing the data, you will have to either get another backup that includes DROP/CREATE, or do that manually.

 

To be honest, I'm not sure what I did. All of what you said is like speaking another language, to me.

 

Don't forget to erase the sample store product images and bring in your own.

 

I can just ftp download the content of the images folder from the old store and then upload the content to the images folder via ftp, to the new store, right?

 

Add-on: http://addons.oscommerce.com/info/8731

 

Is this the add on I need to use, to do what you said? Or is it something that I don't need?

 

If you obtained a "template", make sure it didn't wipe out your 2.3.3 code and replace it with 2.2 code (many "templates" do that -- they are complete replacements). It should be a 2.3.x-compatible template that doesn't do much more than "reskin" your site. You should confirm this.

 

The place I purchased it from ( templatemonster.com ) said that it was a 2.3.3 template. From what I can tell, all it is doing is "reskinning" the website, like you said. Thanks again, for your help and time.

[MrPhil]

You have an .sql file you used to load up a copy of your old database, on your new site? I presume that you're working with a separate database for the new site. Anything you do with the new database will affect only it, and not your production store. Just be careful as you work in your hosting control panel that you select the right database to work on each time. Of course, you know how to take a backup of your database and how to restore it, just in case you mess up and work on the production store's database by mistake, right?

 

When you installed the new store, it created a new database in a somewhat different format ("schema") than the old one, and put some sample products in it. Did you get that far, in seeing your new store work with the sample data? If not, you've got other problems that need to be fixed first.

 

When you're ready to bring in your old store's data, you will have to get rid of all the tables (with sample data) in the new store's database, and replace them with the old store's database content (you're not touching the production store database, just making a copy of it). If the .sql file (backup) contains DROP and CREATE statements, all you have to do is go to the new store database and IMPORT that .sql file. It will vaporize all the sample data and create a copy of the old store's database. If the .sql file does NOT contain DROP and CREATE statements, which of these does it have: EMPTY, TRUNCATE, CREATE, INSERT? We need to know what manual operations need to be done first. Even better, can you get a new .sql file from your production store, that includes DROP and CREATE? It should be a checkbox or button in the backup panel.

 

Once you have created a copy of your old database, under your new store, your new store will NOT work with the old database. You will need to take the .sql file in add-on 8731, and IMPORT it. It is supposed to modify tables and add new tables to change your 2.2RC1 format database to 2.3.3 format. If you have a large enough database, it's possible that it will run too long and be killed by your host. Talk to them about how to get a temporary exemption to the time limit, or how to split up the .sql file into smaller pieces that can run successfully. If you're lucky, you can just keep re-running (IMPORT) the .sql file, and it will get further along each time. At the end of all this, you should have your new database in the osC 2.3.3 format (schema), with your old store's data.

 

All you should need to do at this point is erase the sample store product image files and copy over (FTP) your old store's product image files. You should have a working 2.3.3 store with your existing store's products and customers, if all went well. If it didn't go well, that's why you practice this stuff in a test area and not on your production store and database.

[Kerrigan]

Ok, I think that I'm starting to understand everything better now. Part of my problem, is that I was creating the database backup from the admin panel instead of phpMyAdmin. That's why I didn't know what you were talking about when you said, "DROP and CREATE statements".

 

I did a backup of the old store's database with the drop and create statements. Then I imported that to the new store's database. That I imported the .sql file from the 8731 addon to the new store's database. It didn't work, though. I got this error message:

 

Error

 

SQL query:

############

#RC1 SQL

############

#Increase the sizes of the configuration key and configuration title database fields.

ALTER TABLE configuration MODIFY configuration_title VARCHAR( 255 ) NOT NULL ;

 

MySQL said:

#1046 - No database selected

[MrPhil]

For whatever reason, phpMyAdmin hasn't been told which existing database to work with. When you go into phpMyAdmin (or the equivalent) to work on the new database, you have to tell it which database you're working on. In phpMyAdmin, I recall having to click on the name of the database (even if it's the only one) over in the left column. Then you can do the IMPORT.

 

Check and confirm that your new store's database is actually your old store's data (that you're about to do the 8731 add-on with), and you no longer have the new (2.3.3) database and sample data. Also, if you're starting from RC1 rather than MS2, it's possible that you will get errors in the MS2-to-RC1 conversion steps. I don't think you will, but if you do, you can comment out (with # character) the first section for MS2-to-RC1.

[Kerrigan]

For whatever reason, phpMyAdmin hasn't been told which existing database to work with. When you go into phpMyAdmin (or the equivalent) to work on the new database, you have to tell it which database you're working on. In phpMyAdmin, I recall having to click on the name of the database (even if it's the only one) over in the left column. Then you can do the IMPORT.

 

The right database is selected, from what I can tell. It has been selected from the beginning, when I imported the database from the old store into the database for the new store.

 

Check and confirm that your new store's database is actually your old store's data (that you're about to do the 8731 add-on with), and you no longer have the new (2.3.3) database and sample data.

 

How do I check and confirm that? The size of the database for the new store, jumped up to about the same size as the database of the old store. It's just a little bit larger in the database for the new store, than in the database for the old store. Maybe since the database is a little bigger in the new store, than the old store, there is still some of the new database and sample data in there? I don't know. Just guessing.

 

Also, I can't login to admin anymore. First, it says:

 

No administrators exist in the database table. Please fill in the following information to create the first administrator. (A manual login is still required after this step)

 

So, I create and administrator. Then when I try to login using that username and password, it says,

 

Error: The maximum number of login attempts has been reached. Please try again in 5 minutes.

 

I wait longer than 5 minutes and do it again. After a while it tells me to create a new administrator and the vicious cycle starts again. I was able to login before I imported the database from the old store, to the new store. Now, I am not able to. Just thought I should mention this.

 

Also, if you're starting from RC1 rather than MS2, it's possible that you will get errors in the MS2-to-RC1 conversion steps. I don't think you will, but if you do, you can comment out (with # character) the first section for MS2-to-RC1.

 

I've only gotten one error message so far...the one I shared in my last comment. I don't know how to "comment out" the conversion steps from MS2 to RC1. How do I do this and where do I do this? Thanks...

[Kerrigan]

Oh and if I try to go to my new store, I don't see the template anymore. All I see is this error message:

 

1054 - Unknown column 'banners_target' in 'field list'

 

select banners_id, banners_title, banners_image, banners_target, banners_html_text from banners where status = '1' and banners_group = '468x50'

 

[TEP STOP]

 

[MrPhil]

I told you that the new store will NOT work with the old database, until it has been upgraded (via the add-on). Don't even try running the new store until the database has been sorted out.

 

Every time you enter phpMyAdmin, you are going to have to select the database to work on (it doesn't remember it from visit to visit).

 

Did you clear out the "new" database before importing the old? That's why I was asking if there were DROP and CREATE statements in the .sql backup. They would do it for you. Browse the database contents through phpMyAdmin. Do you see both some sample products (e.g., the movie "There's Something About Mary") and some product from your old store? If so, you've got them mixed together and will have to TRUNCATE or EMPTY all the tables. Then, is the structure of the tables the old or new? It should be the old database's schema with (only) the old data, before you import the add-on to update the structure. You can look in the phpMyAdmin tab for the database structure, and see if the table layouts in the new (post import of the old data) match the old database or the new (2.3.3).

 

If your database is still the new (2.3.3) schema, and your .sql file doesn't include DROP and CREATE, that will be a problem, as it will try to load (INSERT commands) old data into the new structure, and there's no guarantee that will work (it might, provided it emptied out the tables first, but I won't promise anything).

[Kerrigan]

The only reason I gave you the information, concerning the errors in trying to login to the admin and when I try to access the site, is that I thought it might be important information, in order for you to help me properly.

 

When I export the database from the old store (2.2 RC1), I DO include "drop and create", every time I have done it. When I say that I have included "drop and create", I mean that I have clicked on "export" on the database for the old store and then have selected these options/checked the box of them:

 

Display comments (includes info such as export timestamp, PHP version, and server version)

 

Dump Table: Structure and Data

 

Add DROP TABLE / VIEW / PROCEDURE / FUNCTION / EVENT statement

 

Add CREATE PROCEDURE / FUNCTION / EVENT statement

 

CREATE TABLE options:

IF NOT EXISTS

AUTO_INCREMENT

 

Enclose table and field names with backquotes (Protects field and table names formed with special characters or keywords)

 

Syntax to use when inserting data:

both of the above

Example: INSERT INTO tbl_name (col_A,col_ B) VALUES (1,2,3), (4,5,6), (7,8,9)

 

Dump binary columns in hexadecimal notation (for example, "abc" becomes 0x616263)

Dump TIMESTAMP columns in UTC (enables TIMESTAMP columns to be dumped and reloaded between servers in different time zones)

 

I have also gone to the database for the new store and have emptied the tables, that match the tables that are coming over from exported database from the old store, before I have imported the database from the old store. I have done this, just to make sure things go right. When I browse and look at the information in each table in the imported database in the new store, it is only the information coming from the database from my old store.

 

I have selected the right database to work on, each time. I haven't done anything to the database from the old store, except export with "drop and create" to a file that I save onto my desktop. Nothing has been done to the structure of the database of the new store. I know that it is still the new structure, because there are tables on there that aren't in the structure of the old store. Specifically, these are the new tables:

 

action_recorder

products_images

products_related_products

products_tags

sec_directory_whitelist

tags

usu_cache

 

I have never emptied these tables, prior to importing the database from the old store. I figured that the information on them was important. Then, after importing the database from the old store to the database from the new store, I have tried running the .sql from the add-on. I keep getting the same error, over and over again. I found another guy, who was having a very similar problem to me and his question never got answered. You can see it here:

http://www.oscommerce.com/forums/topic/333215-contribution-upgrading-osc-from-22-ms2-to-22-rc2a/page__st__20#entry1427066

 

Just to make sure I am understanding your instructions properly, are you saying either of these two things:

 

1) Run the add-on on the database from the old store, while it is still being used at the old store (before exporting)?

2) Are you saying to run the add-on on the database at the new store, before importing the database from the old store?

 

I just want to make sure I am doing things in the right order. Thanks...

[MrPhil]

Nope. Neither of those. Leave the old (live, production) store's database alone until you've figured out what you're doing with this migration (if you choose to migrate the old site in-place). You import the old store's database to wipe out the new store's database and end up with a copy of the old store's database on your new system. It is not runnable at this point -- don't even try it. It probably wouldn't hurt to TRUNCATE (EMPTY) those new tables you listed. Then you run (import) the add-on on the new store's database, which should change it into the osC 2.3.3 format. Bring over your product image files and you should be up and running.

[Kerrigan]

Nope. Neither of those. Leave the old (live, production) store's database alone until you've figured out what you're doing with this migration (if you choose to migrate the old site in-place). You import the old store's database to wipe out the new store's database and end up with a copy of the old store's database on your new system. It is not runnable at this point -- don't even try it. It probably wouldn't hurt to TRUNCATE (EMPTY) those new tables you listed. Then you run (import) the add-on on the new store's database, which should change it into the osC 2.3.3 format. Bring over your product image files and you should be up and running.

 

Ok, I truncated those other tables as well. I did (and have been doing) everything in the order you said to. I ran the add-on on the new store's database (again) and am still getting this error:

 

Error

 

SQL query:

############

#RC2 SQL

############

#Add indexes to increase performance

#Add public and download status flag fields to the orders_status table

#Increase payment_method field size on the orders table

#Increase the last_page_url field size on the whos_online table

ALTER TABLE banners ADD INDEX idx_banners_group( banners_group ) ;

 

MySQL said:

#1061 - Duplicate key name 'idx_banners_group'

 

This is the same error that I was getting yesterday. I talk about it in post #8 from this thread. I'm not sure where to go from here, in order to fix this. I've tried commenting out (#) the "alter table banners add index idx_banners_group (banners_group);", but then it does the same thing with the next one on the list, and so on. What do I do now?

[♥bruyndoncx]

your new database already has these indexes defined, so it basically says, cant create this, it exists already.

if you didn't add any specific indexes your self for performance, or for certain contributions, you don't need to include indexes in your database export

[Kerrigan]

your new database already has these indexes defined, so it basically says, cant create this, it exists already.

if you didn't add any specific indexes your self for performance, or for certain contributions, you don't need to include indexes in your database export

 

Ok, then should I just keep commented these different changes out, until I don't get the same error message anymore?

[♥joli1811]

OK Grasshopper,

 

Install first a clean 2.33 database

 

Then you should empty first empty / truncate the the tables that contain the standard install products as maybe this could cause an error if they are duplicated then add your products with a simple insert statement.

 

INSERT INTO `

 

This you can take from the sql from your old database skip the CREATE TABLE etc start just with the insert statement so you do not change the format of the new database.

 

Do step by step first empty / truncate the database entry's that are there in a standard install then insert your important entrys( products , customer record ...etc.

 

most of the database format has not changed do not drop the new tables from 2.33 just empty and insert the old records never had a big problem except with the images which have changed a bit.

 

but found a nice SQL for the images

 

which allowed me first to

 

(In phpmyadmin) With an empty products_images table run this query and it will add the data as if you've uploaded the image twice saving hours of time!

 

INSERT INTO `products_images` (`products_id`, `image`) SELECT `products_id`, `products_image` FROM `products`

 

Work out what you need BASIC then get to your add ons which will require a manual insert and possibly update

 

Regards

joli

[Kerrigan]

Ok, now I am really confused. It seems like three different people are trying to tell me three different things.

 

I entered in the categories table from the database from my old store, into the database of my new store. It worked without any errors...at least that I can tell.

 

Then I began to enter in some of the customer information, but I had to modify it, to take out the columns for PayPal from the exported database from my old store. It must be from a PayPal addon that I did, on the old store, that the new store doesn't have yet. I got 100 customers entered in, with no problems...at least I thought. I went to the admin section to see if I could look at the customers. They are there, but so is this error message:

 

Warning: array_merge() [function.array-merge]: Argument #1 is not an array in/home2/pinpoin4/public_html/gospeltractorg/shop/mystore/customers.php on line 706

 

Warning: array_merge() [function.array-merge]: Argument #2 is not an array in/home2/pinpoin4/public_html/gospeltractorg/shop/mystore/customers.php on line 706

 

Warning: array_merge() [function.array-merge]: Argument #2 is not an array in/home2/pinpoin4/public_html/gospeltractorg/shop/mystore/customers.php on line 708

 

Warning: reset() [function.reset]: Passed variable is not an array or object in/home2/pinpoin4/public_html/gospeltractorg/shop/mystore/includes/classes/object_info.php on line 17

 

 

Warning: Variable passed to each() is not an array or object in/home2/pinpoin4/public_html/gospeltractorg/shop/mystore/includes/classes/object_info.php on line 18

 

 

[Kerrigan]

Any idea what I am doing wrong, now?

[Kerrigan]

Can anyone help?

[♥kymation]

Are you certain that you have updated all of the customer tables? It looks like you missed one.

 

Regards

Jim

[Kerrigan]

Are you certain that you have updated all of the customer tables? It looks like you missed one.

 

Regards

Jim

 

Thanks for responding, Jim. Yes, I am sure.

 

One of the errors that I am getting, from admin/customers.php, on lines 706-708, says this:

 

 

$reviews_query = tep_db_query("select count(*) as number_of_reviews from " . TABLE_REVIEWS . " where customers_id = '" . (int)$customers['customers_id'] . "'");

 

The other error I am getting, is coming from admin/includes/classes/object_info.php on lines 17-18, which says:

 

 

reset($object_array);

while (list($key, $value) = each($object_array)) {

$this->$key = tep_db_prepare_input($value);

Any idea what the problem could be?

[♥kymation]

The first error is saying that one of the arrays is not defined. You need to look at where each array comes from and determine what's not getting set. Those are usually values passed from a form or from a database table.

 

The second error means that $object_array has not been defined. You'll have to look above those lines for the definition of that variable.

 

Regards

Jim

[Kerrigan]

Ok, I decided to "start over" on transferring the database information from the old store to the new store. I exported the database from the old store (with drop and create) and then imported it all to the new store. Then I ran this add-on: http://addons.oscommerce.com/info/8731 I was able to get it to run all the way through, after commenting out the ones that were receiving errors.

 

Here's the problem that I'm having now: I can't login to the admin side of the store. It said that there were no admin users/passwords on file. It asked me to create a user name and password. I did and now it won't let me login, using that user name and password saying, "Error: Invalid administrator login attempt".

 

Also, when I try to go to the index.php page of my store, I see this error:

 

1054 - Unknown column 'banners_target' in 'field list'

 

 

 

select banners_id, banners_title, banners_image, banners_target, banners_html_text from banners where status = '1' and banners_group = '468x50'

[TEP STOP]

 

Any idea what the problem is and how to fix it?