Updating 'live' site database from 'development' site

[earthdoctor]

I am developing and maintaining a site for a customer. The customer will be adding (including images) / updating products and prices, and nothing else, on the 'live' site. I will be doing all other maintenance and development on my testing server, and then uploading the database when necessary.

If I upload the database from the dev. box to the live site, it will over-ride any amendments that have been made directly on the live site; it is not practical, nor desirable, for my completely non-technical customer to access my dev. box for product additions / amendments, as it is an old PC sat on my desk at home.

Is there any way around this problem (he asks, expecting the answer no....)?

[DrByte]

You may not like this, but the best way is to take good notes, and manually make the changes onto the live server when it comes time to do implementation.

If you understand MySQL and the SQL language, you could build a script to make the changes; but most of the time this takes 3 times longer than just doing it on the live server, depending on what exactly you're doing.

[wickedklown]

I am developing and maintaining a site for a customer. The customer will be adding (including images) / updating products and prices, and nothing else, on the 'live' site. I will be doing all other maintenance and development on my testing server, and then uploading the database when necessary.

If I upload the database from the dev. box to the live site, it will over-ride any amendments that have been made directly on the live site; it is not practical, nor desirable, for my completely non-technical customer to access my dev. box for product additions / amendments, as it is an old PC sat on my desk at home.

Is there any way around this problem (he asks, expecting the answer no....)?

Yes it will overwrite anything that was on the live site, with your dev site settings. unless you can figgure out a way to only update mysql with the tables you have changed, and first making sure no changes have been made since you last checked. Really, all-in-all this isnt the best way to do it.

i would have your dev site be a copy of the live site, and make your changes/additions to it, once you get to a point where you think you should update the live site, then download a copy of your live database and load it into your dev site, and see that everything is working good, then make the swap. this way your not loosing customer data, or new orders that where placed or newly added products. at least that is how we do it here.

i have a live site on one server, and another server which is only local. we use our staging server to build new features and so-on, and then just swap the live site for the newer staged one after we have imported the order/customer/product data into its corrisponding database. this way we not only have a backup of what was just released, but we can keep building off of it without worring about loss of data.

hope this helps
let me know if you need anything else

[earthdoctor]

You may not like this, but the best way is to take good notes, and manually make the changes onto the live server when it comes time to do implementation.

If you understand MySQL and the SQL language, you could build a script to make the changes; but most of the time this takes 3 times longer than just doing it on the live server, depending on what exactly you're doing.

Thanks - seems like a good, fairly fool-proof, way to start, and then look at doing something like Wickedklown suggests below, to be instigated when I'm more confident - at the moment I think I might make an unfortunate mistake!

[fymbscu]

Hi folks,

I am in a similar situation but we are on the cusp of "going live," and realize the importance of this issue. Sounds like store downtime is required to make these changes safely. Am I missing something here?

To extend the discussion further:

What tools are available to backup/download only customers and/or orders information? Are we compelled to manually input order information from the emails that zencart generates to sync up with a system such as Quickbooks?

Thanks for a great thread.

Regards,

Mark Harrison

[DrByte]

Mark,

1. Yes, some downtime is required. In my experience, typically about 15 minutes. Most times less, occasionally more. Then again, I do them all the time.

2. Ajeh summarized the steps quite thoroughly here:
http://www.zen-cart.com/forum/showth...456#post274456

3. Following the steps Ajeh outlined will retain all your live data in-place, as it will simply upgrade the live database to the new structure.
If you have special database-customizations to replicate in the new database setup (such as for new addons, etc), keep good notes, and do them just after the database-upgrade step.


If you are not fully comfortable with that, do a dry run !
Copy your current site+database to another folder+database, and test the entire upgrade process described in Ajeh's link using that set of files+database. That way you'll be able to have some comfort knowing what steps are required, as well as an idea of how long it's going to take you to do it all, thus knowing how much down time it will require.

[fymbscu]

Thanks DrByte, for your wise words,

I have made a note of Ajeh's procedure.

I realized that I had successfully used phpMyAdmin on my local system to backup my dev database and then use the SQL Query Executor (under Tools in the Admin) to upload/restore this file to the remote site, also successfully. When I tried to reverse the procedure, to backup from the remote site and then restore to the local site, I get the following error:

1064 You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'DEFAULT CHARSET=latin1 AUTO_INCREMENT=6' at line 1
in:
[CREATE TABLE address_book ( address_book_id int(11) NOT NULL auto_increment, customers_id int(11) NOT NULL default '0', 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), KEY idx_address_book_customers_id_zen (customers_id) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;]
If you were entering information, press the BACK button in your browser and re-check the information you had entered to be sure you left no blank fields.

I think the remote version of phpMyAdmin has been upgraded. It is now at version 2.9.0.2, while locally I have 2.6.4-pl4 installed. This is the first time I have tried to go from newer to older. Does this explain the syntax error described in the quote?

We can't go forward if we are unable to reliably backup and revise our system.

Thanks for the help.

Regards,

Mark Harrison

[fymbscu]

Hi folks,

Upgrading phpMyAdmin on my local machine to 2.9.0.2 did not resolve my syntax error. Expecting this to be a "version" error, phpMyAdmin says this:

Remote Server (Linux):
# Server version: 4.1.21-standard
# Protocol version: 10
# Server: Localhost via UNIX socket
# User: xxxxxx@localhost
# MySQL charset: UTF-8 Unicode (utf8)
# MySQL connection collation: utf8_unicode_ci
# MySQL client version: 4.1.21
# Used PHP extensions: mysql
# Language Info: English

Local Server (Macintosh G3; Mac OS X 10.2.8):
# Server version: 4.0.25-standard
# Protocol version: 10
# Server: Localhost via UNIX socket
# User: xxxxxx@localhost
# MySQL client version: 3.23.49
# Used PHP extensions: mysql
# Language Info: English (iso-8859-1)

I don't get the MySQL charset information from my Mac system. Perhaps because my MySQL version is too old? Would this produce this error?

This reminds me of another issue I've had with em dashes typed on the Mac being changed to upside down questionmarks when the database was originally moved to the remote host. Similarly, carriage returns have been changed to the letters "rn."

I guess this Latin/Unicode issue it greater than I thought. Again, all help very much appreciated.

Regards,

Mark Harrison