View Issue Details

IDProjectCategoryView StatusLast Update
09587Development [All Projects] Otherpublic2015-08-21 15:51
Reportersickpig Assigned ToDenisChenu  
PriorityhighSeveritymajor 
Status closedResolutionfixed 
Product Version 
Target Version2.06Fixed in Version2.06 
Summary09587: Quotas do not work if lime is backed by PostgreSQL DB since build 140717 up to the current 15031
Description

Hi all,

I'm in the process to migrate a old instance (2.05+ build 140520), that use postgresql as db server, to the current stable 2.05+ build.

Unluckily I can't perform such a task since I've found a bug in the limesurvey Quotas management.

Build 140703 is the last working build.

Builds 140717, 140730, 140811 gives an internal error every time limesurvey try to evaluate a quota. The error was due to improper column name quoting and was fixed by Denis Chenu with the commit c85494d76557845166a7dbffaa760ceaffb38081.

From the 140821 build the internal error is fixed but the Quotas system does not work anymore.

I.E. even if the quota limit is reached for a given Quota group, instead of being redirected to the "over quota" page the user continue to the next question as if the quota is not set.

I've bisected the problem and the offending commit is 535035816af172fc5b82f3fa896cdb73d53c9800, this is the commit message log:

Author: Denis Chenu <courriel<CHEZ>shnoulle<POINT>net>
Date: Sat Jul 5 17:03:12 2014 +0200

Fixed issue 07895: Quota are not tested for hidden question (relevance to 1)
New feature 09066: afterSurveyComplete aren't call from Quota : afterSurveyQuota
Dev: update all function needed for quota system

Unfortunately I'm not skilled enough to quickly cook a patch on my own.

This is why I've reported the issue.

Steps To Reproduce

Import the attached lss file.
Install lime 2.05+ build 20150310 suing PostgreSQL as db server.
Activate the survey.
Switch to token based access.
Create a token.
Start the survey for the given token.
Reply "No" to the first question.

Expected result --> Over quota message.

Actual result --> The second question is displayed.

Additional Information

sorry but the attached survey is in italian.

TagsNo tags attached.

Relationships

child of 07895 closedDenisChenu Bug reports Quota are not tested for hidden question (relevance to 1) 

Activities

sickpig

sickpig

2015-03-24 15:02

reporter  

limesurvey_survey_556876.lss (15,741 bytes)
sickpig

sickpig

2015-03-24 15:06

reporter   ~31890

I forgot to mention that everything work like a charm if you chose to use mysql server rather than postgresql as database server.

DenisChenu

DenisChenu

2015-03-24 15:19

developer   ~31892

I do a new fix for quota this morning : https://github.com/LimeSurvey/LimeSurvey/commit/d25b7357dddbee3d02a1fa21e975098fe2ce5f8b

Can you try with the last GIT version (you can download it via https://github.com/LimeSurvey/LimeSurvey/archive/master.zip

Thanks

DenisChenu

DenisChenu

2015-03-24 15:22

developer   ~31893

A question more : without token it's OK ?

sickpig

sickpig

2015-03-24 15:29

reporter   ~31894

Hi Denis, thanks for the prompt reply. I'm going to try the latest git version now and I'll let you know if it works.

I haven't tried without token till know, I going to do also this test on the master tip.

sickpig

sickpig

2015-03-24 15:40

reporter   ~31896

I confirm that test case described does not trigger the issue anymore.

I've also tested a survey without token and it works also in this case.

kudos to you for the prompt reply and pre-emptive fix :)

DenisChenu

DenisChenu

2015-03-24 15:44

developer   ~31897

Don't really undestarnd why it don't work before ? Why it work on MySQL and msSQL and not with PG ????

Remind to download the next relerase to allow auto updating again. The GIT version disallow confort update.

sickpig

sickpig

2015-03-24 15:47

reporter   ~31898

I was going go ask the same question to you, the fact that you don't know why it didn't worked before is a little bit scary ;).

Don't worry for the auto-update stuff I was testing the migration path on a stage server.

DenisChenu

DenisChenu

2015-03-24 15:51

developer   ~31899

I do a test with pg next week i think.

sickpig

sickpig

2015-03-24 15:57

reporter   ~31900

Last edited: 2015-03-24 15:59

View 2 revisions

Still on the same matter while trying to debug the problem I've checked all the queries executed by limesurvey and I can assure that it produced all the needed sql statement (e.g. SELECT COUNT(*) FROM "lime_survey_556876" "t" WHERE (submitdate IS NOT NULL) AND ("556876X1X1" = '2') ).

It seems that the problem occurred somewhere in checkCompletedQuota rather than inside getQuotaCompletedCount.

sickpig

sickpig

2015-03-24 16:01

reporter   ~31901

about your test session with pg let me know if you want me to do something, I'll be glad to help you.

DenisChenu

DenisChenu

2015-03-24 16:37

developer   ~31902

Think it's the switch before , maybe pgSQL return other than '1' or '2' ?

sickpig

sickpig

2015-03-24 17:25

reporter   ~31903

Last edited: 2015-03-24 17:38

View 2 revisions

sorry but I'm not familiar enough with limesurvey codebase.

I can confirm that both mysql and pg store the same exact tuple in lime_quota table for the test case survey, though:

  • pg

┌────┬────────┬──────┬────────┬────────┬────────┬──────────────┐
│ id │ sid │ name │ qlimit │ action │ active │ autoload_url │
├────┼────────┼──────┼────────┼────────┼────────┼──────────────┤
│ 1 │ 556876 │ test │ 0 │ 1 │ 1 │ 0 │
└────┴────────┴──────┴────────┴────────┴────────┴──────────────┘

  • mysql

+----+--------+------+--------+--------+--------+--------------+
| id | sid | name | qlimit | action | active | autoload_url |
+----+--------+------+--------+--------+--------+--------------+
| 1 | 556876 | test | 0 | 1 | 1 | 0 |
+----+--------+------+--------+--------+--------+--------------+

I've even enabled logging at Yii level and add this:

Yii::log("aQuotaInfo action is: " . $aQuotaInfo['action'],"warning","TEST");

at line 1954 and this is what I got in the log while running an up-to-date git master branch:

2015/03/24 17:19:31 [warning] [TEST] aQuotaInfo action is: 1

tomorrow I will test it on 140811 build.

sickpig

sickpig

2015-03-25 10:34

reporter   ~31905

I've add some log statements to application/helpers/frontend_helper.php (build 140821) inside checkCompleteQuota body function and I've discovered we don't even reach the switch, the function exit at:

https://github.com/LimeSurvey/LimeSurvey/blob/c65c03001d2a2223b97e6975c7098caea1c8d4d9/application/helpers/frontend_helper.php#L1933

so I think that the problem is in the for cycle before (line 1925 - 1930).

sickpig

sickpig

2015-03-25 12:48

reporter   ~31906

found it.

while using postgres getQuotaCompletedCount return an integer not a string, hence ctype_digit($iCompleted) return always false regardless of the $iCompleted value.

I think that the postgresql php adapter / AR probably maps db types with php types rather than returning always string.

DenisChenu

DenisChenu

2015-03-25 12:57

developer   ~31907

http://www.yiiframework.com/doc/api/1.1/CActiveRecord#count-detail

$result = SurveyDynamic::model($iSurveyId)->count($criteria);

Yii bug ?

sickpig

sickpig

2015-03-25 13:56

reporter   ~31908

Last edited: 2015-03-25 13:56

View 2 revisions

it seems so, maybe this is fixed and the latest version of the 1.1 branch.

I've quickly went through the Yii 1.1.16 changelog and the only thing that seems related is:

Bug #268: Fixed Active Record count error when some field name starting from 'count' (nineinchnick)

but I don't really know if it is the case.

sickpig

sickpig

2015-03-26 10:18

reporter   ~31911

I think we have to reopen this issue :/

you're last change works only if qlimit is less than 1, because zero is treated as a special case in the code:

      if((int)$aQuotaInfo['qlimit'] &lt; 1){
          $aMatchedQuotas[]=$aQuotaInfo;
      }else{
          $iCompleted=getQuotaCompletedCount($surveyid, $aQuotaInfo['id']);// Return a string
          if(ctype_digit($iCompleted) && ((int)$iCompleted >= (int)$aQuotaInfo['qlimit'])) // This remove invalid quota and not completed
                 $aMatchedQuotas[]=$aQuotaInfo;
      }

if qlimit is >= 1 the else branch will be executed and the ctype_digit($iCompleted) is still there...

I've verified on a fresh git master limesurvey installation that if qlimit is grather than 0 the bus is still present

sickpig

sickpig

2015-03-26 10:19

reporter   ~31912

see Note 31911 (http://bugs.limesurvey.org/view_user_page.php?id=22104)

if qlimit is > 0 the bug is still triggerd

DenisChenu

DenisChenu

2015-03-26 15:08

developer   ~31913

Last edited: 2015-03-26 15:14

View 3 revisions

Testing with simple quota :
tracevar(gettype($result)); return string for PG too.

PG version :PostgreSQL 9.1.15 on i686-pc-linux-gnu, compiled by gcc (Debian 4.7.2-5) 4.7.2, 32-bit

Can not reproduce with YOUR lss, set quota max at 2 and PostgreSQL 9.1.15
http://lspg.sondages.pro/index.php/survey/index/sid/556876/newtest/Y/lang/en

sickpig

sickpig

2015-03-26 15:18

reporter   ~31914

test server come with a postgres which version is:

PostgreSQL 9.1.15 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 4.8.2-19ubuntu1) 4.8.2, 64-bit

we have also another production server (lime 2.05+ Build 150310) where we are experiencing the same problem, on this server postgres has a slightly different version:

PostgreSQL 9.3.6 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 4.8.2-19ubuntu1) 4.8.2, 64-bit

I'm going to add output of tracevar(gettype()) to the test server and I'll report back the result.

DenisChenu

DenisChenu

2015-03-26 15:21

developer   ~31915

OK, great.

Same version of PG, gthen no reason ....
I can add a strval ot count but .... not sure it fix it.

Actually : can not reproduce ... on debian. Maybe PHP PDO ?

sickpig

sickpig

2015-03-26 15:38

reporter   ~31916

Last edited: 2015-03-26 15:40

View 2 revisions

could be, I've just verified both $result (inside getQuotaCompletedCount) and $iCompleted are integer:

2015/03/26 15:31:16 [warning] [TEST] XXXX result : integer
2015/03/26 15:31:16 [warning] [TEST] iCompleted type is : integer

it could be definitely related to psqgl pdo that come with ubuntu. this is the installed version:

php5-pgsql 5.5.9+dfsg-1ubuntu4.7

the actual module seems to be form 2012:

/usr/lib/php5/20121212/pdo_pgsql.so

I've no handy debian server available, which debian version are you testing on? wheezy? 32 or 64 bit (dunno if matter but just to be sure)

I'm going to quickly spin a virtual machine and I'll give it a try once you'll give me the debian version of your test env

sickpig

sickpig

2015-03-26 16:08

reporter   ~31917

Last edited: 2015-03-26 16:13

View 3 revisions

I can reproduce the problem also on a wheezy server with limesurvey 150310, postgresql 9.1. I've applied the same patch to get the type of the count and the
result is the same:

2015/03/26 15:06:56 [warning] [TEST] XXXX result : integer
2015/03/26 15:06:56 [warning] [TEST] iCompleted type is : integer

edit: the only diff is that I've set up a 64 bit virtualbox rather than 32

edit2: if you want I could give you access to the test machine I've just set up.

DenisChenu

DenisChenu

2015-03-26 17:05

developer   ~31918

Last edited: 2015-03-26 17:06

View 2 revisions

K, i strval the result in getCompletedCount ... :/

sickpig

sickpig

2015-03-26 17:33

reporter   ~31919

Ok, just let me know if you need more testing.

DenisChenu

DenisChenu

2015-03-26 18:39

developer   ~31920

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

DenisChenu

DenisChenu

2015-03-26 18:40

developer   ~31921

The only change : https://github.com/LimeSurvey/LimeSurvey/commit/962a62e38786392383c19589e4f04a1ce18532ec#diff-a9dbc6157f3066d12e04af89a4bca062R5512

Denis

DenisChenu

DenisChenu

2015-03-31 10:45

developer   ~31926

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

DenisChenu

DenisChenu

2015-03-31 11:19

developer   ~31927

Reopen for dev.

Return null for N/A, fix quota view and export to show N/A (or something else) ?

Related Changesets

LimeSurvey: master d25b7357

2015-03-24 10:38:43

DenisChenu

Details Diff
Dev: Quota speed optimisation : SQL only for matched quota in session
Dev: Quota system is a good candidate for a core plugin in a future release
Affected Issues
09587
mod - application/helpers/common_helper.php Diff File
mod - application/helpers/frontend_helper.php Diff File

LimeSurvey: master 962a62e3

2015-03-26 17:39:31

DenisChenu

Details Diff
Fixed issue 09587: Quotas do not work with PostgreSQL on some system
Dev: can not reproduce with PgSQL (32bit) and PHP 5.4
Dev: force getQuotaCompletedCount to string
Affected Issues
09587
mod - application/helpers/common_helper.php Diff File

LimeSurvey: 2.06 5bb72b3b

2015-03-31 08:44:56

DenisChenu

Details Diff
Fixed issue 09587: Quotas do not work with PostgreSQL on some system
Dev: can not reproduce with PgSQL (32bit) and PHP 5.4
Dev: force getQuotaCompletedCount to string
Affected Issues
09587
mod - application/helpers/common_helper.php Diff File

LimeSurvey: 2.06 3aa594b5

2015-03-31 10:11:19

DenisChenu

Details Diff
Dev: return null for not started survey and invalid quota for getQuotaCompletedCount
Dev: test !is_null in checkCompletedQuota for adding to matched quotas
Affected Issues
09587
mod - application/helpers/common_helper.php Diff File
mod - application/helpers/frontend_helper.php Diff File
mod - application/views/admin/quotas/viewquotasrow_view.php Diff File

Issue History

Date Modified Username Field Change
2015-03-24 15:02 sickpig New Issue
2015-03-24 15:02 sickpig File Added: limesurvey_survey_556876.lss
2015-03-24 15:06 sickpig Note Added: 31890
2015-03-24 15:13 c_schmitz Assigned To => DenisChenu
2015-03-24 15:13 c_schmitz Status new => assigned
2015-03-24 15:19 DenisChenu Note Added: 31892
2015-03-24 15:21 DenisChenu Relationship added child of 07895
2015-03-24 15:21 DenisChenu Description Updated View Revisions
2015-03-24 15:22 DenisChenu Note Added: 31893
2015-03-24 15:29 sickpig Note Added: 31894
2015-03-24 15:40 sickpig Note Added: 31896
2015-03-24 15:43 DenisChenu Changeset attached => LimeSurvey master d25b7357
2015-03-24 15:44 DenisChenu Note Added: 31897
2015-03-24 15:44 DenisChenu Status assigned => resolved
2015-03-24 15:44 DenisChenu Resolution open => fixed
2015-03-24 15:44 DenisChenu Fixed in Version => 2.05+
2015-03-24 15:44 DenisChenu Target Version => 2.05+
2015-03-24 15:47 sickpig Note Added: 31898
2015-03-24 15:51 DenisChenu Note Added: 31899
2015-03-24 15:57 sickpig Note Added: 31900
2015-03-24 15:59 sickpig Note Edited: 31900 View Revisions
2015-03-24 16:01 sickpig Note Added: 31901
2015-03-24 16:37 DenisChenu Note Added: 31902
2015-03-24 17:25 sickpig Note Added: 31903
2015-03-24 17:38 sickpig Note Edited: 31903 View Revisions
2015-03-25 10:34 sickpig Note Added: 31905
2015-03-25 12:48 sickpig Note Added: 31906
2015-03-25 12:57 DenisChenu Note Added: 31907
2015-03-25 13:56 sickpig Note Added: 31908
2015-03-25 13:56 sickpig Note Edited: 31908 View Revisions
2015-03-26 10:18 sickpig Note Added: 31911
2015-03-26 10:19 sickpig Note Added: 31912
2015-03-26 10:19 sickpig Status resolved => feedback
2015-03-26 10:19 sickpig Resolution fixed => reopened
2015-03-26 15:08 DenisChenu Note Added: 31913
2015-03-26 15:11 DenisChenu Note Edited: 31913 View Revisions
2015-03-26 15:12 DenisChenu Resolution reopened => unable to reproduce
2015-03-26 15:14 DenisChenu Note Edited: 31913 View Revisions
2015-03-26 15:18 sickpig Note Added: 31914
2015-03-26 15:18 sickpig Status feedback => assigned
2015-03-26 15:21 DenisChenu Note Added: 31915
2015-03-26 15:38 sickpig Note Added: 31916
2015-03-26 15:40 sickpig Note Edited: 31916 View Revisions
2015-03-26 16:08 sickpig Note Added: 31917
2015-03-26 16:10 sickpig Note Edited: 31917 View Revisions
2015-03-26 16:13 sickpig Note Edited: 31917 View Revisions
2015-03-26 17:05 DenisChenu Note Added: 31918
2015-03-26 17:06 DenisChenu Note Edited: 31918 View Revisions
2015-03-26 17:33 sickpig Note Added: 31919
2015-03-26 18:39 DenisChenu Changeset attached => LimeSurvey master 962a62e3
2015-03-26 18:39 DenisChenu Note Added: 31920
2015-03-26 18:39 DenisChenu Resolution unable to reproduce => fixed
2015-03-26 18:40 DenisChenu Note Added: 31921
2015-03-31 10:45 DenisChenu Changeset attached => LimeSurvey 2.06 5bb72b3b
2015-03-31 10:45 DenisChenu Note Added: 31926
2015-03-31 10:45 DenisChenu Status assigned => resolved
2015-03-31 11:18 DenisChenu Project Bug reports => Development
2015-03-31 11:19 DenisChenu Note Added: 31927
2015-03-31 11:19 DenisChenu Status resolved => feedback
2015-03-31 11:19 DenisChenu Resolution fixed => reopened
2015-03-31 11:19 DenisChenu Status feedback => acknowledged
2015-03-31 11:19 DenisChenu Product Version 2.05+ =>
2015-03-31 11:19 DenisChenu Fixed in Version 2.05+ =>
2015-03-31 11:19 DenisChenu Target Version 2.05+ => 2.06
2015-03-31 12:12 DenisChenu Changeset attached => LimeSurvey 2.06 3aa594b5
2015-03-31 12:12 DenisChenu Status acknowledged => resolved
2015-03-31 12:12 DenisChenu Fixed in Version => 2.06
2015-03-31 12:12 DenisChenu Resolution reopened => fixed
2015-08-21 15:51 c_schmitz Status resolved => closed