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.
Bookmarks