View Issue Details
|ID||Project||Category||View Status||Date Submitted||Last Update|
|16311||Bug reports||Question editor||public||2020-05-20 02:29||2020-05-21 13:16|
|Summary||16311: Problem saving questions after upgrade: qid duplicated (conflicts with existing surveys/questions in the database)|
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.
CDbCommand failed to execute the SQL statement: SQLSTATE: 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()
(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.
|Tags||No tags attached.|
|Complete LimeSurvey version number (& build)||4.2.4+200520|
|I will donate to the project if issue is resolved||No|
|Database & DB-Version||postgresql-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 Version||php 7.0.33 (latest Debian 9 package), php7.0-fpm|
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.
This is somehow related to the bug https://bugs.limesurvey.org/view.php?id=16145
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.
Oh. I got confused because the 4.x version is shown as "stable release" in the download section of limesurvey.org
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).
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:
with debug on, and trying to import the .lsa file, it says:
698 $aFiles = $pclzip->listContent();
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.
Yes, it's a know issue … sorry …
|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|