I understand I am reopening an old issue, but the problem still exists in 7.x-10-rc4.

The same cause as described in previous bug report: "cid" is a "serial" type. For SQLite, a serial or auto_increment type column becomes the primary key automatically, preventing setting primary keys later by the statement "'primary key' => array('cid', 'aka')".

Changing "cid"'s type to "int" allows creating the table, but would produce errors when inserting into the table. As the 'cid' is expected to be auto-increment, no statement is setting this value.

Changing 'cid' into "int auto_increment" does not solve the problem. SQLite does not have "serial" type. Drupal automatically change 'serial' into "auto_increment" for SQLite. So "int auto_increment" equals to "serial", and the problem persists.

One possible fix is to change the primary key ('cid', 'aka) into a unique constraint. This should work on all databases. Actually, you even don't need this constraint. Because "cid" is uniqe, any column combination that has "cid" is automatically unique.

This problem may have another deeper cause. After reading the comments in the source code, my understanding is that "cid" should not be unique. It seems that each contributor should have one "cid", but he/she can have multiple entries in the "biblio_contributor_data" table. These entries have same "cid" but different "aka" (which justifies the use of "cid" with "aka" as the primary key). If this is true, then "cid" should not be created with "serial", which is unique in both mysql and sqlite.

Please correct me if I am mistaken.

Thanks.

Comments

rjerome’s picture

rjerome commented

Looking at this again, it is cid which is unique in the table and aka could be the same in multiple rows. To be honest, I don't remember why the primary key is (cid,aka) but at first glance it would seem that making the primary key just "cid" would solve the problem and not cause any negative side effects.

fall3131’s picture

fall3131 commented

I just tried, if you change the line from:
'primary key' => array('cid', 'aka'),

to:
'primary key' => array('cid'),

would solve the installation problem of sqlite. And the change is not causing problem for adding/showing/deleting new biblio entries. It should also work for mysql as well.

tricasse’s picture

tricasse commented

Apparently, the issue is deeper than that. Using 'serial' is not the culprit; the problem is that using 'serial' or 'auto_increment' sets it as a primary key in Drupal's SQLite driver while there is also a composite primary key set (the error is exactly what is described in the SQLite documentation in paragraph SQL Data Constraints)

This issue is linked to this one from core: 1571842. I'm not an SQL expert, but Damien Tournoud says at comment #2 in this issue that "This table definition doesn't make any sense. Compound primary keys containing a serial columns have no purpose, because the serial part is already unique. I suggest you fix the definition of the table."

Using the workaround proposed there makes biblio install flawlessly.

What do you think?