View Issue Details

IDProjectCategoryView StatusLast Update
12588Bug reports[All Projects] Installationpublic2017-10-17 13:29
ReporterfvanderstarreAssigned To 
PrioritynoneSeveritycrash 
Status newResolutionopen 
Product Version3.0.0-beta.x 
Target VersionFixed in Version 
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.

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