How to overcome a database error on field deletion

Question

I have read the other SQLSTATE[42S22] questions here, and they seem to all be about modules that have not completed setup properly, but I think my issue is different.

Whenever I try to delete a field the following error shows up:

PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 
'field_data_field_doc_file0.field_doc_file_value' in 'where clause': 
SELECT field_data_field_doc_file0.entity_type AS entity_type, 
field_data_field_doc_file0.entity_id AS entity_id, 
field_data_field_doc_file0.revision_id AS revision_id, 
field_data_field_doc_file0.bundle AS bundle FROM 
{field_data_field_doc_file} field_data_field_doc_file0 WHERE 
(field_data_field_doc_file0.field_doc_file_value = :db_condition_placeholder_0) 
AND (field_data_field_doc_file0.deleted = :db_condition_placeholder_1) ; 
Array ( [:db_condition_placeholder_0] => 61531 [:db_condition_placeholder_1] => 0 ) 
in field_sql_storage_field_storage_query() 
(line 652 of /path/to/drupal/modules/field/modules/field_sql_storage/field_sql_storage.module).

It seems that perhaps some previous field deletion went awry or something? If I hit the back button it does appear that my field has been deleted, but I fear that if I keep deleting fields, I may be digging myself into a much worse problem to untangle. What would be the best course of action to resolve this issue, and get rid of these db errors? I'll start by looking at the field_sql_storage module, but suspect I'm not familiar enough with drupal yet, to quickly ascertain what is going wrong via that route.

UPDATE: Line 652 of that module is the last line of the field_sql_storage_field_storage_query method:

return $query->finishQuery($select_query, $id_key);

UPDATE 2: I found the entity with the id in [:db_condition_placeholder_0] in a table called field_deleted_data_143, but I haven't find a column named field_doc_file_value anywhere yet (The deleted table has a field_doc_file_title_value table). The FROM portion of the query is FROM {field_data_field_doc_file} field_data_field_doc_file0, but I don't know what table {field_data_field_doc_file} represents here. Is it field_data_field_doc_file (which is an empty table), or are do those curly brackets mean that name is being replaced with something else?

I guess maybe the next step is to try adding the column to the table.

Answer 1

I tried manually adding a column with the missing name to the table in question. Now a cron run spits out a series of 20 sets of error messages like this:

Notice: Undefined property: stdClass::$fid in file_usage_list() (line 649 of /path/to/drupal/includes/file.inc).
Notice: Undefined property: stdClass::$status in file_field_delete_file() (line 363 of /path/to/drupal/modules/file/file.field.inc).
Notice: Undefined property: stdClass::$fid in file_usage_delete() (line 728 of /path/to/drupal/includes/file.inc).
Notice: Undefined property: stdClass::$uri in file_delete() (line 1273 of /path/to/drupal/includes/file.inc).
Notice: Undefined property: stdClass::$uri in file_delete() (line 1274 of /path/to/drupal/includes/file.inc).
Notice: Undefined property: stdClass::$uri in file_delete() (line 1278 of /path/to/drupal/includes/file.inc).
Notice: Undefined property: stdClass::$uri in file_delete() (line 1280 of /path/to/drupal/includes/file.inc).
The specified file could not be deleted, because it is not a valid URI. More information is available in the system log.

At the same time I notice the number of rows in my various deleted_data tables seems to decrease by 10 with each run. Deleting a field likewise triggers 20 errors and a decrease in the number of rows in the deleted_data tables.

I'm hoping that this has been the fix. It makes sense that it can't delete the files as they come in via a migration, and I've started bringing the files in on a different field, so I rolled back and imported. I'll update this once cron has run enough times that I can see if my deleted data logjam has cleared and fields can be deleted again.

UPDATE: It seems like there was a tremendous amount of backlog building up behind this database error. I set a system cron job to trigger drupal cron once a minute (dev site, so no performance concerns) and over the course of many hours, the rows in the deleted_data and deleted_revision tables have started to go away, followed by the tables themselves. A few empty tables and one with three rows in it remain, and I'm not sure if they will eventually be cleared, or if they aren't in whatever queue is being run through. If someone knows how to check what tasks drupal has queued up to run in the background, please do comment and let me know.

IN CONCLUSION: In this particular case, where the system was trying to make a selection and not finding the column it expected to find, it seems like the only possible solution was to add the column in, so the select would return 0 rows instead of an error that stopped things from moving on. I'm not sure what caused the discrepancy between expected columns and actual database schema, but I suspect it has something to do with an earlier struggle to get the media module working for me, that involved switching back and forth between the 7.1.x and 7.2.x branches of that module. Somewhere along the way I think drupal lost track of what version was currently installed, and stopped being able to properly run the right database upgrades on install and update. I'll read up some more on how to check if this is something I need to address, but if you've had a similar experience, that may be a place to start on debugging the source of your woes.