Strange Account import error in 5.0.

[spectheintro]

Hey all. I am trying to import records from 4.5.1h into a clean 5.0 install. It consistently fails as soon as I get to an account with a "parent ID." Originally, I thought this might be because the parent account did not exist in the database yet, so I re-sorted the CSV to make sure all of the accounts with a blank Parent ID field were imported before any others. This resulted in all of those accounts being imported successfully, but the first account with a populated Parent ID field returning the same error. Here's the results from sugarcrm.log:

 Wed Feb  6 11:20:22 2008,350 [31950] INFO SugarCRM - select * from accounts WHERE id='abcdefg-0012890-defhjlu-000022'
Wed Feb  6 11:20:22 2008,350 [31950] INFO SugarCRM - Query:select * from accounts WHERE id='abcdefg-0012890-defhjlu-000022'
Wed Feb  6 11:20:22 2008,351 [31950] INFO SugarCRM - Query Execution Time:0.00037
Wed Feb  6 11:20:22 2008,444 [31950] INFO SugarCRM - Query:SELECT * FROM fields_meta_data WHERE custom_module='Accounts' AND deleted = 0
Wed Feb  6 11:20:22 2008,444 [31950] INFO SugarCRM - Query Execution Time:0.000176
Wed Feb  6 11:20:22 2008,457 [31950] INFO SugarCRM - Query:SELECT * FROM fields_meta_data WHERE custom_module='Accounts' AND deleted = 0
Wed Feb  6 11:20:22 2008,458 [31950] INFO SugarCRM - Query Execution Time:0.000202
Wed Feb  6 11:20:22 2008,472 [31950] INFO SugarCRM - select * from accounts WHERE id='abcdefg-0012890-defhjlu-000725'
Wed Feb  6 11:20:22 2008,472 [31950] INFO SugarCRM - Query:select * from accounts WHERE id='abcdefg-0012890-defhjlu-000725'
Wed Feb  6 11:20:22 2008,473 [31950] INFO SugarCRM - Query Execution Time:0.000378
Wed Feb  6 11:20:22 2008,664 [31950] INFO SugarCRM - Query:SELECT * FROM fields_meta_data WHERE custom_module='Accounts' AND deleted = 0
Wed Feb  6 11:20:22 2008,665 [31950] INFO SugarCRM - Query Execution Time:0.000194
Wed Feb  6 11:20:22 2008,680 [31950] INFO SugarCRM - Query:INSERT INTO accounts_cstm (id_c ,hea_car_c ,hea_ren_c ,fsa_c ,life_ven_c ,life_ren_c ,life_max_c ,std_ven_c ,std_ren_c ,std_ben_c ,std_elim_c ,std_dur_c ,life_core_c ,std_core_c ,std_max_c ,ltd_ven_c ,ltd_ren_c ,ltd_ben_c ,ltd_dur_c ,ltd_elim_c ,ltd_own_c ,ltd_max_c ,ltd_core_c ,den_ven_c ,den_plan_c ,den_ren_c ,den_type_c ,den_in_c ,den_out_c ,den_fam_in_c ,den_fam_out_c ,den_inprev_c ,den_outprev_c ,den_inbase_c ,den_outbase_c ,den_inmaj_c ,den_outmaj_c ,den_core_c ,den_ort_c ,den_max_c ,den_ortmax_c ,den_pla2_c ,den_typ2_c ,den_in2_c ,den_out2_c ,den_fam_in2_c ,den_fam_out2_c ,den_inprev2_c ,den_outprev2_c ,den_inbase2_c ,den_outbase2_c ,den_inmaj2_c ,den_outmaj2_c ,den_core2_c ,den_ort2_c ,den_max2_c ,den_ortmax2_c ) VALUES ('' ,'none' ,'na' ,'none' ,'none' ,'na' ,'na' ,'none' ,'na' ,'na' ,'na' ,'na' ,'na' ,'na' ,'na' ,'none' ,'na' ,'na' ,'na' ,'na' ,'na' ,'na' ,'na' ,'none' ,'na' ,'na' ,'na' ,'na' ,'na' ,'na' ,'na' ,'na' ,'na' ,'na' ,'na' ,'na' ,'na' ,'na' ,'na' ,'na' ,'na' ,'na' ,'na' ,'na' ,'na' ,'na' ,'na' ,'na' ,'na' ,'na' ,'na' ,'na' ,'na' ,'na' ,'na' ,'na' ,'na' )
Wed Feb  6 11:20:22 2008,774 [31950] INFO SugarCRM - Query Execution Time:0.09371
Wed Feb  6 11:20:22 2008,774 [31950] ERROR SugarCRM - MySQL error 1062:
Wed Feb  6 11:20:22 2008,775 [31950] INFO SugarCRM - Insert: INSERT into accounts set id=null, name=null, date_entered='2008-02-06 16:20:22', date_modified='2008-02-06 16:20:22', modified_user_id='1', created_by='1', deleted='0', parent_id='abcdefg-0012890-defhjlu-000725'
Wed Feb  6 11:20:22 2008,775 [31950] INFO SugarCRM - Save: INSERT into accounts set id=null, name=null, date_entered='2008-02-06 16:20:22', date_modified='2008-02-06 16:20:22', modified_user_id='1', created_by='1', deleted='0', parent_id='abcdefg-0012890-defhjlu-000725'
Wed Feb  6 11:20:22 2008,776 [31950] INFO SugarCRM - Query:INSERT into accounts set id=null, name=null, date_entered='2008-02-06 16:20:22', date_modified='2008-02-06 16:20:22', modified_user_id='1', created_by='1', deleted='0', parent_id='abcdefg-0012890-defhjlu-000725'
Wed Feb  6 11:20:22 2008,776 [31950] INFO SugarCRM - Query Execution Time:0.000186
Wed Feb  6 11:20:22 2008,776 [31950] FATAL SugarCRM - MySQL error 1048: 
  

Any help would be greatly appreciated: because of the large amount of custom fields, a manual import to the mysql database is highly undesirable. Thanks!

[spectheintro]

Can anyone help me with this? I would really prefer to not have to manually re-associate 2500 accounts with their parents.

[kpit]

You will have to upgrade Sugar to match the instance you are trying to copy the data.. the structure changes on major version changes. and the databases are incompatible. unless you do the manipulations to make it match.

[mrvinch]

I had many errors trying to upgrade my 4.5.1d to 5.0.0a, the end result was a dead Sugar install beyond my ability to repair.

For what it's worth, this is how I moved from 4.5 to 5.0
(it worked very well, but keep inmind I have a very small DB and didn't care about losing customised pages.)
1. Using PHPMyAdmin exported my 4.5 DB to a SQL text file
2. Downloaded and installed a clean 4.5.1i edition. ENSURING all DB users, names, locations etc were identical to my original 4.5.1d and my planned 5.0.0a editions.
3. Using PHPMyAdmin, dropped ALL tables from the NEW 4.5.1i DB and IMPORTED the DB from the SQL text file created in step 1.

I now have a fully functioning, 4.5.1i Sugar with my data (less any custom pages)

4. Using the upgrade wizard and the locally saved file SugarCE-Upgrade-4.5.1-to-5.0.0a.zip I upgraded the 4.5.1i edition to 5.0.0a
(this was reasonalbly smooth. It hung once on the scroll bar after clicking 'Upload File' after browsing to SugarCE-Upgrade-4.5.1-to-5.0.0a.zip. I clicked BACK in the browser to the CRM home page and re-entered the Upgrade Wizard from the Admin page. The upgrade process picked up where it had hung... not sure why but it worked)
There were a few cosmetic problems with the upgrade 5.0.0a edition, but nothing drastic.
5. Using PHPMyAdmin, EXPORT to newly upgraded 5.0.0a DB to an SQL text file.
6. Download and install a clean 5.0.0a edition. New DB, but SAME DB User and pass (I don't think the user and pass bit is critical). Log in and verify all is working in the CRM
7. Using PHPMyAdmin DROP ALL tables from the newly installed and clean 5.0.0a DB then IMPORT your data from the SQL text file created in step 5.

Barring unforseen issues (earthquakes, floods etc) you should have a 5.0.0a Sugar edition with all of your data (less any custom pages). I hope this helps anyone, even a little, as I well understand the frustration of such a great system running on community maintained doco.

Cheers,
Nick