Best way to change field length in drupal if data is already present

Assuming you have a text field which machine name is field_text of 10 characters and you want to grow to 25 characters, if the data is already present into that field in database then drupal wont allow to change the field length through User Interface.

How drupal store field data into database (example field name : field_text)

  • 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 to change the length of the field

  1. Alter the data tables columns definitions:[code type=mysql]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;

    [/code]

  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:
      [code type=mysql]
      SELECT CAST(`data` AS CHAR(10000) CHARACTER SET utf8) 
      FROM `field_config` WHERE field_name = 'field_text';

      [/code]

    • This will give you something json type data 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.
    • [code type=mysql]UPDATE `field_config`
      SET data = ‘a:7:{…a:2:{s:10:”max_length”;s:2:”25″;…}’
      WHERE `field_name` = ‘field_text'[/code]
    • 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.