On sqlite install breaks with watchdog message:

PDOException: SQLSTATE[HY000]: General error: 1 table "og_vocab_relation" has more than one primary key: CREATE TABLE {og_vocab_relation} ( id INTEGER PRIMARY KEY AUTOINCREMENT CHECK (id>= 0), group_type VARCHAR(255) NOT NULL DEFAULT '', gid INTEGER NOT NULL, vid INTEGER NOT NULL DEFAULT 0, PRIMARY KEY (vid) ); ; Array ( ) in db_create_table() (line 2688 of /var/www/virtual/clsys/html/clsys/7/includes/database/database.inc).

I suppose it's the known thing that sqlite is strict on the serial where mysql is not.

CommentFileSizeAuthor
#4 og_vocab-primary_keys-1890634-4.patch1.01 KBczigor
#1 0001-Fix-1890634-Install-breaks-on-sqlite.patch674 bytesgeek-merlin
Support from Acquia helps fund testing for Drupal Acquia logo

Comments

geek-merlin’s picture

geek-merlin
they/omg
Primary language German
Location Freiburg, Germany
CreditAttribution: geek-merlin commented
Status: Active » Needs review
FileSize
0001-Fix-1890634-Install-breaks-on-sqlite.patch674 bytes

serial field is primary key, so other keys only unique.
here's the fix.

amitaibu’s picture

amitaibu
Primary language Hebrew
Location Israel
CreditAttribution: amitaibu commented

Wouldn't SQL complain if there's no primary key?

czigor’s picture

czigor CreditAttribution: czigor commented
Issue summary: View changes

Neither MySQL nor SQLite complains about the lack of primary key. I don't know what the patch exactly does (i.e. what the id and vid fields are) but now the install works. I will try to investigate this further.

czigor’s picture

czigor CreditAttribution: czigor commented
FileSize
og_vocab-primary_keys-1890634-4.patch1.01 KB

The last sentence of http://sqlite.org/autoinc.html says: "Any attempt to use AUTOINCREMENT on a WITHOUT ROWID table or on a column other than the INTEGER PRIMARY KEY column results in an error."

This means that a serial type field in a hook_schema() should become a primary key in sqlite and that's how it is done by DatabaseSchema_sqlite, too. If, however, in addition to a serial field another primary key is given in hook_schema(), there will be an attempt to create two primary keys which is impossible.

It seems that in MySQL an AUTO_INCREMENT field does not need to be PRIMARY KEY.

So with the patch in #1 the id field will be a primary key in sqlite while with mysql there will be no primary keys.

To make things more uniform I suggest to make array('id') the primary key. As id is a serial field I see no reason to add the vid to the primary key anyway. Also, og_vocab_update_7000() suggests that in the drupal 7 version only the id field is meant to be the primary key.

discipolo’s picture

discipolo CreditAttribution: discipolo commented

I am grateful for this fix since without it I cannot install with sqlite database!