Recently, I’ve been building a custom module to use on a customer’s site.  When I tried to install it I got the following error message:

 

user warning: 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 ‘NOT NULL DEFAULT 1, PRIMARY KEY (uhid) ) /*!40100 DEFAULT CHARACTER SET UTF8 */’ at line 10 query: CREATE TABLE user_history ( `uhid` INT auto_increment DEFAULT NULL, `nid` INT unsigned NOT NULL DEFAULT 0, `uid` INT unsigned NOT NULL DEFAULT 0, `chapter_status` VARCHAR(32) NOT NULL DEFAULT ”, `title` VARCHAR(32) NOT NULL DEFAULT ”, `type` VARCHAR(32) DEFAULT ”, `start_date` INT DEFAULT NULL, `end_date` INT DEFAULT NULL, `enabled` NOT NULL DEFAULT 1, PRIMARY KEY (mhid) ) /*!40100 DEFAULT CHARACTER SET UTF8 */ in /home/trunk/project/htdocs/includes/database.inc on line 529.

 

I immediately noticed the last line of the query:  `enabled` NOT NULL DEFAULT 1

 

A datatype had not been assigned to the column I was trying to create. 

 

Taking a look at my code I noticed the following:

 

function member_history_schema(){

$schema[‘user_history’] = array(

‘description’ => t(‘Stores user history data’),

‘fields’ => array(

‘uhid’ => array(

‘type’ => ‘serial’,

‘description’ => t(‘primary key’),

),

‘nid’ => array(

‘type’ => ‘int’,

‘unsigned’ => TRUE,

‘not null’ => TRUE,

‘default’ => 0,

‘description’ => t(“The {node}.nid of the user’s history”),

),

‘uid’ => array(

‘type’ => ‘int’,

‘unsigned’ => TRUE,

‘not null’ => TRUE,

‘default’ => 0,

‘description’ => t(“The {user}.uid of the {user}”),

),

‘status’ => array(

‘type’ => ‘varchar’,

‘length’ => 32,

‘not null’ => TRUE,

‘default’ => ”,

),

‘title’ => array(

‘type’ => ‘varchar’,

‘length’ => 32,

‘not null’ => TRUE,

‘default’ => ”,

),

‘type’ => array(

‘type’ => ‘varchar’,

‘length’ => 32, 

‘not_null’ => TRUE,

‘default’ => ”,

),

‘start_date’ => array(

‘type’ =>’int’,

‘description’ => t(‘The start date the {user} status.’),

),

‘end_date’ => array(

‘type’ =>’int’,

‘description’ => t(‘The end date the {user} status.’),

),

‘enabled’ => array(

‘type’ => ‘tinyint’,

‘not null’ => TRUE,

‘default’ => 1,

),

),

‘primary key’ => array(‘uhid’)

);

return $schema;

}

 

I instantly realized my mistake, I was attempting to create a boolean field and chose to use tinyint as the datatype column. When I read the following drupal page http://drupal.org/node/159605 I misunderstood how it was saying to declare the type (I must not have been paying attention at all, because now it makes sense).  I should have read this page http://api.drupal.org/api/group/schemaapi/6. Specifically, the part where they explain the ‘size’ parameter.

 

So instead of: 

 

‘enabled’ => array(

‘type’ => ‘tinyint’,

‘not null’ => TRUE,

‘default’ => 1,

),

 

I should have done the following:

 

‘enabled’ => array(

‘type’ => ‘int’,

‘size’ => ‘tiny’,

‘not null’ => TRUE,

‘default’ => 1,

),

 

This certainly was a mistake on my part, but sometimes I find the Drupal documentation confusing.

 

Moral of the story: declare boolean fields as int with a size of tiny.