Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2007
    Posts
    102
    Plugin Contributions
    0

    Default What part of databse needs updating?

    Hi

    I have been revamping my store over the last couple of weeks. I have upgraded to v1.38 and ran it on a subdirectory until I got it running right and the new look was the way I wanted. I have changed categories, added categories etc. etc.

    My problem is, I had to leave the old site live before the new one was finished (I need the money), so several days ago, I backed up all the databases, created a new one for the new store and imported and upgraded, eveything went swimmingly.

    Now because the old site was still live, I need to import the new accounts and new orders, without destroying all the other changes to categories ,products etc. Is this possible? what parts of the mysql do I need to import and what type of import is it? i.e. what settings for incremental import of orders...

    Many thanks for your help in advance.

    Kind Regards
    AfterHouR

  2. #2
    Join Date
    Aug 2005
    Location
    Arizona
    Posts
    27,761
    Plugin Contributions
    9

    Default Re: What part of databse needs updating

    As long as your newsite is based on a db backup from your live shop you can place your current shop down for maint.
    Take a proper backup of all & save
    Now take another selecting every table that has to do with customers and orders only. This can include authorizenet or linkpoint, paypal etc.
    Apply it to your new shop and re-run zc_install - - upgrade DB only

    https://www.zen-cart.com/tutorials/i...hp?article=103
    Zen-Venom Get Bitten

  3. #3
    Join Date
    Jun 2005
    Location
    Cumbria, UK
    Posts
    10,262
    Plugin Contributions
    3

    Default Re: What part of databse needs updating

    You've given yourself a bit of a challenge here... Fundamentally, the stored data in the two databases, is very different... Particularly when it comes to the "auto-increment" stuff.

    It can only really be fixed by making a manual comparison of the CONTENT of all tables in both databases, and then editing the "Insert Into" SQL lines from the second database so that their increment value is sequential to the last value in the first database.

    So, use phpMyAdmin to do a full SQL dump of each database. You should configure the dumps to include DROP table and CREATE IF NOT EXISTS...

    Use WINMERGE to COMPARE the two files. (while they have the .sql extension, they are fundamentally plaintext files.

    WINMERGE will highlight all areas where the file content does not match.

    So, let's look at a possible scenario...

    While you were upgrading on the SECOND database, customers were busy registering and buying stuff on the FIRST database.

    But you added other stuff to the SECOND database, so now the two are incompatible.

    BEFORE you do the sql dumps, put your store into maintenance mode! This will stop any activity while you are busy, because (as you have discovered), the dump is really just a "snapshot" of site activity up to the point at which the dump was made.

    Let's look at what the ADDRESS BOOK TABLE and its DATA may look like after the sql dumps...

    In your FIRST database, it looks like this:-

    Code:
    --
    -- Table structure for table `address_book`
    --
    
    DROP TABLE IF EXISTS `address_book`;
    CREATE TABLE IF NOT EXISTS `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(64) 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=4 ;
    
    --
    -- Dumping data for table `address_book`
    --
    
    INSERT INTO `address_book` (`address_book_id`, `customers_id`, `entry_gender`, `entry_company`, `entry_firstname`, `entry_lastname`, `entry_street_address`, `entry_suburb`, `entry_postcode`, `entry_city`, `entry_state`, `entry_country_id`, `entry_zone_id`) VALUES
    (1, 1, 'm', 'Acme and Co', 'John', 'Smith', '22 Larksway', 'Thorley Park', 'CM23 9XQ', 'Aucklandvilla', '', 244, 428),
    (2, 2, 'm', 'Johnsons Butchery', 'Howard', 'Jones', '1 Smith Way', 'Horrox', 'BT1 4TH', 'Belfast', '', 241, 329),
    (3, 3, 'm', 'Smith and Co', 'David', 'Petersen', '22 Alpine Close', 'Seaview', 'BR3 8YY', 'Brighton', '', 244, 407);
    In your SECOND database, it looks like this:

    (I have just put the LAST data record in here, to save space and time, but lets assume that the last data record has the incremental ID of 36

    Code:
    --
    -- Table structure for table `address_book`
    --
    
    DROP TABLE IF EXISTS `address_book`;
    CREATE TABLE IF NOT EXISTS `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(64) 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=38 ;
    
    --
    -- Dumping data for table `address_book`
    --
    INSERT INTO `address_book` (`address_book_id`, `customers_id`, `entry_gender`, `entry_company`, `entry_firstname`, `entry_lastname`, `entry_street_address`, `entry_suburb`, `entry_postcode`, `entry_city`, `entry_state`, `entry_country_id`, `entry_zone_id`) VALUES
    (37, 37, 'm', 'Poole Transport', 'Adam', 'Jacobs', '12 Speedie Ave', 'Rustic Close', 'BB29 5TG', 'Guildford', '', 244, 428);
    You now need to COMBINE the data sets, and I'm sure that by just looking at the code below, you'll gather what I've done.
    You will need to MANUALLY EDIT the INCREMENT VALUES - there can be no duplicates, and furthermore, the Incremented ID's must be reflected across ALL TABLES where the ID creates a relationship - a BIG CHALLENGE! PROCEED WITH CARE:

    Code:
    --
    -- Table structure for table `address_book`
    --
    
    DROP TABLE IF EXISTS `address_book`;
    CREATE TABLE IF NOT EXISTS `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(64) 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=38 ;
    
    --
    -- Dumping data for table `address_book`
    --
    
    INSERT INTO `address_book` (`address_book_id`, `customers_id`, `entry_gender`, `entry_company`, `entry_firstname`, `entry_lastname`, `entry_street_address`, `entry_suburb`, `entry_postcode`, `entry_city`, `entry_state`, `entry_country_id`, `entry_zone_id`) VALUES
    (1, 1, 'm', 'Acme and Co', 'John', 'Smith', '22 Larksway', 'Thorley Park', 'CM23 9XQ', 'Aucklandvilla', '', 244, 428),
    (2, 2, 'm', 'Johnsons Butchery', 'Howard', 'Jones', '1 Smith Way', 'Horrox', 'BT1 4TH', 'Belfast', '', 241, 329),
    (3, 3, 'm', 'Smith and Co', 'David', 'Petersen', '22 Alpine Close', 'Seaview', 'BR3 8YY', 'Brighton', '', 244, 407),
    [THEN THE DATA FROM THE OTHER DATABASE, ENDING WITH...]
    (37, 37, 'm', 'Poole Transport', 'Adam', 'Jacobs', '12 Speedie Ave', 'Rustic Close', 'BB29 5TG', 'Guildford', '', 244, 428);
    PLEASE NOTICE THE FOLLOWING... VERY IMPORTANT...

    1. The line
    Code:
    ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=38 ;
    now show a value of 38, as the last record in the table has the value of 37

    2. REMEMBER THE FUNCTION OF THE SEMI COLON !
    In the first database, the FINAL "INSERT INTO" VALUES line ends with a SEMI-COLON.
    Code:
    (3, 3, 'm', 'Smith and Co', 'David', 'Petersen', '22 Alpine Close', 'Seaview', 'BR3 8YY', 'Brighton', '', 244, 407);
    NOW, because I must "blend in" the VALUES from the other database BENEATH it, that line must have a COMMA at the end:
    Code:
    (3, 3, 'm', 'Smith and Co', 'David', 'Petersen', '22 Alpine Close', 'Seaview', 'BR3 8YY', 'Brighton', '', 244, 407),
    ...and the SEMI COLON is put in again after the final VALUES line
    Code:
    (37, 37, 'm', 'Poole Transport', 'Adam', 'Jacobs', '12 Speedie Ave', 'Rustic Close', 'BB29 5TG', 'Guildford', '', 244, 428);
    When you have followed this procedure for ALL tables, then CREATE A NEW DATABASE and dump all the modified SQL into it.

    Point a TEST SHOP to that new database and test all zencart functions. When you are happy that it works, de-couple it from your TEST SHOP front-end, and point your original site to it.
    Last edited by schoolboy; 18 Sep 2009 at 10:35 AM.
    19 years a Zencart User

  4. #4
    Join Date
    Dec 2007
    Posts
    102
    Plugin Contributions
    0

    Default Re: What part of databse needs updating?

    Thanks for the quick responses Guys..

    Two different approaches though...

    The second DB was taken from a backup of the first but I have changed product categories and moved a lot of products but the customer records structure should be the same.

    I used Beyond compare for the file compares for the upgrade, so I am ok with that..

    Kobra, is it that simple to do? or do I have to go in and edit the files manually?

    I have some orders to sort on the site this afternoon, so I will put it in maintenance mode then and await your replies.

    Thanks for your time.

    Regards
    AfterHouR

  5. #5
    Join Date
    Jan 2004
    Posts
    66,364
    Blog Entries
    7
    Plugin Contributions
    274

    Default Re: What part of databse needs updating?

    You're treading in delicate territory. Be sure to TEST TEST TEST everything in your test area BEFORE you take the finished project to your live site.
    .

    Zen Cart - putting the dream of business ownership within reach of anyone!
    Donate to: DrByte directly or to the Zen Cart team as a whole

    Remember: Any code suggestions you see here are merely suggestions. You assume full responsibility for your use of any such suggestions, including any impact ANY alterations you make to your site may have on your PCI compliance.
    Furthermore, any advice you see here about PCI matters is merely an opinion, and should not be relied upon as "official". Official PCI information should be obtained from the PCI Security Council directly or from one of their authorized Assessors.

  6. #6
    Join Date
    Aug 2005
    Location
    Arizona
    Posts
    27,761
    Plugin Contributions
    9

    Default Re: What part of databse needs updating?

    The second DB was taken from a backup of the first but I have changed product categories and moved a lot of products but the customer records structure should be the same.

    Kobra, is it that simple to do? or do I have to go in and edit the files manually?
    This could be a deal breaker or at least cause issues for order history types of items - like if in the past/previous DB product 101 was purchased - you changed it so that it no longer can be located or some other indexing issue...

    With what you have altered - my inclination would be to recommend that you go back and make the product alterations to your live site.
    When done place shop on maint- Take a full DB backup and apply to your upgrade shop.
    Rerun ZC_install - upgrade DB only.
    Check that it looks as you desire.

    Normally, one can do the last part of the above in an hour or 2 depending upon what & how much checking you need to do.
    If it seems to be too much, take shop off maint and address the issues.
    Recommend that in the future, when in the process of upgrading that you make no alterations to your operating shop and none to your shop being upgraded. I am referencing DB changes.
    File based changes are acceptable and a major part of the upgrade process.
    Zen-Venom Get Bitten

 

 

Similar Threads

  1. v151 Theme or plugin that needs updating
    By carlman in forum General Questions
    Replies: 1
    Last Post: 28 Jul 2015, 12:26 PM
  2. Replies: 2
    Last Post: 19 Sep 2012, 03:33 AM
  3. 1.38a to 1.39h upgrade problems - Never get database needs updating
    By letsmod in forum Upgrading from 1.3.x to 1.3.9
    Replies: 1
    Last Post: 9 Dec 2010, 04:13 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
disjunctive-egg
Zen-Cart, Internet Selling Services, Klamath Falls, OR