View Issue Details

IDProjectCategoryView StatusLast Update
15829Bug reports_ Unknownpublic2020-03-05 08:00
ReporterCarmIT Assigned Toc_schmitz  
PriorityurgentSeveritycrash 
Status closedResolutionfixed 
Product Version4.1.0 
Fixed in Version4.1.9 
Summary15829: Upgrade from 3.21 to 4.1 - upgrade Database error (MSSQL)
Description

Hello Support Team,

after an update from v3.21 to 4.1, no connection to the SQL server could be established. The following error message is displayed:

An non-recoverable error happened during the update. Error details:
CDbCommand failed to execute the SQL statement: SQLSTATE[42000]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Explicit value must be specified for identity column in table 'question_themes' either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column.
File C:\inetpub\wwwroot\limesurvey\application\helpers\update\updatedb_helper.php, line 2960.

Steps To Reproduce

Click on "Start database upgrade" after which the error message was displayed

TagsNo tags attached.
Complete LimeSurvey version number (& build)4.1.0
I will donate to the project if issue is resolvedNo
Browserall
Database & DB-VersionMSSQL
Server OS (if known)MSSQL 2008 Server
Webserver software & version (if known)IISv10
PHP Version7.3

Relationships

has duplicate 15841 closedc_schmitz Incorrect dataset / identity insert property in SQL upgrade script on MSSQL DB engine for db object question_themes 

Activities

ollehar

ollehar

2020-02-06 14:00

administrator   ~55790

We need someone to test this on MSSQL.

msvojsik

msvojsik

2020-02-11 01:22

reporter   ~55848

I just found this bugreport, pls check my own duplicate bugreport for proposed solution:
https://bugs.limesurvey.org/view.php?id=15841

msvojsik

msvojsik

2020-02-11 01:26

reporter   ~55849

For reference, the suggested solution copypasted

either

  1. modify the updatedb_helper.php script to check the data in collection $baseQuestionThemeEntries & ensure this collection contains the primary key values as well (values for field 'id')

or

  1. DO NOT use the MSSQL identity insert directive (line 2958 of the updatedb_helper) and let the DB engine add the primary key values on it's own.

Explanation:
You can NOT use the identity insert & ommit the values for primary key field/s at the same time.

ollehar

ollehar

2020-02-11 10:49

administrator   ~55852

We need someone to test this on MSSQL.

There's a docker image with MSSQL on that can be used for testing on Linux machines: https://hub.docker.com/_/microsoft-mssql-server

c_schmitz

c_schmitz

2020-03-04 23:19

administrator   ~56302

Fix committed to master branch: http://bugs.limesurvey.org/plugin.php?page=Source/view&id=29631

Related Changesets

LimeSurvey: master 4bd9487f

2020-03-04 23:19:00

c_schmitz

Details Diff
Fixed issue 15829: Unable to upgrade from 3.x to 4x when using MSSQL Affected Issues
15829
mod - application/helpers/update/updatedb_helper.php Diff File

Issue History

Date Modified Username Field Change
2020-02-06 12:07 CarmIT New Issue
2020-02-06 13:59 ollehar Summary Upgrade from 3.21 to 4.1 - upgrade Database error => Upgrade from 3.21 to 4.1 - upgrade Database error (MSSQL)
2020-02-06 14:00 ollehar Note Added: 55790
2020-02-06 14:15 ollehar Priority none => urgent
2020-02-11 01:22 msvojsik Note Added: 55848
2020-02-11 01:26 msvojsik Note Added: 55849
2020-02-11 10:49 ollehar Relationship added related to 15841
2020-02-11 10:49 ollehar Note Added: 55852
2020-03-04 23:18 c_schmitz Relationship replaced has duplicate 15841
2020-03-04 23:18 c_schmitz Assigned To => c_schmitz
2020-03-04 23:18 c_schmitz Status new => assigned
2020-03-04 23:19 c_schmitz Changeset attached => LimeSurvey master 4bd9487f
2020-03-04 23:19 c_schmitz Note Added: 56302
2020-03-04 23:19 c_schmitz Resolution open => fixed
2020-03-04 23:19 c_schmitz Status assigned => resolved
2020-03-04 23:19 c_schmitz Fixed in Version => 4.1.9
2020-03-05 08:00 CarmIT Status resolved => closed