Why do I get this error when I try to use db_change_field()?

Question

My Drupal installation has a table with a field title of type varchar and length of 255.

For some custom process, I need to increase the length to 500 characters.

I tried doing so with the following code:

function mymodule_schema_alter(&$schema){
    if( isset($schema['field_data_field_document_title']) ){
        db_change_field('field_data_field_document_title', 'field_document_title_value', 'field_document_title_value', 
            array(
                'length' => 11,
            )
        );

    }
}

Originally I had that code in my .module file. When I flushed caches I could verify using the DEVEL module that my field length changed. But in phpmyadmin the field length remained 255.

Then I migrated the function to the .install file. I execute run updates and get the following error:

PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DEFAULT NULL' at line 1: ALTER TABLE {field_data_field_document_title} CHANGE field_document_title_value field_document_title_value DEFAULT NULL; Array ( ) in db_change_field() (line 3020 of /var/www/Intranet/includes/database/database.inc).

I could really use some help, please.

Answer 1

The error you get is because you aren't using db_change_field() with the correct arguments. For example, if you look at block_update_7003(), you will see it calls the function as follows.

  db_change_field('block', 'weight', 'weight', array(
    'type' => 'int',
    'not null' => TRUE,
    'default' => 0,
    'description' => 'Block weight within region.',
  ), array(
    'indexes' => array(
      'list' => array('theme', 'status', 'region', 'weight', 'module'),
    ),
  ));

The last argument is the full array defining the field, not just the part that you need to change.

As the description for block_update_7003() says, its purpose is changing the weight column to normal int. If your code were correct, block_update_7003() should use the following call.

  db_change_field('block', 'weight', 'weight', array(
    'type' => 'int',
  ));

Also, hook_schema_alter() should be used to alter the schema, not adding or removing database fields. Drupal doesn't use that hook, but the example code is clear enough.

function hook_schema_alter(&$schema) {
  // Add field to existing schema.
  $schema['users']['fields']['timezone_id'] = array(
    'type' => 'int',
    'not null' => TRUE,
    'default' => 0,
    'description' => 'Per-user timezone configuration.',
  );
}

The description of the hook is probably not clear, since it doesn't say in which way the schema should be changed, but since is talking of $schema, the idea behind that hook is that it alters what returned from hook_schema(), which is what an alter hook normally does: Change the values returned from another hook.

It is then hook_install() or hook_update_N() that effectively changes the database table.

Answer 2

Check the documentation for hook_schema_alter(): db_change_field() is not supposed to be used there, instead change the $schema array to the required structure.

function mymodule_schema_alter(&$schema){
    if( isset($schema['field_data_field_document_title']) ){
        $schema['field_data_field_document_title']['fields']['field_data_field_document_title_value']['length'] = 500;
    }
}

However, if the module is already installed and has existing data (seems like this is the case), then a call to db_change_field() inside a hook_update_N() implementation is necessary to update the schema.

function mymodule_update_7100() {
    $table = drupal_get_schema('field_data_field_document_title', TRUE);
    db_change_field('field_data_field_document_title', 'field_document_title_value', 'field_document_title_value', $table['fields']['field_document_title_value']);
}

Note (if there is existing data): both these functions need to be included in mymodule.install, as the second function depends on the first: the change of field length made in mymodule_schema_alter() will be included in the table schema returned by drupal_get_schema().