CiviCRM Community Forums (archive)

Hi

Running civicrm 2.2.7

I go through the following steps;
1. Add address to household
2. Select individual. Ensure that individual has 'member of', or 'head of' relationship to the household.
3. Select "Use Household Address", enter household name
4. Select household name.

I get an error:
Sorry. A non-recoverable error has occurred.

DB Error: no such field

Database Error Code: Unknown column 'name' in 'field list', 1054
and the following error details:

Error Details:

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

    [code] => -19
    [message] => DB Error: no such field
    [mode] => 16
    [debug_info] => UPDATE  civicrm_address  SET contact_id = 72 , location_type_id = 1 , is_primary = 1 , is_billing = 0 , street_address = '\'My Home\'' , street_number = NULL , street_number_suffix = NULL , street_number_predirectional = NULL , street_name = NULL , street_type = NULL , street_number_postdirectional = NULL , street_unit = NULL , supplemental_address_1 = 'Highworth Road' , supplemental_address_2 = 'South Marston' , supplemental_address_3 = NULL , city = 'Swindon' , county_id = NULL , state_province_id = 2796 , postal_code_suffix = NULL , postal_code = 'SN3 4SF' , usps_adc = NULL , country_id = 1226 , geo_code_1 = NULL , geo_code_2 = NULL , name = NULL   WHERE (  civicrm_address.id = 33 )   [nativecode=1054 ** Unknown column 'name' in 'field list']
    [type] => DB_Error
    [user_info] => UPDATE  civicrm_address  SET contact_id = 72 , location_type_id = 1 , is_primary = 1 , is_billing = 0 , street_address = '\'My Home\'' , street_number = NULL , street_number_suffix = NULL , street_number_predirectional = NULL , street_name = NULL , street_type = NULL , street_number_postdirectional = NULL , street_unit = NULL , supplemental_address_1 = 'Highworth Road' , supplemental_address_2 = 'South Marston' , supplemental_address_3 = NULL , city = 'Swindon' , county_id = NULL , state_province_id = 2796 , postal_code_suffix = NULL , postal_code = 'SN3 4SF' , usps_adc = NULL , country_id = 1226 , geo_code_1 = NULL , geo_code_2 = NULL , name = NULL   WHERE (  civicrm_address.id = 33 )   [nativecode=1054 ** Unknown column 'name' in 'field list']
    [to_string] => [db_error: message="DB Error: no such field" code=-19 mode=callback callback=CRM_Core_Error::handle prefix="" info="UPDATE  civicrm_address  SET contact_id = 72 , location_type_id = 1 , is_primary = 1 , is_billing = 0 , street_address = '\'My Home\'' , street_number = NULL , street_number_suffix = NULL , street_number_predirectional = NULL , street_name = NULL , street_type = NULL , street_number_postdirectional = NULL , street_unit = NULL , supplemental_address_1 = 'Highworth Road' , supplemental_address_2 = 'South Marston' , supplemental_address_3 = NULL , city = 'Swindon' , county_id = NULL , state_province_id = 2796 , postal_code_suffix = NULL , postal_code = 'SN3 4SF' , usps_adc = NULL , country_id = 1226 , geo_code_1 = NULL , geo_code_2 = NULL , name = NULL   WHERE (  civicrm_address.id = 33 )   [nativecode=1054 ** Unknown column 'name' in 'field list']"]
)

Suggestions?

Thanks![/code]

No 

Any suggestions as to where I should start digging?

Thanks Lobo.

There is indeed a pile of schema differences between the (supposedly 2.2.7) db which contains my data, and a new, empty 2.2.7 db.

Without posting the full detail, there are 20-25 areas in the schemas where there are differences.

Dumping data only from my borked db and then reimporting into the new one is not going to work - I get an error straight away (line1?!)
-- Dumping data for table `civicrm_acl`
--
INSERT INTO `civicrm_acl` ( `id` , `name` , `deny` , `entity_table` , `entity_id` , `operation` , `object_table` , `object_id` , `acl_table` , `acl_id` , `is_active` )
VALUES ( 1, 'Core ACL', 0, 'civicrm_acl_role', 1, 'All', 'access CiviReport', NULL , NULL , NULL , 1 ) , ( 2, 'Core ACL', 0, 'civicrm_acl_role', 1, 'All', 'access Report Criteria', NULL , NULL , NULL , 1 ) , ( 3, 'Core ACL', 0, 'civicrm_acl_role', 1, 'All', 'administer Reports', NULL , NULL , NULL , 1 ) ;

MySQL said: Documentation
#1062 - Duplicate entry '1' for key 1
There are only 250 or so contacts in the db (tho most have notes and activities and attachments), which I've been running on a lab basis since aug 2008. I've upgraded several times since, and as far as I have been aware, each upgrade has gone OK. The inconsistency could have occurred at any point along the way....

Am I going to be able to find a way out of this one??

Thanks Lobo - my nooby ignorance is getting embarrassing here......

I deleted civicrm.settings.php, created a new db, then ran civicrm.mysql from the command line.

When I try to import the data-only dump file fomr my old db, I'm now getting

Error

MySQL said: Documentation
#1452 - Cannot add or update a child row: a foreign key constraint fails (`civicrm4/civicrm_activity`, CONSTRAINT `FK_civicrm_activity_source_contact_id` FOREIGN KEY (`source_contact_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE CASCADE)

Eureka! thanks!

For the record:
1. create new db
2. run civicrm.mysql (I found this easier form the command line)
cd /<yourdrupalroot>/sites/all/modules/civicrm/sql
mysql -u dbusername -pdbuserpwd newmysqldbname < civicrm.mysql
3. copy structure only of new db to a second new db
4. if you have any custom-field tables in your original db, copy the structure only of those tables to your second new db
5. import data-only dump file.

............and then, you'll be back in business!

Happy to put this in the wiki if I'm guided as to where is the best place....

Done

http://wiki.civicrm.org/confluence/display/CRMUPCOMING/What+to+do+if+your+DB+gets+into+an+inconsistent+state