View Issue Details

IDProjectCategoryView StatusLast Update
15806Bug reports[All Projects] _ Unknownpublic2020-02-10 16:40
Reporteradelphi_user Assigned Toc_schmitz  
PriorityhighSeveritymajor 
Status feedbackResolutionopen 
Product Version4.1.0 
Target VersionFixed in Version 
Summary15806: Upgrade from 3.21 to 4.1 - upgrade Database error
Description

Hi,

I have just tried to upgrade LimeSurvey to version 4.1. Unfortunately I cannot upgrade the database. I get the following errors:

First error:

An error occurred during the database update. Error information:
CDbCommand could not execute the SQL statement: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'question' in 'field list'

Second Error:

Ein Fehler trat während des Datenbankupdates auf. Fehlerinformation:
CDbCommand konnte das SQL-Statement nicht ausführen: SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation '='

See more in my screenshots

Steps To Reproduce

I have tried to upgrade Limesurvey from version 3.21.4+200108 to 4.1. The upgrade of my database does not work then.

TagsNo tags attached.
Complete LimeSurvey version number (& build)3.21.4+200108 to 4.1.0+200128
I will donate to the project if issue is resolvedNo
BrowserCHrome, Firefox
Database & DB-Versionmysql Ver 15.1 Distrib 10.1.41-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2
Server OS (if known)Debian 9.11
Webserver software & version (if known)Server version: Apache/2.4.25 (Debian) Server
PHP VersionPHP 7.2.27-1+0~20200123.34+debian9~1.gbp63c0bc

Activities

adelphi_user

adelphi_user

2020-01-31 16:37

reporter  

limesurvey-admin-1.png (24,441 bytes)
limesurvey-admin-1.png (24,441 bytes)
limesurvey-admin-2.png (13,615 bytes)
limesurvey-admin-2.png (13,615 bytes)
limesurvey-admin-3.png (12,582 bytes)
limesurvey-admin-3.png (12,582 bytes)
limesurvey-admin-6.png (314,157 bytes)
limesurvey-admin-5.png (211,221 bytes)
limesurvey-admin-4.png (313,200 bytes)
ollehar

ollehar

2020-01-31 16:38

administrator   ~55600

Can you give us a screenshot of the error you get when debug = 2?

adelphi_user

adelphi_user

2020-01-31 16:41

reporter   ~55601

CDbException
CDbCommand konnte das SQL-Statement nicht ausführen: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'question' in 'field list'. The SQL statement executed was: INSERT INTO question_l10ns (qid, question, help, language) select qid, question, help, language from questions

/srv/www/test/home/www/framework/db/CDbCommand.php(358)

346 {
347 if($this->_connection->enableProfiling)
348 Yii::endProfile('system.db.CDbCommand.execute('.$this->getText().$par.')','system.db.CDbCommand.execute');
349
350 $errorInfo=$e instanceof PDOException ? $e->errorInfo : null;
351 $message=$e->getMessage();
352 Yii::log(Yii::t('yii','CDbCommand::execute() failed: {error}. The SQL statement executed was: {sql}.',
353 array('{error}'=>$message, '{sql}'=>$this->getText().$par)),CLogger::LEVEL_ERROR,'system.db.CDbCommand');
354
355 if(YII_DEBUG)
356 $message.='. The SQL statement executed was: '.$this->getText().$par;
357
358 throw new CDbException(Yii::t('yii','CDbCommand failed to execute the SQL statement: {error}',
359 array('{error}'=>$message)),(int)$e->getCode(),$errorInfo);
360 }
361 }
362
363 /*
364
Executes the SQL statement and returns query result.
365 This method is for executing an SQL query that returns result set.
366
@param array $params input parameters (name=>value) for the SQL execution. This is an alternative
367 to {@link bindParam} and {@link bindValue}. If you have multiple input parameters, passing
368
them in this way can improve the performance. Note that if you pass parameters in this way,
369 you cannot bind parameters or values using {@link bindParam} or {@link bindValue}, and vice versa.
370
Please also note that all values are treated as strings in this case, if you need them to be handled as
Stack Trace
#0
– /srv/www/test/home/www/application/helpers/update/updatedb_helper.php(2451): CDbCommand->execute()
2446 'question' => "text NOT NULL",
2447 'help' => "text",
2448 'language' => "string(20) NOT NULL"
2449 ), $options);
2450 $oDB->createCommand()->createIndex('{{idx1_question_l10ns}}', '{{question_l10ns}}', ['qid', 'language'], true);
2451 $oDB->createCommand("INSERT INTO {{question_l10ns}} (qid, question, help, language) select qid, question, help, language from {{questions}}")->execute();
2452 / questions by rename/insert /
2453 if(Yii::app()->db->schema->getTable('{{questions_update400}}')){
2454 $oDB->createCommand()->dropTable('{{questions_update400}}');
2455 }
2456 $oDB->createCommand()->renameTable('{{questions}}', '{{questions_update400}}');
#1
– /srv/www/test/home/www/application/helpers/update/update_helper.php(31): db_upgrade_all(359)
26 Yii::app()->loadHelper('update/updatedb');
27 if (isset($subaction) && $subaction == "yes") {
28 $header = Yii::app()->getController()->_getAdminHeader(false, true);
29 $header = preg_replace('/<###begin###>/', '', $header);
30 echo $header;
31 $result = db_upgrade_all(intval($currentDBVersion));
32 if ($result) {
33 $data =
34 '<div class="jumbotron message-box">'.
35 '<h2 class="">'.gT('Success').'</h2>'.
36 '<p class="lead">'.
#2
– /srv/www/test/home/www/application/controllers/admin/databaseupdate.php(40): CheckForDBUpgrades("yes")
35 public function db($continue = null)
36 {
37 Yii::app()->loadHelper("update/update");
38 $aData = $aViewUrls = [];
39 if (isset($continue) && $continue == "yes") {
40 $aViewUrls['output'] = CheckForDBUpgrades($continue);
41 $aData['display']['header'] = false;
42 } else {
43 $aData['display']['header'] = true;
44 $aViewUrls['output'] = CheckForDBUpgrades();
45 }
#3
unknown(0): databaseupdate->db("yes")
#4

  • /srv/www/test/home/www/framework/web/actions/CAction.php(109): ReflectionMethod->invokeArgs(databaseupdate, array("yes"))
    #5
  • /srv/www/test/home/www/application/core/Survey_Common_Action.php(86): CAction->runWithParamsInternal(databaseupdate, ReflectionMethod, array("continue" => "yes", "sa" => "db"))
    #6
  • /srv/www/test/home/www/framework/web/CController.php(308): Survey_Common_Action->runWithParams(array("continue" => "yes", "sa" => "db"))
    #7
  • /srv/www/test/home/www/framework/web/CController.php(286): CController->runAction(databaseupdate)
    #8
  • /srv/www/test/home/www/framework/web/CController.php(265): CController->runActionWithFilters(databaseupdate, array())
    #9
  • /srv/www/test/home/www/application/controllers/AdminController.php(165): CController->run("databaseupdate")
    #10
  • /srv/www/test/home/www/framework/web/CWebApplication.php(282): AdminController->run("databaseupdate")
    #11
  • /srv/www/test/home/www/framework/web/CWebApplication.php(141): CWebApplication->runController("admin/databaseupdate/sa/db")
    #12
  • /srv/www/test/home/www/framework/base/CApplication.php(185): CWebApplication->processRequest()
    #13
  • /srv/www/test/home/www/index.php(195): CApplication->run()
    2020-01-31 16:40:58 Apache Yii Framework/1.1.22-dev
adelphi_user

adelphi_user

2020-01-31 16:42

reporter   ~55602

Is that what you need?

ollehar

ollehar

2020-02-03 09:43

administrator   ~55629

Is that what you need?

Yes, thank you. Will have a look.

ollehar

ollehar

2020-02-03 11:07

administrator   ~55632

This will be hard to debug without the complete database. Can you run DESC questions in MySQL and paste the result here? It will contain no sensitive data.

adelphisurveys

adelphisurveys

2020-02-04 11:44

reporter   ~55693

Thank you very much. If you need anything else, please let me know.



limesurvey_sql.png (20,672 bytes)
limesurvey_sql.png (20,672 bytes)
ollehar

ollehar

2020-02-04 13:56

administrator   ~55695

My only explanation is that the update broke in the middle of an update, which is not supposed to be possible since it uses transaction. Do you have a table called questions_update400 in your database?

ollehar

ollehar

2020-02-04 13:59

administrator   ~55696

Also run this SQL: SELECT stg_value FROM settings_global WHERE stg_name = 'DBVersion'. Again, nothing sensitive is in this column, only the current database version.

adelphisurveys

adelphisurveys

2020-02-04 14:55

reporter   ~55698

So I have the following database table "answers_update400", but not the "questions_update400".

Thx



limesurvey_dbversion.png (4,212 bytes)
limesurvey_dbversion.png (4,212 bytes)
ollehar

ollehar

2020-02-04 16:24

administrator   ~55701

Do you have groups_update400?

bismark

bismark

2020-02-04 16:36

reporter   ~55702

olle, imo, transactions will not work here as limesurveys tables are myisam and not innodb

ollehar

ollehar

2020-02-04 16:41

administrator   ~55704

This page might explain the problem: https://dev.mysql.com/doc/refman/8.0/en/implicit-commit.html

adelphisurveys

adelphisurveys

2020-02-04 16:53

reporter   ~55709

"Groups_update400" is not in my database. So what am I supposed to do?

c_schmitz

c_schmitz

2020-02-04 16:58

administrator   ~55710

I am inclined to close this issue for the following reason:
The collation utf8mb4_unicode_ci has been in use for LimeSurvey since 2016, we never used utf8mb4_general_ci so it seems that your database is messed up for some reason (maybe a backup/restore issue or someone manually edited the tables).
Can you tell us which tables/columns are using utf8mb4_general_ci?

adelphisurveys

adelphisurveys

2020-02-05 17:01

reporter   ~55766

Alle Tabellen benutzen "utf8mb4_general_ci". Die Unterschiede sind beim Typ der Datenbank. Dort gibt es die Typen "MyISAM" oder "InnoDB".



LimeSurvey-DB2.png (206,951 bytes)
LimeSurvey-DB.png (261,561 bytes)
adelphisurveys

adelphisurveys

2020-02-05 17:02

reporter   ~55767

All tables use "utf8mb4_general_ci". The differences are in the database type. There are the types "MyISAM" or "InnoDB".

DenisChenu

DenisChenu

2020-02-06 07:56

developer   ~55770

My only explanation is that the update broke in the middle of an update, which is not supposed to be possible since it uses transaction. Do you have a table called questions_update400 in your database?

Seems transaction didn't work good on 400 upgrade … with mysql and debug = 2.

I see an upgrade result where : lime_labels are not updated (broke during update), but lime_question was updated.

Mayvbe we need for this single part : separate in X instcructions (one for each table) 400.1, 400.2 etc …
delete backup db (_400) after transaction.

CarmIT

CarmIT

2020-02-06 08:06

reporter   ~55773

Hello,

we have exactly the same problem here. After the update files have been copied, you can update the DB. Then came the error: An non-recoverable error happened during the update. Error details:
An non-recoverable error happened during the update. Error details:
CDbCommand failed to execute the SQL statement: SQLSTATE[42000]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Explicit value must be specified for identity column in table 'question_themes' either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column.

regards

ollehar

ollehar

2020-02-06 10:21

administrator   ~55781

Seems transaction didn't work good on 400 upgrade … with mysql and debug = 2.

Transactions don't work AT ALL when creating new tables in MySQL. See my link above about implicit commit.

ollehar

ollehar

2020-02-06 10:22

administrator   ~55782

Mayvbe we need for this single part : separate in X instcructions (one for each table) 400.1, 400.2 etc …
delete backup db (_400) after transaction.

Yes, the drop tables commands should happen last in the update.

ollehar

ollehar

2020-02-06 10:23

administrator   ~55783

CDbCommand failed to execute the SQL statement: SQLSTATE[42000]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Explicit value must be specified for identity column in table 'question_themes' either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column.

@CarmIT This is a new error. Do you mind filing a separate bug report for it, please?

CarmIT

CarmIT

2020-02-06 10:45

reporter   ~55784

ok i will create a Bugreport

DenisChenu

DenisChenu

2020-02-06 10:50

developer   ~55785

Transactions don't work AT ALL when creating new tables in MySQL. See my link above about implicit commit.

Right , sorry :)

Yes, the drop tables commands should happen last in the update.

It's a quick solution … my oipinion is more : separate 400 upgrade in multiple upgrade is the best solution …

If we rename : maybe need to check if exist before starting again 400 ?

ollehar

ollehar

2020-02-06 10:59

administrator   ~55786

It's a quick solution … my oipinion is more : separate 400 upgrade in multiple upgrade is the best solution …

Little bit too late now. :|

DenisChenu

DenisChenu

2020-02-06 12:16

developer   ~55789

Last edited: 2020-02-06 12:16

View 2 revisions

Little bit too late now. :|

Too late at the 1st update part … i think mantios have some informlation abouyt this.

I kno DBversion must be an integer : but why not 400.1 for question, 400.2 for group , 400.3 for answers 400.4 for label ?

ollehar

ollehar

2020-02-07 10:23

administrator   ~55795

I kno DBversion must be an integer : but why not 400.1 for question, 400.2 for group , 400.3 for answers 400.4 for label ?

Sounds messy... Would it really add anything?

DenisChenu

DenisChenu

2020-02-07 11:15

developer   ~55796

I make a quick draft pull request …

DenisChenu

DenisChenu

2020-02-07 11:58

developer   ~55797

Open for discussion : https://github.com/LimeSurvey/LimeSurvey/pull/1384

BUT : there are integer between 369 and 400 …

explorerdk

explorerdk

2020-02-10 15:02

reporter   ~55833

I have this same issue as opening poster, I tried to upgrade to Ver4. stable and it stuck at DB Upgrade, with exact same errors, I have had LimeSurvey for years and years and there is 2 collations types in my ver.3, which may be from old old versions of tables.

When I tried updating, it ended up having to be restored, both Site and Database.

As said on Github, I can supply my current database which I restored from, if needed for bug tracking.

Current (Working) Running LimeSurvey: Version 3.17.1+190408

/Steven

ollehar

ollehar

2020-02-10 15:04

administrator   ~55834

Current (Working) Running LimeSurvey: Version 3.17.1+190408

Can you please update to the latest LS3 version and then check your collation again? Thank you.

ollehar

ollehar

2020-02-10 15:58

administrator   ~55838

@explorerdk Can you upload your database dump to this ticket? Mark the message as private if so.

explorerdk

explorerdk

2020-02-10 16:05

reporter   ~55839

@ollehar Do you want me to Update it first or have the one it errored with ?

ollehar

ollehar

2020-02-10 16:07

administrator   ~55840

@explorerdk The one before the update, so we can run the update and test.

explorerdk

explorerdk

2020-02-10 16:31

reporter  

db_fssurv.zip (1,038,817 bytes)
explorerdk

explorerdk

2020-02-10 16:35

reporter   ~55842

"Databasen has been updated from 356 to version 359." I just updated to 3.22. The Database from above is from LimeSurvey: Version 3.17.1+190408.

Ps. @ollehar - can you say when you have retrieved the Database, so I can remove it again.

/Expdk

ollehar

ollehar

2020-02-10 16:36

administrator   ~55843

I have marked the message with the database as private, so if it's OK for you, leave it up for now.

explorerdk

explorerdk

2020-02-10 16:39

reporter   ~55844

@Ollehar, Thanks for that :) I will keep it up, as it is now private. I am now at Version 3.22.2+200204, do you want the DB from here as well ?

ollehar

ollehar

2020-02-10 16:40

administrator   ~55845

@Ollehar, Thanks for that :) I will keep it up, as it is now private. I am now at Version 3.22.2+200204, do you want the DB from here as well ?

No, that's not necessary, I think.

Issue History

Date Modified Username Field Change
2020-01-31 16:37 adelphi_user New Issue
2020-01-31 16:37 adelphi_user File Added: limesurvey-admin-1.png
2020-01-31 16:37 adelphi_user File Added: limesurvey-admin-2.png
2020-01-31 16:37 adelphi_user File Added: limesurvey-admin-3.png
2020-01-31 16:37 adelphi_user File Added: limesurvey-admin-6.png
2020-01-31 16:37 adelphi_user File Added: limesurvey-admin-5.png
2020-01-31 16:37 adelphi_user File Added: limesurvey-admin-4.png
2020-01-31 16:38 ollehar Note Added: 55600
2020-01-31 16:41 adelphi_user Note Added: 55601
2020-01-31 16:42 adelphi_user Note Added: 55602
2020-01-31 16:42 ollehar Assigned To => ollehar
2020-01-31 16:42 ollehar Status new => feedback
2020-02-03 09:43 ollehar Note Added: 55629
2020-02-03 11:07 ollehar Note Added: 55632
2020-02-03 11:10 ollehar Priority none => high
2020-02-03 11:10 ollehar Description Updated View Revisions
2020-02-04 11:44 adelphisurveys File Added: limesurvey_sql.png
2020-02-04 11:44 adelphisurveys Note Added: 55693
2020-02-04 13:56 ollehar Note Added: 55695
2020-02-04 13:59 ollehar Note Added: 55696
2020-02-04 14:55 adelphisurveys File Added: limesurvey_dbversion.png
2020-02-04 14:55 adelphisurveys Note Added: 55698
2020-02-04 16:24 ollehar Note Added: 55701
2020-02-04 16:36 bismark Note Added: 55702
2020-02-04 16:41 ollehar Note Added: 55704
2020-02-04 16:49 ollehar Assigned To ollehar => c_schmitz
2020-02-04 16:53 adelphisurveys Note Added: 55709
2020-02-04 16:58 c_schmitz Note Added: 55710
2020-02-05 17:01 adelphisurveys File Added: LimeSurvey-DB2.png
2020-02-05 17:01 adelphisurveys File Added: LimeSurvey-DB.png
2020-02-05 17:01 adelphisurveys Note Added: 55766
2020-02-05 17:02 adelphisurveys Note Added: 55767
2020-02-06 07:56 DenisChenu Note Added: 55770
2020-02-06 08:06 CarmIT Note Added: 55773
2020-02-06 10:21 ollehar Note Added: 55781
2020-02-06 10:22 ollehar Note Added: 55782
2020-02-06 10:23 ollehar Note Added: 55783
2020-02-06 10:45 CarmIT Note Added: 55784
2020-02-06 10:50 DenisChenu Note Added: 55785
2020-02-06 10:59 ollehar Note Added: 55786
2020-02-06 12:16 DenisChenu Note Added: 55789
2020-02-06 12:16 DenisChenu Note Edited: 55789 View Revisions
2020-02-07 10:23 ollehar Note Added: 55795
2020-02-07 11:15 DenisChenu Note Added: 55796
2020-02-07 11:58 DenisChenu Note Added: 55797
2020-02-10 15:02 explorerdk Note Added: 55833
2020-02-10 15:04 ollehar Note Added: 55834
2020-02-10 15:58 ollehar Note Added: 55838
2020-02-10 16:05 explorerdk Note Added: 55839
2020-02-10 16:07 ollehar Note Added: 55840
2020-02-10 16:31 explorerdk File Added: db_fssurv.zip
2020-02-10 16:35 explorerdk Note Added: 55842
2020-02-10 16:36 ollehar Note Added: 55843
2020-02-10 16:39 explorerdk Note Added: 55844
2020-02-10 16:40 ollehar Note Added: 55845