I updated from version 7.x-1.0-beta4+4-dev to 7.x-1.0-beta4+14-dev.
I run the updates and it failed with :

The following updates returned messages
field_collection module
Update #7001

    Failed: DatabaseSchemaObjectExistsException: Cannot add field <em class="placeholder">field_collection_item</em>.<em class="placeholder">revision_id</em>: field already exists. in DatabaseSchema_mysql->addField() (line 328 of .../.../includes/database/mysql/schema.inc).

After what the site displayed fatal error (on any page) :

Fatal error: Call to undefined function entity_revision_is_default() in /homez.547/dixtree/www/dixtree/sites/all/modules/field_collection/field_collection.module on line 303

I could revert to previous version to keep the site working but now I can't edit FC items, I get the following error :

PDOException: SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'field_bloc_page_accueil_revision_id' cannot be null: INSERT INTO {field_data_field_bloc_page_accueil} (entity_type, entity_id, revision_id, bundle, delta, language, field_bloc_page_accueil_value, field_bloc_page_accueil_revision_id) VALUES (:db_insert_placeholder_0, .../... :db_insert_placeholder_47); Array ( [:db_insert_placeholder_0] => node [:db_insert_placeholder_1] => 39 .../... [:db_insert_placeholder_47] => 77 ) in field_sql_storage_field_storage_write() (line 448 of .../.../modules/field/modules/field_sql_storage/field_sql_storage.module).

Is it a way to fix the update pb ?

If not, could someone tell what tables I have to revert from db back-up (I would avoid to back-up the whole db because I did some change since last back-up)

Thanks.

Support from Acquia helps fund testing for Drupal Acquia logo

Comments

remaye’s picture

I finally revert the following tables to back-up and it seems it is working back again with 7.x-1.0-beta4+4-dev version:
- 'field_collection_item'
- 'field_collection_item_revision' (deleted)
- all 'field_data_field_[FC_field_name]'
- all 'field_revision_field_[FC_field_name]'

But it would still be great to know what's wrong with last 7.x-1.0-beta4+14-dev version...

_redfog’s picture

Hey Remaye,

Please see this issue: http://drupal.org/node/1782664

I think it should help.

remaye’s picture

Entity API 7.x-1.0-rc3+10-dev + Field Collection 7.x-1.0-beta4+14-dev
Run updates (needed by Field Collection)

It's just fine, thank you !

remaye’s picture

Well, I spoke too quick... it worked indeed on a clean site install (with no previous failed update)
But when I applied the same to the previously updated site (the site from my original post, after I revert manually to previous version) the update encounter errors again.

In the clean site install, I had 2 updates : #7001 and #7002 for Field Collection
In the other site, it seems it had kept in memory that #7001 was already done, as only #7002 update was applied.
But in fact #7001 should also be applied because my SQL table had been reverted manually as they was before #7001 update.

Is there any way to force site apply the #7001 update ?
Or where can I find update instructions to make the changes manually ?

_redfog’s picture

In your database, please search the module "field_collection" into "system" table, then you'll see the "schema_version" which correspond to the N of hook_update_N(). Change it then reapply the update.
If necessary, delete manually items which has already been added in database.

remaye’s picture

Category: support » bug

Thanks for your kind and accurate help !
I found the "schema_version" of "field_collection" that is set to 7001 (it explains indeed why this update is not applied)
In the "clean" install (without failed updates) this field is set to 7000, so I guess this is the value I have to set.
I have no time right now to test it but I'll let you know when I'm done.Worked perfectly !
Thank you again.

_redfog’s picture

De rien !

fago’s picture

Category: bug » support
lathan’s picture

Update failing due to different issue here thought its best to add issue here than create a new one as its the same hook.

$ drush @local.concern.systemseed.com updb
The following updates are pending:

field_collection module : 
  7001 -   Add revision support. 
  7002 -   Remove orphaned field collection item entities. 

Do you wish to run all pending updates? (y/n): y
SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'field_cw_profile_contact_prefs_revision_id' cannot be null                                                                                [error]
Performed update: field_collection_update_7001                                                                                                                                                          [ok]
Finished performing updates.                                                                                                                                                                            [ok]
13:eck jucallme$ drush @local.concern.systemseed.com updb
The following updates are pending:

field_collection module : 
  7001 -   Add revision support. 
  7002 -   Remove orphaned field collection item entities. 

Do you wish to run all pending updates? (y/n): y
Cannot add field <em class="placeholder">field_collection_item</em>.<em class="placeholder">revision_id</em>: field already exists.                                                                     [error]
Performed update: field_collection_update_7001                                                                                                                                                          [ok]
Finished performing updates.

my field_collection schema version is 7000 which is correct as the update field_collection_update_7001 failed, strange that there was no role back on this error. As now the column revision_id and table field_collection_item_revision now exist, and successive executions of field_collection_update_7001 will fail.

Looking at the update hook the following is an incorrect assumption.

      $table = sprintf('%s_%s', $table_prefix, $field_name);
      $value_column = sprintf('%s_value', $field_name);
      $revision_id_column = sprintf('%s_revision_id', $field_name);

      // Add a revision_id column.
      $revision_id_spec['description'] = 'The field collection item revision id.';
      db_add_field($table, $revision_id_column, $revision_id_spec);

      // Initialize the revision_id to be the same as the item_id.
      db_update($table)
        ->expression($revision_id_column, $value_column)
        ->execute();

$value_column can be null in many different circumstances. Should this not in-fact be the value of entity_id in the tables?

if i change $value_column = sprintf('%s_value', $field_name); to $value_column = sprintf('entity_id', $field_name); the update works for me.

_redfog’s picture

You have to delete manually added column and tables.

But in fact, I'm afraid to do this update on prod, as for the moment, I couldn't do this update without errors. I'm re-installing again and again database to check if I won't have any issue...

_redfog’s picture

Category: bug » support

Okay, I carry out the update successfully on my local environment.

1 - Update module Field Collection to 7.x-1.0-beta4+14-dev
2 - Update module Entity to 7.x-1.0-rc3+10-dev (because of http://drupal.org/node/1782664)
3 - Uncheck the administrator permission about the field collection module (because of http://drupal.org/node/1407174)
4 - Then run update.php \o/

The update take a very long time to be executed....

_redfog’s picture

After doing this update, when I save a content with field collection, I have this error:

PDOException : SQLSTATE[HY000]: General error: 1364 Field 'revision_id' doesn't have a default value: INSERT INTO {field_collection_item} (field_name) VALUES (:db_insert_placeholder_0); Array ( [:db_insert_placeholder_0] => field_variantes ) dans drupal_write_record() (ligne 7020 dans [path]\includes\common.inc).

Edit : I think it's because I changed git branch and didn't flush the cache. After flushing, no issue.

jakubmrozonline’s picture

I also have the same problem. Only downgrade to beta 4 helped.

elvis2’s picture

field_collection does not drop tables when uninstalling. Which causes issues if you are enabling a module or feature that requires it. In my case, the feature was attempting to install field_collection, which was previously installed but tables still exist, I would get an error on enabling field_collection (via features).

I put a patch in that drops the field_collection tables once uninstalled.
http://drupal.org/node/1882836#comment-6914474

Your situation may be similar...

aendra’s picture

Status: Needs review » Active

I was having this issue (I think my initial db update failed) and the last few days have been nightmarish trying to figure out what's wrong.

Essentially, my field collection items disappeared, so I restored a backup and rolled back to beta4. However, while content would display then, I was having issues adding new content (at least, content involving a field collection).

I did what _redfog did in #11, however, update.php kept returning errors on 7001. I then had to:

  1. drop table field_collection_item_revision;
  2. alter table field_collection_item drop column archived;
  3. alter table field_collection_item drop column revision_id;

Then drush updb completed and everything's great now. I shall reward myself with a particularly tall beer this evening...

drupal a11y’s picture

I got the same error:

Update #7001
Failed: DatabaseSchemaObjectExistsException: Table <em class="placeholder">field_collection_item_revision</em> already exists. in DatabaseSchema->createTable() (line 657 of .../includes/database/schema.inc).
kaidjohnson’s picture

Status: Active » Needs review
FileSize
1.84 KB

We were in the same situation and mucking around in the db wasn't an option. I modified the update method to prevent this error from being thrown. For anyone that is stuck in this scenario, this patch should get things going again.

nixter’s picture

Status: Active » Needs review

#5 and #6 fixed my issues too. Thanks guys!

kris-o3’s picture

recently downgraded from beta5 to beta4 in order to resolve the 'broken' appearance
of field_collection_table upon clicking "Add another item"... and experienced this same problem.

following #15, here's what worked for me...

DROP TABLE `field_collection_item_revision`;
ALTER TABLE `field_collection_item` DROP COLUMN `archived`;
ALTER TABLE `field_collection_item` DROP COLUMN `revision_id`;

and then for each instance of a field_collection field,

ALTER TABLE `field_data_field_YOUR_FIELD` DROP COLUMN `field_YOUR_FIELD_revision_id`;
ALTER TABLE `field_revision_field_YOUR_FIELD` DROP COLUMN `field_YOUR_FIELD_revision_id`;

and finally,

UPDATE `system` SET `schema_version` = '7000' WHERE `filename` = 'sites/all/modules/field_collection/field_collection.module';

Danny Englander’s picture

I just ran into this issue and mucking about in the database was not an option for me as I have code in Git that needs to propagate from local to production. I tried the patch from #17 and it enabled the update to work but then after most of my pages with field collections got borked with 500 errors and did not load. Nothing particular in the logs to say exactly what happend though. I suppose I will try to backtrack this somehow and revert back via git and a database backup.

kaidjohnson’s picture

@highrockmedia: Each 500 error should be throwing an error into the system log. Do you have dblog enabled? Could you try loading a few of those pages and then post any errors or warnings from the log? It sounds like something else may be going on here.

Also note that the patch in #17 is intended to be run before the update error happens, as updating with the error, patching, and then updating again won't trigger the update and you'll still be in a bad state. Try getting back to a pre-update state, apply the patch, run update.

As a final though, be sure the cache is fully cleared after the update is run. There may be something hung up in there.

kaidjohnson’s picture

Glancing back at the patch, there may have been an issue when the revision table was dropped and rebuilt. Here's a slight variation on that patch that should retain the table if it already exists. There is no reason to drop the table if it already exists... and doing so may be causing more issues than expected.

Danny Englander’s picture

@kaidjohnson - You are correct, I had applied the patch and then ran the update after I got the error. I'm back to pre-update, both code and DB and I think for now I'll stay with that. I may have some time to play around with this in the next few weeks but I lost too much time already, need to finish the project I'm on. Thanks!

roderik’s picture

Title: Update error for 7.x-1.0-beta4+4-dev to 7.x-1.0-beta4+14-dev » Enable re-running failed update_7001(), adding revision fields
FileSize
2 KB

Same patch, just coding standards. No need for credit.

(Otherwise RTBC-OK from me...)

dungkal’s picture

Same here. Thanks, too.

joericapens’s picture

I too ran into this issue after a 30-second execution timeout of update.php. Re-running update.php failed because the fields and table were already added.

After applying the patch from https://drupal.org/node/1783456#comment-7533057 the update still failed for me because filling the field_collection_item_revision table takes way too long. Re-running update.php fails because of primary key errors; "Duplicate entry '1' for key 'PRIMARY'".

The code;

  // Fill the new table with the correct data.
  $items = db_select('field_collection_item', 'fci')
    ->fields('fci')
    ->execute();
  foreach ($items as $item) {
    // Update field_collection_item_revision table.
    db_insert('field_collection_item_revision')
      ->fields(array(
        'revision_id' => $item->item_id,
        'item_id' => $item->item_id,
      ))
      ->execute();
  }

is pretty inefficient because it fires a query for every insert. After I wrapped the db_insert() in a try...catch block to overcome the primary key errors, running "drush updatedb" took 4 minutes on my test database with 91000 field_collection_item records. My production database has 164000 records.

Manually running the following query;

INSERT INTO field_collection_item_revision (revision_id, item_id)
SELECT item_id, item_id
FROM field_collection_item

only takes 2 seconds.

Is this query safe to run on all supported databases? If so, wouldn't it be better to use db_query() in this case? If not, could a) the try..catch block be added and b) a warning be printed so people are advised to use "drush updatedb" if they have large amounts of field collections?

Lastly, update 7002 takes 12 minutes on my test database. Any way to speed that up?

praneeta’s picture

i used db_add_field to add a new field in entity registration module table but it is showing an error ?
I don't know how to add it plsss help?