View Issue Details

Jump to NotesJump to History
This bug affects 2 person(s).
 12
IDProjectCategoryView StatusDate SubmittedLast Update
19323Bug reportsOtherpublic2023-12-21 23:182024-01-12 17:41
ReporterLemonLime Assigned Tomohabmes  
PrioritynoneSeverityblock 
Status assignedResolutionopen 
Product Version6.4.x 
Summary19323: Error When updating running update_619.php on MSSQL
Description

An error has occurred when updating the database during migration to limesurvey 6.4.0

https://github.com/LimeSurvey/LimeSurvey/blob/master/application/helpers/update/updates/Update_619.php#L14

CDbCommand failed to execute the SQL statement: SQLSTATE[42000]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The name "TRUE" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.. The SQL statement executed was: ALTER TABLE [lime_users] ADD [user_status] BOOLEAN DEFAULT TRUE NULL

MS SQL has no Boolean datatype for column. it uses BIT instead.

Steps To Reproduce

Steps to reproduce

1) using an existing MS SQL instance, update the limesurvey version to 6.4.0 following this instructions
https://manual.limesurvey.org/upgrading_from_a_previous_version#Upgrading_using_CLI

2) access the admin page (e.g. /limesurvey/admin/admin.php)

3) update db page will appear and click on update database.

Expected result

login page will show after successful update.

Actual result

CDbCommand failed to execute the SQL statement: SQLSTATE[42000]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The name "TRUE" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.. The SQL statement executed was: ALTER TABLE [lime_users] ADD [user_status] BOOLEAN DEFAULT TRUE NULL

TagsNo tags attached.
Bug heat12
Complete LimeSurvey version number (& build)limesurvey6.4.0+231218
I will donate to the project if issue is resolvedNo
Browser
Database type & versionMS SQL Server 2019
Server OS (if known)WIndows Server
Webserver software & version (if known)
PHP Version8.2.1

Users monitoring this issue

There are no users monitoring this issue.

Activities

DenisChenu

DenisChenu

2023-12-22 07:52

developer   ~79062

https://github.com/LimeSurvey/LimeSurvey/blob/master/application/helpers/update/updates/Update_619.php
ConnorPrettyman

ConnorPrettyman

2023-12-22 11:00

reporter   ~79063

Hi,
On our production instance of LimeSurvey (Win Server, SQL Server 2019 - 15.0.2104.1), I modified line 14 of Update_619.php to look like this:

    $this->db->createCommand()->addColumn('{{users}}', 'user_status', 'BIT DEFAULT 1 NOT NULL');

Re-loaded the admin page, and the upgrade ran without issue.
Hopefully this helps.
DenisChenu

DenisChenu

2023-12-22 15:42

developer   ~79064

Yep, must use something like

$this->db->createCommand()->addColumn('{{users}}', 'user_status', App()->getDb()->getSchema()->columnTypes[''boolean] . 'DEFAULT 1 NOT NULL');

https://github.com/LimeSurvey/LimeSurvey/blob/8c332cc638306875f965ccd347ef5e5573f27ed0/vendor/yiisoft/yii/framework/db/schema/mssql/CMssqlSchema.php#L41
LemonLime

LemonLime

2024-01-12 17:41

reporter   ~79159

Thanks guys! the notes added was the solution. we can close this now

Issue History

Date Modified Username Field Change
2023-12-21 23:18 LemonLime New Issue
2023-12-22 07:52 DenisChenu Assigned To => mohabmes
2023-12-22 07:52 DenisChenu Status new => feedback
2023-12-22 07:52 DenisChenu Note Added: 79062
2023-12-22 07:52 DenisChenu Bug heat 0 => 2
2023-12-22 11:00 ConnorPrettyman Note Added: 79063
2023-12-22 11:00 ConnorPrettyman Bug heat 2 => 4
2023-12-22 11:00 ConnorPrettyman Bug heat 4 => 10
2023-12-22 15:42 DenisChenu Note Added: 79064
2024-01-12 17:41 LemonLime Note Added: 79159
2024-01-12 17:41 LemonLime Bug heat 10 => 12
2024-01-12 17:41 LemonLime Status feedback => assigned