When trying to run the database updates I get the following error..

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 /home/websites/platform/core/drupal-7.18/includes/database/mysql/schema.inc).

Apparently I have two pending database updates..

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

Can you shed any light on a fix for this?

I suspect it's this that is causing another error I am getting when trying to update nodes with field collections being..
PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'field_contacts_revision_id'.......
and also
Notice: Undefined property: stdClass::$field_log_revision_id in field_sql_storage_field_storage_load() (line 360 of /home/websites/platform/core/drupal-7.18/modules/field/modules/field_sql_storage/field_sql_storage.module).

TIA

Files: 

Comments

wipeout_dude’s picture

Title:Database update won't run..» Beta4 -> Beta5 upgrade fails.

I have done some more digging and this is what I have found so far..

I have 4 field collections in total.. The upgrade only added the revision_id field to the first field collection table..

Attempting to re-run the database update failed because the fist field collection table already had the revision_id field..

As a test I copied the beta4 code back to the modules directory and manually deleted the revision_id field from the table.. The result of this was that I was able to save nodes that contained a field collection but still couldn't create or save new field collection items because the Drupal schema appears to be aware of the revision_id field even though it no longer exists..

I then attempted to upgrade to Beta5 again.. The DB update fails because it says the revision_id field already exists.. When trying to upgrade again not even the first field gets the revision_id field created..

So it seems that the update process to add the revision_id field is failing and only updating the first field collection field on the first attempt.. Any subsequent fields are no updated and attempting to re-run the update also fails..

Is there any way to manually clear the Drupal schema to allow a complete rollback to Beta4 or is there a method to complete the upgrade to Beta5??

I desperately need this resolved because a lot of my sites functionality is now broken.. Any help greatly appreciated..

brantwynn’s picture

I can confirm I also experienced this when running updates for Entity API and Field Collection.

At the moment we've rolled back to Entity API 7.x-1.0-rc3 and Field Collection 7.x-1.0-beta4

Always back up your db's before updating!

wipeout_dude’s picture

I have backups but I upgraded on 30th Dec and didn't pickup the issue until yesterday by which time a lot of other content had changed.. :(

Now I am hoping there is a way to either roll back the database changes that were made or manually update the database to work with the beta5 code.. Either of these options would be fine but really going to need some help working it out.. Hopefully one of the module dev's can point me in the right direction..

brian_c’s picture

I was having upgrade issues as well, usually complaining that the "field_collection_item_revision" table already exists (this was due to rolling back using Backup/Migrate, which does not delete extraneous tables)

So I manually deleted the "field_collection_item_revision" table, and then the beta4 -> beta5 upgrade proceded smoothly.

wipeout_dude’s picture

Thanks for the pointer brian_c I think it has got me much closer to getting my site working properly again..

Here is where I am now..

I deleted the "field_collection_item_revision" table, the "field_collection_item.revision_id" field, the "field_collection_item.archived" field and the "_revision_id" field from the first field collection field table..

Then attempted to run the upgrade again and get the following error..

SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'field_contacts_revision_id' cannot be null

This field is being added by the upgrade script.. If it exists before the update is run it produces an error saying it already exists so I can't even change it to allow NULL values.. Not sure how to get around that..

Any suggestions?

Thanks..

wipeout_dude’s picture

For anyone finding this thread I have managed to successfully roll back to Beta4 using the following procedure..

1. Copy the Beta4 module code back to sites/all/modules (or wherever your field_collection folder is)
2. Delete the "field_collection_item_revision" table.
3. In the "field_collection_item" table delete the "revision_id" and "archived" fields.
4. In your field collection field tables delete the "_revision_id" fields. (mine was only present on the first field collection field but check them all in case)
5. Clear all caches.

You should now be back to a working system on beta4..

lanzs’s picture

I faced this error:

Update #7001
Failed: PDOException: SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'field_look_items_revision_id' cannot be null: UPDATE {field_data_field_look_items} SET field_look_items_revision_id=field_look_items_value; Array ( ) in function field_collection_update_7001() (line 188 ~modules/field_collection/field_collection.install).

But after this I haven`t faced any problems on the site yet. Nevertheless I`m going to roll back to beta4

donnie26’s picture

Same problem here:
PDOException : SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'field_x_revision_id' cannot be null: UPDATE {field_data_field_x} SET field_x_revision_id=field_x_value; Array ( ) in field_collection_update_7001() (ligne 188 in /.../drupal/sites/all/modules/field_collection/field_collection.install).
I went back to beta4 with no problem, any ideas to fix that?

liquidcms’s picture

i was getting this issue last night; but fine now:

when i first tried this i had:
Field collection 7.x-1.0-beta4+4-dev (2012-Jun-27)
Entity API 7.x-1.0-rc3+11-dev (2012-Oct-23) - not sure if this matters

and i was getting this error when trying to update a single collection within a node:

DatabaseTransactionNoActiveException: in DatabaseConnection->rollback() (line 1019 of E:\wamp\www\boma\includes\database\database.inc).

digging a round in issue queue and some people suggested latest -dev would fix this, so i grabbed latest -dev (which turns out was now beta5)

this was last night so a bit fuzzy:

- when i ran db updates i got the revision table exists msg and update failed
- after that i could see data of many of my collections was wiped out and other issues when trying to save

woke up this morning thinking a little more clear and started this over again.

- loaded backup db and noticed revision table actually was not there (i assumed it was since i was getting table exists error on update)
- i also updated to latest stable rel (1.0) of Entity - again, not sure that mattered
- immediately ran update (didnt go to any node with a collection or try editing anything - only mention since i think i may have done this last night; but doubt it would have mattered)
- no error this time when i ran update
- nodes that were missing fc data seem fine now
- i loaded a test node (massive node with over 700 fields nested in 4 levels of f collections) and saved it and it seemed to work fine.

havent done too much else yet (like test issue i was originally trying to solve); but fc seems fine so far.

a couple other notes which i doubt impact any of this; but perhaps:

- last night i first added a couple of fc patches i require before running update script. these are for i18n and node cloning support; this morning when i got this to work i hadn't added these. really be surprised if these caused the problem as they are pretty minor fixes
- i also have a core hack because D7 does such a useless job now of doing updates: if you hit table exists it throws exception in the middle of your update and you are left with a 1/2 updated system and major pita to fix - my "fix" simply reports the table already existed and continues on.

liquidcms’s picture

also, when i successfully ran update this morning i got a pile of these msgs:

Deleted 0 orphaned field collection items.
Deleted 3 orphaned field collection items.
Deleted 0 orphaned field collection items.
Deleted 0 orphaned field collection items.

and no other errors, msgs or warnings of any type

liquidcms’s picture

although, after all that, my initial issue of not being able to edit a collection on its own from the view of a node still fails. different error this time though:

EntityMalformedException: Missing bundle property on entity of type node. in entity_extract_ids() (line 7633 of E:\wamp\www\boma\includes\common.inc).

but will raise separate case on this

federico.parra’s picture

Hi! I used your advice #6 to return to Beta 4, followed step by step but I am getting a ton of errors now!
When I try to delete/add/modify a field collection item I get this:

PDOException: SQLSTATE[HY000]: General error: 1364 Field 'field_weekly_checklist_revision_id' doesn't have a default value: INSERT INTO {field_data_field_weekly_checklist} (entity_type, entity_id, revision_id, bundle, delta, language, field_weekly_checklist_value) 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, :db_insert_placeholder_8, :db_insert_placeholder_9, :db_insert_placeholder_10, :db_insert_placeholder_11, :db_insert_placeholder_12, :db_insert_placeholder_13), (:db_insert_placeholder_14, :db_insert_placeholder_15, :db_insert_placeholder_16, :db_insert_placeholder_17, :db_insert_placeholder_18, :db_insert_placeholder_19, :db_insert_placeholder_20), (:db_insert_placeholder_21, :db_insert_placeholder_22, :db_insert_placeholder_23, :db_insert_placeholder_24, :db_insert_placeholder_25, :db_insert_placeholder_26, :db_insert_placeholder_27), (:db_insert_placeholder_28, :db_insert_placeholder_29, :db_insert_placeholder_30, :db_insert_placeholder_31, :db_insert_placeholder_32, :db_insert_placeholder_33, :db_insert_placeholder_34), (:db_insert_placeholder_35, :db_insert_placeholder_36, :db_insert_placeholder_37, :db_insert_placeholder_38, :db_insert_placeholder_39, :db_insert_placeholder_40, :db_insert_placeholder_41), (:db_insert_placeholder_42, :db_insert_placeholder_43, :db_insert_placeholder_44, :db_insert_placeholder_45, :db_insert_placeholder_46, :db_insert_placeholder_47, :db_insert_placeholder_48); Array ( [:db_insert_placeholder_0] => node [:db_insert_placeholder_1] => 8 [:db_insert_placeholder_2] => 243 [:db_insert_placeholder_3] => circle [:db_insert_placeholder_4] => 0 [:db_insert_placeholder_5] => und [:db_insert_placeholder_6] => 58 [:db_insert_placeholder_7] => node [:db_insert_placeholder_8] => 8 [:db_insert_placeholder_9] => 243 [:db_insert_placeholder_10] => circle [:db_insert_placeholder_11] => 1 [:db_insert_placeholder_12] => und [:db_insert_placeholder_13] => 59 [:db_insert_placeholder_14] => node [:db_insert_placeholder_15] => 8 [:db_insert_placeholder_16] => 243 [:db_insert_placeholder_17] => circle [:db_insert_placeholder_18] => 2 [:db_insert_placeholder_19] => und [:db_insert_placeholder_20] => 61 [:db_insert_placeholder_21] => node [:db_insert_placeholder_22] => 8 [:db_insert_placeholder_23] => 243 [:db_insert_placeholder_24] => circle [:db_insert_placeholder_25] => 3 [:db_insert_placeholder_26] => und [:db_insert_placeholder_27] => 62 [:db_insert_placeholder_28] => node [:db_insert_placeholder_29] => 8 [:db_insert_placeholder_30] => 243 [:db_insert_placeholder_31] => circle [:db_insert_placeholder_32] => 4 [:db_insert_placeholder_33] => und [:db_insert_placeholder_34] => 63 [:db_insert_placeholder_35] => node [:db_insert_placeholder_36] => 8 [:db_insert_placeholder_37] => 243 [:db_insert_placeholder_38] => circle [:db_insert_placeholder_39] => 5 [:db_insert_placeholder_40] => und [:db_insert_placeholder_41] => 64 [:db_insert_placeholder_42] => node [:db_insert_placeholder_43] => 8 [:db_insert_placeholder_44] => 243 [:db_insert_placeholder_45] => circle [:db_insert_placeholder_46] => 6 [:db_insert_placeholder_47] => und [:db_insert_placeholder_48] => 65 ) in field_sql_storage_field_storage_write() (line 448 of /srv/bindings/e49d00280243440ba70fa2bf8e2e6208/code/modules/field/modules/field_sql_storage/field_sql_storage.module).

wipeout_dude’s picture

In step 4 of the roll back procedure you must delete the revision_id fields form the field collection field tables..

In this case you need to delete the field_weekly_checklist_revision_id field from the field_data_field_weekly_checklist table..

That should stop the error..

federico.parra’s picture

Hi and thank you for your help. I went ahead and deleted the column from that table. I refreshed the caches and still have the same error appear. Any ideas? Tank you so much.

wipeout_dude’s picture

Sorry, I'm afraid I don't.. What I wrote is what worked for me to roll back my site..

I used the "upgrade" sections from the module install file to see what had changed and that's how I worked out what to delete on my site.. Maybe you have something else still lurking there that I didn't have..

Hopefully someone can chime in with some suggestions or figure out how for get the upgrade to complete correctly..

federico.parra’s picture

Priority:Normal» Critical

Someone?

This update made several sites unusable for several people.
This thread and this other thread: http://drupal.org/node/1876624
show this.

Please, can we be provided with either a patch or at least full instructions to roll back to beta 4?

I tried tht with no luck.

Thanks!

Federico

kevinquillen’s picture

Go into each of your field collection data tables in the database (the one that actually is for the FC entity, not its fields) and set the revision id fields to NULL and enable ALLOW NULL on them. Do the same for revision fields.

I was able to get around this error by doing so... but definitely could not upgrade cleanly from beta4 to beta5.

federico.parra’s picture

Thank you so much Kevin.

In order to make the revision_id field in the FC entities nullable, I used the following SQL query:
ALTER TABLE `pantheon`.`field_data_field_weekly_checklist` MODIFY `revision_id` varchar(20) NULL;
Is that correct? it worked for me (now I can save NULL values in the revision_id column).
I tried with a few of my FC entities (i have dozens and dozens of them on my site) but the error keeps
coming up when deleting/editing/saving FC entities.

About the revision fields, I deleted them completely (drop table). That was part of the instructions of
http://drupal.org/node/1877800#comment-6900826

So I cannot make them null.

Do you think that if I fill ALL revision_id columns from all FC entities tables with null value then the error would stop?

I wonder, was the revision_id column inserted by the Beta 5 version? if yes, wouldn't it be a good idea to DROP that
column as well?

It is so bad nobody is responding about how to either roll back "professionally" or a patch to repair whatever it is that Beta 5 did.

My site is basically broken and have been since early January when I updated...

Best,
Federico

kevinquillen’s picture

Did you do it to both the data and revision tables per collection entity in the database? That's what I did. I didn't use a sql query, I used SequelPro gui to do it. Errors went away.

federico.parra’s picture

Hi Kevin, thank you so much for your prompt response.

For one thing that software is for Mac and I'm using PC.

I have a GUI (MySQL Workbench) but it doesn't seem to have a way to make columns null-enabled other than with a query.

I don't quite understand what you mean by "data" and "revision" tables.

This table "field_collection_item_revision" I dropped/deleted and I cannot imagine a way of recovering it, since I even tried re-installing Beta 5 (which should add the new revision tables) and for some reason it didn't work.

From the table "field_collection_item" I dropped/deleted both the "archived" and "revision_id" columns.

Then I have all the data tables like "field_data_field_weekly_checklist".
From those tables (I have a lot because there is one per FC entity) I deleted/dropped the field_xxx_revision_id" column (in this case "field_weekly_checklist_revision_id").

The only thing left is a revision_id column, that I now am making null-able and then filling with null value.

Is this what you meant to do?

Best and thank you,
Federico

kevinquillen’s picture

You can't just delete the tables, that will cause even more problems. Don't delete any fields in the tables either. I did not revert from beta5 to beta4.

All I did to get around this was (and I went from beta3 to beta5) set the revision ID fields to allow NULLs.

If you have a recent backup, restore that. You may want to look into this:

http://www.heidisql.com/

federico.parra’s picture

Kevinquillen "You can't just delete the tables, that will cause even more problems."

That's the opposite of what wipeout_dude recommended here:

2. Delete the "field_collection_item_revision" table.
3. In the "field_collection_item" table delete the "revision_id" and "archived" fields.
4. In your field collection field tables delete the "_revision_id" fields.

Additionally I'm lost when you say

I did not revert from beta5 to beta4.

As I understood, Beta 5 requires those tables (revision id) to actually have information because beta 5 does create revisions for FC entities; rolling back to beta 4 (what wipeout_dude was suggesting) seemed to be the only way for Field Collection to work regardless of the content of the revision_id columns (this, in theory; in my experience, both ways -either version of Field Collection- gives me error at this point.)

I installed heidisql, great piece of software. thank you.

How should I proceed? This is really killing me, this is work for a company and FC has been crazy since the update.

I wish the actual moderator/person in charge of the module would help us!

What do you recommend Kevin? updating again to Beta 5?

How do I recover the tables that wipeout_dude suggested to delete?

Best and thank you,
Federico

federico.parra’s picture

Status:Active» Needs work

When I try to reinstall Beta 5 I get this on update.php procedure:

Warning: array_keys() expects parameter 1 to be array, null given in drupal_schema_fields_sql() (line 6982 of /srv/bindings/e49d00280243440ba70fa2bf8e2e6208/code/includes/common.inc).

and the revision tables are not created.

kevinquillen’s picture

Not sure of the 'accepted' procedure, but to address the error that is on the screen, those fields have to allow for null values. That is how I got around it.

The fields that are throwing up the error need to be allow to have NULL. I am not sure if that was the intent of beta5 changes, but I got around it that way.

kris-o3’s picture

i followed the same procedure as in #6 to downgrade from beta5 to beta4 and it worked for me as well...

http://drupal.org/node/1783456#comment-7014142

why downgrade? because beta5 causes field_collection_table to break pretty badly.
http://drupal.org/node/1905520

federico.parra’s picture

Hey guys,

I broke my site following your instructions :(

Here's the recapitulation:

  • So I started with a greatly working site using FC beta 4.
  • When I updated to beta 5 I found I couldn't save, edit, or delete -reported here- (sometimes it would work, but erratically - like taking 5 minutes to delete, then showing that it didn't delete the field, but later the field not being there in later renderings, etc).
  • Then I follow #6 to roll back to Beta 4. That didn't work and the following error #12 started to appear IN ADDITION to the slow/interminable time for delete/edit/create and the randomness of the result of such operations (including sometimes creating multiple fields with the same name or deleting many too).
  • After that, I've tried to update to Beta 5 again, since logically that would undo the changes suggested in #6 and re-generate the revision tables (I would be again on square 1); not so fast - the update this time around presented the error listed in #23.
  • After the install/update I checked the sql tables to prove it hasn't changed and indeed - the update of Beta 5 did not add the revision tables.
  • Now I am left in the situation that neither the Beta 4 (rolling back) nor the Beta 5 work at all, Beta 4 god knows why, Beta 5 since the tables it requires (revision) have been deleted by me (following #6) and the update is not adding them again.
  • I have no way out!
  • I would uninstall the whole module and wipe the field_collection table in a blink of an eye if it wasn't because my site has a structure of literally dozens of nested field collections and the structure is SO complex that thinking in re-doing it makes me want to trow up :(
  • However the content itself is not at all important - since the site is in development so all the dozen-field collection items have in them is either nothing or random characters I wrote in order to test.
  • Is there any way of starting the whole install again (of field collections beta 4) without losing the structure? with structure meaning, the actual nested field collections?
  • Kevin's advice sounds great but it begins with the assumption that I haven't deleted tables, which I did (and quite a bit) following #6 so his advice doesn't apply to me any more - it seems.

Please folks (and specially the responsible for this module), work on this!!!

Best and thank you,
Federico

robearls’s picture

Federico, I had the same issue as you, in #23.. A little investigation showed that drupal_get_schema("menu_links"); was being called, giving this error. This lead me to realise there was a conflict in the module "Menu Links", which in my case was installed, but not enabled. I removed this module and all it's files, and Viola!, the error went away.

Danny Englander’s picture

To revert back to beta4 on my local dev, I simply restored a backup and migrate backup for the DB and replaced beta 5 with beta 4 via git. I'm no longer getting a WSOD. I am not sure what I am missing but it seems ok now. I think it was trivial here as it didn't matter if I lost any other data as it's just a local dev.

Danny Englander’s picture

Title:Beta4 -> Beta5 upgrade fails.» Field collection Beta4 -> Beta5 upgrade fails.

Just updating the title for better tracking on the dashboard.

Matthew Davidson’s picture

Status:Needs work» Needs review
StatusFileSize
new2.94 KB
PASSED: [[SimpleTest]]: [MySQL] 132 pass(es).
[ View ]

Here's a patch for the wonky update function. It aims to cope gracefully with broken databases where you've run the failing update function already, but I can't guarantee any success if you've already tried any of the manual fixes suggested above.

Seems to work for me so far. The update completes, and I've been able to create new field collection items.

Matthew Davidson’s picture

Come to think of it, we also need another update function to make the necessary schema change in cases where the beta5 version of field_collection_update_7001() completes without complaint (like my development site, but not my production site - d'oh!).

Matthew Davidson’s picture

StatusFileSize
new4.21 KB
PASSED: [[SimpleTest]]: [MySQL] 132 pass(es).
[ View ]

Here it is.

federico.parra’s picture

Hi Matthew, thank you for the patches.

I wonder if you could give some advice for some of us that basically destroyed our databases trying to make beta 5 or 4 work once broken, while waiting for this patch during almost a month.

I copy here my last message describing what I did, maybe you can suggest some steps for coming back to normal. I don't care about the contents of my FC databases (there was almost no data yet) but I do care about the rest of the database (other modules DO have tons of data) so I cannot use a backup.

Let me know if you have any idea,
Thanks!
federico

Hey guys,

I broke my site following your instructions :(

Here's the recapitulation:

So I started with a greatly working site using FC beta 4.
When I updated to beta 5 I found I couldn't save, edit, or delete -reported here- (sometimes it would work, but erratically - like taking 5 minutes to delete, then showing that it didn't delete the field, but later the field not being there in later renderings, etc).
Then I follow #6 to roll back to Beta 4. That didn't work and the following error #12 started to appear IN ADDITION to the slow/interminable time for delete/edit/create and the randomness of the result of such operations (including sometimes creating multiple fields with the same name or deleting many too).
After that, I've tried to update to Beta 5 again, since logically that would undo the changes suggested in #6 and re-generate the revision tables (I would be again on square 1); not so fast - the update this time around presented the error listed in #23.
After the install/update I checked the sql tables to prove it hasn't changed and indeed - the update of Beta 5 did not add the revision tables.
Now I am left in the situation that neither the Beta 4 (rolling back) nor the Beta 5 work at all, Beta 4 god knows why, Beta 5 since the tables it requires (revision) have been deleted by me (following #6) and the update is not adding them again.
I have no way out!
I would uninstall the whole module and wipe the field_collection table in a blink of an eye if it wasn't because my site has a structure of literally dozens of nested field collections and the structure is SO complex that thinking in re-doing it makes me want to trow up :(
However the content itself is not at all important - since the site is in development so all the dozen-field collection items have in them is either nothing or random characters I wrote in order to test.
Is there any way of starting the whole install again (of field collections beta 4) without losing the structure? with structure meaning, the actual nested field collections?
Kevin's advice sounds great but it begins with the assumption that I haven't deleted tables, which I did (and quite a bit) following #6 so his advice doesn't apply to me any more - it seems.
Please folks (and specially the responsible for this module), work on this!!!

Best and thank you,
Federico

Matthew Davidson’s picture

Federico, first thing to try is to edit the field_collection row in your database's 'system' table so the value of the 'schema_version' field is 7000. Then when you run update.php (or `drush updb`) it will re-apply update 7001 and later, creating the missing table/fields. Make sure you've applied my patch from #32 first.

federico.parra’s picture

Matthew, I'm trying but it doesn't want to work, apparently because some of the tables actually are already created.
What I need is a mechanism by which it will write the ones that are lacking and leave alone the ones that are already created. How can I do that?

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 /srv/bindings/ded1964d0010439288402402f0101e92/code/includes/database/mysql/schema.inc)

federico.parra’s picture

Dear Matthew, I finally made it work by deleting manually every instance of the field_xxx_revision_id field and then running the update.php, with the patch already installed of course and by adding 7000 to the schema field.

The update this time went through perfectly.

However, I am at the same place I begun!

When I add or try to delete a field in a field collection drupal keeps "waiting" and doesn't go anywhere.
When back into the page, it sometimes didn't do anything, and most of the time created multiple versions (and keep creating them!) of the field.

In other words, it is as erratic as it was the first time I updated to Beta 5. I don't see any change made by the patch you submitted.

Please tell me how to proceed.

Thanks,
Federico

federico.parra’s picture

Matthew, I figured out the patch hasn't really patched the file.
I run the patch again and this time it did patch the install file, even
adding to it the 7003 install.
However after going through the update.php and succesfully running 7001 7002 and 7003
the system continues the same, it doesn't add or delete anything.
What could it be?

larowlan’s picture

Here is a utility function I used to fix this

/**
* Function to repair missing revisions.
*/
function _mymodule_repair_revisions() {
  $query = db_select('node', 'n')
    ->fields('n');
  $query->leftJoin('node_revision', 'nr', 'n.vid = nr.vid');
  $results = $query->isNull('nr.nid')
    ->execute()
    ->fetchAll();
  $execute = FALSE;
  $revision_insert = db_insert('node_revision')
    ->fields(array(
    'nid',
    'vid',
    'uid',
    'title',
    'log',
    'timestamp',
    'status',
    'comment',
    'promote',
    'sticky'
  ));
  foreach ($results as $row) {
    $execute = TRUE;
    $revision_insert->values(array(
      'nid' => $row->nid,
      'vid' => $row->vid,
      'uid' => $row->uid,
      'title' => $row->title,
      'log' => t('Repaired from code'),
      'timestamp' => $row->changed,
      'status' => $row->status,
      'comment' => $row->comment,
      'promote' => $row->promote,
      'sticky' => $row->sticky
    ));
    $fields = field_info_instances('node', $row->type);
    foreach ($fields as $field_name => $field) {
      if (empty($field['widget']['module']) || $field['widget']['module'] != 'field_collection') {
        // No need to inspect this field.
        continue;
      }
      $item_id_field = $field_name . '_value';
      $revision_id_field = $field_name . '_revision_id';
      $field_collections = db_select('field_data_' . $field_name, 'f')
        ->condition('entity_id', $row->nid)
        ->condition('entity_type', 'node')
        ->fields('f', array($item_id_field, $revision_id_field))
        ->execute();
      foreach ($field_collections as $field_collection) {

        $vid_query = db_select('field_collection_item_revision', 'fc')
          ->condition('item_id', $field_collection->{$item_id_field});
        $vid_query->addExpression('MAX(revision_id)', 'revision_id');
        $max_vid = $vid_query->groupBy('item_id')
          ->execute()
          ->fetchField();

        if ($max_vid && $max_vid != $field_collection->{$revision_id_field}) {
          db_update('field_data_' . $field_name)
            ->condition('entity_id', $row->nid)
            ->condition('entity_type', 'node')
            ->condition('revision_id', $row->vid)
            ->condition($item_id_field, $field_collection->{$item_id_field})
            ->fields(array(
              $revision_id_field => $max_vid
            ))
            ->execute();
        }
      }
    }
  }
  if ($execute) {
    $revision_insert->execute();
    drupal_flush_all_caches();
  }
}

I added this to my install profile for the project in an update hook, ran updb and all good.

Hope that helps.

Note this makes non-restorable changes to your database - please backup first.

kmare’s picture

Thank you Matthew Davidson, #32 fixed the issue for me as well!

coreyp_1’s picture

#32 worked perfectly for me as well. Thank you very much, Matthew Davidson!

Patribu-former-osmanedosbatuque’s picture

Hello!

After reading this forum again and again, I have to post also something, since nothing is working in my case.

My last attempt to reapair the big mess of the last FC update, was to apply the patch in #32.

know, when I try to edit/save node with FC, I get the following error...

PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'revision_id' in 'where clause': DELETE FROM {field_revision_field_ag_textblock_titel} WHERE (entity_type = :db_condition_placeholder_0) AND (entity_id = :db_condition_placeholder_1) AND (revision_id = :db_condition_placeholder_2) AND (language IN (:db_condition_placeholder_3)) ; Array ( [:db_condition_placeholder_0] => field_collection_item [:db_condition_placeholder_1] => 36 [:db_condition_placeholder_2] => 874 [:db_condition_placeholder_3] => und ) in field_sql_storage_field_storage_write() (line 405 of /home/www/web48/html/drupal7/modules/field/modules/field_sql_storage/field_sql_storage.module).

Any ideas?

xanks

UPDATE:

Well, in my dispair with this whole story, I loaded my backuped DB where I still had the DB tables related to the FC Beta4 version into drupal (using the migration/backup module).
THEN I carried out the the patched version of the FC beta5 install....

And know the error seem to be gone... I hope it stays like this...

Cheers

SLeDiot’s picture

Applying #32 patch , works fine for me , starting from Beta4 version Database

MrOigres’s picture

my installation failed with patch #32:
db_change_field() in 7003 throws an exception ...

since monday I (and others too) spent time in that f****** update.

dear module-writer: please check your works, before publishing!

greetzn from italy!

ibuildit’s picture

I can confirm that there is a way out both if you are coming from beta4 and if you come from beta 5, with or without updates.

Running all updates up to 7003 was important for me to ensure all fields in db tables are there.

Also verifying that all fields that say whatever_revision_id in the field collection data and revision fields are set to NULL and tick that box in phpmyadmin, if thats allow null?

run updates, clear cache, that should be it

could even switch between beta 4 and 5 code no problem after that

marblegravy’s picture

Patch at #32 worked for me. Patched over 7.x-1.x pulled from git and update.php run successfully.

Nice work Matthew. Thanks.

BBC’s picture

Patch #32 worked for me as well. Matthew, thanks very much for taking the time to create something that would help us clean things up after the initial upgrade issues.

sg88’s picture

#32 Worked for me perfectly, tanks a lot ! :-)

wipeout_dude’s picture

Any idea is the patch be rolled into a release version any time soon?
My sites are all still on beta4 because the policy is only to use release versions of modules..

coreyp_1’s picture

Status:Needs review» Reviewed & tested by the community

Agreed, I think this has rocked on for long enough. A lot of people are saying that it works and nobody has given steps to demonstrate a failure, therefore I'm marking this as RTBC.

To the module maintainers, first of all, thanks for the hard work. Second, I feel that this patch alone warrants a new beta to be released. For all the great things that this module does, it's image (and Drupal's) is marred by the persistent bug on a recommended release (beta 5) that has been known for 6 months now. Is there anything blocking the release of a new beta, and if so, how can we help to get this done?

robearls’s picture

What is the procedure for abandonware?

ibevamp’s picture

#32 worked for me, on 7.x-1.0-beta5. thank you.

dshields’s picture

Hopefully, this is committed soon. This issue drove me crazy until I found this thread.

Alexander Hadj Hassine’s picture

You make my day!

DrColossos’s picture

#32 should really be commited, works without a problem

fago’s picture

Status:Reviewed & tested by the community» Fixed

Not sure what exactly the problem with the old upgrade was, howsoever #32 seems to work for people and is written in a cautious way - so committed.

Status:Fixed» Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.

squarecandy’s picture

Version:7.x-1.0-beta5» 7.x-1.0-beta7
Issue summary:View changes
Status:Closed (fixed)» Active

Sorry to say, but I'm experiencing this again, upgrading beta5 to beta7.
Haven't have a chance to dig around and compare with the patch…
Can anyone who has already been working on this take a quick look? Seems like some regression happen here?

Thanks!

cimo75’s picture

Same problem here for beta7.

dieuwe’s picture

Title:Field collection Beta4 -> Beta5 upgrade fails.» Field collection Beta7 upgrade fails.

Same problem with update #7004 in beta7.

Cannot add index ... to table ... index already exists.

dmengstrom’s picture

I have the same problem with update #7004 using beta7 ... Can anything be done?

bdeusanio’s picture

Same feedback.
I'ms experiencing this problem when I try to set a field collection required to not required.
I'm using beta7 too.

dieuwe’s picture

Status:Active» Needs review
StatusFileSize
new653 bytes
PASSED: [[SimpleTest]]: [MySQL] 132 pass(es).
[ View ]

Not sure I would count this as a fix, but I've just wrapped the db_add_index() in a try-catch block and watchdog() the exception message that is thrown when the index already exists.

<?php
     
try {
       
db_add_index($table, $revision_id_column, array($revision_id_column));
      }
      catch (
DatabaseSchemaObjectExistsException $ex) {
       
watchdog('field_collection', $ex->getMessage());
      }
?>
Anonymous’s picture

I solved this by wrapping the last line of the .install module in an if statement consistent with the checks done in update 7001:

if (!db_index_exists($table, $revision_id_column)) {
db_add_index($table, $revision_id_column, array($revision_id_column));
}
jmuzz’s picture

Status:Needs review» Postponed (maintainer needs more info)

I took a look through the .install . It seems like all the latest updates have the checks that should be required, similar to what michael.conley recommends. Does anybody still see this in beta8? Maybe it can be closed again.

wipeout_dude’s picture

As soon as I get a chance I will be testing the upgrade, I am still on beta4.. Once done I will feed back the results..

wipeout_dude’s picture

Version:7.x-1.0-beta7» 7.x-1.0-beta8
Status:Postponed (maintainer needs more info)» Active

I have just synced my live site to a virtual box dev server and attempted to upgrade from field collection beta 4 to the latest beta 8..

Drush gave a warning when doing the database updates..

Performed update: field_collection_update_7006     [ok]
array_keys() expects parameter 1 to be array, null given   [warning]
common.inc:7102

After the update any attempt to edit a node with a field collection returns the following error and a white screen so the node can't be edited..

PDOException: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'field_collection_item_revision' doesn't exist: SELECT revision.revision_id AS revision_id, base.item_id AS item_id, base.field_name AS field_name, base.archived AS archived, CASE WHEN base.revision_id = revision.revision_id THEN 1 ELSE 0 END AS default_revision FROM {field_collection_item} base INNER JOIN {field_collection_item_revision} revision ON revision.revision_id = base.revision_id WHERE (base.item_id IN (:db_condition_placeholder_0)) ; Array ( [:db_condition_placeholder_0] => 9994 ) in EntityAPIController->query() (line 187 of /var/www/sites/all/modules/contrib/entity/includes/entity.controller.inc).

Hope that is of some use in resolving the update issue..

jmuzz’s picture

Status:Active» Postponed (maintainer needs more info)

If you are updating from beta4 then it should be doing all the updates from 7001 on, not starting with 7006. You can try this:

update system set schema_version=7000 where name='field_collection';

Though there is a chance another attempt to upgrade will have messed up the database so it can not be updated this way now, it should give a better idea of where to start if it gives errors.

wipeout_dude’s picture

Hi,

Sorry for the delay in checking this out.. I have done as suggested and was able to upgrade from beta4 to beta8..

I do however still have a problem in that I can't create new content of types that contain more than one field collection field. If I do I get this error.

Fatal error: __clone method called on non-object in /var/www/sites/all/modules/contrib/field_collection/field_collection.module on line 939 

wipeout_dude’s picture

Status:Postponed (maintainer needs more info)» Active
jmuzz’s picture

Status:Active» Postponed (maintainer needs more info)

I don't know if that's a problem with the upgrade. Try making a new content type with multiple field collection fields and see if content of that type can be saved. If it can't then it is likely something else, I am guessing an incompatibility with another module.

wipeout_dude’s picture

Finally I have managed to upgrade and so far it seems it's al working.. The trick on the content type with multiple field collections was that I had to edit and save each field collection field on the content type.. After that it seems to have worked.. So after two years I am finally running the current Field Collection module.. :)

Thanks jmuzz!

jmuzz’s picture

Status:Postponed (maintainer needs more info)» Fixed

That's great wipeout_dude.

Thanks for the explanation. Hard to imagine what the problem could have been.

Status:Fixed» Closed (fixed)

Automatically closed - issue fixed for 2 weeks with no activity.

mfarineau’s picture

I have to say, this has been one of the most infuriating issues to try to work around. Thank you, wipeout_dude; your tip about editing the field collection for the content types and then saving them worked... astoundingly. I, too, am now running the latest version of this module after two years.