CiviCRM Community Forums (archive)

see http://forum.civicrm.org/index.php/topic,9618.0.html for further.  essentially, a screwed up upgrade led to us having to data dump and start with a fresh install; however, we discovered, in restoring to a fresh 2.2.8 db then reimporting, that we lost CiviReport upon completing the re-import whole cloth.  we then stepped through the import table by table and found it was these final three tables: civicrm_domain, civicrm_menu and civicrm_option_value that led to losing CiviReport when we imported them.

however, when we imported them manually (with foreign key constraints off) obviously that caused its own host of problems.

we now have our database in a state where it mostly functions; however, we're missing some crucial features, such as the menu tabs on /civicrm/contact/view?reset=1, and also there are problems with display data of contributions. 

what we'd like to do is dump our data once again and start fresh with all the menu option stuff:  what is the best method to procced?  start with a fresh install, yet somehow preserve as much of the contributor data as possible for re-import.  If possible, we'd also like to preserve our contribution pages, groups and contacts.  It's really only the menu/display options that appear to be messed up and need to be replaced, but we don't know how that corresponds to the db tables.   What are our options?

thanks for any assistance.

->Arrow

>maintaining the consistency of option group/values across the old/new data set

we aren't interested in doing that.  we can just re-input our choices for the option group values.  it's the admin and user data we would like to preserve as much as possible. 

I like the idea of using export.php/import.php.  is there a doc on its usage?  can I use that to export/import the admin and user data? 

so, if the export/import scripts are for custom/profile and option groups, then I will use it to dump the custom groups and reimport.  what are profiles?  we don't want the option group data since it's all screwy (unless you think dumping it to xml and re-importing it will fix it somehow; remember, our foreign keys are messed up). 

I promise if I end up using the export/import script I will doc it; I'm very good at writing docs. 

of these four you mentioned:

user data (contact record, contribution record etc)
option group/values etc (this has both system data and admin data (typically at the end))
admin data (contribution pages)
system data (menu, navigation etc)

you said we would get system data from the fresh install, obviously we'll use xml for groups/profile, so the remainder of what we transfer from the old install is user data and admin data.  what is the best approach in preserving user data and admin data?  can I just dump those tables and import over a fresh install?  will things get messed up if I try?  which tables, precisely?  I'm still really lost as to the exact steps on how to proceed.

thanks once again for your patience, lobo.  do you like chocolate? 

hey lobo:

as far as I can see, even the contact info has a bunch of key constraints that could cause us problems.  and there are so many tables.  is there no tool for dumping the data that is *not* associated with menus or options and re-importing that data clean?  e.g., I have a user. I don't care if that user's id is 2 or 53, ultimately, I just care that I dump that user from one session and when I reimport, her all her associated contributions are still related to her...but I don't know how to import one table and make sure the foreign keys are updated while I am re-importing....how do I do that?

it's either that, or go back to fixing the broken database somehow.   

heyo lobo:

I'm encountering a problem using the import script in bin/migrate. the CRM core insert routine is giving me a 'duplicate' error for the 'extends' field, which is plainly silly:  you should be able to have duplicate values in that field, I'm thinking, particularly since I exported the xml from a functioning db (the groups are fine in that db) where that field was duplicated numerous times.  I'm attaching the xml file truncated for size, if you have time to take a look.  Do you have any clue about this behavior or why I'm getting this error?  what is the purpose of extending "Contacts" in this context? 

Thanks for any help on this issue. We'd love to retain our custom groups. We're going to start fresh with just contacts and whatever we can export and import manually.

stage:/var/www/drupal/sites/all/modules/civicrm/bin/migrate# php limited_import.php
value: _b_Where_you_heard_of_OTW_b_
value:
value: Contact
value: Inline
value: 0
value:
value:
value: 1
value: 0
value: civicrm_value_where_they_heard_of_us_1
value: 0
value: 0
value: 0
value: _b_Newsletter_b_
value:
value: Contact
value: Inline
value: 0
value:
value:
value: 2
value: 1
value: civicrm_value_newsletter_2
value: 0
value: 0
value: 0
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" lang="en" xml:lang="en">

<head>
  <title></title>
  <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
  <base href="http://stage.transformativeworks.org/sites/all/modules/civicrm/" />
  <style type="text/css" media="screen">@import url(http://stage.transformativeworks.org/sites/all/modules/civicrm/css/civicrm.css);</style>
</head>

<body>

<div id="crm-container" lang="en" xml:lang="en">

<div class="messages status">
  <dl>
  <dt><img src="http://stage.transformativeworks.org/sites/all/modules/civicrm/i/Error.gif" alt="unrecoverable error" /></dt>
  <dd>
      Sorry. A non-recoverable error has occurred.      <p></p>
    <hr style="solid 1px" />
    <p>DB Error: already exists</p>
      <p>Database Error Code: Duplicate entry 'Contact' for key 2, 1062</p>
      <p><a href="http://stage.transformativeworks.org/" title="Main Menu">Return to home page.</a></p>
  </dd>
  </dl>
</div>

</div>
</body>
</html><p>Error Details:</p><p><pre>Array
(
    [callback] =&gt; Array
        (
           

• =&gt; CRM_Core_Error
[1] =&gt; handle
        )

   

=> -5
    [message] => DB Error: already exists
    [mode] => 16
    [debug_info] => INSERT INTO civicrm_custom_group (name , title , extends , style , collapse_display , help_pre , help_post , weight , is_active , table_name , is_multiple , min_multiple , max_multiple ) VALUES ('_b_Newsletter_b_' , '' , 'Contact' , 'Inline' ,  0 , '' , '' ,  2 ,  1 , 'civicrm_value_newsletter_2' ,  0 ,  0 ,  0 )  [nativecode=1062 ** Duplicate entry 'Contact' for key 2]
    [type] => DB_Error
    [user_info] => INSERT INTO civicrm_custom_group (name , title , extends , style , collapse_display , help_pre , help_post , weight , is_active , table_name , is_multiple , min_multiple , max_multiple ) VALUES ('_b_Newsletter_b_' , '' , 'Contact' , 'Inline' ,  0 , '' , '' ,  2 ,  1 , 'civicrm_value_newsletter_2' ,  0 ,  0 ,  0 )  [nativecode=1062 ** Duplicate entry 'Contact' for key 2]
    [to_string] => [db_error: message="DB Error: already exists" code=-5 mode=callback callback=CRM_Core_Error::handle prefix="" info="INSERT INTO civicrm_custom_group (name , title , extends , style , collapse_display , help_pre , help_post , weight , is_active , table_name , is_multiple , min_multiple , max_multiple ) VALUES ('_b_Newsletter_b_' , '' , 'Contact' , 'Inline' ,  0 , '' , '' ,  2 ,  1 , 'civicrm_value_newsletter_2' ,  0 ,  0 ,  0 )  [nativecode=1062 ** Duplicate entry 'Contact' for key 2]"]
)