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)?

  • Can you tell us what version of Drupal and CCK? Also any CCK contrib modules? – pthurmond Aug 8 '11 at 20:26
  • Drupal version is 7. – Ek Kosmos Aug 9 '11 at 6:16
  • And you can't modify it under the content type manage fields section? You should be able to modify it at any point. It might give you a warning, but it should still allow it. This may be bug/limitation in the field collections module. – pthurmond Aug 9 '11 at 14:15
  • Have you tried using field groups? You can now do things like group duplication that you could not before. – pthurmond Aug 9 '11 at 14:15
  • I can't modify it under the content type manage fields section! This is the point. Drupal do not let me do it. – Ek Kosmos Aug 10 '11 at 10:50
up vote 85 down vote accepted
+50

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 you have to adapt the "s" part, for example putting 100 will be s:3:"100" as 100 length is 3.

    • 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.

  • That first SQL statement should say ALTER TABLE not SELECT TABLE. Also, you can make it cleaner like: ALTER TABLE field_data_field_text MODIFY field_text_value... (MODIFY is like CHANGE when you don't want to change the name. And you don't.) – artfulrobot Feb 2 '12 at 10:16
  • 4
    Thanks for this. I wish I had more than one upvote to give. – BC01 Feb 9 '12 at 19:33
  • 1
    This post uses Drupal's Schema API to achieve the same approach: up2technology.com/blog/converting-drupal-fields – Juampy NR Sep 12 '13 at 13:23
  • 2
    If you don't mind sending your serialized data to the internets, I found this tool, great for editing: pines.sourceforge.net/phpserialeditor.php – Pete Dec 7 '13 at 0:46
  • If you get an error about 'CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL', you can use PhpMyAdmin. – tog22 May 6 '14 at 19:39
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));
}
  • Like this fix the most since it doesn't rely on manual DB hackery...having a replicable solution via a hook_update_N is essential! – areynolds Mar 31 '15 at 20:53
  • Its really very helpfull...Thanks Kevin – shivam Oct 2 '15 at 7:02

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.

  • 2
    Thanks for your answer. I have comment the line and now I can modify the value form the field, however I get the following error after sumbit: Attempt to update field Serial No failed: field_sql_storage cannot change the schema for an existing field with data.. – Ek Kosmos Aug 27 '11 at 8:36
  • Concerning committing the drupal issue, please go ahead and do it. – Ek Kosmos Aug 27 '11 at 8:37
  • 1
    I could not get this to work. After commenting that line, I still got a MYSQL error that ALTER TABLE failed. – jchwebdev Sep 4 '14 at 19:49
  • If you get a FieldUpdateForbiddenException: cannot change the schema for an existing field with data you also need to comment out L282 on modules/field/modules/field_sql_storage/field_sql_storage.module. – dieuwe Nov 23 '15 at 6:12
  • @dieuwe's comment is wrong. That file doesn't exist in recent versions of D8 (if it ever did). You need to comment out the two lines in core/lib/Drupal/Core/Entity/Sql/SqlContentEntityStorageSchema.php that throw exceptions with the text "The SQL storage cannot change the schema for an existing field". About lines 1312 and 1403. – Dalin Oct 20 '17 at 15:26

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
    );
  }
}
  • 1
    We used this drupal 8 method on a project and ran into issues whereby when you ran drush updb --entity-updates the fields that we ran through this function would be marked as requiring an update. It would then attempt to update the field schema again in mysql. For us this would then fail as there was already field data in there. This was preventing other update tasks from running. – leon.nk Jul 19 '17 at 21:05
  • Long story short, it's probably safer to create new fields and migrate data across. – leon.nk Jul 19 '17 at 21:05

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);    
  }
}

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.

  • This worked like a charm. As of Drupal 8.5.3 the lines are 1505-1507 in protected function updateDedicatedTableSchema and 1596-1598 in protected function updateSharedTableSchema . Be sure to change back when it's done! Thank you. – HongPong May 10 at 21:51
  • I first tried this approach. I'm trying to modify a paragraph field. It works but anyway is mandatory to ALTER the corresponding tables. – Yaazkal Jul 16 at 3:07
  • @Yaazkal Hmmm, that wasn't my experience. Are you trying to lengthen or shorten the field? – Dalin Jul 16 at 14:48
  • @Dalin lengthen, from 255 to 510. Using D 8.5.5 and Paragraphs 1.3 – Yaazkal Jul 16 at 15:19

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"
  • Did you submit this as a patch to D7? If this is truly the issue then you should really issue a patch to them to resolve this issue in the core code. – pthurmond Aug 15 '11 at 15:32
  • I have changed my the field size value from phpmyadmin(from 10 to 25 of type VARCHAR), and refreshed the cache, however is not working. In database ramained VARCHAR(25) but in Drupal, the field is at the same maximum size value as before 10. Maybe there is some hidden field setting? – Ek Kosmos Aug 16 '11 at 13:37

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.