Author Topic: Find Members, View Membership; "DB Error: no such field" (Read 1256 times)

gholt · May 19, 2011, 07:30:46 pm

I'm running Drupal 6.20 and CiviCRM 3.4.1. I'm getting "DB Error: no such field" when I try to do a "Find Members" search. It doesn't seem to matter which parameters I choose. I also get the same error when I try to delete a membership from the membership tab while viewing a contact. The default membership reports work fine.

Here's the full text of the error message:

Code: [Select]

Database Error Code: Unknown column 'civicrm_membership.campaign_id' in 'field list', 1054
Additional Details:

Array
(
    [callback] => Array
        (
            [0] => CRM_Core_Error
            [1] => handle
        )

    [code] => -19
    [message] => DB Error: no such field
    [mode] => 16
    [debug_info] => SELECT  DISTINCT(civicrm_membership.id), contact_a.id as contact_id, contact_a.contact_type  as `contact_type`, contact_a.contact_sub_type  as `contact_sub_type`, contact_a.sort_name  as `sort_name`, contact_a.display_name  as `display_name`, civicrm_membership.id as membership_id, civicrm_membership.join_date as join_date, civicrm_membership.start_date as membership_start_date, civicrm_membership.end_date as  membership_end_date, civicrm_membership.source as membership_source, civicrm_membership.owner_membership_id as owner_membership_id, civicrm_membership.is_test as `member_is_test`, civicrm_membership.is_pay_later as `member_is_pay_later`, civicrm_membership.campaign_id as member_campaign_id, civicrm_membership_type.id as membership_type_id, civicrm_membership_type.name as membership_type, civicrm_membership_status.id as membership_status_id, civicrm_membership_status.label as membership_status, civicrm_membership.contribution_recur_id as membership_recur_id  FROM civicrm_contact contact_a LEFT JOIN civicrm_membership ON civicrm_membership.contact_id = contact_a.id  LEFT JOIN civicrm_contribution_recur ccr ON ( civicrm_membership.contribution_recur_id = ccr.id ) INNER JOIN civicrm_membership_status ON civicrm_membership.status_id = civicrm_membership_status.id  INNER JOIN civicrm_membership_type ON civicrm_membership.membership_type_id = civicrm_membership_type.id  WHERE  ( civicrm_membership.membership_type_id = 4 AND civicrm_membership.status_id = 2 AND civicrm_membership.is_test = 0 )  AND (contact_a.is_deleted = 0)    ORDER BY join_date desc  LIMIT 0, 50  [nativecode=1054 ** Unknown column 'civicrm_membership.campaign_id' in 'field list']
    [type] => DB_Error
    [user_info] => SELECT  DISTINCT(civicrm_membership.id), contact_a.id as contact_id, contact_a.contact_type  as `contact_type`, contact_a.contact_sub_type  as `contact_sub_type`, contact_a.sort_name  as `sort_name`, contact_a.display_name  as `display_name`, civicrm_membership.id as membership_id, civicrm_membership.join_date as join_date, civicrm_membership.start_date as membership_start_date, civicrm_membership.end_date as  membership_end_date, civicrm_membership.source as membership_source, civicrm_membership.owner_membership_id as owner_membership_id, civicrm_membership.is_test as `member_is_test`, civicrm_membership.is_pay_later as `member_is_pay_later`, civicrm_membership.campaign_id as member_campaign_id, civicrm_membership_type.id as membership_type_id, civicrm_membership_type.name as membership_type, civicrm_membership_status.id as membership_status_id, civicrm_membership_status.label as membership_status, civicrm_membership.contribution_recur_id as membership_recur_id  FROM civicrm_contact contact_a LEFT JOIN civicrm_membership ON civicrm_membership.contact_id = contact_a.id  LEFT JOIN civicrm_contribution_recur ccr ON ( civicrm_membership.contribution_recur_id = ccr.id ) INNER JOIN civicrm_membership_status ON civicrm_membership.status_id = civicrm_membership_status.id  INNER JOIN civicrm_membership_type ON civicrm_membership.membership_type_id = civicrm_membership_type.id  WHERE  ( civicrm_membership.membership_type_id = 4 AND civicrm_membership.status_id = 2 AND civicrm_membership.is_test = 0 )  AND (contact_a.is_deleted = 0)    ORDER BY join_date desc  LIMIT 0, 50  [nativecode=1054 ** Unknown column 'civicrm_membership.campaign_id' in 'field list']
    [to_string] => [db_error: message="DB Error: no such field" code=-19 mode=callback callback=CRM_Core_Error::handle prefix="" info="SELECT  DISTINCT(civicrm_membership.id), contact_a.id as contact_id, contact_a.contact_type  as `contact_type`, contact_a.contact_sub_type  as `contact_sub_type`, contact_a.sort_name  as `sort_name`, contact_a.display_name  as `display_name`, civicrm_membership.id as membership_id, civicrm_membership.join_date as join_date, civicrm_membership.start_date as membership_start_date, civicrm_membership.end_date as  membership_end_date, civicrm_membership.source as membership_source, civicrm_membership.owner_membership_id as owner_membership_id, civicrm_membership.is_test as `member_is_test`, civicrm_membership.is_pay_later as `member_is_pay_later`, civicrm_membership.campaign_id as member_campaign_id, civicrm_membership_type.id as membership_type_id, civicrm_membership_type.name as membership_type, civicrm_membership_status.id as membership_status_id, civicrm_membership_status.label as membership_status, civicrm_membership.contribution_recur_id as membership_recur_id  FROM civicrm_contact contact_a LEFT JOIN civicrm_membership ON civicrm_membership.contact_id = contact_a.id  LEFT JOIN civicrm_contribution_recur ccr ON ( civicrm_membership.contribution_recur_id = ccr.id ) INNER JOIN civicrm_membership_status ON civicrm_membership.status_id = civicrm_membership_status.id  INNER JOIN civicrm_membership_type ON civicrm_membership.membership_type_id = civicrm_membership_type.id  WHERE  ( civicrm_membership.membership_type_id = 4 AND civicrm_membership.status_id = 2 AND civicrm_membership.is_test = 0 )  AND (contact_a.is_deleted = 0)    ORDER BY join_date desc  LIMIT 0, 50  [nativecode=1054 ** Unknown column 'civicrm_membership.campaign_id' in 'field list']"]
)

I was getting this error under 3.4.0, so I updated to 3.4.1, and I'm still getting the same error.

Any tips on how can I fix this, or how I can troubleshoot it?

I can't find anything that seems to match in the forums. And I don't have enough experience to determine the problem from the error message.

Thanks in advance.

gholt · May 19, 2011, 08:23:21 pm

As an update, I just tried to import members, and I got the same "DB Error: no such field".

Here are the error details:

Code: [Select]

Database Error Code: Unknown column 'membership_type_id' in 'field list', 1054
Additional Details:

Array
(
    [callback] => Array
        (
            [0] => CRM_Core_Error
            [1] => handle
        )

    [code] => -19
    [message] => DB Error: no such field
    [mode] => 16
    [debug_info] => INSERT INTO civicrm_membership_log (membership_id , status_id , start_date , end_date , modified_id , modified_date , renewal_reminder_date , membership_type_id ) VALUES ( 56 ,  2 ,  20101001 ,  20110930 ,  102 ,  20110519 ,  NULL ,  6 )  [nativecode=1054 ** Unknown column 'membership_type_id' in 'field list']
    [type] => DB_Error
    [user_info] => INSERT INTO civicrm_membership_log (membership_id , status_id , start_date , end_date , modified_id , modified_date , renewal_reminder_date , membership_type_id ) VALUES ( 56 ,  2 ,  20101001 ,  20110930 ,  102 ,  20110519 ,  NULL ,  6 )  [nativecode=1054 ** Unknown column 'membership_type_id' in 'field list']
    [to_string] => [db_error: message="DB Error: no such field" code=-19 mode=callback callback=CRM_Core_Error::handle prefix="" info="INSERT INTO civicrm_membership_log (membership_id , status_id , start_date , end_date , modified_id , modified_date , renewal_reminder_date , membership_type_id ) VALUES ( 56 ,  2 ,  20101001 ,  20110930 ,  102 ,  20110519 ,  NULL ,  6 )  [nativecode=1054 ** Unknown column 'membership_type_id' in 'field list']"]
)

I did a test import 2-3 weeks ago with the same columns/fields and it worked fine. Unfortunately, I didn't log whether I did that test import before or after updating to 3.4.0, but I think the successful import happened under 3.3.6.

speleo · May 19, 2011, 10:27:28 pm

Looks like somewhere down the line a schema update has not added the fields. I'd suggest that the quickest and safest way to move forward is to rebuild the database against a new table.

http://wiki.civicrm.org/confluence/display/CRMDOC40/Ensuring+Schema+Integrity+on+Upgrades

Took me under an hour to do this last week.

gholt · May 20, 2011, 10:15:57 am

Thanks, speleo. I'm a little confused though about how to adapt it to my situation.

First, since I didn't get an error message or warning during an upgrade, I've made changes to my database over the past 3 weeks since my update to 3.4.0 and then the update to 3.4.1. How can I eliminate the need to make all of those changes again if the instructions say to use the structure of the database back before all of those changes have been made?

Changes I've made to the database over the past 3 weeks include:
1. creation of custom field sets and custom fields
2. imported a test set of contacts
3. imported a test set of memberships (still working fine at that point)
4. updated to 3.4.0 at this point, or possibly before #3
5. imported the full set of contacts (a little less than 500); I had some problems with the import of these contacts. It failed part way through, so I broke the csv file into smaller numbers of contacts and eventually got them all imported, but I still had a couple of failed imports that I had to repeat -- even with the smaller data sets.
6. started getting the error messages when I was checking the membership list (using Find Members) before I was about to import the full set of memberships.

So, I'm hoping I don't have to revert to a backup from before I made those changes. Any advice?

gholt · May 22, 2011, 12:38:07 pm

The procedure recommended by speleo (to ensure that the schema matches current version) says:

"Procedure to rebuild schema for database versions 2.2.x or later
Take a DATA dump of existing, intact database. If possible, do not dump data from a database upon which you have attempted an upgrade and failed. Dump data from a database backup you did prior to upgrading that has a schema version number that is accurate. Find this information in civicrm_domain.version."

I checked, and it says 3.3.5. First, I don't think I updated to version 3.3.5. According to my notes, I went from 3.3 to 3.3.6, 3.4, and 3.4.1.

Does that mean that I need to rebuild the database from a back-up from when I was still running 3.3?

gholt · May 22, 2011, 01:27:03 pm

Hmmm. I stumbled upon the "Administration Console" (I'm new to CiviCRM) and found a note that my database structure was still set for 3.3.5, and that I needed to update it, and it provided a link. I followed that link, upgraded the database, and it seems to have fixed my DB errors! Yeah! (I think/hope.)

I don't remember reading anything about the value of visiting the "Administration Console" on a regular basis, but I'll make it a habit now. Also, wouldn't it be a good idea to always check the civicrm_domain version number in the database after every upgrade? If so, it would be helpful to us newbies for it to be added to the upgrade instructions.

Now, things seem to be working, so I've backed-up my DB again, and I'm starting to troubleshoot the note in the administration console that tells me to "convert any MyISAM tables in your database to InnoDB". I've found some forum discussions on this, and I'm heading that way.

Speleo, thanks again for the help.

gholt · May 22, 2011, 03:10:56 pm

Just a note in case someone else comes upon this thread with a similar problem. The reason I wasn't updating the civicrm database as per the instructions for upgrading is because I thought it was telling me to run the update script in Drupal, which I've been doing diligently.
;-)
But I realize now that this was updating the Drupal DB, not the CiviCRM database. I'm guessing my future updates/upgrades will go more smoothly.

CiviCRM Community Forums (archive)

News:

Author Topic: Find Members, View Membership; "DB Error: no such field" (Read 1256 times)

gholt

Find Members, View Membership; "DB Error: no such field"

gholt

Re: Find Members, View Membership; "DB Error: no such field"

speleo

Re: Find Members, View Membership; "DB Error: no such field"

gholt

Re: Find Members, View Membership; "DB Error: no such field"

gholt

Re: Find Members, View Membership; "DB Error: no such field"

gholt

Re: Find Members, View Membership; "DB Error: no such field"

gholt

Re: Find Members, View Membership; "DB Error: no such field"