CiviCRM Community Forums (archive)

Hi,

I'm getting this error at Step One:
Database Error Code: Subquery returns more than 1 row, 1242

Here's the query:
SET @dupe_field_names = (SELECT name FROM civicrm_custom_field group by name having count(*) > 1)

Here's the result of manually running SELECT name FROM civicrm_custom_field group by name having count(*) > 1 :
+---------------------------------+
| name                            |
+---------------------------------+
| Food_in_Community__Groups_You_W |
| Producing_Food__Freedom_Foods_C |
| Producing_Food__Organic_Certifi |
| Producing_Food__Supply_Public_S |
+---------------------------------+

CiviCRM 2.0.5
Drupal 5.8
MySQL 5.0.45 (also tested on 5.0.22).

I'm upgrading a test copy of a 1.8 db. I've gone through the intermediate upgrades and the "Rebuild your 1.9 Database to Ensure Schema Integrity" steps. I had previously got past this stage in a trial upgrade of the same 1.8 db to 2.0.3, so this may be a new error since 2.0.3 . I documented the steps I took in my 2.0.3 trial upgrade and have followed them here.

   1. Upgrade db from 1.8 to 1.9 - see  Upgrade from Prior Versions:
          * Create civicrm19 and import dump of civicrm18.
          * Run civicrm_upgradedb_v1.8_v1.9_41.mysql from 2.0.5 on civicrm19 - OK.
   2. Rebuild your 1.9 Database to Ensure Schema Integrity:
          * Create civicrm20 and run civicrm_41.mysql from civicrm-1.9.13019-drupal-php5 on it.
          * Dump the data ONLY from civicrm19:
            mysqldump -u root -p -c -e -n -t civicrm19 > ...
          * Import this data dump into civicrm20.
   3. Reset config_backend:
      update civicrm_domain set config_backend = NULL where id = 1
   4. Check perms on files/ .
   5. Visit /index.php?q=civicrm/upgrade&reset=1 ->
      Upgrade Step One (Database Cleanup)

Any help appreciated. Thanks,

Dave

Thanks Lobo, will do so as soon as I receive permission from the data's owner. Which would be more helpful, the original 1.8 db or after upgrading to 1.9 & rebuilding?

Dave,
The error you noticed will only occur for cases when there are more than one duplicate custom-field-name in the db (as was in your case).

I applied the following patch to fix the error:

--- CRM/Upgrade/TwoZero/sql/cleanup.mysql       (revision 16083)
+++ CRM/Upgrade/TwoZero/sql/cleanup.mysql       (working copy)
@@ -79,13 +79,13 @@
 
 -- fix duplicate names in custom fields / groups
 
-SET @dupe_field_names = (SELECT name FROM civicrm_custom_field group by name having count(*) > 1);
+SELECT @dupe_field_names := name FROM civicrm_custom_field group by name having count(*) > 1;
 
 UPDATE civicrm_custom_field
 SET name = LOWER(REPLACE(REPLACE(REPLACE(label, '/', ''), '-', ''), ' ', '_'))
 WHERE name IN (@dupe_field_names);
 
-SET @dupe_group_names = (SELECT name FROM civicrm_custom_group group by name having count(*) > 1);
+SELECT @dupe_group_names := name FROM civicrm_custom_group group by name having count(*) > 1;
 
 UPDATE civicrm_custom_group
 SET name = LOWER(REPLACE(REPLACE(REPLACE(title, '/', ''), '-', ''), ' ', '_'))

though it didn't actually solve the problem since the query could not convert those duplicate names into unique ones, which was clear /w the following error:
         Database consistency check failed for step 1. Duplicate entries found in civicrm_custom_field for the column 'name'.
which i had to fix using some custom queries which i 'll mail you separately.

I also came across errors which implied you had special characters in you custom-field names. Following is the query i used to fix this problem:

UPDATE civicrm_custom_field
SET name = LOWER( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( name, '?', '' ) , ':', '' ) , '(', '' ), ')', '' ), '&', ''), '.', '') );