View Issue Details

IDProjectCategoryView StatusLast Update
12588Bug reports[All Projects] Installationpublic2017-08-14 09:07
ReporterfvanderstarreAssigned To 
PrioritynoneSeveritycrash 
Status newResolutionopen 
Product Version3.0.0-beta.x 
Target VersionFixed in Version 
Summary12588: Upgrade from 3.0.0-beta.1 to 3.0.0-beta.2 fails with db error (mssql)
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
TagsNo tags attached.
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
Operating System (Server)Linux RHEL 7
Webserver software & versionApache 2.4.6
PHP Version5.6.31

Activities

fvanderstarre

2017-08-11 13:48

reporter   ~44292

3.0.0-beta.2 : db version 309

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

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.

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