View Issue Details

This bug affects 1 person(s).
 14
IDProjectCategoryView StatusLast Update
18793Bug reportsDatabase designpublic2023-06-19 18:35
ReporterJanE Assigned Togabrieljenik  
PrioritynormalSeveritycrash 
Status closedResolutionfixed 
Product Version5.6.x 
Summary18793: Quick Translation crash with MS SQL Database Error
Description

One of our custumers gets this error in LS 5.6 with MS SQL:

CDbCommand faalde tijdens het uitvoeren van volgend SQL statement: SQLSTATE[42000]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]A column has been specified more than once in the order by list. Columns in the order by list must be unique.

This happens in the following LS versions:

Microsoft SQL
Production 5.4.1+220919
Staging 5.6.5+230214

The error is not triggered with MySQL
MySQL
2nd staging: 5.4.9+ 221101

I am trying to reproduce the error locally, but that is not easy because we do not have MS SQL running.

TagsNo tags attached.
Bug heat14
Complete LimeSurvey version number (& build)5.6.5+230214
I will donate to the project if issue is resolvedNo
Browser
Database type & versionMS SQL Server 2016
Server OS (if known)
Webserver software & version (if known)
PHP VersionPHP 8.0 (AFAIK)

Users monitoring this issue

JanE

Activities

JanE

JanE

2023-04-28 01:15

reporter   ~74723

@DenisChenu
I could reproduce https://bugs.limesurvey.org/view.php?id=18280 in LS 5.3.26+220720 and found a query with 'ORDER BY sortorder, answer, sortorder, code' in the MySQL full query log. But that happened only the first time when I opened the Conditions Designer after adding a new language. Is that expected behaviour for that bug with MySQL?

DenisChenu

DenisChenu

2023-04-28 01:19

developer   ~74724

found a query with 'ORDER BY sortorder, answer, sortorder, code' in the MySQL full query log

It's because it's added in defaultscope, MySQL and PGSDQL accept same order 2 times, not MSSQL

https://github.com/LimeSurvey/LimeSurvey/blob/1337418e4a02e3a8be83f0740912e89a39dec0c5/application/models/Answer.php#L101

It's the attended behaviour.

JanE

JanE

2023-04-28 11:32

reporter   ~74731

Any idea what causes the crash in Quick Translation?

DenisChenu

DenisChenu

2023-04-28 15:27

developer   ~74732

An order added on a request in Quick translation

JanE

JanE

2023-04-28 16:38

reporter   ~74733

Added by whom or what? If this is added by the LS code like in 18280 where should we start looking?

DenisChenu

DenisChenu

2023-04-28 17:07

developer   ~74734

I don't know : do a grep in controllers

ollehar

ollehar

2023-05-02 10:08

administrator   ~74735

Can you enable debug = 2 in application/config/config.php and try again, please? Paste the complete error message here.

JanE

JanE

2023-05-02 11:23

reporter   ~74746

Hi Olle, thanks for replying. I do not get the error myself, but one of our customers does. I will ask them to change the debug level on their staging server. May take some time, because it happens to be a holiday over here.

DenisChenu

DenisChenu

2023-05-03 12:27

developer   ~74773

I have to update a local 5.X (but not 6.X)

I check when it's done

JanE

JanE

2023-05-04 12:44

reporter   ~74807

Hi Olle. Attached is the complete error report, pasted into a Word doc. This was on their staging server with LS 5.6.5+230214.

JanE

JanE

2023-05-16 11:51

reporter   ~75005

Hi Olle, did you have a chance to look at this bug report?

JanE

JanE

2023-06-13 08:46

reporter   ~75569

@ollehar Friendly ping.

ollehar

ollehar

2023-06-13 09:50

administrator   ~75574

@gabrieljenik or @DenisChenu could pick this up, perhaps?

gabrieljenik

gabrieljenik

2023-06-13 14:52

manager   ~75604

I take it

JanE

JanE

2023-06-13 14:57

reporter   ~75606

Top. Tell me if you want more info. Then I will ask our customer.

gabrieljenik

gabrieljenik

2023-06-13 16:44

manager   ~75616

PR for v5: https://github.com/LimeSurvey/LimeSurvey/pull/3218
If good will port to master

gabrieljenik

gabrieljenik

2023-06-13 22:11

manager   ~75618

@JanE Can you help testing? Maybe applyng the patch?

gabrieljenik

gabrieljenik

2023-06-14 18:29

manager   ~75636

PR for master: https://github.com/LimeSurvey/LimeSurvey/pull/3224

JanE

JanE

2023-06-14 18:46

reporter   ~75637

Thanks. Looking at the patch it should be easy tot backport:
https://github.com/LimeSurvey/LimeSurvey/commit/f92b8169568c0f17d7c820a292d6bffa52e74743.patch

guest

guest

2023-06-16 00:24

viewer   ~75652

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

guest

guest

2023-06-16 00:24

viewer   ~75653

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

guest

guest

2023-06-16 00:33

viewer   ~75654

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

guest

guest

2023-06-16 00:35

viewer   ~75655

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

LimeBot

LimeBot

2023-06-19 11:52

administrator   ~75707

Fixed in Release 6.1.4+230620

JanE

JanE

2023-06-19 18:35

reporter   ~75724

And fixed in Release 5.6.27+230621, I see. Thanks @gabrieljenik

Related Changesets

LimeSurvey: 5.x 6dd9385b

2023-06-16 02:24

Gabriel Jenik

Committer: GitHub


Details Diff
Fixed issue 18793: Quick Translation crash with MS SQL Database Error (#3218) Affected Issues
18793
mod - application/models/services/QuickTranslation.php Diff File

LimeSurvey: 5.x 6dd9385b

2023-06-16 02:24

Gabriel Jenik

Committer: GitHub


Details Diff
Fixed issue 18793: Quick Translation crash with MS SQL Database Error (#3218) Affected Issues
18793
mod - application/models/services/QuickTranslation.php Diff File

LimeSurvey: master e0432a41

2023-06-16 02:24

Gabriel Jenik

Committer: GitHub


Details Diff
Fixed issue 18793: Quick Translation crash with MS SQL Database Error (#3224) Affected Issues
18793
mod - application/models/services/QuickTranslation.php Diff File

LimeSurvey: master e0432a41

2023-06-16 02:24

Gabriel Jenik

Committer: GitHub


Details Diff
Fixed issue 18793: Quick Translation crash with MS SQL Database Error (#3224) Affected Issues
18793
mod - application/models/services/QuickTranslation.php Diff File

Issue History

Date Modified Username Field Change
2023-04-28 01:12 JanE New Issue
2023-04-28 01:15 JanE Note Added: 74723
2023-04-28 01:15 JanE Bug heat 0 => 2
2023-04-28 01:19 DenisChenu Note Added: 74724
2023-04-28 01:19 DenisChenu Bug heat 2 => 4
2023-04-28 01:19 JanE Issue Monitored: JanE
2023-04-28 01:19 JanE Bug heat 4 => 6
2023-04-28 11:32 JanE Note Added: 74731
2023-04-28 15:27 DenisChenu Note Added: 74732
2023-04-28 16:38 JanE Note Added: 74733
2023-04-28 17:07 DenisChenu Note Added: 74734
2023-05-02 10:08 ollehar Note Added: 74735
2023-05-02 10:08 ollehar Bug heat 6 => 8
2023-05-02 11:23 JanE Note Added: 74746
2023-05-03 10:44 ollehar Assigned To => ollehar
2023-05-03 10:44 ollehar Status new => feedback
2023-05-03 10:44 ollehar Priority none => normal
2023-05-03 12:27 DenisChenu Note Added: 74773
2023-05-04 12:44 JanE Note Added: 74807
2023-05-04 12:44 JanE File Added: error-staging-LS5.6.5.docx
2023-05-04 12:44 JanE Status feedback => assigned
2023-05-16 11:51 JanE Note Added: 75005
2023-06-13 08:46 JanE Note Added: 75569
2023-06-13 09:50 ollehar Note Added: 75574
2023-06-13 14:52 gabrieljenik Assigned To ollehar => gabrieljenik
2023-06-13 14:52 gabrieljenik Note Added: 75604
2023-06-13 14:52 gabrieljenik Bug heat 8 => 10
2023-06-13 14:57 JanE Note Added: 75606
2023-06-13 16:44 gabrieljenik Assigned To gabrieljenik => DenisChenu
2023-06-13 16:44 gabrieljenik Status assigned => ready for code review
2023-06-13 16:44 gabrieljenik Note Added: 75616
2023-06-13 22:11 gabrieljenik Note Added: 75618
2023-06-13 22:11 gabrieljenik Assigned To DenisChenu => gabrieljenik
2023-06-13 22:11 gabrieljenik Status ready for code review => ready for testing
2023-06-14 18:29 gabrieljenik Note Added: 75636
2023-06-14 18:46 JanE Note Added: 75637
2023-06-16 00:24 Changeset attached => LimeSurvey 5.x 6dd9385b
2023-06-16 00:24 Changeset attached => LimeSurvey 5.x 6dd9385b
2023-06-16 00:24 guest Note Added: 75652
2023-06-16 00:24 guest Note Added: 75653
2023-06-16 00:24 guest Bug heat 10 => 12
2023-06-16 00:24 guest Bug heat 10 => 12
2023-06-16 00:26 c_schmitz Status ready for testing => resolved
2023-06-16 00:26 c_schmitz Resolution open => fixed
2023-06-16 00:33 Changeset attached => LimeSurvey master e0432a41
2023-06-16 00:33 guest Note Added: 75654
2023-06-16 00:35 Changeset attached => LimeSurvey master e0432a41
2023-06-16 00:35 guest Note Added: 75655
2023-06-19 11:52 LimeBot Note Added: 75707
2023-06-19 11:52 LimeBot Status resolved => closed
2023-06-19 11:52 LimeBot Bug heat 12 => 14
2023-06-19 18:35 JanE Note Added: 75724