How to change the length of field settings?

Question

I have set once to a website a lenght limit to a field. And now the client wants to put more characters to that field.

I cannot change the maximum size from Drupal because I get the following error message:

There is data for this field in the database. The field settings can no longer be changed.

However there must be a solution. Shall I change it in database (the field is one implemented by the Field-collections module)?

Answer 1

Dylan Tack solution is the easiest, but personally I enjoy exploring the inner wards of Drupal's database to seen how things are managed down there.

So, assuming you have a text field which machine name is field_text of 10 characters you want to grow to 25:

• data will be stored in two tables: field_data_field_text and field_revision_field_text
• definition is stored in field_config for the storage data, and field_config_instance for each instance of this field (stuff like label).

Now let's do a little heart surgery.

1. Alter the data tables columns definitions:

   ALTER TABLE `field_data_field_text` 
   CHANGE `field_text_value` `field_text_value` VARCHAR( 25 ) 
   CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL;
   
   ALTER TABLE `field_revision_field_text` 
   CHANGE `field_text_value` `field_text_value` VARCHAR( 25 ) 
   CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL;
   

2. Change the definition column, this one is very tricky because it's stored in a BLOB, but that's not something that will stop you for doing this.

   • Dissect the guts of this BLOB thing:

   SELECT CAST(`data` AS CHAR(10000) CHARACTER SET utf8) 
   FROM `field_config` WHERE field_name = 'field_text';
   

   • This will give you something like:

   a:7:{s:12:"translatable";s:1:"1";s:12:"entity_types";a:0:{}s:8:"settings";a:2:
       {s:10:"max_length";s:2:"10";s:17:"field_permissions";a:5:
       //a lot more stuff...
   

   • This is a PHP serialized array, the interesting part is s:10:"max_length";s:2:"10";, this mean this array has a property named max_length (which name is a 10 characters string - hence the "s") which value is 10 (which is a 2 characters long string). It's pretty easy, isn't it?

   • Changing its value is as easy as replacing the s:2:"10" part by s:2:"25". Be careful: if your new value is longer in character length, you have to adapt the "s" part accordingly. For example putting 100 will be s:3:"100" as 100 length is 3 characters.

   • Let's put this new value back in the DB, don't forget to keep the whole string.

   UPDATE `field_config` 
   SET data = 'a:7:{...a:2:{s:10:"max_length";s:2:"25";...}'
   WHERE `field_name` = 'field_text'
   

   • Flush your caches.

3. ???

4. PROFIT!

By the way, PhpMyAdmin has some setting to allow direct modification of BLOB columns, but why go the easy way?

PS: This can also save your life when putting some PHP code in views and getting a WSOD because of an error in your code.

Answer 2

function mymodule_update_7100() {
  $items = array();
  _field_maxlength_fix('field_name');
  return $items;
}

function _field_maxlength_fix($field_name, $maxlength = 255) {
  _alter_field_table($field_name, $maxlength);
  $data = _get_field_data($field_name);
  $data = _fix_field_data($data, $maxlength);
  _update_field_config($data, $field_name);
}

function _alter_field_table($field_name, $maxlength) {
  db_query("ALTER TABLE field_data_".$field_name." CHANGE ".$field_name."_value ".$field_name."_value VARCHAR( ".$maxlength." ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL");
  db_query("ALTER TABLE field_revision_".$field_name." CHANGE ".$field_name."_value ".$field_name."_value VARCHAR( ".$maxlength." ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL");
}

function _get_field_data($field_name) {
  $qry = "SELECT data FROM field_config WHERE field_name = :field_name";
  $result = db_query($qry, array(':field_name' => $field_name))->fetchObject();
  return unserialize($result->data);
}

function _fix_field_data($data, $maxlength) {
  $data['settings']['max_length'] = (string)$maxlength;
  return serialize($data);
}

function _update_field_config($data, $field_name) {
  $qry = "UPDATE field_config SET data = :data WHERE field_name = :field_name";
  db_query($qry, array(':data' => $data, ':field_name' => $field_name));
}

Answer 3

This appears to be a limitation of the current text module. text_field_settings_form() has this comment: "@todo: If $has_data, add a validate handler that only allows max_length to increase.".

As a temporary workaround, you could comment out '#disabled' => $has_data in modules/field/modules/text/text.module, around line 77.

I couldn't find an existing issue for this specific case, but you might mention it on #372330.

Answer 4

Drupal 7

When creating a text field in Drupal 7, you must a choose a maximum length for your data. As soon as you create any data for this field, the maximum length become immutable in the Drupal field settings.

It's understandable that this would be disabled for decreasing the maximum length because it could results in a loss of data, however, it should be possible to increase the maximum length for any field. A todo in the Drupal 7 Text module code shows this was intended but never accomplished.

The 3 things that need to happen:

1. Change the VARCHAR length of the value column in the field_data_{fieldname} table
2. Change the VARCHAR length of the value column in the field_revision_{fieldname} table
3. Update the configuration of the field to reflect the new max length setting The following function accomplishes all 3 of these steps and takes 2 easy parameters including the name of the field and the new maximum length.

/**
 * Helper function to change the max length of a text field.
 */
function MYMODULE_change_text_field_max_length($field_name, $new_length) {
  $field_table = 'field_data_' . $field_name;
  $field_revision_table = 'field_revision_' . $field_name;
  $field_column = $field_name . '_value';

  // Alter value field length in fields table.
  db_query("UPDATE `{$field_table}` SET `{$field_column}`=SUBSTR(`{$field_column}`, 0, {$new_length})");
  db_query("ALTER TABLE `{$field_table}` CHANGE `{$field_column}` `{$field_column}` VARCHAR( {$new_length} )");
  // Alter value field length in fields revision table.
  db_query("UPDATE `{$field_revision_table}` SET `{$field_column}`=SUBSTR(`{$field_column}`, 0, {$new_length})");
  db_query("ALTER TABLE `{$field_revision_table}` CHANGE `{$field_column}` `{$field_column}` VARCHAR( {$new_length} )");

  // Update field config with new max length.
  $result = db_query("SELECT CAST(`data` AS CHAR(10000) CHARACTER SET utf8) FROM `field_config` WHERE field_name = '{$field_name}'");
  $config = $result->fetchField();
  $config_array = unserialize($config);
  $config_array['settings']['max_length'] = $new_length;
  $config = serialize($config_array);
  db_update('field_config')
    ->fields(array('data' => $config))
    ->condition('field_name', $field_name)
    ->execute();
}

Once this function is available in your custom install file you can create a new database update function that uses this new function to make your required changes.

/**
 * Change max_length of Name field
 */
function mymodule_update_7002() {
  MYMODULE_change_text_field_max_length('field_name', 50);
}

^{Source: http://nathan.rambeck.org/blog/42-modify-drupal-7-text-field-maximum-length}

---

Drupal 8

Here is the version of the same function proposed by @Christopher:

/**
 * Utility to change the max length of a text field.
 *
 * @param string $field_name
 *   Field name. 
 * @param int $new_length
 *   Field length in characters. 
 *
 * @throws \DrupalUpdateException
 */
function MYMODULE_change_text_field_max_length($field_name, $new_length) {
  // The transaction opens here. 
  $txn = db_transaction();

  try {
    // Update field content tables with new max length.
    foreach (['field_data_', 'field_revision_'] as $prefix) {
      db_query('
      ALTER TABLE {' . $prefix . $field_name . '} 
        MODIFY ' . $field_name . '_value VARCHAR( ' . $new_length . ' )
      ');
    }

    // Update field config record with new max length.
    $result = db_query("
        SELECT CAST(data AS CHAR(10000) CHARACTER SET utf8) 
        FROM {field_config} 
        WHERE field_name = :field_name
      ", [':field_name' => $field_name]
    );
    $config = $result->fetchField();
    if ($config) {
      $config_array = unserialize($config);
      $config_array['settings']['max_length'] = $new_length;
      $new_config = serialize($config_array);
      db_update('field_config')
        ->fields(['data' => $new_config])
        ->condition('field_name', $field_name)
        ->execute();
    }
  }
  catch (Exception $e) {
    // Something went wrong somewhere, so roll back now.
    $txn->rollback();
    // Allow update to be re-run when errors are fixed.
    throw new \DrupalUpdateException(
      "Failed to change $field_name field max length: " . $e->getMessage(),
      $e->getCode(), $e
    );
  }
}

Answer 5

Try this... this will update the field data type from TEXT to LONG_TEXT and update the max_length from 4000 to max long text length... hope this helps.

function my_module_update_1001(&$sandbox) {
  // Check if our field is already created.
  if (field_info_field('sample_text_field')) {
    db_query('ALTER TABLE field_data_sample_text_field CHANGE sample_text_field_value sample_text_field_value LONGTEXT');
    db_query('ALTER TABLE field_revision_sample_text_field CHANGE sample_text_field_value sample_text_field_value LONGTEXT');
    db_query("UPDATE field_config SET type = 'text_long' WHERE field_name = 'sample_text_field' ");

    $field = array(
      'field_name' => 'sample_text_field',
      'type' => 'text_long',
      'cardinality' => 1,
      'settings' => array(
         'max_length' => 0,
      ),
    );
    field_update_field($field);    
  }
}

Answer 6

D8

1. Comment out the two lines in core/lib/Drupal/Core/Entity/Sql/SqlContentEntityStorageSchem‌​a.php that throw exceptions with the text "The SQL storage cannot change the schema for an existing field". About lines 1312 and 1403.
2. In your browser, navigate to /admin/config/development/configuration/single/export. The configuration type is "Field Storage", and choose the field in question. Copy the configuration.
3. Navigate to /admin/config/development/configuration/single/import. . The configuration type is "Field Storage". Paste the configuration. Change the length.
4. Submit the form.

Note (as you probably already know), if you shorten the length, then existing data will be truncated.

Answer 7

In Drupal 7, I made modifications to 2 tables in phpmyadmin, and refreshed the cache.

1. "field_data_field_lorem": changed "field_lorem_value" to "longtext"
2. "field_config": changed "type" to "text_long"