20771: Update_700 re-run irreversibly destroys timing data and ranking subquestions; migrated 6.x surveys then fail with 500 DB error

This issue affects 1 person(s).

ID	Project	Category	View Status	Date Submitted	Last Update
20771	Bug reports	Installation	public	2026-10-16 07:06	2026-10-16 09:30

Reporter	watappo	Assigned To	c_schmitz
Priority	none	Severity	partial_block
Status	resolved	Resolution	fixed
Product Version	7.3.x

Summary	20771: Update_700 re-run irreversibly destroys timing data and ranking subquestions; migrated 6.x surveys then fail with 500 DB error
Description	When Update_700 (6.x -> 7.x migration, DBVersion 651 -> 700) runs on a database where some or all tables are already migrated, data is deleted without any error. What is lost: Timing data. Already migrated tables "limetimings<sid>" and "lime_oldtimings<sid><date>" are replaced by EMPTY tables "lime<sid>" and "limeold<sid>_<date>". The original rows are dropped. Ranking questions. All subquestions of ranking (type R) questions are deleted and cannot be rebuilt (subquestions 3 -> 0; the answers were already removed by the first run). Response tables (limeresponses<sid>) are not affected. The damage cannot be undone inside LimeSurvey: the original tables are dropped and no copy is kept, so the only way back is a backup taken before the update. Impact on a real site: our staging server (updated from 6.x to 7.x on 2026-06-20, now 7.3.0) lost timing and ranking data in the same way. Since then, opening any survey that was created in 6.x as a respondent always shows: "500: Internal Server Error - Database error!" So the surveys inherited from 6.x can no longer be used. (This error stays for months, so it is not the 1-hour schema cache problem described at the end.) This can happen in real life, not only in a test: (a) An update stops in the middle (web timeout, killed process) and the admin starts the update again. Depending on where it stopped, the second run ends with "Database has been successfully upgraded to version 717" and NO error, but the tables that were migrated before the stop are now empty. The admin does not notice. (b) DBVersion is below 700 again after a finished migration (for example after a partial DB restore). Cause (application/helpers/update/updates/Update_700.php, line numbers from 7.5.1 / master; the same code is in 7.3.0 and 7.4.0): getTimingScript() (line ~1199) finds timing tables with TABLE_NAME LIKE '%timings%'. This also matches the NEW name "limetimings<sid>". REPLACE() then makes the new name "lime_<sid>". These tables are not "handled" by the column conversion, so the loop at lines ~2310-2316 runs CREATE (empty copy with the new name) and then DROP of the original table. No data is copied. up() (line ~2015) first runs deleteRankingSubquestions() (line ~1376), which deletes ALL subquestions of ranking questions without any condition. The answers that are needed to rebuild them were already deleted at the end of the first run. Response tables are found with 'survey[0-9]', so already migrated "responses" tables are skipped. This is why only timings (and ranking subquestions) are lost. Expected result: Update_700 should skip tables that are already migrated, or stop with a clear error. It should never drop a table that has rows without copying them. Ranking subquestions should only be deleted when there are answers to rebuild them from. Actual result: Timing data and ranking subquestions are deleted silently. In case (a) there is no error message at all. Ideas for a fix: getTimingScript(): match only the old name pattern (for example "survey_%_timings"), not "%timings%". Before CREATE/DROP of a not handled table, check if it has rows. If yes, copy the data or throw an error. deleteRankingSubquestions(): only delete when the ranking question still has rows in {{answers}}. Related problems found in the same tests (I can file them separately if you want): After "kill -9" during "console.php updatedb", maintenancemode stays "hard", even after a successful second run. CLI updatedb does not clear the web schema cache (tmp/runtime/cache). console.php sets YII_DEBUG=true, so Yii::app()->cache->flush() only flushes CDummyCache. If a web page was opened during the update (for example the "update is already in progress" page), the web side caches the old (651) schema for up to 3600 s (schemaCachingDuration) and shows 500 errors after the update: Property "Survey.preselectnoanswer" is not defined / Unknown column 'survey.nokeyboard'.
Steps To Reproduce	Test setup: LimeSurvey 6.17.19 (DBVersion 651), MariaDB 10.5, prefix "lime_", update with CLI. Survey 111111: active, timings ON, 3 responses, 1 ranking question with 3 answers. Survey 222222: deactivated (old_survey222222<date> and old_survey_222222timings<date>, 3 rows each), 1 ranking question. Survey 333333: not active, 1 ranking question. === Case A: update stops in the middle, then run again (no error) === Replace the files with 7.3.0. php application/commands/console.php updatedb Kill the process (kill -9) after the archived tables are migrated, but before the new table for 111111 is created (between two tables). State now: DBVersion 651, lime_old_timings222222<date> = 3 rows, survey 111111 not migrated yet, maintenancemode = hard. php application/commands/console.php updatedb (again) Output: "Database has been successfully upgraded to version 717" Result: lime_old_timings222222<date> (3 rows) is gone new table lime_old222222<date> with 0 rows survey 111111 is OK (it was not migrated before the stop) maintenancemode is still "hard" === Case B: run Update_700 again after a finished update === php application/commands/console.php updatedb (651 -> 717, all OK) UPDATE lime_settings_global SET stg_value='651' WHERE stg_name='DBVersion'; php application/commands/console.php updatedb (It stops at Update_712 with "Duplicate column name 'preselectnoanswer'". This is expected and is not the point. The data is already lost by Update_700.) Result (table list and row counts, before -> after): lime_timings_111111 (4 rows) -> gone, new lime_111111 (0 rows) lime_old_timings222222<date> (3 rows) -> gone, new lime_old222222<date> (0 rows) ranking subquestions of 111111, 222222, 333333: 3 -> 0 (all) lime_responses_111111: no change Control test: same as case B, but set DBVersion to 700 (so only Update_701-711 run). No data change. So the loss comes from Update_700. Attached files (server paths and DB name are masked): t4b_mid.txt / t4b_after.txt: table list, row counts and ranking subquestions for case A (after the stop / after the second run) t4b_second.log: output of the second run in case A (no error) t2_before.txt / t2_after.txt: the same for case B (before / after the second run) t2_updatedb.log: output of the second run in case B (stops at Update_712, see step 3) I cannot attach a test survey (.lss/.lsa): this bug is about migrating an existing 6.x database with responses, timings and archived tables, so a survey file alone cannot show it, and I no longer have the 6.x environment.
Tags	No tags attached.
Attached Files	t2_after.txt (1,226 bytes) ## DBVersion 711 ## Tables and row counts lime_111111 0 lime_old_222222_20260927233210 0 lime_old_questions_222222_20260927233210 10 lime_old_responses_222222_20260927233210 3 lime_old_tokens_222222_20260927233210 3 lime_responses_111111 4 lime_tokens_111111 4 lime_tokens_333333 3 ## Columns lime_111111 id,interviewtime,G3time,Q11time,Q17time,Q18time,Q19time,G4time,Q20time lime_old_222222_20260927233210 id,interviewtime,G5time,Q21time,Q27time,Q28time,Q29time,G6time,Q30time lime_old_questions_222222_20260927233210 sid,gid,qid,parent_qid,type lime_old_responses_222222_20260927233210 id,token,submitdate,lastpage,startlanguage,seed,startdate,datestamp,ipaddr,Q21_S22,Q21_S23,Q21_S24,Q21_S25,Q21_S26,Q27,Q28,Q29_S44,Q29_S45,Q29_S46,Q30 lime_old_tokens_222222_20260927233210 tid,participant_id,firstname,lastname,email,emailstatus,token,language,blacklisted,sent,remindersent,remindercount,completed,usesleft,validfrom,validuntil,mpid lime_responses_111111 id,token,submitdate,lastpage,startlanguage,seed,startdate,datestamp,ipaddr,Q11_S12,Q11_S13,Q11_S14,Q11_S15,Q11_S16,Q17,Q18,Q20,Q19,quota_exit ## Ranking questions (sid, qid, title, subquestions, answers) 111111 19 G01Q04 0 0 222222 29 G01Q04 0 0 333333 39 G01Q04 0 0 t2_after.txt (1,226 bytes) t2_before.txt (1,258 bytes) ## DBVersion 717 ## Tables and row counts lime_old_questions_222222_20260927233210 10 lime_old_responses_222222_20260927233210 3 lime_old_timings_222222_20260927233210 3 lime_old_tokens_222222_20260927233210 3 lime_responses_111111 4 lime_timings_111111 4 lime_tokens_111111 4 lime_tokens_333333 3 ## Columns lime_old_questions_222222_20260927233210 sid,gid,qid,parent_qid,type lime_old_responses_222222_20260927233210 id,token,submitdate,lastpage,startlanguage,seed,startdate,datestamp,ipaddr,Q21_S22,Q21_S23,Q21_S24,Q21_S25,Q21_S26,Q27,Q28,Q29_S44,Q29_S45,Q29_S46,Q30 lime_old_timings_222222_20260927233210 id,interviewtime,G5time,Q21time,Q27time,Q28time,Q29time,G6time,Q30time lime_old_tokens_222222_20260927233210 tid,participant_id,firstname,lastname,email,emailstatus,token,language,blacklisted,sent,remindersent,remindercount,completed,usesleft,validfrom,validuntil,mpid lime_responses_111111 id,token,submitdate,lastpage,startlanguage,seed,startdate,datestamp,ipaddr,Q11_S12,Q11_S13,Q11_S14,Q11_S15,Q11_S16,Q17,Q18,Q20,Q19,quota_exit lime_timings_111111 id,interviewtime,G3time,Q11time,Q17time,Q18time,Q19time,G4time,Q20time ## Ranking questions (sid, qid, title, subquestions, answers) 111111 19 G01Q04 3 0 222222 29 G01Q04 3 0 333333 39 G01Q04 3 0 t2_before.txt (1,258 bytes) t2_updatedb.log (1,642 bytes) Update mysql:host=localhost;port=3306;dbname=limesurvey_test; with prefix :lime_ from 651 to 717 CDbException: CDbCommand failed to execute the SQL statement: SQLSTATE[42S21]: Column already exists: 1060 Duplicate column name 'preselectnoanswer'. The SQL statement executed was: ALTER TABLE `lime_surveys` ADD `preselectnoanswer` varchar(1) NULL DEFAULT 'I' in /path/to/limesurvey/vendor/yiisoft/yii/framework/db/CDbCommand.php:358 Stack trace: #0 /path/to/limesurvey/vendor/yiisoft/yii/framework/db/CDbCommand.php(1405): CDbCommand->execute() #1 /path/to/limesurvey/application/helpers/update/updatedb_helper.php(3018): CDbCommand->addColumn('{{surveys}}', 'preselectnoansw...', 'string(1) NULL ...') #2 /path/to/limesurvey/application/helpers/update/updates/Update_712.php(15): addColumn('{{surveys}}', 'preselectnoansw...', 'string(1) NULL ...') #3 /path/to/limesurvey/application/helpers/update/DatabaseUpdateBase.php(47): LimeSurvey\Helpers\Update\Update_712->up() #4 /path/to/limesurvey/application/helpers/update/updatedb_helper.php(148): LimeSurvey\Helpers\Update\DatabaseUpdateBase->safeUp() #5 /path/to/limesurvey/application/commands/UpdateDbCommand.php(69): db_upgrade_all(651) #6 /path/to/limesurvey/vendor/yiisoft/yii/framework/console/CConsoleCommandRunner.php(71): UpdateDBCommand->run(Array) #7 /path/to/limesurvey/vendor/yiisoft/yii/framework/console/CConsoleApplication.php(92): CConsoleCommandRunner->run(Array) #8 /path/to/limesurvey/vendor/yiisoft/yii/framework/base/CApplication.php(185): CConsoleApplication->processRequest() #9 /path/to/limesurvey/application/commands/console.php(82): CApplication->run() #10 {main} t2_updatedb.log (1,642 bytes) t4b_after.txt (1,242 bytes) ## DBVersion 717 ## Tables and row counts lime_old_222222_20260927233210 0 lime_old_questions_222222_20260927233210 10 lime_old_responses_222222_20260927233210 3 lime_old_tokens_222222_20260927233210 3 lime_responses_111111 3 lime_timings_111111 3 lime_tokens_111111 3 lime_tokens_333333 3 ## Columns lime_old_222222_20260927233210 id,interviewtime,G5time,Q21time,Q27time,Q28time,Q29time,G6time,Q30time lime_old_questions_222222_20260927233210 sid,gid,qid,parent_qid,type lime_old_responses_222222_20260927233210 id,token,submitdate,lastpage,startlanguage,seed,startdate,datestamp,ipaddr,Q21_S22,Q21_S23,Q21_S24,Q21_S25,Q21_S26,Q27,Q28,Q29_S44,Q29_S45,Q29_S46,Q30 lime_old_tokens_222222_20260927233210 tid,participant_id,firstname,lastname,email,emailstatus,token,language,blacklisted,sent,remindersent,remindercount,completed,usesleft,validfrom,validuntil,mpid lime_responses_111111 id,token,submitdate,lastpage,startlanguage,seed,startdate,datestamp,ipaddr,Q11_S12,Q11_S13,Q11_S14,Q11_S15,Q11_S16,Q17,Q18,Q20,Q19,quota_exit lime_timings_111111 id,interviewtime,G3time,Q11time,Q17time,Q18time,Q19time,G4time,Q20time ## Ranking questions (sid, qid, title, subquestions, answers) 111111 19 G01Q04 3 0 222222 29 G01Q04 3 0 333333 39 G01Q04 3 0 t4b_after.txt (1,242 bytes)

Bug heat	4
Complete LimeSurvey version number (& build)	7.3.x
I will donate to the project if issue is resolved
Browser
Database type & version
Server OS (if known)
Webserver software & version (if known)
PHP Version
Git branch / Pull Request URL

User List	There are no users monitoring this issue.

c_schmitz 2026-10-16 08:09 administrator ~86486	It is not possible to continue a migration that was half-done, because necesseray references are lost. If it fails you have to restore the previous DB backup and try again.

watappo 2026-10-16 08:19 reporter ~86487	Thank you for the answer. I understand that a half-done migration cannot be continued, and that restoring the backup is the correct way. My point is a little different: LimeSurvey does not stop the admin from doing it, and it reports success. In case A, the second run printed "Database has been successfully upgraded to version 717" with no error. After a half-done migration, DBVersion is still 651 (see t4b_mid.txt). Then AdminController::run() starts the update again by itself ("Try a silent update first", `db_upgrade_all()`) as soon as anyone opens any admin page, even the login page. The admin does not choose to "continue"; it just happens. So the admin has no chance to know that a restore is needed. The data loss is found much later (in our case, months later), when the backup may be gone. Could Update_700 at least detect this state and stop with a clear error? For example: before Update700 runs, check if any table with a new 7.x name already exists (`responses<sid>`,`timings_<sid>`,`oldresponses...`,`oldtimings...`), or set a flag when Update_700 starts, and refuse to run it again until the flag is cleared. Then the admin sees "the migration was interrupted, please restore your backup" instead of silent data loss. Some parts of my report were lost when I submitted it. I add them here. 1. Missing attachments Two files could not be uploaded (anti-spam limit). I attach them to this note: t4b_mid.txt: case A, state after the stop (before the second run) t4b_second.log: case A, output of the second run (no error) 2. Empty custom fields Complete LimeSurvey version number (& build): 7.3.0 (build 260922). The same code is in 7.4.0 (260928), 7.5.1 (261007) and master. Browser: not related (the update was run with the CLI: `php application/commands/console.php updatedb`) Database type & version: MariaDB 10.5.26 (MySQL driver) Server OS: Linux (shared hosting) PHP Version: 8.2.33 3. Names broken by Markdown Markdown removed some underscores. The correct names are (shown text = correct name): Description: `limetimings<sid>` = `lime_timings_<sid>` `lime_oldtimings<sid><date>` = `lime_old_timings_<sid>_<date>` `lime<sid>` = `lime_<sid>` `limeold<sid>_<date>` = `lime_old_<sid>_<date>` `limeresponses<sid>` = `lime_responses_<sid>` `survey[0-9]` = `survey_[0-9]` `responses` = `responses_` Fix idea: `survey\_%\_timings` (SQL LIKE pattern with escaped underscores) Steps to reproduce: `old_survey222222<date>` = `old_survey_222222_<date>` `old_survey_222222timings<date>` = `old_survey_222222_timings_<date>` `lime_old_timings222222<date>` = `lime_old_timings_222222_<date>` `lime_old222222<date>` = `lime_old_222222_<date>` t4b_mid.txt (1,411 bytes) ## DBVersion 651 ## Tables and row counts lime_old_questions_222222_20260927233210 10 lime_old_responses_222222_20260927233210 3 lime_old_timings_222222_20260927233210 3 lime_old_tokens_222222_20260927233210 3 lime_survey_111111 3 lime_survey_111111_timings 3 lime_tokens_111111 3 lime_tokens_333333 3 ## Columns lime_old_questions_222222_20260927233210 sid,gid,qid,parent_qid,type lime_old_responses_222222_20260927233210 id,token,submitdate,lastpage,startlanguage,seed,startdate,datestamp,ipaddr,Q21_S22,Q21_S23,Q21_S24,Q21_S25,Q21_S26,Q27,Q28,Q29_S44,Q29_S45,Q29_S46,Q30 lime_old_timings_222222_20260927233210 id,interviewtime,G5time,Q21time,Q27time,Q28time,Q29time,G6time,Q30time lime_old_tokens_222222_20260927233210 tid,participant_id,firstname,lastname,email,emailstatus,token,language,blacklisted,sent,remindersent,remindercount,completed,usesleft,validfrom,validuntil,mpid lime_survey_111111 id,token,submitdate,lastpage,startlanguage,seed,startdate,datestamp,ipaddr,111111X3X11SQ001,111111X3X11SQ002,111111X3X11SQ003,111111X3X11SQ004,111111X3X11SQ005,111111X3X17,111111X3X18,111111X3X191,111111X3X192,111111X3X193,111111X4X20 lime_survey_111111_timings id,interviewtime,111111X3time,111111X3X11time,111111X3X17time,111111X3X18time,111111X3X19time,111111X4time,111111X4X20time ## Ranking questions (sid, qid, title, subquestions, answers) 111111 19 G01Q04 3 3 222222 29 G01Q04 3 3 333333 39 G01Q04 3 3 t4b_mid.txt (1,411 bytes) t4b_second.log (153 bytes) Update mysql:host=localhost;port=3306;dbname=limesurvey_test; with prefix :lime_ from 651 to 717 Database has been successfully upgraded to version 717 t4b_second.log (153 bytes)

c_schmitz 2026-10-16 08:41 administrator ~86488	Fix committed to master branch: http://bugs.limesurvey.org/plugin.php?page=Source/view&id=40879

c_schmitz 2026-10-16 08:41 administrator ~86489	Thank you for the detailed follow-up. You are right: LimeSurvey should not silently run the update again. If a database update fails or is interrupted, LimeSurvey now remembers this. It refuses to run the update again and asks the admin to restore the backup made before the update.

watappo 2026-10-16 09:05 reporter ~86490	Thank you very much for reconsidering the issue and for the quick fix. This solves the problem that worried us most.


c_schmitz 2026-10-16 09:30 administrator ~86491	Please don't close the issue. We will close it once the fix is released.

LimeSurvey: master 368636da 2026-10-16 08:40 c_schmitz Details Diff	Fixed issue 20771: A database update that failed or was interrupted could be run again and silently destroy data Dev A DBUpdateIncomplete marker in settings_global now blocks further updates until the pre-update backup is restored	Affected Issues 20771
	mod - application/commands/UpdateDbCommand.php	Diff File
	mod - application/helpers/update/update_helper.php	Diff File
	mod - application/helpers/update/updatedb_helper.php	Diff File
	mod - application/views/admin/databaseupdate/verify.php	Diff File
	mod - tests/TestHelper.php	Diff File
	mod - tests/unit/helpers/UpdateDbHelperTest.php	Diff File

Date Modified	Username	Field	Change
2026-10-16 07:06	watappo	New Issue
2026-10-16 07:06	watappo	File Added: t2_after.txt
2026-10-16 07:06	watappo	File Added: t2_before.txt
2026-10-16 07:06	watappo	File Added: t2_updatedb.log
2026-10-16 07:06	watappo	File Added: t4b_after.txt
2026-10-16 08:09	c_schmitz	Assigned To	=> c_schmitz
2026-10-16 08:09	c_schmitz	Status	new => closed
2026-10-16 08:09	c_schmitz	Resolution	open => won't fix
2026-10-16 08:09	c_schmitz	Note Added: 86486
2026-10-16 08:09	c_schmitz	Bug heat	0 => 2
2026-10-16 08:19	watappo	Note Added: 86487
2026-10-16 08:19	watappo	File Added: t4b_mid.txt
2026-10-16 08:19	watappo	File Added: t4b_second.log
2026-10-16 08:19	watappo	Bug heat	2 => 4
2026-10-16 08:33	c_schmitz	Status	closed => assigned
2026-10-16 08:33	c_schmitz	Complete LimeSurvey version number (& build)	=> 7.3.x
2026-10-16 08:41	c_schmitz	Changeset attached	=> LimeSurvey master 368636da
2026-10-16 08:41	c_schmitz	Note Added: 86488
2026-10-16 08:41	c_schmitz	Status	assigned => resolved
2026-10-16 08:41	c_schmitz	Resolution	won't fix => fixed
2026-10-16 08:41	c_schmitz	Note Added: 86489
2026-10-16 09:05	watappo	Note Added: 86490
2026-10-16 09:05	watappo	Status	resolved => closed
2026-10-16 09:30	c_schmitz	Status	closed => resolved
2026-10-16 09:30	c_schmitz	Note Added: 86491

View Issue Details

Users monitoring this issue

Activities

Related Changesets

Issue History