View Issue Details

IDProjectCategoryView StatusLast Update
16311Bug reportsQuestion editorpublic2020-05-21 13:16
Reporterlarjona Assigned To 
PrioritynoneSeverityblock 
Status newResolutionopen 
Product Version4.2.x 
Summary16311: Problem saving questions after upgrade: qid duplicated (conflicts with existing surveys/questions in the database)
Description

Hello
Thanks for working in LimeSurvey, it's very useful software, and thanks for offering it as free, open source software.

I have an instance which was running Limesurvey 3.15.5 with Postgresql 9.4 database in Debian 9 stretch, with several surveys.

I recently decided to put it up to date so I upgraded the postgresql cluster from 9.4 to 9.6 (which is the last stable version for Debian 9), updated limesurvey to 3.22 and then to limesurvey_4.2.2+20200504 which was the last version at the time.

Everything seemed to work well, the web interface was reporting the upgrades of the database, and the new limesurvey version, and the old surveys were still there.

Then I created a new user and this user created a new survey.
Then the user reported that while he could save some questions in the new survey, somehow he hit an error and he couldn't add more questions or subquestions.
I tried with my user to create a new test survey and add a test question and I couldn't, either.
I tried Database Integrity check and it reported no issues.
I turned on the debug settings and got the error message when trying to save the question:

CDbException

CDbCommand failed to execute the SQL statement: SQLSTATE[23505]: Unique violation: 7 ERROR: llave duplicada viola restricción de unicidad «lime_questions_pkey1» DETAIL: Ya existe la llave (qid)=(415).. The SQL statement executed was: INSERT INTO "lime_questions" ("parent_qid", "sid", "gid", "type", "title", "other", "scale_id", "same_default", "encrypted", "mandatory", "relevance", "modulename", "question_order", "preg") VALUES (:yp0, :yp1, :yp2, :yp3, :yp4, :yp5, :yp6, :yp7, :yp8, :yp9, :yp10, :yp11, :yp12, :yp13) (/var/www/surveys/framework/db/CDbCommand.php:358)

#0 /var/www/surveys/framework/db/ar/CActiveRecord.php(1083): CDbCommand->execute()
#1 /var/www/surveys/framework/db/ar/CActiveRecord.php(811): CActiveRecord->insert(NULL)
#2 /var/www/surveys/application/controllers/admin/questionedit.php(838): CActiveRecord->save()
#3 /var/www/surveys/application/controllers/admin/questionedit.php(231): questionedit->storeNewQuestionData(Array)
#4 [internal function]: questionedit->saveQuestionData(637167)
#5 /var/www/surveys/framework/web/actions/CAction.php(109): ReflectionMethod->invokeArgs(Object(questionedit), Array)
#6 /var/www/surveys/application/core/Survey_Common_Action.php(86): CAction->runWithParamsInternal(Object(questionedit), Object(ReflectionMethod), Array)
#7 /var/www/surveys/framework/web/CController.php(308): Survey_Common_Action->runWithParams(Array)
#8 /var/www/surveys/framework/web/CController.php(286): CController->runAction(Object(questionedit))
#9 /var/www/surveys/framework/web/CController.php(265): CController->runActionWithFilters(Object(questionedit), Array)
#10 /var/www/surveys/application/controllers/AdminController.php(180): CController->run('questioneditor')
#11 /var/www/surveys/framework/web/CWebApplication.php(282): AdminController->run('questioneditor')
#12 /var/www/surveys/framework/web/CWebApplication.php(141): CWebApplication->runController('admin/questione...')
#13 /var/www/surveys/framework/base/CApplication.php(185): CWebApplication->processRequest()
#14 /var/www/surveys/index.php(182): CApplication->run()
#15 {main}

(sorry the error is part in Spanish but it refers to: duplicate key value violates unique constraint, existing qid in table lime_questions).

I went to the postgresql database and had a look at the lime_questions table, and saw that the qid field was taking almost sequential, increasing values (starting with 268 for my oldest survey), until it arrives to this new survey, where the qid field started with number 155, and increased, and it seems that at some moment it started to have numbers more than 268 and then started to clash with existing questions and then throwing the errors and not being able to save the questions.

It also seems that each try to save a question generates a new qid, since in my postgresql log I find the first error trying to insert with (qid)=(291) and then errors all along, with increasing sequential numbers.

My highest qid number in the database is 551 and the last error was with qid 422 so maybe to workaround the issue we can just repeat these "unsuccessful save operations" with fake questions until we get a qid which is not duplicated, but I think it may be useful to report the issue here anyways.

Not sure if this is a postgresql problem or a problem of the limesurvey upgrade or a problem in the 4.x version of limesurvey.

I upgraded today to Version 4.2.4+200520 but it didn't solve the problem.

Kind regards

TagsNo tags attached.
Complete LimeSurvey version number (& build)4.2.4+200520
I will donate to the project if issue is resolvedNo
BrowserAny
Database & DB-Versionpostgresql-9.6 (latest Debian 9 package)
Server OS (if known)Debian 9 stretch
Webserver software & version (if known)nginx 1.10.3 (latest Debian 9 package)
PHP Versionphp 7.0.33 (latest Debian 9 package), php7.0-fpm

Activities

larjona

larjona

2020-05-20 12:24

reporter   ~57953

I have workarounded my problem running this command in my Postgresql limesurvey database:

select setval('lime_questions_qid_seq1', 551, true);

Then when I create a new question I obtain a value higher than any of the old questions that were present, and I can save it.

I leave the bug open because maybe something went wrong in the upgrade that needs to be fixed when upgrading the database schemas to new limesurvey versions.

larjona

larjona

2020-05-20 13:08

reporter   ~57955

This is somehow related to the bug https://bugs.limesurvey.org/view.php?id=16145

DenisChenu

DenisChenu

2020-05-20 13:35

developer   ~57956

If you can help us to debug and test with PGSQL : it can be great.

But : if you need a stable production system it's best if you stay with 3.X LTS.

larjona

larjona

2020-05-20 15:17

reporter   ~57966

Oh. I got confused because the 4.x version is shown as "stable release" in the download section of limesurvey.org
I'll rollback to 3.x versions. I have a backup that would take me back to version 3.15.5, and then I looked at the releases of the Github repo and I cannot see any recent 3.x tagged as LTS, so I guess 3.22.15+200505 (the last 3.x version published) is the one I would need to upgrade to, isn't it? (correct me if I am wrong).

I'll try to keep the 4.x instance around too, so if you want me to test anything, just tell.

Both instances would run with Postgresql 9.6 database system (different limesurvey databases, obviously).

Kind regards

larjona

larjona

2020-05-20 20:47

reporter   ~57978

I have rolled back my instance to 3.x but I cannot import the survey created with 4.x. In normal situation when importing it says:

Success
File upload succeeded.
Error
Unable to import survey.

with debug on, and trying to import the .lsa file, it says:
Undefined index: newsid

/var/www/surveys3/application/helpers/admin/import_helper.php(710)

698 $aFiles = $pclzip->listContent();
699
700 if ($pclzip->extract(PCLZIP_OPT_PATH, Yii::app()->getConfig('tempdir').DIRECTORY_SEPARATOR, PCLZIP_OPT_BY_EREG, '/(lss|lsr|lsi|lst)$/') == 0) {
701 unset($pclzip);
702 }
703 $aImportResults = [];
704 // Step 1 - import the LSS file and activate the survey
705 foreach ($aFiles as $aFile) {
706
707 if (pathinfo($aFile['filename'], PATHINFO_EXTENSION) == 'lss') {
708 //Import the LSS file
709 $aImportResults = XMLImportSurvey(Yii::app()->getConfig('tempdir').DIRECTORY_SEPARATOR.$aFile['filename'], null, null, null, true, false);
710 if ($aImportResults && $aImportResults['newsid']) {
711 TemplateConfiguration::checkAndcreateSurveyConfig($aImportResults['newsid']);
712 }
713 // Activate the survey
714 Yii::app()->loadHelper("admin/activate");
715 activateSurvey($aImportResults['newsid']);
716 unlink(Yii::app()->getConfig('tempdir').DIRECTORY_SEPARATOR.$aFile['filename']);
717 break;
718 }
719 }
720
721 // Step 2 - import the responses file
722 foreach ($aFiles as $aFile) {
Stack Trace
#0
– /var/www/surveys3/application/controllers/admin/surveyadmin.php(1354): importSurveyFile("/var/www/surveys3/tmp/8t6665njzh8dfjbtibasnrciesg4vz.lsa", true)
1349
1350 // Now, we have the survey : start importing
1351 Yii::app()->loadHelper('admin/import');
1352
1353 if ($action == 'importsurvey' && !$aData['bFailed']) {
1354 $aImportResults = importSurveyFile($sFullFilepath, (Yii::app()->request->getPost('translinksfields') == '1'));
1355 if (is_null($aImportResults)) {
1356 $aImportResults = array(
1357 'error'=>gT("Unknown error while reading the file, no survey created.")
1358 );
1359 }
#1
unknown(0): SurveyAdmin->copy()
#2

  • /var/www/surveys3/framework/web/actions/CAction.php(109): ReflectionMethod->invokeArgs(SurveyAdmin, array())
    #3
    – /var/www/surveys3/application/core/Survey_Common_Action.php(83): CAction->runWithParamsInternal(SurveyAdmin, ReflectionMethod, array("r" => "admin/survey/sa/copy", "sa" => "copy", "sAction" => "importsurvey"))
    78 $oMethod = new ReflectionMethod($this, $sDefault);
    79 }
    80
    81 // We're all good to go, let's execute it
    82 // runWithParamsInternal would automatically get the parameters of the method and populate them as required with the params
    83 return parent::runWithParamsInternal($this, $oMethod, $params);
    84 }
    85
    86 /*
    87
    Some functions have different parameters, which are just an alias of the
    88 * usual parameters we're getting in the url. This function just populates
    #4
  • /var/www/surveys3/framework/web/CController.php(308): Survey_Common_Action->runWithParams(array("r" => "admin/survey/sa/copy", "sa" => "copy", "sAction" => "importsurvey"))
    #5
  • /var/www/surveys3/framework/web/CController.php(286): CController->runAction(SurveyAdmin)
    #6
  • /var/www/surveys3/framework/web/CController.php(265): CController->runActionWithFilters(SurveyAdmin, array())
    #7
    – /var/www/surveys3/application/controllers/AdminController.php(165): CController->run("survey")
    160 $this->redirect(array('/admin/authentication/sa/login'));
    161 }
    162 }
    163 }
    164
    165 return parent::run($action);
    166 }
    167
    168 /*
    169
    Routes all the actions to their respective places
    170 *
    #8
  • /var/www/surveys3/framework/web/CWebApplication.php(282): AdminController->run("survey")
    #9
  • /var/www/surveys3/framework/web/CWebApplication.php(141): CWebApplication->runController("admin/survey/sa/copy")
    #10
  • /var/www/surveys3/framework/base/CApplication.php(185): CWebApplication->processRequest()
    #11
  • /var/www/surveys3/index.php(194): CApplication->run()

Now I have both instances running. Do you have any advice about how to adapt the export file (.lss or .lsa or .txt) from the 4.x instance, to be able to import it to the 3.x instance? We don't need answers, only the survey question structure and settings.

Thanks!

DenisChenu

DenisChenu

2020-05-21 13:16

developer   ~57984

I have rolled back my instance to 3.x but I cannot import the survey created with 4.x. In normal situation when importing it says:

Yes, it's a know issue … sorry …
https://bugs.limesurvey.org/view.php?id=15784

so I guess 3.22.15+200505 (the last 3.x version published) is the one I would need to upgrade to, isn't it? (correct me if I am wrong).

Correct :)

Issue History

Date Modified Username Field Change
2020-05-20 02:29 larjona New Issue
2020-05-20 12:24 larjona Note Added: 57953
2020-05-20 13:08 larjona Note Added: 57955
2020-05-20 13:35 DenisChenu Note Added: 57956
2020-05-20 15:17 larjona Note Added: 57966
2020-05-20 20:47 larjona Note Added: 57978
2020-05-21 13:16 DenisChenu Note Added: 57984