CiviCRM Community Forums (archive)

Hello all,

I'm a CiviCRM power user with developer experience in other CRM systems (especially NetSuite). My background is in database analysis, design, and development.  I'm now working for a not-for-profit that's using CiviCRM 3.1 (the upgrade is in the planning stages) that is having problems with their data from a less than satisfactory conversion from 2.2 to 3.1.  In particular the household and individual contacts need some corrective work.

My first thought was to just fire up MySQLWorkbench, connect to a copy of the database and begin work.  I reverse-engineered the schema so I can get an idea of how the data is structured.  The first problem I checked is the household_name and display_name values in household contacts, so I created the query to find problems with a specific contact:

SELECT id, contact_type, household_name, display_name, first_name, middle_name, last_name
FROM civicrm_contact
WHERE display_name LIKE "%smithe%" AND display_name "%john%";

Then in the Search Builder I created a search to do the same by selecting the record type "Household" and using the same criteria as above.

Name or Email LIKE - '%smithe%' ...AND...
 Name or Email LIKE - '%john%' ...AND...
 Contact Type - 'Household'

The three problem contacts show up in both result sets, however I noticed that the following.

* In the SQL query results the value of contact_type is "Individual" rather than the expected "Household".  In fact when perform a SQL query to see how many records there are for each contact_type there is only one household but when I use the Search Builder it finds over 250K contacts of type household. Why? 
* It was obvious by inspecting the household details in Civi contact page that the civicrm_contact.id field is the External ID that shows up in details of the Search Results page.  But I don't see what field in the table is related to the "CiviCRM ID" on the Search Results.  Where is it?

The question you may be thinking is, "why doesn't he write PHP code to do the queries?"  The quick answer is - why do I have to use another tool to get to the data when the data is stored in MySQL? 

-Ricardo

Thanks Dave; I had found CiviCRM DB Tools earlier that afternoon so I'm glad you referred it.  I read through the documentation and found that it was meant to be used up to CiviCRM 3.0, so I kept this in mind as I plowed forward.

I ran the integrity check and the first thing I noticed was:

   Got version 2.2.0 from civicrm_domain.version

I don't think this is good news if the conversion was supposed to bring the database to version 3.1.  As you might imagine there were screens of warnings and errors as the integrity check scanned the database.  I'm going to need to review the reference 3.1 schema to compare the schema errors/warnings.

I'll probably try manually updated the version value just to see how the integrity check responds., but if the guy to supposedly did this conversion missed something as fundamental as changing the domain version then it looks like I have a lot of work ahead of me.

This might explain some of the anomalies I'm seeing when I query the database directly versus using the Search Builder, as well as some of the other quirks that the staff has related to me.

Once I've verified the extent of the problems, then I'll review at my options to move forward.  Any suggestions if what the integrity check reported is mostly true?

-R

Ricardo,

The CiviFolks have announced they don't plan to develop 3.x beyond 3.4 (though there is a make-it-happen initiative to extend that).

Ken