Modifying integer field to decimal gives error when editing field information

Question

I have implemented php code to update a field from integer to decimal. However when trying to modify the field name from UI, it gives error:

Attempt to update field Maximum Temperature failed: field_sql_storage cannot change the schema for an existing field with data.

The system behaves well when editing existing field data or creating a new one.

Please find the code executed via hook_update_N() below:

/**
* Modify the integer field of nursing notes temperature field
* to decimal field with precision and scale
*/
function ji_custom_update_7001() {

  // Manual database changes.
  db_query("UPDATE {field_config} SET type = 'number_decimal'
      WHERE field_name = 'field_nursing_note_max_temp'");

  $new_field = array(
    'type' => 'numeric',
    'precision' => 10,
    'scale' => 2,
    'not null' => FALSE,
  );

  db_change_field('field_data_field_nursing_note_max_temp', 'field_nursing_note_max_temp_value'
    , 'field_nursing_note_max_temp_value', $new_field);

  db_change_field('field_revision_field_nursing_note_max_temp', 'field_nursing_note_max_temp_value'
    , 'field_nursing_note_max_temp_value', $new_field);

  // Clear caches.
  field_cache_clear(TRUE);
}

Answer 1

If the only tables that you have updated are field_config, field_data_field_nursing_note_max_temp and field_revision_field_nursing_note_max_temp then you missed one: field_config_instance.

Without being able to explain in detail how your manual mingling with the database produces the error, I'll try to outline what I understand.

The error you're seeing is produced in field_sql_storage_field_update_forbid. This is a hook implementation that is called by the Field CRUD API in order to give modules the chance to object the updating of a field:

// See if any module forbids the update by throwing an exception.
foreach (module_implements('field_update_forbid') as $module) {
  $function = $module . '_field_update_forbid';
  $function($field, $prior_field, $has_data);
}

The hook function receives three argumentes, the first is the current field definition coming from the field settings form, the second is the prior field defintion which is read from the field_config_instance table:

$prior_field = field_read_field($field['field_name']);

I assume that those two arrays differ and produce the error that you are encountering.

So I'd say, welcome to field hell! It's in general not advisable to mingle directly with the fields underlying table structure and column definitions. If you need to change a field, it might be a lot better to create a temporary field and migrate the data using API functions in batch scripts for example. That sounds like more work in the first place, but it will spare you these kinds of inconsistencies.