Upgrade issues with ADDRESS_BOOK

[carefreews]

I recently upgraded a site to Online Merchant v2.2 Release Candidate 2a (not sure what I was running before, maybe 2.1?).

 

I'm having issues with the ADDRESS_BOOK. Existing customers work fine... (as long as they don't edit/add an address book entry). New customers who create an account do not have an address associated with their account at checkout time.

 

It appears that the DB schema changed in that ADDRESS_BOOK_ID is now supposed to increment 1...N (where before it was always 1 for a given customer's first address, 2 for the next, etc.)

 

Since we have over 1,000 customers, is there any way to migrate to the new schema? Is there a script I can run to convert to the new format? Or can I make a code change to allow it to work as it has in the past?

 

Please help, we have a live site that cannot accept new customers right now!

 

Mike

[♥ecartz]

You might want to look into finding a copy of 2.2 MS2, as that is around the time when this change was made. If you look in that distribution, you can find update code that looks like the following:

  /* Now convert the address_book_id to unique entries, now most are =1 */
 tep_db_query("alter table address_book add temp_id int(11) not NULL default '0' FIRST");
 $ab_query = tep_db_query("select customers_id, address_book_id from address_book order by address_book_id");
 $ab_id = 1;
 while ($ab = tep_db_fetch_array($ab_query)) {
   tep_db_query("update customers set customers_default_address_id = '" . $ab_id . "' where customers_id = '" . $ab['customers_id'] . "'");
   tep_db_query("update address_book set temp_id = '" . $ab_id . "' where customers_id = '" . $ab['customers_id'] . "' and address_book_id = '" . $ab['address_book_id'] . "'");
   $ab_id++;
 }

 tep_db_query("ALTER TABLE address_book DROP PRIMARY KEY");
 tep_db_query("ALTER TABLE address_book DROP COLUMN address_book_id");
 tep_db_query("ALTER TABLE address_book ADD PRIMARY KEY (temp_id)");
 tep_db_query("ALTER TABLE address_book CHANGE COLUMN temp_id address_book_id int(11) NOT NULL auto_increment");
 tep_db_query("ALTER TABLE address_book ADD INDEX idx_address_book_customers_id (customers_id)");

 tep_db_query("ALTER TABLE customers CHANGE COLUMN customers_default_address_id customers_default_address_id int(11) NOT NULL default '0'");

Note that the suggested way to use this would be to backup your database, create a new database and upload your backup to it, then run the commands on that database. If that works, you can then use this code on your existing database.

 

In general (for anyone else who might be thinking about upgrading), if you are updating from an old version of osCommerce (2.1, 2.2 MS1, etc.), it's best to follow all the upgrades. In this case, you would have wanted to upgrade from MS1 to MS2 before upgrading to RC2a.