0

When attempting to add a new column to a table within an update hook, the error Null value not allowed: 1138 Invalid use of NULL value occurred, the definition was changed to allow NULL, and the value for existing rows was set to NULL.

function MYMODULE_update_8101() {
  Drupal::database()->schema()->addField(
    'MYMODULE_table',
    'created',
    [
      'type' => 'int',
      'unsigned' => TRUE,
      'not null' => TRUE,
    ]
  );
}

When adding the column to the database with an SQL query, using the NOT NULL restriction causes the field to have a default value of 0, and no error happens.

How can the error be fixed, and the proper schema set?

  • have you tried 'null' => FALSE, by removing 'not null' => TRUE, – vgoradiya Jan 4 '16 at 5:41
  • Having the 'not null' restriction is a valuable and required attribute of the column and simply removing it does not actually fix the problem, it just masks the error. There's a similar reason for not providing a 'default' attribute. Using 'initial' is the correct approach, but unfortunately not very discoverable from searching based on the error message. – gapple Jan 8 '16 at 21:31
2

If the field spec sets 'not null' => TRUE but does not define a default value, the column is created allowing null values and later altered to disallow them (see the mysql driver Schema::addField(), and the $fixnull variable). The second query attempting add the restriction fails, causing the error.

To fix the error, either

  • An 'initial' value needs to be added to the spec provided to addField()

    Drupal::database()->schema()->addField(
      'MYMODULE_table',
      'created',        
      [
        'type' => 'int',
        'unsigned' => TRUE,
        'not null' => TRUE,
        'initial' => 0,
      ]
    )
    
  • Or a 'default' value needs to be added to the spec provided to addField().
    When providing a default value, the spec in hook_schema() should also be changed to match.

  • I think what I suggested it is worked :) – vgoradiya Jan 11 '16 at 8:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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