Error creating Custom Data - DB Error: already exists

Question

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']"]    
)    

Answer 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.

Answer 2

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.