View Issue Details

This bug affects 2 person(s).
 20
IDProjectCategoryView StatusLast Update
18102Bug reportsImport/Exportpublic2022-12-09 10:10
Reporterahhsung Assigned ToDenisChenu  
PrioritynormalSeveritypartial_block 
Status closedResolutionfixed 
Product Version5.3.x 
Fixed in Version5.4.x 
Summary18102: Export Duplicate entries when export result
Description

I am currently using LimeSurvey 5.1.10 and it is found that the there are duplicated entries in the Survey Result if there are more than 200 rows.

Steps To Reproduce

Steps to reproduce

Survey with more than 200 responses, for example 300
Export completed responses in Excel Format or CSV format

Expected result

Have 300 lines of data + one header

Actual result

Have more than 300 lines + on header
A few rows in the survey is duplicated

TagsNo tags attached.
Attached Files
bug.png (40,409 bytes)   
bug.png (40,409 bytes)   
Bug heat20
Complete LimeSurvey version number (& build)5.1.10
I will donate to the project if issue is resolvedNo
Browserhttps://github.com/LimeSurvey/LimeSurvey/pull/2673
Database type & versionMSSQL Server 2016
Server OS (if known)Windows Server 2016
Webserver software & version (if known)
PHP Version8.0.10

Relationships

related to 18420 closedDenisChenu Different behaviour on grid between MSSQL and MariaDB 
related to 18547 closedDenisChenu Response browsing on MSSQL server could go to 500 error 

Users monitoring this issue

Mazi

Activities

gabrieljenik

gabrieljenik

2022-05-13 15:11

manager   ~69624

Has this happened more than once?
Can you please provide more precise instructions on how to reproduce? Preferably in a step-by-step manner, like 1) Go to question x; 2) Click button y; 3) Expected z but saw v. Thank you very much.

ahhsung

ahhsung

2022-05-16 02:52

reporter   ~69643

Yes. This happened when the survey is more than 200 responses.
Also, it is found that there are no duplication in the submission table.

Step-by-Step manner:

  1. Select an survey with more than 200 responses
  2. Select Responses and Statistic under Responses
  3. Select Export responses under Export
  4. Select "Export Format" as Microsoft Excel and Completed Responses only
  5. Export

After that, the downloaded excel contains duplicated entries in the file.

Thank you.

gabrieljenik

gabrieljenik

2022-05-16 17:22

manager   ~69671

Does this happen on many surveys or just one?

DenisChenu

DenisChenu

2022-05-16 18:10

developer   ~69676

But here : it's not duplicate for sure. Order of id can be different when export thand ASC

Can you export the LSA (private upload is possible)

ahhsung

ahhsung

2022-05-17 03:07

reporter   ~69693

The problem happened in all the Surveys

DenisChenu

DenisChenu

2022-05-17 08:57

developer   ~69695

Can you export the 453141 data here ? Want to check something.
(i can not reproduce with the lsa)

DenisChenu

DenisChenu

2022-05-25 08:18

developer   ~70022

WTF …
No idea here …

hinghang

hinghang

2022-05-27 12:27

reporter   ~70066

Hi Denis,

I would like to supplement additional information to see if you could simulate our case.

  1. We are using Microsoft SQL Server 2016 as the database
  2. I have tried to install the latest version, i.e. v5.3.17+220525 and import the lsa, the problem still occurs

I am wondering if it is related to the database we are using.

Thank you.

DenisChenu

DenisChenu

2022-05-27 14:15

developer   ~70067

Oh,
let let check on MS SQL

gabrieljenik

gabrieljenik

2022-06-03 22:53

manager   ~70220

I am sorry. I wasn't able to reproduce the issue.
Maybe you have 2 copies of the plugins working together?

Can you please try on a fresh installation?
I am sorry to suggest this, but not sure there is much to suggest without debugging on your ENV (which is kind of out of the scope) :) Hope you understand.

Thanks

hinghang

hinghang

2022-06-06 02:56

reporter   ~70223

I have tried to import the LSA in a fresh installation of the limesurvey and the issue is still exists.
I am not sure if the issue is related to the environment or not.

We are using Windows Server 2016 with SQL Server 2016 as the database.

gabrieljenik

gabrieljenik

2022-06-06 14:09

manager   ~70228

let let check on MS SQL

@DenisChenu Were you able to reproduce with MsSQL?

DenisChenu

DenisChenu

2022-06-06 15:57

developer   ~70241

Need to found time …

hinghang

hinghang

2022-06-07 04:12

reporter   ~70257

hi Gabriel & Denis,

I have just tried to install the a clean LimeSurvey Version 5.3.17+220525 , SQL Server 2016, PHP 8.1 and php_pdo_sqlsrv_81 in another Windows Server and import the LSA again.
The export issue also exist.

I think it is very likely a SQL server issue.

DenisChenu

DenisChenu

2022-06-07 15:04

developer   ~70281

Issue confirmed with 5.3.9 / PHP 7.4.27 / IIS/10.0 / SQLServer 15

happen after 763 id, goes to 673 id (already export before

I update to last and check again.

DenisChenu

DenisChenu

2022-06-09 19:49

developer   ~70326

last git : confirmed

DenisChenu

DenisChenu

2022-06-09 20:02

developer   ~70327

timing related ?

    'from' => '[{{survey_453141}}]'
    'join' => array
    (
        0 => 'LEFT JOIN [{{survey_453141_timings}}] [survey_timings] ON {{survey_453141}}.id = survey_timings.id'
    )
    'where' => '{{survey_453141}}.id >= :min AND {{survey_453141}}.id <= :max'
    'order' => '[{{survey_453141}}].[id] ASC'
    'select' => '[{{survey_453141}}].[id], [survey_timings].[interviewtime], [survey_timings].[453141X27time], [survey_timings].[453141X27X830time], [survey_timings].[453141X27X834time], [survey_timings].[453141X27X835time], [survey_timings].[453141X27X831time], [survey_timings].[453141X27X832time], [survey_timings].[453141X27X833time], [survey_timings].[453141X27X836time]'
)
hinghang

hinghang

2022-06-20 05:27

reporter   ~70410

Hi Denis,

I am not sure about the source code above...

DenisChenu

DenisChenu

2022-06-20 08:21

developer   ~70411

@hinghang : it the instruction done when try to export data with MS SQL.
It's a really hard issue to fix … 1. i don't have a direct access to a SQL database ;) 2. There are no clean error error here 3. No issue in code 4. SQL issue like this is always more complex to trace …

hinghang

hinghang

2022-07-05 04:06

reporter   ~70695

@DenisChenu

Sorry to interrupt again. Any idea for fixing the issue? :)

Maxlo158

Maxlo158

2022-07-19 17:26

reporter   ~71016

Same problem here... but with surveys with more than 100 responses :(

DenisChenu

DenisChenu

2022-07-20 19:16

developer   ~71062

Sorry to interrupt again. Any idea for fixing the issue? :)

No i have to find where and why it happens only to MSSQL

@Maxlo158 : this picture show only disorder not duplicate

Maxlo158

Maxlo158

2022-07-21 08:47

reporter   ~71067

...for more clarity

DenisChenu

DenisChenu

2022-07-21 09:04

developer   ~71068

MS SQL too ?
Timing activated ?

(i try to reproduce with a more simple survey to find where it broken …)

Maxlo158

Maxlo158

2022-07-22 10:37

reporter   ~71101

Yes, we're using MS SQL on SUSE linux.
Where to check timings/activate them?

(PS: sorry, I'm new to LimeSurvey administration)

DenisChenu

DenisChenu

2022-10-13 19:21

developer   ~72247

Origin of this issue https://github.com/LimeSurvey/LimeSurvey/commit/b72beace8740d02e13c478cf2f390cc731948370

Maybe fixed in Yii 2 : https://github.com/yiisoft/yii2/blob/364e907875fd57ee218085cca796ac5d1c3c8d51/framework/db/mssql/QueryBuilder.php#L73

DenisChenu

DenisChenu

2022-10-14 09:23

developer   ~72250

See the issue when not check reponse id :

CDbException.html (22,795 bytes)   
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en"><head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>CDbException</title>

<style type="text/css">
/*<![CDATA[*/
html,body,div,span,applet,object,iframe,h1,h2,h3,h4,h5,h6,p,blockquote,pre,a,abbr,acronym,address,big,cite,code,del,dfn,em,font,img,ins,kbd,q,s,samp,small,strike,strong,sub,sup,tt,var,b,u,i,center,dl,dt,dd,ol,ul,li,fieldset,form,label,legend,table,caption,tbody,tfoot,thead,tr,th,td{border:0;outline:0;font-size:100%;vertical-align:baseline;background:transparent;margin:0;padding:0;}
body{line-height:1;}
ol,ul{list-style:none;}
blockquote,q{quotes:none;}
blockquote:before,blockquote:after,q:before,q:after{content:none;}
:focus{outline:0;}
ins{text-decoration:none;}
del{text-decoration:line-through;}
table{border-collapse:collapse;border-spacing:0;}

body {
	font: normal 9pt "Verdana";
	color: #000;
	background: #fff;
}

h1 {
	font: normal 18pt "Verdana";
	color: #f00;
	margin-bottom: .5em;
}

h2 {
	font: normal 14pt "Verdana";
	color: #800000;
	margin-bottom: .5em;
}

h3 {
	font: bold 11pt "Verdana";
}

pre {
	font: normal 11pt Menlo, Consolas, "Lucida Console", Monospace;
}

pre span.error {
	display: block;
	background: #fce3e3;
}

pre span.ln {
	color: #999;
	padding-right: 0.5em;
	border-right: 1px solid #ccc;
}

pre span.error-ln {
	font-weight: bold;
}

.container {
	margin: 1em 4em;
}

.version {
	color: gray;
	font-size: 8pt;
	border-top: 1px solid #aaa;
	padding-top: 1em;
	margin-bottom: 1em;
}

.message {
	color: #000;
	padding: 1em;
	font-size: 11pt;
	background: #f3f3f3;
	-webkit-border-radius: 10px;
	-moz-border-radius: 10px;
	border-radius: 10px;
	margin-bottom: 1em;
	line-height: 160%;
}

.source {
	margin-bottom: 1em;
}

.code pre {
	background-color: #ffe;
	margin: 0.5em 0;
	padding: 0.5em;
	line-height: 125%;
	border: 1px solid #eee;
}

.source .file {
	margin-bottom: 1em;
	font-weight: bold;
}

.traces {
	margin: 2em 0;
}

.trace {
	margin: 0.5em 0;
	padding: 0.5em;
}

.trace.app {
	border: 1px dashed #c00;
}

.trace .number {
	text-align: right;
	width: 2em;
	padding: 0.5em;
}

.trace .content {
	padding: 0.5em;
}

.trace .plus,
.trace .minus {
	display:inline;
	vertical-align:middle;
	text-align:center;
	border:1px solid #000;
	color:#000;
	font-size:10px;
	line-height:10px;
	margin:0;
	padding:0 1px;
	width:10px;
	height:10px;
}

.trace.collapsed .minus,
.trace.expanded .plus,
.trace.collapsed pre {
	display: none;
}

.trace-file {
	cursor: pointer;
	padding: 0.2em;
}

.trace-file:hover {
	background: #f0ffff;
}
/*]]>*/
</style>
</head>

<body>
<div class="container">
	<h1>CDbException</h1>

	<p class="message">
		CDbCommand failed to execute the SQL statement: SQLSTATE[42S22]: 
[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid column 
name 'id'.. The SQL statement executed was: SELECT * FROM (SELECT TOP 
100 * FROM (SELECT TOP 200 [lime_survey_444682].[submitdate], 
[tokentable].[tid], [tokentable].[participant_id], 
[tokentable].[firstname], [tokentable].[lastname], [tokentable].[email],
 [tokentable].[emailstatus], [tokentable].[language], 
[tokentable].[blacklisted], [tokentable].[sent], 
[tokentable].[remindersent], [tokentable].[remindercount], 
[tokentable].[completed], [tokentable].[usesleft], 
[tokentable].[validfrom], [tokentable].[validuntil], 
[tokentable].[mpid], [tokentable].[attribute_1], 
[tokentable].[attribute_2], [tokentable].[attribute_3]<br>
FROM [lime_survey_444682]<br>
LEFT JOIN [lime_tokens_444682] [tokentable] ON lime_survey_444682.token=tokentable.token<br>
WHERE lime_survey_444682.id &gt;= :min AND lime_survey_444682.id &lt;= :max<br>
ORDER BY [lime_survey_444682].[id] ASC) as [__inner__] ORDER BY 
[__inner__].[id] DESC) as [__outer__] ORDER BY [__outer__].[id] ASC	</p>

	<div class="source">
		<p class="file">E:\IIS\website\third_party\yiisoft\yii\framework\db\CDbCommand.php(543)</p>
		<div class="code"><pre><span class="ln">531</span>         {
<span class="ln">532</span>             if($this-&gt;_connection-&gt;enableProfiling)
<span class="ln">533</span>                 Yii::endProfile('system.db.CDbCommand.query('.$this-&gt;getText().$par.')','system.db.CDbCommand.query');
<span class="ln">534</span> 
<span class="ln">535</span>             $errorInfo=$e instanceof PDOException ? $e-&gt;errorInfo : null;
<span class="ln">536</span>             $message=$e-&gt;getMessage();
<span class="ln">537</span>             Yii::log(Yii::t('yii','CDbCommand::{method}() failed: {error}. The SQL statement executed was: {sql}.',
<span class="ln">538</span>                 array('{method}'=&gt;$method, '{error}'=&gt;$message, '{sql}'=&gt;$this-&gt;getText().$par)),CLogger::LEVEL_ERROR,'system.db.CDbCommand');
<span class="ln">539</span> 
<span class="ln">540</span>             if(YII_DEBUG)
<span class="ln">541</span>                 $message.='. The SQL statement executed was: '.$this-&gt;getText().$par;
<span class="ln">542</span> 
<span class="error"><span class="ln error-ln">543</span>             throw new CDbException(Yii::t('yii','CDbCommand failed to execute the SQL statement: {error}',
</span><span class="ln">544</span>                 array('{error}'=&gt;$message)),(int)$e-&gt;getCode(),$errorInfo);
<span class="ln">545</span>         }
<span class="ln">546</span>     }
<span class="ln">547</span> 
<span class="ln">548</span>     /**
<span class="ln">549</span>      * Builds a SQL SELECT statement from the given query specification.
<span class="ln">550</span>      * @param array $query the query specification in name-value pairs. The following
<span class="ln">551</span>      * query options are supported: {@link select}, {@link distinct}, {@link from},
<span class="ln">552</span>      * {@link where}, {@link join}, {@link group}, {@link having}, {@link order},
<span class="ln">553</span>      * {@link limit}, {@link offset} and {@link union}.
<span class="ln">554</span>      * @throws CDbException if "from" key is not present in given query parameter
<span class="ln">555</span>      * @return string the SQL statement
</pre></div>	</div>

	<div class="traces">
		<h2>Stack Trace</h2>
				<table style="width:100%;">
						<tbody><tr class="trace core collapsed">
			<td class="number">
				#0			</td>
			<td class="content">
				<div class="trace-file">
											<div class="plus">+</div>
						<div class="minus">–</div>
										&nbsp;E:\IIS\website\third_party\yiisoft\yii\framework\db\CDbCommand.php(377): <strong>CDbCommand</strong>-&gt;<strong>queryInternal</strong>("", 0, array("min" =&gt; "4814", "max" =&gt; "5082"))				</div>

				<div class="code"><pre><span class="ln">372</span>      * @return CDbDataReader the reader object for fetching the query result
<span class="ln">373</span>      * @throws CException execution failed
<span class="ln">374</span>      */
<span class="ln">375</span>     public function query($params=array())
<span class="ln">376</span>     {
<span class="error"><span class="ln error-ln">377</span>         return $this-&gt;queryInternal('',0,$params);
</span><span class="ln">378</span>     }
<span class="ln">379</span> 
<span class="ln">380</span>     /**
<span class="ln">381</span>      * Executes the SQL statement and returns all rows.
<span class="ln">382</span>      * @param boolean $fetchAssociative whether each row should be returned as an associated array with
</pre></div>			</td>
		</tr>
						<tr class="trace app expanded">
			<td class="number">
				#1			</td>
			<td class="content">
				<div class="trace-file">
											<div class="plus">+</div>
						<div class="minus">–</div>
										&nbsp;E:\IIS\website\application\helpers\admin\exportresults_helper.php(118): <strong>CDbCommand</strong>-&gt;<strong>query</strong>()				</div>

				<div class="code"><pre><span class="ln">113</span>         for ($i = 0; $i &lt; $maxPages; $i++) {
<span class="ln">114</span>             $offset = $i * $maxRows;
<span class="ln">115</span>             $responsesQuery = $surveyDao-&gt;loadSurveyResults($survey, $oOptions-&gt;responseMinRecord, $oOptions-&gt;responseMaxRecord, $sFilter, $oOptions-&gt;responseCompletionState, $oOptions-&gt;selectedColumns, $oOptions-&gt;aResponses);
<span class="ln">116</span>             $responsesQuery-&gt;offset($offset);
<span class="ln">117</span>             $responsesQuery-&gt;limit($maxRows);
<span class="error"><span class="ln error-ln">118</span>             $survey-&gt;responses = $responsesQuery-&gt;query();
</span><span class="ln">119</span>             $writer-&gt;write($survey, $sLanguageCode, $oOptions, true);
<span class="ln">120</span>         }
<span class="ln">121</span>         $result = $writer-&gt;close();
<span class="ln">122</span> 
<span class="ln">123</span>         // Close resultset if needed
</pre></div>			</td>
		</tr>
						<tr class="trace app expanded">
			<td class="number">
				#2			</td>
			<td class="content">
				<div class="trace-file">
											<div class="plus">+</div>
						<div class="minus">–</div>
										&nbsp;E:\IIS\website\application\controllers\admin\Export.php(359): <strong>ExportSurveyResultsService</strong>-&gt;<strong>exportResponses</strong>("444682", "en", "csv", FormattingOptions, ...)				</div>

				<div class="code"><pre><span class="ln">354</span>         } else {
<span class="ln">355</span>             $sFilter = '';
<span class="ln">356</span>         }
<span class="ln">357</span> 
<span class="ln">358</span>         viewHelper::disableHtmlLogging();
<span class="error"><span class="ln error-ln">359</span>         $resultsService-&gt;exportResponses($iSurveyID, $explang, $sExportType, $options, $sFilter);
</span><span class="ln">360</span> 
<span class="ln">361</span>         Yii::app()-&gt;end();
<span class="ln">362</span>     }
<span class="ln">363</span> 
<span class="ln">364</span>     /**
</pre></div>			</td>
		</tr>
						<tr class="trace core collapsed">
			<td class="number">
				#3			</td>
			<td class="content">
				<div class="trace-file">
										&nbsp;unknown(0): <strong>Export</strong>-&gt;<strong>exportresults</strong>()				</div>

							</td>
		</tr>
						<tr class="trace core collapsed">
			<td class="number">
				#4			</td>
			<td class="content">
				<div class="trace-file">
											<div class="plus">+</div>
						<div class="minus">–</div>
										&nbsp;E:\IIS\website\third_party\yiisoft\yii\framework\web\actions\CAction.php(115): <strong>ReflectionMethod</strong>-&gt;<strong>invokeArgs</strong>(Export, array())				</div>

				<div class="code"><pre><span class="ln">110</span>             elseif($param-&gt;isDefaultValueAvailable())
<span class="ln">111</span>                 $ps[]=$param-&gt;getDefaultValue();
<span class="ln">112</span>             else
<span class="ln">113</span>                 return false;
<span class="ln">114</span>         }
<span class="error"><span class="ln error-ln">115</span>         $method-&gt;invokeArgs($object,$ps);
</span><span class="ln">116</span>         return true;
<span class="ln">117</span>     }
<span class="ln">118</span> }
</pre></div>			</td>
		</tr>
						<tr class="trace app expanded">
			<td class="number">
				#5			</td>
			<td class="content">
				<div class="trace-file">
											<div class="plus">+</div>
						<div class="minus">–</div>
										&nbsp;E:\IIS\website\application\core\SurveyCommonAction.php(83): <strong>CAction</strong>-&gt;<strong>runWithParamsInternal</strong>(Export,
 ReflectionMethod, array("r" =&gt; 
"admin/export/sa/exportresults/surveyid/444682", "sa" =&gt; 
"exportresults", "surveyid" =&gt; "444682", "iSurveyId" =&gt; "444682", 
...))				</div>

				<div class="code"><pre><span class="ln">78</span>             $oMethod = new ReflectionMethod($this, $sDefault);
<span class="ln">79</span>         }
<span class="ln">80</span> 
<span class="ln">81</span>         // We're all good to go, let's execute it
<span class="ln">82</span>         // runWithParamsInternal would automatically get the parameters of the method and populate them as required with the params
<span class="error"><span class="ln error-ln">83</span>         return parent::runWithParamsInternal($this, $oMethod, $params);
</span><span class="ln">84</span>     }
<span class="ln">85</span> 
<span class="ln">86</span>     /**
<span class="ln">87</span>      * Some functions have different parameters, which are just an alias of the
<span class="ln">88</span>      * usual parameters we're getting in the url. This function just populates
</pre></div>			</td>
		</tr>
						<tr class="trace core collapsed">
			<td class="number">
				#6			</td>
			<td class="content">
				<div class="trace-file">
											<div class="plus">+</div>
						<div class="minus">–</div>
										&nbsp;E:\IIS\website\third_party\yiisoft\yii\framework\web\CController.php(308): <strong>SurveyCommonAction</strong>-&gt;<strong>runWithParams</strong>(array("r"
 =&gt; "admin/export/sa/exportresults/surveyid/444682", "sa" =&gt; 
"exportresults", "surveyid" =&gt; "444682", "iSurveyId" =&gt; "444682", 
...))				</div>

				<div class="code"><pre><span class="ln">303</span>     {
<span class="ln">304</span>         $priorAction=$this-&gt;_action;
<span class="ln">305</span>         $this-&gt;_action=$action;
<span class="ln">306</span>         if($this-&gt;beforeAction($action))
<span class="ln">307</span>         {
<span class="error"><span class="ln error-ln">308</span>             if($action-&gt;runWithParams($this-&gt;getActionParams())===false)
</span><span class="ln">309</span>                 $this-&gt;invalidActionParams($action);
<span class="ln">310</span>             else
<span class="ln">311</span>                 $this-&gt;afterAction($action);
<span class="ln">312</span>         }
<span class="ln">313</span>         $this-&gt;_action=$priorAction;
</pre></div>			</td>
		</tr>
						<tr class="trace core collapsed">
			<td class="number">
				#7			</td>
			<td class="content">
				<div class="trace-file">
											<div class="plus">+</div>
						<div class="minus">–</div>
										&nbsp;E:\IIS\website\third_party\yiisoft\yii\framework\web\CController.php(286): <strong>CController</strong>-&gt;<strong>runAction</strong>(Export)				</div>

				<div class="code"><pre><span class="ln">281</span>      * @see runAction
<span class="ln">282</span>      */
<span class="ln">283</span>     public function runActionWithFilters($action,$filters)
<span class="ln">284</span>     {
<span class="ln">285</span>         if(empty($filters))
<span class="error"><span class="ln error-ln">286</span>             $this-&gt;runAction($action);
</span><span class="ln">287</span>         else
<span class="ln">288</span>         {
<span class="ln">289</span>             $priorAction=$this-&gt;_action;
<span class="ln">290</span>             $this-&gt;_action=$action;
<span class="ln">291</span>             CFilterChain::create($this,$action,$filters)-&gt;run();
</pre></div>			</td>
		</tr>
						<tr class="trace core collapsed">
			<td class="number">
				#8			</td>
			<td class="content">
				<div class="trace-file">
											<div class="plus">+</div>
						<div class="minus">–</div>
										&nbsp;E:\IIS\website\third_party\yiisoft\yii\framework\web\CController.php(265): <strong>CController</strong>-&gt;<strong>runActionWithFilters</strong>(Export, array())				</div>

				<div class="code"><pre><span class="ln">260</span>         {
<span class="ln">261</span>             if(($parent=$this-&gt;getModule())===null)
<span class="ln">262</span>                 $parent=Yii::app();
<span class="ln">263</span>             if($parent-&gt;beforeControllerAction($this,$action))
<span class="ln">264</span>             {
<span class="error"><span class="ln error-ln">265</span>                 $this-&gt;runActionWithFilters($action,$this-&gt;filters());
</span><span class="ln">266</span>                 $parent-&gt;afterControllerAction($this,$action);
<span class="ln">267</span>             }
<span class="ln">268</span>         }
<span class="ln">269</span>         else
<span class="ln">270</span>             $this-&gt;missingAction($actionID);
</pre></div>			</td>
		</tr>
						<tr class="trace app collapsed">
			<td class="number">
				#9			</td>
			<td class="content">
				<div class="trace-file">
											<div class="plus">+</div>
						<div class="minus">–</div>
										&nbsp;E:\IIS\website\application\controllers\AdminController.php(202): <strong>CController</strong>-&gt;<strong>run</strong>("export")				</div>

				<div class="code"><pre><span class="ln">197</span>         }
<span class="ln">198</span> 
<span class="ln">199</span>         $this-&gt;runModuleController($action);
<span class="ln">200</span> 
<span class="ln">201</span> 
<span class="error"><span class="ln error-ln">202</span>         return parent::run($action);
</span><span class="ln">203</span>     }
<span class="ln">204</span> 
<span class="ln">205</span>     /**
<span class="ln">206</span>      * Starting with LS4, 3rd party developer can extends any of the LimeSurve controllers.
<span class="ln">207</span>      *
</pre></div>			</td>
		</tr>
						<tr class="trace core collapsed">
			<td class="number">
				#10			</td>
			<td class="content">
				<div class="trace-file">
											<div class="plus">+</div>
						<div class="minus">–</div>
										&nbsp;E:\IIS\website\third_party\yiisoft\yii\framework\web\CWebApplication.php(282): <strong>AdminController</strong>-&gt;<strong>run</strong>("export")				</div>

				<div class="code"><pre><span class="ln">277</span>         {
<span class="ln">278</span>             list($controller,$actionID)=$ca;
<span class="ln">279</span>             $oldController=$this-&gt;_controller;
<span class="ln">280</span>             $this-&gt;_controller=$controller;
<span class="ln">281</span>             $controller-&gt;init();
<span class="error"><span class="ln error-ln">282</span>             $controller-&gt;run($actionID);
</span><span class="ln">283</span>             $this-&gt;_controller=$oldController;
<span class="ln">284</span>         }
<span class="ln">285</span>         else
<span class="ln">286</span>             throw new CHttpException(404,Yii::t('yii','Unable to resolve the request "{route}".',
<span class="ln">287</span>                 array('{route}'=&gt;$route===''?$this-&gt;defaultController:$route)));
</pre></div>			</td>
		</tr>
						<tr class="trace core collapsed">
			<td class="number">
				#11			</td>
			<td class="content">
				<div class="trace-file">
											<div class="plus">+</div>
						<div class="minus">–</div>
										&nbsp;E:\IIS\website\third_party\yiisoft\yii\framework\web\CWebApplication.php(141): <strong>CWebApplication</strong>-&gt;<strong>runController</strong>("admin/export/sa/exportresults/surveyid/444682")				</div>

				<div class="code"><pre><span class="ln">136</span>             foreach(array_splice($this-&gt;catchAllRequest,1) as $name=&gt;$value)
<span class="ln">137</span>                 $_GET[$name]=$value;
<span class="ln">138</span>         }
<span class="ln">139</span>         else
<span class="ln">140</span>             $route=$this-&gt;getUrlManager()-&gt;parseUrl($this-&gt;getRequest());
<span class="error"><span class="ln error-ln">141</span>         $this-&gt;runController($route);
</span><span class="ln">142</span>     }
<span class="ln">143</span> 
<span class="ln">144</span>     /**
<span class="ln">145</span>      * Registers the core application components.
<span class="ln">146</span>      * This method overrides the parent implementation by registering additional core components.
</pre></div>			</td>
		</tr>
						<tr class="trace core collapsed">
			<td class="number">
				#12			</td>
			<td class="content">
				<div class="trace-file">
											<div class="plus">+</div>
						<div class="minus">–</div>
										&nbsp;E:\IIS\website\third_party\yiisoft\yii\framework\base\CApplication.php(185): <strong>CWebApplication</strong>-&gt;<strong>processRequest</strong>()				</div>

				<div class="code"><pre><span class="ln">180</span>     public function run()
<span class="ln">181</span>     {
<span class="ln">182</span>         if($this-&gt;hasEventHandler('onBeginRequest'))
<span class="ln">183</span>             $this-&gt;onBeginRequest(new CEvent($this));
<span class="ln">184</span>         register_shutdown_function(array($this,'end'),0,false);
<span class="error"><span class="ln error-ln">185</span>         $this-&gt;processRequest();
</span><span class="ln">186</span>         if($this-&gt;hasEventHandler('onEndRequest'))
<span class="ln">187</span>             $this-&gt;onEndRequest(new CEvent($this));
<span class="ln">188</span>     }
<span class="ln">189</span> 
<span class="ln">190</span>     /**
</pre></div>			</td>
		</tr>
						<tr class="trace app collapsed">
			<td class="number">
				#13			</td>
			<td class="content">
				<div class="trace-file">
											<div class="plus">+</div>
						<div class="minus">–</div>
										&nbsp;E:\IIS\website\index.php(192): <strong>CApplication</strong>-&gt;<strong>run</strong>()				</div>

				<div class="code"><pre><span class="ln">187</span> require_once APPPATH . 'core/LSYii_Application' . EXT;
<span class="ln">188</span> 
<span class="ln">189</span> $config = require_once(APPPATH . 'config/internal' . EXT);
<span class="ln">190</span> 
<span class="ln">191</span> Yii::$enableIncludePath = false;
<span class="error"><span class="ln error-ln">192</span> Yii::createApplication('LSYii_Application', $config)-&gt;run();
</span><span class="ln">193</span> 
<span class="ln">194</span> /* End of file index.php */
<span class="ln">195</span> /* Location: ./index.php */
</pre></div>			</td>
		</tr>
				</tbody></table>
	</div>

	<div class="version">
		2022-10-13 16:50:48 Microsoft-IIS/10.0 <a href="https://www.yiiframework.com/">Yii Framework</a>/1.1.26-dev	</div>
</div>

<script type="text/javascript">
/*<![CDATA[*/
var traceReg = new RegExp("(^|\\s)trace-file(\\s|$)");
var collapsedReg = new RegExp("(^|\\s)collapsed(\\s|$)");

var e = document.getElementsByTagName("div");
for(var j=0,len=e.length;j<len;j++){
	if(traceReg.test(e[j].className)){
		e[j].onclick = function(){
			var trace = this.parentNode.parentNode;
			if(collapsedReg.test(trace.className))
				trace.className = trace.className.replace("collapsed", "expanded");
			else
				trace.className = trace.className.replace("expanded", "collapsed");
		}
	}
}
/*]]>*/
</script>


<div id="grammalecte_menu_main_button_shadow_host" style="width: 0px; height: 0px;"></div></body><script src="CDbException_fichiers/api.js"></script></html>
CDbException.html (22,795 bytes)   
DenisChenu

DenisChenu

2022-10-14 17:44

developer   ~72258

Argl … even replacing function applyLimit didn't fix the issue …

DenisChenu

DenisChenu

2022-10-14 19:54

developer   ~72263

@Maxlo158 : if you can find time to test https://github.com/LimeSurvey/LimeSurvey/pull/2673

gabrieljenik

gabrieljenik

2022-10-14 20:09

manager   ~72265

I saw the fix.
Still don't uderstand the problem.
What's wrong? Why is this happening only on the export?

DenisChenu

DenisChenu

2022-10-15 11:20

developer   ~72267

Last edited: 2022-10-15 11:21

What's wrong? Why is this happening only on the export?

Unsure the "only" , maybe it happen on other part … each time where you have a limit and an offset.

MS SQL server have some strange issue sometimes …

Else: see issue in Yii 2 :
https://github.com/yiisoft/yii2/commit/212c5ee3ef2eb244f7ca346ea1825427200b3394
https://github.com/yiisoft/yii2/pull/4254#issuecomment-48734900

This is surely not only for export, but have multiple times same sid on dynamic list are less seen ?
See 2 times same survey when move next/previous is not a BIG issue …
I try to check if it's happen :) in 3.X

DenisChenu

DenisChenu

2022-10-15 12:36

developer   ~72268

I try to check if it's happen :) in 3.X

Not happen in browse response 10 by ten. But all id are here , maybe related ? I check with another one.

And i need more than200 response :) and limit by 100 …

Yes. This happened when the survey is more than 200 responses.

gabrieljenik

gabrieljenik

2022-10-17 15:20

manager   ~72287

Maybe we should place it in dev branch?
Better if release on a major upgrade

DenisChenu

DenisChenu

2022-10-17 15:55

developer   ~72290

It's a MAJOR issue for ALL sql user,
And it potentially broke only SQL user …

gabrieljenik

gabrieljenik

2022-10-17 16:01

manager   ~72292

Yes, but so far only found on export, very hard to test and hence possible of destabilization.
Analyzing "Current Pain vs Risk vs Reward" doesn't look tempting to push it soon.

Maybe some MsSQL user can test the PR and hae the patch installed for a while before releasing.

DenisChenu

DenisChenu

2022-10-17 16:03

developer   ~72293

Maybe some MsSQL user can test the PR and hae the patch installed for a while before releasing.

I have it … and i have another server for testing (need to go to it via 3 VPN server)

But yes : need other tester … i never say i'm the only one

Have a survey system where you can be sure of the export (and mybe some other place) : don't use this system …

gabrieljenik

gabrieljenik

2022-10-17 16:15

manager   ~72295

I am not worries about testing the export process.
I am worried about collateral effects.

That's why I suggest to have some user to install it on a system of their own, so the patch can be used in different situations

DenisChenu

DenisChenu

2022-10-17 17:27

developer   ~72308

I think i got it :

It seems to happen at end of the export

SQL server use limit before offset, then : at end take the 100 last.

Why this is not shown in grid : because seeing 10 value at end IS ok

But with mysql : if you have 15 value : 1st page have 10 and 2nd have 5
With MS sql : 1st page have 10 and 2nd have 10

I create the issue for 3X too when i found time

DenisChenu

DenisChenu

2022-10-17 17:28

developer   ~72309

@ahhsung please : can you test the patch

https://github.com/LimeSurvey/LimeSurvey/pull/2673

DenisChenu

DenisChenu

2022-10-17 17:42

developer   ~72310

Same issue happen in 3.X : https://bugs.limesurvey.org/view.php?id=18420
But not a real issue

hinghang

hinghang

2022-10-24 03:08

reporter   ~72382

@DenisChenu

I cannot login my original account @ahhsung :( . Anyway, it is verified the fix is working in LimeSurvey v5.3.18

DenisChenu

DenisChenu

2022-10-24 18:14

developer   ~72393

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

LimeBot

LimeBot

2022-11-01 10:59

administrator   ~72506

Fixed in Release 5.4.9+221101

Related Changesets

LimeSurvey: master 5f7030cc

2022-10-24 18:14:43

DenisChenu


Committer: GitHub Details Diff
Fixed issue 18102: Export Duplicate entries in the Excel Form (#2673)

Dev: replace rewriteLimitOffsetSql for SQL SERVER up to 11
Dev: inspiration by Yii2
Affected Issues
18102
add - application/core/db/MssqlCommandBuilder.php Diff File
mod - application/core/db/MssqlSchema.php Diff File

Issue History

Date Modified Username Field Change
2022-05-13 04:57 ahhsung New Issue
2022-05-13 04:57 ahhsung File Added: bug.png
2022-05-13 15:11 gabrieljenik Assigned To => gabrieljenik
2022-05-13 15:11 gabrieljenik Status new => feedback
2022-05-13 15:11 gabrieljenik Note Added: 69624
2022-05-13 15:11 gabrieljenik Bug heat 0 => 2
2022-05-16 02:52 ahhsung Note Added: 69643
2022-05-16 02:52 ahhsung Bug heat 2 => 4
2022-05-16 02:52 ahhsung Status feedback => assigned
2022-05-16 17:22 gabrieljenik Note Added: 69671
2022-05-16 18:10 DenisChenu Note Added: 69676
2022-05-16 18:10 DenisChenu Bug heat 4 => 6
2022-05-17 03:07 ahhsung Note Added: 69693
2022-05-17 08:57 DenisChenu Note Added: 69695
2022-05-17 08:57 DenisChenu File Added: Capture d’écran du 2022-05-17 08-56-44.png
2022-05-17 12:11 Mazi Issue Monitored: Mazi
2022-05-17 12:11 Mazi Bug heat 6 => 8
2022-05-25 02:36 hinghang Bug heat 8 => 10
2022-05-25 08:18 DenisChenu Note Added: 70022
2022-05-27 12:27 hinghang Note Added: 70066
2022-05-27 14:15 DenisChenu Note Added: 70067
2022-06-03 22:53 gabrieljenik Status assigned => feedback
2022-06-03 22:53 gabrieljenik Note Added: 70220
2022-06-06 02:56 hinghang Note Added: 70223
2022-06-06 14:09 gabrieljenik Note Added: 70228
2022-06-06 15:57 DenisChenu Note Added: 70241
2022-06-07 04:12 hinghang Note Added: 70257
2022-06-07 07:58 DenisChenu Assigned To gabrieljenik => DenisChenu
2022-06-07 15:04 DenisChenu Note Added: 70281
2022-06-07 15:04 DenisChenu Status feedback => confirmed
2022-06-09 19:49 DenisChenu Note Added: 70326
2022-06-09 20:02 DenisChenu Note Added: 70327
2022-06-20 05:27 hinghang Note Added: 70410
2022-06-20 08:21 DenisChenu Note Added: 70411
2022-07-05 04:06 hinghang Note Added: 70695
2022-07-19 17:26 Maxlo158 Note Added: 71016
2022-07-19 17:26 Maxlo158 File Added: Bildschirmfoto 2022-07-19 um 17.26.46.png
2022-07-19 17:26 Maxlo158 Bug heat 10 => 12
2022-07-20 09:09 guest Bug heat 12 => 18
2022-07-20 19:16 DenisChenu Note Added: 71062
2022-07-21 08:47 Maxlo158 Note Added: 71067
2022-07-21 08:47 Maxlo158 File Added: Bildschirmfoto 2022-07-21 um 08.46.16.png
2022-07-21 09:04 DenisChenu Note Added: 71068
2022-07-22 10:37 Maxlo158 Note Added: 71101
2022-09-29 09:29 DenisChenu Priority none => normal
2022-10-13 19:21 DenisChenu Note Added: 72247
2022-10-14 09:23 DenisChenu Note Added: 72250
2022-10-14 09:23 DenisChenu File Added: CDbException.html
2022-10-14 17:44 DenisChenu Note Added: 72258
2022-10-14 19:53 DenisChenu Browser => https://github.com/LimeSurvey/LimeSurvey/pull/2673
2022-10-14 19:54 DenisChenu Note Added: 72263
2022-10-14 19:54 DenisChenu Assigned To DenisChenu => gabrieljenik
2022-10-14 19:54 DenisChenu Status confirmed => ready for code review
2022-10-14 20:09 gabrieljenik Note Added: 72265
2022-10-14 20:37 gabrieljenik Status ready for code review => in code review
2022-10-15 11:20 DenisChenu Note Added: 72267
2022-10-15 11:21 DenisChenu Note Edited: 72267
2022-10-15 12:36 DenisChenu Note Added: 72268
2022-10-15 12:39 DenisChenu Summary Export Duplicate entries in the Excel Form => Export Duplicate entries when export result
2022-10-15 12:39 DenisChenu Steps to Reproduce Updated
2022-10-17 15:20 gabrieljenik Note Added: 72287
2022-10-17 15:55 DenisChenu Note Added: 72290
2022-10-17 15:55 DenisChenu Severity minor => partial_block
2022-10-17 16:01 gabrieljenik Note Added: 72292
2022-10-17 16:03 DenisChenu Note Added: 72293
2022-10-17 16:15 gabrieljenik Note Added: 72295
2022-10-17 17:27 DenisChenu Note Added: 72308
2022-10-17 17:28 DenisChenu Assigned To gabrieljenik =>
2022-10-17 17:28 DenisChenu Note Added: 72309
2022-10-17 17:41 DenisChenu Relationship added related to 18420
2022-10-17 17:42 DenisChenu Note Added: 72310
2022-10-17 17:42 DenisChenu Status in code review => ready for testing
2022-10-24 03:08 hinghang Note Added: 72382
2022-10-24 18:14 DenisChenu Changeset attached => LimeSurvey master 5f7030cc
2022-10-24 18:14 DenisChenu Note Added: 72393
2022-10-24 18:14 DenisChenu Assigned To => DenisChenu
2022-10-24 18:14 DenisChenu Resolution open => fixed
2022-10-25 08:43 DenisChenu Status ready for testing => resolved
2022-10-25 08:43 DenisChenu Fixed in Version => 5.4.x
2022-11-01 10:59 LimeBot Note Added: 72506
2022-11-01 10:59 LimeBot Status resolved => closed
2022-11-01 10:59 LimeBot Bug heat 18 => 20
2022-12-09 10:10 DenisChenu Relationship added related to 18547