My installation attempts of Commerce Kickstart 2.x or Commons 3.x distribution with the SQLite database backend have been utterly unsuccessful, failing with the mysterious error message:
PDOException: SQLSTATE[HY000]: General error: 1 there is already a table named message_type:
CREATE INDEX main.message_type ON message (type);
I tried to investigate by using "drush qd --debug" and looking at commons-7.x-3.0/profiles/commons/commons.info, and determined that the installation failed at "message" module. I then tried a minimal Drupal 7.21 installation, and upon enabling the "message" module, the same error message appeared.
It appears that this bug is due to a limitation in SQLite where a table and an index cannot be given the same name in a database. According to http://www.sqlite.org/lang_createtable.html:
It is usually an error to attempt to create a new table in a database that already contains a table, index or view of the same name. However, if the "IF NOT EXISTS" clause is specified as part of the CREATE TABLE statement and a table or view of the same name already exists, the CREATE TABLE command simply has no effect (and no error message is returned). An error is still returned if the table cannot be created because of an existing index, even if the "IF NOT EXISTS" clause is specified.
Other users have run into the same problem. For example, #1894006: Commerce Kickstart installation fails at Message module with SQLite (TABLE and INDEX both named "message_type") (originally titled "drush qd command fails to build and start running commerce kickstart with PDOException error") was filed against Commerce Kickstart 2.x.
Many thanks in advance for your help, as I really look forward to using Commerce Kickstart 2.x and Commons 2.x with SQLite on some of my small servers where MySQL may not be available. :-)
Comment | File | Size | Author |
---|---|---|---|
#3 | message-fix-table-index-name-1941448-3.patch | 688 bytes | jsacksick |
Comments
Comment #1
Anthony Fok CreditAttribution: Anthony Fok commentedComment #2
jsacksick CreditAttribution: jsacksick commentedThe Entity forms module seems to have the same issue and a patch has been posted there : See #1876872: SQLite error: "there is already an index named entityform_type"
Comment #3
jsacksick CreditAttribution: jsacksick commentedThe attached patch should fix the issue.
Comment #4
Anthony Fok CreditAttribution: Anthony Fok commentedThank you very much, @jsacksick and the Commerce Guys!
Your patch works wonderfully, and I am pleasantly surprised to find this patch already incorporated in Commerce Kickstart since version 2.7, and I have just enjoyed a very smooth and delightful installation experience of Commerce Kickstart with SQLite!
Thanks again!
@Amitaibu, please commit this patch when you have time! Thanks in advance!
Cheers,
Anthony Fok
Comment #5
jeromev CreditAttribution: jeromev commentedyay! thanks
Comment #6
amitaibuCommitted, thanks.