CiviCRM Community Forums (archive)

I tried creating the table manually, pulling the schema from the module install file. The create table command fails when defined per the schema.  The problem appears to be related to the definition of the rule_id field. Here's the schema:


$schema['civicrm_member_roles_rules'] = array(
'fields' => array(
'rule_id' => array(
   'type' => 'serial',
   'unsigned' => TRUE,
   'not null' => TRUE,
   ),
'rid' => array(
   'type' => 'int',
   'unsigned' => TRUE,
   'not null' => TRUE,
   ),
'type_id' => array(
   'type' => 'int',
   'unsigned' => TRUE,
   'not null' => TRUE,
   ),
'status_codes' => array(
'type' => 'text',
'not null' => TRUE,
),
),
'primary key' => array('rule_id'),
);

And here's what I found:

CREATE TABLE fails on the definition of rule_id field when type=serial and the attribute unsigned=true is defined. If I remove the unsigned=true attribute, the create table operation performs properly.

According to MySQL website: SERIAL is an alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE. (http://dev.mysql.com/doc/refman/5.0/en/numeric-type-overview.html)

So it appears that defining the unsigned attribute is redundant. But does the primary key for this table really need to be a bigint? Seems unlikely that there would be more than a few billion CiviMember role association rules defined for any given site.

Hello, I have a Drupal / CiviCRM site that has been through many upgrades.  I started with D6 / CiviCRM 3.x and am now on D7 / CiviCRM 4.2.6.  I ran in to this issue today as I am preparing to use Role sync (previously I've never enabled or used this module until today).  After I got the error I found that by uninstalling the module, either with drush or the UI, then re-enabling the module, this recreated the table properly. So:

drush pm-uninstall civicrm_member_roles -y
then

drush pm-enable civicrm_member_roles -y
This fixed the error for me.