View Issue Details

IDProjectCategoryView StatusLast Update
12588Bug reports[All Projects] Installationpublic2017-10-25 11:12
ReporterfvanderstarreAssigned Toc_schmitz 
PrioritynoneSeveritycrash 
Status closedResolutionfixed 
Product Version3.0.0-beta.x 
Target VersionFixed in Version3.0.0-beta.x 
Summary12588: Upgrade from 3.0.0-beta.1 (or 2.6lts) to 3.0.0-beta.2 fails with db error
DescriptionWhen upgrading from 3.0.0-beta.1 (db version 263) to 3.0.0-beta.2 (db version 3) an error occurs:

An non-recoverable error happened during the update. Error details:
Table "templates" does not have a column named "name".
Steps To ReproducePerform upgrade as mentioned above (on a ms-sql db)
Additional InformationUsing dblib + freetds
Tagsdata types, mssql, update
Complete LimeSurvey version number (& build)3.0.0-beta.2+170810
I will donate to the project if issue is resolvedNo
BrowserAny
Database & DB-VersionMS-SQL 2008 R2 OR MySQL
Operating System (Server)Linux RHEL 7
Webserver software & versionApache 2.4.6
PHP Version5.6.31

Relationships

has duplicate 12799 closed Error when update from 2.6lts 

Activities

fvanderstarre

fvanderstarre

2017-08-11 13:48

reporter   ~44292

3.0.0-beta.2 : db version 309
fvanderstarre

fvanderstarre

2017-08-11 14:48

reporter   ~44293

I set up a SQL server profiler trace - the funny thing is no update statements are performed but somehow an exception is thrown resulting in a transaction rollback. See screen shot from profiler.

LStrace.jpg (187,994 bytes)
fvanderstarre

fvanderstarre

2017-08-14 09:07

reporter   ~44296

I fixed it by rewriting lines 118-122 in updatedb_helper.php:
        if ($iOldDBVersion < 290) {

            $oTransaction = $oDB->beginTransaction();

            // Loop all surveys
            $surveys = Survey::model()->findAll();
            foreach ($surveys as $survey)
            {
                $prefix = Yii::app()->getDb()->tablePrefix;
                $tableName = $prefix . 'survey_' . $survey->sid;
            .... etc.
to:

        if ($iOldDBVersion < 290) {

            $oTransaction = $oDB->beginTransaction();

            // Loop all surveys
                    $surveyquery = "SELECT sid FROM ".dbQuoteID('{{surveys}}');
                    $surveyids = Yii::app()->db->createCommand($surveyquery)->queryAll();
                    foreach ( $surveyids as $surv )
             {
                $prefix = Yii::app()->getDb()->tablePrefix;
                $tableName = $prefix . 'survey_' . $surv['sid'];
              etc.........

Now I'm getting stuck in : function createSurveyMenuTable293($oDB):
    $oDB->createCommand()->createTable('{{surveymenu}}', array(
        "id" => "int NOT NULL AUTO_INCREMENT",
        "parent_id" => "int DEFAULT NULL",
        "survey_id" => "int DEFAULT NULL",
        "order" => "int DEFAULT '0'",
        "level" => "int DEFAULT '0'",
        "title" => "character varying(255) NOT NULL DEFAULT ''",
        "description" => "text ",
        "changed_at" => "timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP",
.........
"AUTO_INCREMENT" is not valid in ms-sql, IDENTITY should be used.
"timestamp" is not a valid data type in ms-sql - I think datetime should be used.
"ON UPDATE " can not be used this way in ms-sql, a separate trigger has to be defined for this functionality. See: https://stackoverflow.com/questions/22594567/sql-server-on-update-set-current-timestamp.
fvanderstarre

fvanderstarre

2017-09-01 10:54

reporter   ~44393

'MEDIUMTEXT' is not a valid ms-sql datatype (creating tables templates & template_configuration) ...
fvanderstarre

fvanderstarre

2017-09-01 13:28

reporter   ~44394

I'd appreciate some developer feedback on the changes I made to updatedb.helper.php to get it working with ms-sql. See attachment... Thx!

updatedb_helper.php (50,883 bytes)
DenisChenu

DenisChenu

2017-10-17 12:26

developer   ~44709

I don't like to use dbQuoteID (and here , it's a table name).

Did you try with
$surveyids = Yii::app()->db->createCommand("SELECT sid FROM {{surveys}}")->queryAll();

Or :
$aSurveyId=Yii::app()->db->createCommand()
    ->select('sid')
    ->from('{{surveys}}')
    ->queryAll();

PS : best is to offer the pull request directly on github :)
fvanderstarre

fvanderstarre

2017-10-17 13:00

reporter   ~44710

Hi Denis,
I'm not a developer, just trying to find out what's happening (and what should be happening maybe...).
But thanks for looking into this.
I think the create-mssql.sql script should be updated as well to get rid of the errors.
DenisChenu

DenisChenu

2017-10-17 13:29

developer   ~44711

You're right about create-mssql.sql .
For “I'm not a developer” : with FOSS : everyione can be a developper ;).

I fix the specific issue with oSurvey, but unsure about the other update : Carsten already update some part.
c_schmitz

c_schmitz

2017-10-17 18:28

administrator   ~44712

Last edited: 2017-10-17 18:32

View 2 revisions

I can't even install the release versions of 3.00 beta 1-3 on MSSQL or Postgres so there will be no fix for these databases.

DenisChenu

DenisChenu

2017-10-17 18:40

developer   ~44713

@c_schmitz : i try (because i need one for testing)
2.6lts to 2.71
2.71 to 3.0RC.

I think the Survey model call broke, but i make a test. Do you know the upgradeTemplateTables298 function , it seems really strange
c_schmitz

c_schmitz

2017-10-17 18:58

administrator   ~44714

I did two fixes for MySQL (see changesets below) and tested the updates from beta1 to beta 3 now. Will test from stable to beta (latest develop) tomorrow.
DenisChenu

DenisChenu

2017-10-19 11:03

developer   ~44730

Update this morning from 2.6lts to last GIT : OK :)
c_schmitz

c_schmitz

2017-10-25 11:12

administrator   ~44823

3.0.0-RC1 Build 171025 released

Related Changesets

LimeSurvey: develop f1347e8a

2017-10-17 17:46:21

c_schmitz

Details Diff
Fixed issue: Update from Beta 1 not possible
Dev Partial fix
mod - application/helpers/update/updatedb_helper.php Diff File

LimeSurvey: develop 52e7b7cf

2017-10-17 18:05:22

c_schmitz

Details Diff
Fixed issue: Update from Beta 1 not possible
Dev MySQL tested
mod - application/helpers/update/updatedb_helper.php Diff File

Issue History

Date Modified Username Field Change
2017-08-11 09:52 fvanderstarre New Issue
2017-08-11 13:48 fvanderstarre Note Added: 44292
2017-08-11 14:48 fvanderstarre File Added: LStrace.jpg
2017-08-11 14:48 fvanderstarre Note Added: 44293
2017-08-14 09:07 fvanderstarre Note Added: 44296
2017-09-01 10:54 fvanderstarre Note Added: 44393
2017-09-01 11:06 fvanderstarre Tag Attached: mssql
2017-09-01 11:06 fvanderstarre Tag Attached: data types
2017-09-01 11:07 fvanderstarre Tag Attached: update
2017-09-01 13:28 fvanderstarre File Added: updatedb_helper.php
2017-09-01 13:28 fvanderstarre Note Added: 44394
2017-10-17 12:12 DenisChenu Relationship added has duplicate 12799
2017-10-17 12:13 DenisChenu Summary Upgrade from 3.0.0-beta.1 to 3.0.0-beta.2 fails with db error (mssql) => Upgrade from 3.0.0-beta.1 (or 2.6lts) to 3.0.0-beta.2 fails with db error
2017-10-17 12:13 DenisChenu Database & DB-Version MS-SQL 2008 R2 => MS-SQL 2008 R2 OR MySQL
2017-10-17 12:26 DenisChenu Note Added: 44709
2017-10-17 13:00 fvanderstarre Note Added: 44710
2017-10-17 13:29 DenisChenu Note Added: 44711
2017-10-17 18:28 c_schmitz Note Added: 44712
2017-10-17 18:28 c_schmitz Assigned To => c_schmitz
2017-10-17 18:28 c_schmitz Status new => assigned
2017-10-17 18:32 c_schmitz Note Edited: 44712 View Revisions
2017-10-17 18:40 DenisChenu Note Added: 44713
2017-10-17 18:54 c_schmitz Changeset attached => LimeSurvey develop f1347e8a
2017-10-17 18:54 c_schmitz Changeset attached => LimeSurvey develop 52e7b7cf
2017-10-17 18:58 c_schmitz Note Added: 44714
2017-10-19 11:03 DenisChenu Note Added: 44730
2017-10-19 17:01 c_schmitz Status assigned => resolved
2017-10-19 17:01 c_schmitz Resolution open => fixed
2017-10-19 17:01 c_schmitz Fixed in Version => 3.0.0-beta.x
2017-10-25 11:12 c_schmitz Note Added: 44823
2017-10-25 11:12 c_schmitz Status resolved => closed