I have a field collection attached to the user entity. And a field collection attached to a commerce customer profile entity. When a commerce order is completed, we fire a Rule that creates a new user account and defaults several profile field to the data that was collected in the commerce customer profile. One of those fields is the field collection. We get a fatal error (full backtrace below).

It appears that then field_attach_insert() runs, and the field collection is inserted into the database, it is not setting anything for the new [xyz]_revision_id column, introduced in field_collection_update_7001.

NOTE: I do have the patch from #1281974-52: Missing bundle property on entity - related to translations (comment 52) applied.

PDOException: SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'field_cw_profile_contact_prefs_revision_id' cannot be null: INSERT INTO {field_data_field_cw_profile_contact_prefs} (entity_type, entity_id, revision_id, bundle, delta, language, field_cw_profile_contact_prefs_value, field_cw_profile_contact_prefs_revision_id) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2, :db_insert_placeholder_3, :db_insert_placeholder_4, :db_insert_placeholder_5, :db_insert_placeholder_6, :db_insert_placeholder_7); Array ( [:db_insert_placeholder_0] => user [:db_insert_placeholder_1] => 2870 [:db_insert_placeholder_2] => 2870 [:db_insert_placeholder_3] => user [:db_insert_placeholder_4] => 0 [:db_insert_placeholder_5] => und [:db_insert_placeholder_6] => 1192 [:db_insert_placeholder_7] => ) in field_sql_storage_field_storage_write() (line 448 of /Users/tom/workspace/concern7/modules/field/modules/field_sql_storage/field_sql_storage.module). Backtrace:
PDOStatement->execute(Array) database.inc:2139
DatabaseStatementBase->execute(Array, Array) database.inc:664
DatabaseConnection->query('INSERT INTO {field_data_field_cw_profile_contact_prefs} (entity_type, entity_id, revision_id, bundle, delta, language, field_cw_profile_contact_prefs_value, field_cw_profile_contact_prefs_revision_id) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2, :db_insert_placeholder_3, :db_insert_placeholder_4, :db_insert_placeholder_5, :db_insert_placeholder_6, :db_insert_placeholder_7)', Array, Array) query.inc:36
InsertQuery_mysql->execute() field_sql_storage.module:448
field_sql_storage_field_storage_write('user', Object, 'insert', Array) 
call_user_func_array('field_sql_storage_field_storage_write', Array) module.inc:826
module_invoke('field_sql_storage', 'field_storage_write', 'user', Object, 'insert', Array) field.attach.inc:940
field_attach_insert('user', Object) user.module:587
user_save(Object, Array) callbacks.inc:792
entity_metadata_user_save(Object) entity.module:190
entity_save('user', Object) entity.wrapper.inc:830
EntityDrupalWrapper->save() rules.state.inc:229
RulesState->saveNow('account_created') rules.state.inc:207
RulesState->save('account-created', Object, 1) rules.state.inc:173
RulesState->saveChanges('account-created', Object, 1) entity.eval.inc:125
rules_action_entity_save(Object, 1, Array, Object, Object, 'execute') 
call_user_func_array('rules_action_entity_save', Array) faces.inc:123
FacesExtendable->__call('execute', Array) rules.core.inc:297
RulesExtendable->__call('execute', Array) rules.plugins.inc:20
RulesAction->executeCallback(Array, Object) rules.core.inc:1570
RulesAbstractPlugin->evaluate(Object) rules.core.inc:2215
RulesActionContainer->evaluate(Object) rules.plugins.inc:216
Rule->evaluate(Object) rules.plugins.inc:392
RulesReactionRule->evaluate(Object) rules.core.inc:2215
RulesActionContainer->evaluate(Object) rules.plugins.inc:695
RulesEventSet->executeByArgs(Array) rules.module:860
rules_invoke_event_by_args('commerce_checkout_complete', Array) rules.module:805
rules_invoke_all('commerce_checkout_complete', Object) commerce_checkout.module:842
commerce_checkout_complete(Object) commerce_payment.module:337
commerce_payment_redirect_pane_next_page(Object, 'Customer skipped the Payment page because payment was already submitted.') commerce_payment.checkout_pane.inc:234
commerce_payment_redirect_pane_checkout_form(Array, Array, Array, Object) commerce_checkout.pages.inc:104
commerce_checkout_form(Array, Array, Object, Array) 
call_user_func_array('commerce_checkout_form', Array) form.inc:800
drupal_retrieve_form('commerce_checkout_form_payment', Array) form.inc:341
drupal_build_form('commerce_checkout_form_payment', Array) form.inc:131
drupal_get_form('commerce_checkout_form_payment', Object, Array) commerce_checkout.pages.inc:58
commerce_checkout_router(Object, Array) 
call_user_func_array('commerce_checkout_router', Array) menu.inc:516
menu_execute_active_handler() index.php:21

Comments

loziju’s picture

Priority: Normal » Major

Facing exactly the same issue here. For my case, this error is triggered when I create node with field collection in my custom module.

loziju’s picture

Priority: Major » Normal

I circumvented the issue by creating the field collection first.

  $node2 = new stdClass();
  (....)

  //circumventing field collection problem
  $field_collection_item = entity_create('field_collection_item', array('field_name' => 'field_business_hours')); // Create new field collection item.
  $field_collection_item->setHostEntity('node', $node2); // Attach it to the node.
  $field_collection_item->save(TRUE);

  $node2->field_business_hours[LANGUAGE_NONE][0]['value'] = $field_collection_item->item_id;
  $node2->field_business_hours[LANGUAGE_NONE][0]['revision_id'] = $field_collection_item->revision_id;

  unset($node2->nid);
  $node2 = node_submit($node2); // Prepare node for saving
  node_save($node2);

@mrfelton: I suppose for your case, the field collection field is not created for the account. This might be related to the fact that field collection field must always be created, despite empty (might be related to http://drupal.org/node/1662998).

AaronBauman’s picture

Priority: Normal » Major

In my circumstance, I cannot create the host entity first, so this solution does not address the problem.
I'm saving a new field_collection entity -- field_collection should prevent setting the revision_id on insert.

AaronBauman’s picture

Priority: Major » Critical

Bumping this to critical, as this is now happening when I submit the node form, which means I can no longer save nodes.

h3rj4n’s picture

I also got this problem after updating the field collection module. Don't got the problem with saving the node.

The solution you gave, save the field collection before populating it, solved my problem.

I got one small question, what does the TRUE do in the $field_collection->save(TRUE); part. Without it my code won't run.

forssto’s picture

Confirming this. I have a custom module creating field collection items programmatically and attaching them to nodes.

The problem is that the new field column "revision_id" was made required, so code like this:

$fieldcollection = /* create new field collection item */
$node->field_foo[LANGUAGE_NONE] = array(
  array("value" =>$field_collection->item_id)
)
node_save($node);

...stops working. I just needed to change this to:

$fieldcollection = /* create new field collection item */
$node->field_foo[LANGUAGE_NONE] = array(
  array("value" =>$field_collection->item_id, "revision_id" =>$field_collection->revision_id)
)
node_save($node);

...which sucks as I have absolutely no need for revisions.

(also, this is not my actual code, just made it as simple as possible to show the issue here).

arne_hortell’s picture

This issue occurs because field_collection for any reason doesnt delete empty values correctly

My quick-fix is to change in field_collection/field_collection.install at row185 and add a constraint to not get rows with NULL that anyway will be rejected due to the new constraint.

// Initialize the revision_id to be the same as the item_id.
db_update($table)
->expression($revision_id_column, $value_column)
->condition($value_column,$value=NULL,$operator='IS NOT NULL') /* arne(Att) hortell(dott)se */
->execute();

After applying this, and cleaning the database manually for the field-xxx_revision_id in all used field_collections
removing the table
field_collection_item_revision
removing the fields
revision_id and archived
in
field_collection_item

AND
Removing column revision_id everywhere in
field_revision_field_xxx

And then applying the update with
drush upgradedb
and then wait almost infinite, for me 10-15 minutes.
Then it worked again.

shendric’s picture

Version: 7.x-1.x-dev » 7.x-1.0-beta5

I'm just adding that I'm having the same issue on 7.x-1.0-beta5. Is #7 the only way to fix this right now?

Just to add some data that may help. I'm having the issue when I try adding a node that does not have any information in the field collection field. If I add some dummy data, and then go back and edit it out, it seems to work fine.

shendric’s picture

More data for this issue:

This seems to be related to the use of a date field in the field collection. I have a date field in the collection I'm having difficulty with, and when it is in an empty field collection, it seems to create problems. What's odd is that I added some debugging to field_collection_item_is_empty, and it seems to evaluate as "empty," so I'm wondering if the Date module is overriding this?

In the meantime, since the Date field was only capturing a year, we're going to replace that field with a Textfield, which doesn't seem to have this problem.

Infoloko’s picture

I have exactly this problem (#4) .. the collection item is set as 'not required' - the default value is "-None-" and trying to save the node results in the error "Integrity constraint violation .... 'field_xxx_revision_id' cannot be null" (which of course it will be as the collection is empty). Workaround for me is to add a value 'None' into the first field of the collection (its a list(text) for day of the week) .. which to me is corrupting my data set but I can live with it for the moment!

shendric’s picture

Just curious to know if there's been any updates on this issue. This is starting to cause some more critical issues for us. We have sites that were using field collections with date fields, and now we're having to either go back and change those to text fields (with all of the field data juggling that entails) or we're looking at trying to write a module to use hook_node_presave to try to adjust the node to remove any empty field collections that only have a date field.

adaddinsane’s picture

I've got this one as well - it's a serious issue for us this is a live (UK Govt) site - though we haven't migrated the module update yet.

In reference to #09 and #10, we also have a date field in the collection that's breaking. And it's not a required field.

However I have a quick workaround if you can live with it: Let the date field have a default date value of "now" (or whatever). And then it will save without a problem.

John Pitcairn’s picture

Version: 7.x-1.0-beta5 » 7.x-1.x-dev

Ouch. Just bit me on a production site pulled down to dev, and running the latest field collection dev (updating the issue version accordingly).

There is a date field (unix timestamp) which is non-required. Adding a default value of "now" does not solve the problem here. Removing the date field from the field collection does not help either...

John Pitcairn’s picture

Title: "Integrity constraint violation column [xyz]_revision_id cannot be null" when saving user entity with attached field collection » "Integrity constraint violation column [xyz]_revision_id cannot be null" when saving entity with attached field collection

And mine is not a user entity, it's a node. Re-titling.

John Pitcairn’s picture

This is pretty nasty even for admins - we can't even delete field instances from any parent entity that contains a field collection without this error. The fields are successfully deleted from the parent entity. We'll be rebuilding a lot of things on this dev anyway, so I've bitten the bullet and deleted all field collection instances, then manually cleaned out the screwed-up DB tables:

field_config (remove all deleted refs to field collections)
field_config_instance (remove all deleted refs to field collections)
field_deleted_data_* (drop tables)
field_deleted_revision_* (drop tables)
field_collection_item (truncate)
field_collection_item_revision (truncate)

which stops Drupal thinking there are fields in use, allowing me to completely uninstall field collection and all its dependent modules and start from a clean slate with latest dev. A fair bit of work to re-create some stuff, but I really don't want this one to keep biting me or some other poor soul further down the track.

liquidcms’s picture

just weighing in.

i am trying to use Feeds to import users and i get:
SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'field_cv_revision_id' cannot be null

field_cv is a FC so guessing this is same issue.

i just upgraded core to 7.23 from 7.20 but hadn't tried this import in a very long time; so not sure if thats issue. i was running beta5 (which failed) and updated to latest -dev (still fails)

liquidcms’s picture

liquidcms’s picture

Issue summary: View changes

Updated issue summary.

jmuzz’s picture

Status: Active » Postponed (maintainer needs more info)

Is it fixed then?

I tried duplicating this with a bunch of field collections and date fields in them with and without default values but never saw it. If it still happens can anybody provide steps to reproduce?

jmuzz’s picture

Status: Postponed (maintainer needs more info) » Closed (cannot reproduce)