View Issue Details

IDProjectCategoryView StatusLast Update
13106Bug reportsInstallationpublic2018-01-10 17:16
Reporterfvanderstarre Assigned Tomarkusfluer 
Status closedResolutionfixed 
Product Version3.0.0-rc.x 
Summary13106: SQL error when installing 3.0.1

When installing LS 3.0.1 a sql error is thrown "SQLSTATE[HY000]: General error: 20018 Could not create constraint. See previous errors. [20018] (severity 16".
The offending statement in create-database.php is:
$oDB->createCommand()->createTable('{{groups}}', array(
'gid' => "autoincrement",
'sid' => "integer NOT NULL default '0'",
'group_name' => "string(100) NOT NULL default ''",
'group_order' => "integer NOT NULL default '0'",
'description' => "text",
'language' => "string(20) default 'en'",
'randomization_group' => "string(20) NOT NULL default ''",
'grelevance' => "text NULL",
'composite_pk' => array('gid', 'language')
which in my setup (mssql,dblib) translates to
CREATE TABLE [groups] (
[gid] integer NOT NULL IDENTITY (1,1),
[sid] int NOT NULL default '0',
[group_name] nvarchar(100) NOT NULL default '',
[group_order] int NOT NULL default '0',
[description] nvarchar(max) NULL,
[language] nvarchar(20) default 'en' NULL,
[randomization_group] nvarchar(20) NOT NULL default '',
[grelevance] nvarchar(max) NULL,
PRIMARY KEY ([gid], [language]).
You see the problem here, language may not be NULLable being part of the primary key.

Steps To Reproduce

See above, install 3.0.1 on mssql.

TagsNo tags attached.
Complete LimeSurvey version number (& build)3.0.1 build 171228
I will donate to the project if issue is resolvedNo
Database & DB-VersionMS-SQL 2008 R2
Server OS (if known)Linux RHEL 7
Webserver software & version (if known)Apache 2.4.6
PHP Version5.6.32


has duplicate 13122 closedmarkusfluer Can't populate database (MSSQL) 




2017-12-29 15:27

reporter   ~45406

Same error with "create table questions" (language not defined as NOT NULL)



2017-12-29 15:32

reporter   ~45407

SQLSTATE[HY000]: General error: 20018 Cannot insert explicit value for identity column in table 'tutorials' when IDENTITY_INSERT is set to OFF. [20018] (severity 16) [(null)] #0 /data/websites/limesurvey-test/html/temp/framework/db/CDbCommand.php(1278): CDbCommand->execute(Array)
#1 /data/websites/limesurvey-test/html/temp/installer/create-database.php(917): CDbCommand->insert('{{tutorials}}', Array)



2018-01-02 09:21

reporter   ~45414

Same problem with table "tutorial_entries".
Primary key definition should be deferred to after initial values have been inserted.
To finish, table "tutorial_entry_relation" should be specified as follows:
'teid' => 'int NOT NULL',
'tid' => 'int NOT NULL',
'uid' => 'int NULL',
'sid' => 'int NULL'
The "DEFAULT NULL" clause has to be avoided in mssql for INT columns.

I am well aware you guys would prefer me to set up a GitHub repository and do a pull request, I tried that for one of the issues I reported earlier but it didn't work out. So please let me know if the information I gave here is sufficient for you!



2018-01-05 10:38

reporter   ~45424

Same issue as 13122



2018-01-08 11:41

administrator   ~45460

Fix committed to master branch:

Related Changesets

LimeSurvey: master b6c06ef7

2018-01-08 11:40:56


Details Diff
Fixed issue 13106: SQL error when installing 3.0.1 Affected Issues
mod - installer/create-database.php Diff File

Issue History

Date Modified Username Field Change
2017-12-29 15:19 fvanderstarre New Issue
2017-12-29 15:27 fvanderstarre Note Added: 45406
2017-12-29 15:32 fvanderstarre Note Added: 45407
2018-01-02 09:21 fvanderstarre Note Added: 45414
2018-01-05 10:38 fvanderstarre Note Added: 45424
2018-01-08 11:26 LouisGac Relationship added related to 13122
2018-01-08 11:40 markusfluer Relationship replaced has duplicate 13122
2018-01-08 11:41 markusfluer Assigned To => markusfluer
2018-01-08 11:41 markusfluer Status new => resolved
2018-01-08 11:41 markusfluer Resolution open => fixed
2018-01-08 11:41 markusfluer Fixed in Version => 3.0.x
2018-01-08 11:41 markusfluer Changeset attached => LimeSurvey master b6c06ef7
2018-01-08 11:41 markusfluer Note Added: 45460
2018-01-10 17:16 ollehar Status resolved => closed
2018-01-10 17:16 ollehar Fixed in Version 3.0.x => 3.0.2