database question: insert new users, orders

[rebusB]

I am working on rolling out an updated version of my site, switching hosts in the process. So now I have the old site live, and the new site hosted and viewable, just not under the official domain. Just before I go live with the new store I would like to bring in any changes made to the users and orders (and possibly parts of the product catalog) sections of the old store's database leaving the new configuration intact.

I started with a new install of the latest version (1.3.9h), importing and updating the database of the existing store at the time for a base of products, users, etc. Now that I am close to rolling out the new site there are new users and order history in the old sites database.

I dumped both databases and using FileMerge I can see the differences and could easily merge those into the new database. Is this the best way to do this? It is only a few dozen additions so any syntax changes I could edit by hand. Just wondering if this is safe and effective or if there is another recommended method/tool for doing this kind of thing.

Thanks in advance,
Rebus

(version 1.3.9h)

[DrByte]

I always recommend keeping the live database as the main database, and then simply applying any changes to that live database ... instead of what you're suggesting which is the other way around. Far less likely to lose critical data that way.

However, if your database skills are strong enough, and you know exactly what you'll do to recover from a problem if you have one, then proceeding with the approach you mentioned, could work. Riskier, but can work. As long as you understand how databases work and how table structures and data records interact, etc. Many people don't have any clue about those aspects, and thus they should never foray into that space .. which is another reason why I strongly encourage people to always keep the live database as the main one, and merely update IT, as I said in my opening paragraph.
(Yes, those are disclaimer comments, and especially intended for the benefit of others who read this post in the future.)

[rebusB]

Thanks for the response Doctor!

I do understand why that is the sensible approach. Figured that since this is my 2.0 site, with an updated ZC, and was going to be fairly different I didn't want to mess with the live database and could take my time with the new site. At this point, I have already gone down that road with the 2.0 site about ready to go. Just pushed the complicated bits down the road to now.

I have done a little mySQL work and plenty of coding, enough to know the basic concepts of relational databases, so I am okay with messing around with them. It just isn't my main thing and this is far more complicated an db app than I have dealt with. (That is why I was trying to find out more about the API.)

So my main concern is that I be sure to update all the tables involved in recording the users and transactions. Is there a document that shows the relationships, or can you tell me which tables I should pay attention to?

[DrByte]

www.zen-cart.com/wiki

[rebusB]

So I think I can get this to work. The docs, especially the ones at http://www.zen-cart.com/docs/ look like great reference.

Needed to update the database to 1.3.9 from 1.3.8 which was easy, just exported from the live store and imported it into my localhost database (yeah, there are actually three databases involved but the local and the new host are in sync.) where I ran the install/sql/ patch to bring it up to date.

Then it I exported it again (all import and export through phpMyAdmin) and ran it against the export from the new host database in FileMerge. Here is my strategy...

The tables to change are:
address_book; authorizenet; coupon_redeem_track; customers; customers_basket; customers_basket_attributes; customers_info; orders; orders_products; orders_products_attributes; orders_status_history; orders_total; products; products_description

not sure about:
counter; counter_history;

the rest of the changes are in the configuration of the site, things like ez-pages, which I do not want to change.

thankfully I have not made any changes to the catalog of products, and the only differences are in inventory and view count. I could probably leave those alone.

So when the merge is done (merging the changes and additions from the live site in) the dump file should look like it came from the live store, only configured visually to match the new store.

Only other concern is the new store has database caching enabled. Hopefully that wont be a problem. Or maybe I should turn it off before I import the new dump?

[DrByte]

Needed to update the database to 1.3.9 from 1.3.8 which was easy, just exported from the live store and imported it into my localhost database (yeah, there are actually three databases involved but the local and the new host are in sync.) where I ran the install/sql/ patch to bring it up to date.

I STRONGLY recommend using zc_install as per the official upgrade instructions. Manually running SQL scripts causes you to miss the programmatic changes that are included in the zc_install program.

Then it I exported it again (all import and export through phpMyAdmin) and ran it against the export from the new host database in FileMerge.

Assuming you do a thorough very careful study of the data you're moving, that process will probably work.

BUT I MUST ADD THIS IMPORTANT DISCLAIMER:, even if only for future readers of this discussion
WE ALWAYS RECOMMEND USING THE ORIGINAL LIVE DATABASE, and applying your new changes to it. NOT the other way around.
ANY DEVIATION FROM THIS RECOMMENDED PROCESS is risky. Be sure to "count the cost" before deviating from the advised approach.

Only other concern is the new store has database caching enabled. Hopefully that wont be a problem. Or maybe I should turn it off before I import the new dump?

If by "database caching" you're referring to settings in configure.php, don't worry about it. If you set your store to Down For Maintenance there won't be a problem.

[rebusB]

All that red type!

But I agree, this is definitely seat of the pants stuff... I painted myself into a corner with the database.

The problem is that the configuration of the look and behavior of the site (like ez-pages, layout, etc...) which needed to be changed for the new site lives along side the customer and order data which needed to be live on the old site while the new site was developed.

Not sure how else to do it other than to bring the live site down while it gets reconfigured so there is no change to that part of the database.

btw - I did use the zc_install to upgrade the new site, this was just to be sure the imported database matched the syntax. Well, fingers crossed. I will post the results soon.

[rebusB]

My head hurts... but I get it now, I think.

First merge didn't take. Although the site ran fine I could not log in to admin area to examine the user changes. Seemed like a session error, even the default Admin login failed. Restored previous db and am back in.

New plan, use live database, merge in changes to configuration. Mostly this will be ez-pages tables. Much less risky, right?

Will also look at the zc_install page to see how to update database since I am importing 1.3.8 db into 1.3.9 ZC. Thought that was just a .sql action.

Thanks for the advice!

[rebusB]

Just to follow up on what happened.

First try was merging the user and orders info from the live store into the new store. That may have worked, but I got hung up on session problems.

Second I replaced the data in the ez-pages tables and a few entries in the configuration tables from the new store into the database from the live store, after updating the live store through my localhost site running the zc_install php files. Much better, much less data to compare, basically the same goal. A database with live store user and order data, but with configuration changes copied from new store. All up and running now. Still had to track down some session errors and https problems with new host. But that is for another thread.

Thanks for your advice DrByte and for the great store app. The latest version is great.

Rebus

[DrByte]

Glad you got it worked out!