osc 2.2ms2 database changes

[syscon]

I'm trying to make changes to my old database so I can import it to new installation. According to program mysqldiff.pl I need to make few changes to my database:

--------

ALTER TABLE address_book CHANGE COLUMN address_book_id address_book_id int(11) NOT NULL auto_increment; # was int(11) NOT NULL default '1'

 

ALTER TABLE address_book ADD INDEX idx_address_book_customers_id (customers_id);

ALTER TABLE address_book DROP PRIMARY KEY; # was ((address_book_id,customers_id))

ALTER TABLE address_book ADD PRIMARY KEY ((address_book_id));

----------

 

The problem is the previous PRIMARY INDEX was based on address_book_id + customers_id this generated a unique number; I've added the INDEX "idx_address_book_customers_id" but when I try to drop the PRIMARY KEY Index I get an error message:

 

Incorrect table definition; There can only be one auto column and it must be defined as a key

 

I'm getting an error when I try to ADD PRIMARY KEY address_book_id

Duplicate entry '1' for key 1

 

I think because there are duplicate entires in the column.

Do I need to rename entries in column "address_book_id" to unique number?

 

If I try to do that I'm getter a message that there are "Affected rows:"

 

Joseph :!:

[Guest]

As a quick hint in case it stimulates someone to come up with a more robust solution, you might want to dump the contents of the table to a text file or something (without the address_book_id), drop the table, and recreate it in the new form.

 

Hth,

Matt

[syscon]

I was thinking about it but I don't know how the data will be affected as every time I change "address_book_id" to a unique number a message comes up "Affected rows" with some entries.

 

I don't know how will this affect the linking of the customer to their purchase orders; I think in the old setup it was done by the Primary Index and that was Customer_ID + Address_book_ID.

 

If I go and alter the number as I wish, will the linking to the sales order be lost? I'm not sure; can anybody with more experience in MYSQL answer this?

 

Joseph

[syscon]

Update!

 

Renaming "address_book_id" to unique number ONE BY ONE seemed to work.

Slow and painful solution but it is working.

 

If anybody has better idea please post it.

 

Joseph.

[sgflowers]

Update!

 

Renaming "address_book_id" to unique number ONE BY ONE seemed to work.

Slow and painful solution but it is working.  

 

If anybody has better idea please post it.

 

Joseph.

 

that is what i ended up doing probably there is an easier way but it works!

[syscon]

Update Again. IT IS NOT WORKING!

 

Fixing "address_book_id" index manually isn't working.

Customers appear to be linked to the correct past orders but when the customer returns and try to place a new order the information in database is empty with the following error message:

 

Warning: reset()[function.reset]: Passed variable is not an array or object in .../catalog/admin/includes/classes/object_info.php on line 17 line 18

 

Back to square one.

 

Joseph :idea:

[syscon]

Problem SOLVED.

 

This time 99.99% :D

 

There is a directory EXTRA - pr21_to_pr22 The solution is there!

 

I wish I noticed it earlier.

 

Joseph

[markchiu]

Hi, I am so sorry, syscon, but could you please post the solution step by step, I am having the same problem, I am very stupid at fixing php problem..thank you so much or anyone will be very appreciated as well...thank you for the help in advance :unsure: :(