Database Schema errors [#437730]

Doing a clean installation (with any old table deleted) of version 6.x-0-3, I got this ugly error:

* user warning: Incorrect table definition; there can be only one auto column and it must be defined as a key query: CREATE TABLE node_limit_role ( `lid` INT NOT NULL, `rid` INT unsigned NOT NULL auto_increment, PRIMARY KEY (lid, rid) ) /*!40100 DEFAULT CHARACTER SET UTF8 */ in /*******/includes/database.inc on line 515.

Deleted second key in node_limit_role and node_limit_user install files, and defined rid and uid as integers instead than serial (other error about repeated rid when created a second rule when serial).

Comment	File	Size	Author
#2	437730_pk.patch	1.54 KB	nnewton
	node_limit_install.patch	1.49 KB	jcmarco

Comments

Comment #1

dungiis commented 30 August 2009 at 07:42

I am running drupal 6.13 and get the problem

user warning: Incorrect table definition; there can be only one auto column and it must be defined as a key query: CREATE TABLE node_limit_role ( `lid` INT NOT NULL, `rid` INT unsigned NOT NULL auto_increment, PRIMARY KEY (lid, rid) ) /*!40100 DEFAULT CHARACTER SET UTF8 */ in C:\Program Files\Apache Group\Apache2\htdocs\drupal\includes\database.inc on line 517.

I also apply the patch as recommend but it seems not work as i expected

Comment #2

nnewton commented 11 December 2010 at 00:46

Status	File	Size
new	437730_pk.patch	1.54 KB

Attached is a patch for this issue.

I've only tested the node_limit_role section, but the other is a direct translation. The issue here is that while MyISAM doesn't particularly care, InnoDB requires the auto_increment column be the PK and if its in a multi-column index, it should be the leading column. This module violates that. To fix this I've switched the PK to just rid/uid and added a secondary multi-column index as I saw a few queries in this module that use it.