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

Comments

geek-merlin’s picture

geek-merlin
Primary language German
Location Freiburg, Germany
commented
Status: Active » Needs review
StatusFileSize
new 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
commented

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

czigor’s picture

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 commented
StatusFileSize
new 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 commented

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