Author Topic: Database Inconsistency (Read 883 times)

Hershel · May 07, 2009, 07:53:22 am

I do believe that this query:

SELECT COUNT(*) FROM civicrm_email WHERE contact_id IS NULL;

Should yield 0. In a Drupal 5 CiviCRM 2.0.4 site I have however, it yields 330. This query:

Code: [Select]

SELECT * FROM civicrm_email WHERE contact_id IS NULL GROUP BY email;

reveals 23 rows and all are staff members. To clarify, the DB has nearly 40K contacts and each NPO staff member has a contact record. That second query brings up 23 such records.

I believe this is bad data and should be corrected.

Right?

Kiran Jagtap · May 07, 2009, 10:51:49 pm

hi,

Quote

SELECT COUNT(*) FROM civicrm_email WHERE contact_id IS NULL;
Should yield 0. In a Drupal 5 CiviCRM 2.0.4 site I have however, it yields 330. This query:

I don't think so.

Quote

count 1 = SELECT COUNT( * ) FROM civicrm_email WHERE contact_id IS NULL;
count 2 = SELECT (count(email_id)+count(email_2_id)) locationBlockEmailsCount FROM civicrm_loc_block WHERE ( email_id IS NOT NULL OR email_2_id IS NOT NULL);

if count 1 and count 2 are equal db is consistent.

If count is equal to number of entries in your civicrm_loc_block table that time database is consistent.

civicrm_email store :
1. email address for contact record.
2. store email address for a location block
(location block get create when you create a event location or domain address are store as location blocks.)

HTH

kiran

Hershel · May 08, 2009, 12:25:19 am

Quote from: kiran on May 07, 2009, 10:51:49 pm

civicrm_email store :
1. email address for contact record.
2. store email address for a location block
(location block get create when you create a event location or domain address are store as location blocks.)

OK, I understand better now. The civicrm_email table will store a record with contact_id null for each Location Block. That makes sense. This DB, however, still seems to have a problem. I put your queries into one:

Code: [Select]

SELECT * FROM
  (SELECT (COUNT(email_id)+COUNT(email_2_id)) locationBlockEmailsCount 
      FROM civicrm_loc_block 
      WHERE ( email_id IS NOT NULL OR email_2_id IS NOT NULL)
   ) AS q1,
  (SELECT COUNT( * ) count2 
    FROM civicrm_email 
    WHERE contact_id IS NULL
   ) AS q2;

and the result is:

Code: [Select]

"locationBlockEmailsCount"  "count2"
"520"                       "330"

So it seems something is not quite right here I think.

CiviCRM Community Forums (archive)

News:

Author Topic: Database Inconsistency (Read 883 times)

Hershel

Database Inconsistency

Kiran Jagtap

Re: Database Inconsistency

Hershel

Re: Database Inconsistency