2

When I try to create Custom Data ( Administer-->Customize Data and Screens-->Custom Fields ), I get the following message. The query info is different depending on the type of Custom Data I am trying to create. The basic message is always the same. In #11-#13, it almost looks like it is trying to insert into the table 3 times or 3 different ways. Of course demo.civicrm.org works nicely.

backTrace

#0 /var/www/administrator/components/com_civicrm/civicrm/CRM/Core/Error.php(187): CRM_Core_Error::backtrace()
#1 [internal function](): CRM_Core_Error::handle(Object(DB_Error))
#2 /var/www/administrator/components/com_civicrm/civicrm/packages/PEAR.php(921): call_user_func((Array:2), Object(DB_Error))
#3 /var/www/administrator/components/com_civicrm/civicrm/packages/DB.php(985): PEAR_Error->__construct("DB Error: already exists", -5, 16, (Array:2), "INSERT INTO civicrm_custom_group (table_name ) VALUES ('civicrm_value_multipl...")    
#4 /var/www/administrator/components/com_civicrm/civicrm/packages/PEAR.php(575): DB    _Error->__construct(-5, 16, (Array:2), "INSERT INTO civicrm_custom_group (    table_name ) VALUES ('civicrm_value_multipl...")    
#5 [internal function](): PEAR->_raiseError(Object(DB_mysqli), NULL, -5, NULL, NULL, "INSERT INTO civicrm_custom_group (table_name ) VALUES ('civicrm_value_multipl...", "DB_Error", TRUE)    
#6 /var/www/administrator/components/com_civicrm/civicrm/packages/PEAR.php(224): call_user_func_array((Array:2), (Array:8))
#7 /var/www/administrator/components/com_civicrm/civicrm/packages/DB/common.php(1905): PEAR->__call("raiseError", (Array:7))
#8 /var/www/administrator/components/com_civicrm/civicrm/packages/DB/common.php(1905): PEAR->raiseError(NULL, -5, NULL, NULL, "INSERT INTO civicrm_custom_group (table_name ) VALUES ('civicrm_value_multipl...", "DB_Error", TRUE)    
#9 /var/www/administrator/components/com_civicrm/civicrm/packages/DB/mysqli.php(933): DB_common->raiseError(-5, NULL, NULL, NULL, "1062 ** Duplicate entry     '0' for key 'PRIMARY'")    
#10 /var/www/administrator/components/com_civicrm/civicrm/packages/DB/mysqli.php(403): DB_mysqli->mysqliRaiseError()
#11 /var/www/administrator/components/com_civicrm/civicrm/packages/DB/common.php(1216): DB_mysqli->simpleQuery("INSERT INTO civicrm_custom_group (table_name ) VALUES ('civicrm_value_multipl...")    
#12 /var/www/administrator/components/com_civicrm/civicrm/packages/DB/DataObject.php(2438): DB_common->query("INSERT INTO civicrm_custom_group (table_name )     VALUES ('civicrm_value_multipl...")    
#13 /var/www/administrator/components/com_civicrm/civicrm/packages/DB/DataObject.php(1060): DB_DataObject->_query("INSERT INTO civicrm_custom_group (table_name ) VALUES ('civicrm_value_multipl...")    
#14 /var/www/administrator/components/com_civicrm/civicrm/CRM/Core/DAO.php(482): DB_DataObject->insert()    
#15 /var/www/administrator/components/com_civicrm/civicrm/CRM/Core/DAO.php(1112): CRM_Core_DAO->save()    
#16 /var/www/administrator/components/com_civicrm/civicrm/CRM/Core/BAO/CustomGroup.php(195): CRM_Core_    DAO::setFieldValue("CRM_Core_DAO_CustomGroup", 0,     "table_name", "civicrm_value_multiples_of_12_12_0")    
#17 /var/www/administrator/components/com_civicrm/civicrm/CRM/Custom/Form/Group.php(455): CRM_Core_BAO_CustomGroup::create((Array:16))
#18 /var/www/administrator/components/com_civicrm/civicrm/CRM/Core/Form.php(431): CRM_Custom_Form_Group->postProcess()
#19 /var/www/administrator/components/com_civicrm/civicrm/CRM/Core/StateMachine.php(160): CRM_Core_Form->mainProcess()
#20 /var/www/administrator/components/com_civicrm/civicrm/CRM/Core/QuickForm/Action/Next.php(61): CRM_Core_StateMachine->perform(Object(CRM_Custom_Form_Group), "next", "Next")    
#21 /var/www/administrator/components/com_civicrm/civicrm/packages/HTML/QuickForm/Controller.php(203): CRM_Core_QuickForm_Action_Next->perform(Object(CRM_Custom_Form_Group), "next")    
#22 /var/www/administrator/components/com_civicrm/civicrm/packages/HTML/QuickForm/Page.php(103): HTML_QuickForm_Controller->handle(Object(CRM_Custom_Form_Group), "next")    
#23 /var/www/administrator/components/com_civicrm/civicrm/CRM/Core/Controller.php(351): HTML_QuickForm_Page->handle("next")
#24 /var/www/administrator/components/com_civicrm/civicrm/CRM/Custom/Page/Group.php(172): CRM_Core_Controller->run()
#25 /var/www/administrator/components/com_civicrm/civicrm/CRM/Custom/Page/Group.php(139): CRM_Custom_Page_Group->edit(NULL, 1)
#26 /var/www/administrator/components/com_civicrm/civicrm/CRM/Core/Invoke.php(312): CRM_Custom_Page_Group->run((Array:4), NULL)
#27 /var/www/administrator/components/com_civicrm/civicrm/CRM/Core/Invoke.php(86): CRM_Core_Invoke::runItem((Array:13))
#28 /var/www/administrator/components/com_civicrm/civicrm/CRM/Core/Invoke.php(54): CRM_Core_Invoke::_invoke((Array:4))
#29 /var/www/administrator/components/com_civicrm/civicrm.php(121): CRM_Core_Invoke::invoke((Array:4))
#30 /var/www/administrator/components/com_civicrm/civicrm.php(40): civicrm_invoke()
#31 /var/www/libraries/cms/component/helper.php(405): require_once("/var/www/administrator/components/com_civicrm/civicrm.php")
#32 /var/www/libraries/cms/component/helper.php(380): JComponentHelper::executeComponent("/var/www/administrator/components/com_civicrm/civicrm.php")
#33 /var/www/libraries/cms/application/administrator.php(98): JComponentHelper::renderComponent("com_civicrm")
#34 /var/www/libraries/cms/application/administrator.php(152): JApplicationAdministrator->dispatch()
#35 /var/www/libraries/cms/application/cms.php(257): JApplicationAdministrator->doExecute()
#36 /var/www/administrator/index.php(51): JApplicationCms->execute()
#37 {main}
 Sorry but we are not able to provide this at the moment.
DB Error: already exists
Error Details
Database Error Code: Duplicate entry '0' for key 'PRIMARY', 1062
Additional Details:
Array
(
   [callback] => Array
       (
           [0] => CRM_Core_Error
           [1] => handle
       )

   [code] => -5
   [message] => DB Error: already exists
   [mode] => 16
   [debug_info] => INSERT INTO civicrm_custom_group (table_name ) VALUES ('civicrm_value_multiples_of_12_12_0' )  [nativecode=1062 ** Duplicate entry '0'     for key 'PRIMARY']    
   [type] => DB_Error    
   [user_info] => INSERT INTO civicrm_custom_group (table_name ) VALUES ('civicrm_value_multiples_of_12_12_0' )  [nativecode=1062 ** Duplicate entry '0' f    or     key 'PRIMARY']    
   [to_string] => [db_error: message="DB Error: already exists" code=-5 mode=callback callback=CRM_Core_Error::handle prefix="" info="INSERT INTO     civicrm_custom    _group (table_name ) VALUES ('civicrm_value_multiples_of_12_12_0' )  [nativecode=1062 ** Duplicate entry '0' for key 'PRIMARY']"]    
)    
  • What other custom data is defined? Is this your first attempt at creating it or have there been other attempts / deletes / disables etc – Aidan Nov 14 '16 at 9:19
  • I want elaborate a bin on Aidans comment. CiviCRM creates new database tables for custom data. The error suggest it tries to create a table that already exists. A direct loop in the database should give some more information. – Kainuk - CiviCooP Nov 14 '16 at 9:49
  • @Kainuk - CiviCooP What do you mean by a ..direct loop in the database... "? How do I do that? – ermSO Mar 27 '18 at 23:11
  • @Aidan This is my first time creating the custom field set. – ermSO Mar 27 '18 at 23:14
  • @ermSO it was a typo a direct look into the database. – Kainuk - CiviCooP Mar 28 '18 at 7:20
1

Try creating the custom data fieldset with a completely different name. In the code, the fieldset name you provide gets converted into a 'safe' table name for the fieldset, and it's telling you that the table already exists.

For example, if you already have a custom data fieldset called 'Multiples of 12 12 0', then creating a new one with the name 'multiples of 12-12-0' will fail with your error above.

You can create the fieldset with any name that won't collide and then rename it afterwards.

At a guess, you might be misusing custom fieldsets if you've got more than one with similar names.

0

After many days of research, I piece together that there was something wrong with the AUTO_INCREMENT setup of the civicrm_custom_group table. I did a SHOW CREATE TABLE civicrm_custom_group. Below is the messed up setup.

CREATE TABLE `civicrm_custom_group`
(
                      `id`                          INT(10) UNSIGNED NOT NULL comment 'Unique Custom Group ID',
                      `name`                        VARCHAR(64) collate utf8_unicode_ci DEFAULT NULL comment 'Variable name/programmatic handle for this group.',
                      `title`                       VARCHAR(64) COLLATE utf8_unicode_ci DEFAULT NULL comment 'Friendly Name.',
                      `extends`                     VARCHAR(255) COLLATE utf8_unicode_ci DEFAULT 'Contact' comment 'Type of object this group extends (can add other options later e.g. contact_address, etc.).',
                      `extends_entity_column_id`    INT(10) UNSIGNED DEFAULT NULL comment 'FK to civicrm_option_value.id (for option group custom_data_type.)',
                      `extends_entity_column_value` VARCHAR(255) COLLATE utf8_unicode_ci DEFAULT NULL comment 'linking custom group for dynamic object.',
                      `style`                       VARCHAR(15) COLLATE utf8_unicode_ci DEFAULT NULL comment 'Visual relationship between this form and its parent.',
                      `collapse_display`            INT(10) UNSIGNED DEFAULT '0' comment 'Will this group be in collapsed or expanded mode on initial display ?',
                      `help_pre` text COLLATE utf8_unicode_ci comment 'Description and/or help text to display before fields in form.',
                      `help_post` text COLLATE utf8_unicode_ci comment 'Description and/or help text to display after fields in form.',
                      `weight`               INT(11) NOT NULL DEFAULT '1' comment 'Controls display order when multiple extended property groups are setup for the same class.',
                      `is_active`            TINYINT(4) DEFAULT NULL comment 'Is this property active?',
                      `table_name`           VARCHAR(255) COLLATE utf8_unicode_ci DEFAULT NULL comment 'Name of the table that holds the values for this group.',
                      `is_multiple`          TINYINT(4) DEFAULT NULL comment 'Does this group hold multiple values?',
                      `min_multiple`         INT(10) UNSIGNED DEFAULT NULL comment 'minimum number of multiple records (typically 0?)',
                      `max_multiple`         INT(10) UNSIGNED DEFAULT NULL comment 'maximum number of multiple records, if 0 - no max',
                      `collapse_adv_display` INT(10) UNSIGNED DEFAULT '0' comment 'Will this group be in collapsed or expanded mode on advanced search display ?',
                      `created_id`           INT(10) UNSIGNED DEFAULT NULL comment 'FK to civicrm_contact, who created this custom group',
                      `created_date`         datetime DEFAULT NULL comment 'Date and time this custom group was created.',
                      `is_reserved`          TINYINT(4) DEFAULT NULL,
                      `is_public`            TINYINT(1) DEFAULT '1' comment 'Is this property public?',
                      PRIMARY KEY (`id`),
                      UNIQUE KEY `ui_name_extends` (`name`,`extends`),
                      UNIQUE KEY `ui_title_extends` (`title`,`extends`),
                      KEY `fk_civicrm_custom_group_created_id` (`created_id`)
         )
         engine=innodb DEFAULT charset=utf8 COLLATE=utf8_unicode_ci

I also did a SHOW CREATE TABLE civicrm_custom_group on a local development environment. The result of the good local dev environment is below.

CREATE TABLE `civicrm_custom_group` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Unique Custom Group ID',
`name` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Variable name/programmatic handle for this group.',
`title` varchar(64) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Friendly Name.',
`extends` varchar(255) COLLATE utf8_unicode_ci DEFAULT 'Contact' COMMENT 
'Type of object this group extends (can add other options later e.g. contact_address, etc.).',
`extends_entity_column_id` int(10) unsigned DEFAULT NULL COMMENT 'FK to civicrm_option_value.id (for option group custom_data_type.)',
`extends_entity_column_value` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'linking custom group for dynamic object',
`style` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Visual relationship between this form and its parent.',
`collapse_display` int(10) unsigned DEFAULT '0' COMMENT 'Will this group be in collapsed or expanded mode on initial display ?',
`help_pre` text COLLATE utf8_unicode_ci COMMENT 'Description and/or help text to display before fields in form.',
`help_post` text COLLATE utf8_unicode_ci COMMENT 'Description and/or help text to display after fields in form.',
`weight` int(11) NOT NULL DEFAULT '1' COMMENT 'Controls display order when multiple extended property groups are setup for the same class.',
`is_active` tinyint(4) DEFAULT NULL COMMENT 'Is this property active?',
`table_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Name of the table that holds the values for this group.',
`is_multiple` tinyint(4) DEFAULT NULL COMMENT 'Does this group hold multiple values?',
`min_multiple` int(10) unsigned DEFAULT NULL COMMENT 'minimum number of multiple records (typically 0?)',
`max_multiple` int(10) unsigned DEFAULT NULL COMMENT 'maximum number of multiple records, if 0 - no max',
`collapse_adv_display` int(10) unsigned DEFAULT '0' COMMENT 'Will this group be in collapsed or expanded mode on advanced search display ?',
`created_id` int(10) unsigned DEFAULT NULL COMMENT 'FK to civicrm_contact, who created this custom group',
`created_date` datetime DEFAULT NULL COMMENT 'Date and time this custom group was created.',
`is_reserved` tinyint(4) DEFAULT '0' COMMENT 'Is this a reserved Custom Group?',
`is_public` tinyint(4) DEFAULT '1' COMMENT 'Is this property public?',
PRIMARY KEY (`id`),
UNIQUE KEY `UI_title_extends` (`title`,`extends`),
UNIQUE KEY `UI_name_extends` (`name`,`extends`),
KEY `FK_civicrm_custom_group_created_id` (`created_id`),
CONSTRAINT `FK_civicrm_custom_group_created_id` FOREIGN KEY (`created_id`) 
REFERENCES `civicrm_contact` (`id`) ON DELETE SET NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

There are several differences between the two table definitions. The issue I was having was caused by the lack of AUTO_INCREMENT in the primary key definition. I also suspect I would have had other issues if I had just fixed the auto increment.

To solve the issue, I used the local dev create table statement to create a table called civicrm_custom_group_mod on the production environment. I renamed the bad civicrm_custom_group table to civicrm_custom_group_orig. NOTE: Doing this will cause many pages with forms to display an error and not load even if they do not use custom field sets I then renamed civicrm_custom_group_mod to civicrm_custom_group. Reestablishing civicrm_custom_group table fixes the pages with forms not loading issue.

And all of the sudden this issue that has been plaguing the production environment for over 1.5 year, was solved. A better solution would be if, CiviCRM had a database integrity scanner which would be updated after each CiviCRM update. It could then be used anytime to troubleshoot issues.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.