View Issue Details

This bug affects 1 person(s).
 6
IDProjectCategoryView StatusLast Update
08519Bug reportsInstallationpublic2014-01-25 17:22
Reporteri_meyer Assigned Toc_schmitz  
PrioritynormalSeverityminor 
Status closedResolutionfixed 
Product Version2.05+ 
Fixed in Version2.05+ 
Summary08519: Error during upgrade vom 2.00 to 2.05 on a PostgreSQL-Backend
Description

While training an update from 2.00 (latest build) to 2.05 (build 131219) the following error occured:

CDbCommand failed to execute the SQL statement: SQLSTATE[25P02]: In failed sql transaction: 7 ERROR: current transaction is aborted, commands ignored until end of transaction block

The update was performed as follows:

  • Unpack new 2.05-release in a fresh directory in the web directory
  • Copy config.php + upload-dir from 2.00 installation
  • Start admin page on the new 2.05 directory from browser
  • Click "next"
Additional Information

No database-prefix is used.
DBVersion: 164 (after trying the update)

TagsNo tags attached.
Bug heat6
Complete LimeSurvey version number (& build)131219
I will donate to the project if issue is resolvedNo
BrowserFirefox
Database type & versionPostgreSQL 9.1
Server OS (if known)Ubuntu-Linux
Webserver software & version (if known)Apache 2
PHP Version5.3

Users monitoring this issue

There are no users monitoring this issue.

Activities

riqcles

riqcles

2014-01-13 15:55

reporter   ~27838

I have the same problem with easyphp and Postgresql 9.1
error message when trying to contact: update.limesurvey.org / updates

If I typed in the address: update.limesurvey.org / updates
I get this:
1:53:33 p.m. "}}

how to build value found?
I do not have version 1.92 + on my server.

c_schmitz

c_schmitz

2014-01-17 15:43

administrator   ~27962

Fix committed to master branch: http://bugs.limesurvey.org/plugin.php?page=Source/view&id=13706

c_schmitz

c_schmitz

2014-01-17 16:16

administrator   ~27963

Fix committed to master branch: http://bugs.limesurvey.org/plugin.php?page=Source/view&id=13708

dichteros

dichteros

2014-01-17 20:34

reporter   ~27970

I updated to Build 140116 via Comfort-Update and got the same Error.

After Fixing as recommended here, now i get this Error:

"CDbCommand failed to execute the SQL statement: SQLSTATE[42804]: Datatype mismatch: 7 ERROR: column "active" is of type boolean but expression is of type integer
LINE 1: ...NSERT INTO "plugins" ("name", "active") VALUES ('Authdb', 1)
^
HINT: You will need to rewrite or cast the expression."

What should i do?

c_schmitz

c_schmitz

2014-01-17 21:14

administrator   ~27973

When exactly do you get this error? Did you activate any plugins?

dichteros

dichteros

2014-01-17 21:19

reporter   ~27974

No I just updatet to Build 140116 via Comfort-Update. Step 4 showed a white screen.
After that i got the error:

CDbCommand failed to execute the SQL statement: SQLSTATE[25P02]: In failed sql transaction: 7 ERROR: current transaction is aborted, commands ignored until end of transaction block.

After fixing it with the procedure her (08519) i get this errors:

"CDbCommand failed to execute the SQL statement: SQLSTATE[42804]: Datatype mismatch: 7 ERROR: column "active" is of type boolean but expression is of type integer
LINE 1: ...NSERT INTO "plugins" ("name", "active") VALUES ('Authdb', 1)
^
HINT: You will need to rewrite or cast the expression."

dichteros

dichteros

2014-01-17 21:43

reporter   ~27976

The error appears, when i want to get to the admin-panel

c_schmitz

c_schmitz

2014-01-17 22:24

administrator   ~27977

Did you apply both patches below?

dichteros

dichteros

2014-01-17 23:00

reporter   ~27978

yes, i did.

c_schmitz

c_schmitz

2014-01-18 01:13

administrator   ~27979

Fix committed to master branch: http://bugs.limesurvey.org/plugin.php?page=Source/view&id=13712

c_schmitz

c_schmitz

2014-01-18 01:14

administrator   ~27980

Did some more changes - please let me know if it is alright now.

dichteros

dichteros

2014-01-19 23:55

reporter   ~28016

Still get the error:

"CDbCommand failed to execute the SQL statement: SQLSTATE[42804]: Datatype mismatch: 7 ERROR: column "active" is of type boolean but expression is of type integer
LINE 1: ...NSERT INTO "plugins" ("name", "active") VALUES ('Authdb', 1)
^
HINT: You will need to rewrite or cast the expression."

With an empty postgress-database everything works.
But if i refer to the existing postgress-database i get the error.

The dump of the postgress is the dump after the failed update.
What should i do to get to the data of the after-update-failed-dump in lime survey?

c_schmitz

c_schmitz

2014-01-20 01:38

administrator   ~28017

I think I found the issue.

It should be enough to change the column 'active' in table 'plugins' to type 'integer'.

dichteros

dichteros

2014-01-20 11:31

reporter   ~28022

Did that.
Now i have access to my old label set and to my user-administration.
But the old survey are not shown.

dichteros

dichteros

2014-01-20 11:49

reporter   ~28025

After i did that, If i install a ne LS i get this Error-Message:
"The table "{{permissions}}" for active record class "Permission" cannot be found in the database."

If i install with an empty database and edit the config-file later to a cleaned-up dump i only get access to the label set and the user-administration

dichteros

dichteros

2014-01-20 12:19

reporter   ~28027

Data-Integrity-Check:

Couldn't make backup of the survey table. Please try again. The database reported the following error:
exception 'CDbException' with message 'CDbCommand failed to execute the SQL statement: SQLSTATE[42P01]: Undefined table: 7 ERROR: relation "umfragen_survey_958369" does not exist' in /var/www/forschungbackup4/framework/db/CDbCommand.php:358 Stack trace: #0 /var/www/forschungbackup4/framework/db/CDbCommand.php(1336): CDbCommand->execute() #1 /var/www/forschungbackup4/application/controllers/admin/checkintegrity.php(382): CDbCommand->renameTable('{{survey_958369...', '{{old_survey_95...') #2 /var/www/forschungbackup4/application/controllers/admin/checkintegrity.php(41): CheckIntegrity->_checkintegrity() #3 [internal function]: CheckIntegrity->index() #4 /var/www/forschungbackup4/framework/web/actions/CAction.php(108): ReflectionMethod->invokeArgs(Object(CheckIntegrity), Array) #5 /var/www/forschungbackup4/application/core/Survey_Common_Action.php(99): CAction->runWithParamsInternal(Object(CheckIntegrity), Object(ReflectionMethod), Array) #6 /var/www/forschungbackup4/framework/web/CController.php(308): Survey_Common_Action->runWithParams(Array) #7 /var/www/forschungbackup4/framework/web/CController.php(286): CController->runAction(Object(CheckIntegrity)) #8 /var/www/forschungbackup4/framework/web/CController.php(265): CController->runActionWithFilters(Object(CheckIntegrity), Array) #9 /var/www/forschungbackup4/application/controllers/AdminController.php(169): CController->run('checkintegrity') #10 /var/www/forschungbackup4/framework/web/CWebApplication.php(282): AdminController->run('checkintegrity') #11 /var/www/forschungbackup4/framework/web/CWebApplication.php(141): CWebApplication->runController('admin/checkinte...') #12 /var/www/forschungbackup4/framework/base/CApplication.php(180): CWebApplication->processRequest() #13 /var/www/forschungbackup4/index.php(190): CApplication->run() #14 {main}

dichteros

dichteros

2014-01-20 19:12

reporter   ~28030

I can access the formular of each survey, modifying the specific "sid" in the url, but the surveys are not shown up in the admin Panel.

dichteros

dichteros

2014-01-21 11:33

reporter   ~28037

I
A) Set up a new LS with new postgress-database "test" ans install in folder /var/www/test/.
B) Set up a second new LS with your old postgres-database and install in /var/www/old/.
C) Dump the empty db "test"
D) Open db "test" and search for "permissions", copy the SQL-Code with "permissions" inside and execute it in the db-old.
E) Now the old surveys appear in the admin panel in /var/www/old/

if i click on them i get this error:

"Fehler

No permission"

dichteros

dichteros

2014-01-21 12:51

reporter   ~28042

Reminder sent to: c_schmitz

As u see, the problem is still there.
Is there any possibility, to get the old surveys (they are imported in a new database) shown and worked in the adminpanel?

Should I need to edit the dump?

c_schmitz

c_schmitz

2014-01-21 13:23

administrator   ~28043

it looks to me like the rest of your problems are coming from incomplete update tries.

You will need to revert your database to the state from before you tried the update for the first time (I hope you created a backup before you updated).

Then do the update again.

dichteros

dichteros

2014-01-21 13:32

reporter   ~28044

Actually i was so dumb, to not dump the db before.
Any method to rescue The Data?

c_schmitz

c_schmitz

2014-01-21 14:32

administrator   ~28047

YOu will need to add permissions in the permission table. Check the permissions on the new installation then try to replicate that in the old one.

dichteros

dichteros

2014-01-21 17:39

reporter   ~28051

Thanks a lot. Here the way i did it:

for each survey execute in Postgres this SQL:

INSERT INTO permissions VALUES (SID_OF_YOUR_SURVEY, 1, 'responses', 1, 1, 1, 1, 1, 1, 'survey', NUMBER_STARTING_FROM_100);
INSERT INTO permissions VALUES (SID_OF_YOUR_SURVEY, 1, 'assessments', 1, 1, 1, 1, 0, 0, 'survey', NUMBER_STARTING_FROM_x+1);
INSERT INTO permissions VALUES (SID_OF_YOUR_SURVEY, 1, 'translations', 0, 1, 1, 0, 0, 0, 'survey', NUMBER_STARTING_FROM_x+2);
INSERT INTO permissions VALUES (SID_OF_YOUR_SURVEY, 1, 'statistics', 0, 1, 0, 0, 0, 0, 'survey', NUMBER_STARTING_FROM_x+3);
INSERT INTO permissions VALUES (SID_OF_YOUR_SURVEY, 1, 'quotas', 1, 1, 1, 1, 0, 0, 'survey', NUMBER_STARTING_FROM_x+4);
INSERT INTO permissions VALUES (
SID_OF_YOUR_SURVEY, 1, 'surveylocale', 0, 1, 1, 0, 0, 0, 'survey', NUMBER_STARTING_FROM_x+5);
INSERT INTO permissions VALUES (SID_OF_YOUR_SURVEY, 1, 'survey', 0, 1, 0, 1, 0, 0, 'survey' 'survey', NUMBER_STARTING_FROM_x+6);
INSERT INTO permissions VALUES (
SID_OF_YOUR_SURVEY, 1, 'surveysettings', 0, 1, 1, 0, 0, 0, 'survey', NUMBER_STARTING_FROM_x+7);
INSERT INTO permissions VALUES (SID_OF_YOUR_SURVEY, 1, 'surveysecurity', 1, 1, 1, 1, 0, 0, 'survey', NUMBER_STARTING_FROM_x+8);
INSERT INTO permissions VALUES (
SID_OF_YOUR_SURVEY, 1, 'surveyactivation', 0, 0, 1, 0, 0, 0, 'survey', NUMBER_STARTING_FROM_x+9);
INSERT INTO permissions VALUES (SID_OF_YOUR_SURVEY, 1, 'surveycontent', 1, 1, 1, 1, 1, 1, 'survey', NUMBER_STARTING_FROM_x+10);
INSERT INTO permissions VALUES (
SID_OF_YOUR_SURVEY, 1, 'tokens', 1, 1, 1, 1, 1, 1, 'survey', NUMBER_STARTING_FROM_x+11);

c_schmitz

c_schmitz

2014-01-22 09:04

administrator   ~28066

Fix committed to master branch: http://bugs.limesurvey.org/plugin.php?page=Source/view&id=13733

c_schmitz

c_schmitz

2014-01-25 17:22

administrator   ~28154

2.05+ Build 140125 released

Related Changesets

LimeSurvey: master 5798462f

2014-01-17 14:44:05

c_schmitz

Details Diff
Fixed issue 08519: Error during upgrade vom 2.00 to 2.05 using PostgreSQL Affected Issues
08519
mod - application/helpers/update/updatedb_helper.php Diff File

LimeSurvey: master cb2bf2eb

2014-01-17 15:06:36

c_schmitz

Details Diff
Fixed issue 08519: Error during upgrade vom 2.00 to 2.05 using PostgreSQL
Dev Also fixed non-appearing inputs for username and password in login mask when using Postgres
Affected Issues
08519
mod - application/libraries/PluginManager/PluginManager.php Diff File

LimeSurvey: master aac90ac5

2014-01-18 00:14:05

c_schmitz

Details Diff
Fixed issue 08519: Error during upgrade vom 2.00 to 2.05 using PostgreSQL Affected Issues
08519
mod - application/controllers/admin/authentication.php Diff File
mod - application/views/installer/precheck_view.php Diff File
mod - installer/sql/create-pgsql.sql Diff File

LimeSurvey: master 0abd4aef

2014-01-20 00:46:02

c_schmitz

Details Diff
Fixed issue 08519: Error during upgrade vom 2.00 to 2.05 using PostgreSQL Affected Issues
08519
mod - application/config/version.php Diff File
mod - application/helpers/update/updatedb_helper.php Diff File
mod - installer/sql/create-mssql.sql Diff File
mod - installer/sql/create-mysql.sql Diff File
mod - installer/sql/create-pgsql.sql Diff File

Issue History

Date Modified Username Field Change
2014-01-08 16:36 i_meyer New Issue
2014-01-13 15:55 riqcles Note Added: 27838
2014-01-17 15:43 c_schmitz Assigned To => c_schmitz
2014-01-17 15:43 c_schmitz Status new => assigned
2014-01-17 15:43 c_schmitz Changeset attached => LimeSurvey master 5798462f
2014-01-17 15:43 c_schmitz Note Added: 27962
2014-01-17 15:43 c_schmitz Resolution open => fixed
2014-01-17 15:44 c_schmitz Status assigned => resolved
2014-01-17 15:44 c_schmitz Fixed in Version => 2.05+
2014-01-17 16:16 c_schmitz Changeset attached => LimeSurvey master cb2bf2eb
2014-01-17 16:16 c_schmitz Note Added: 27963
2014-01-17 20:34 dichteros Note Added: 27970
2014-01-17 21:14 c_schmitz Note Added: 27973
2014-01-17 21:19 dichteros Note Added: 27974
2014-01-17 21:43 dichteros Note Added: 27976
2014-01-17 22:24 c_schmitz Note Added: 27977
2014-01-17 23:00 dichteros Note Added: 27978
2014-01-18 01:13 c_schmitz Changeset attached => LimeSurvey master aac90ac5
2014-01-18 01:13 c_schmitz Note Added: 27979
2014-01-18 01:14 c_schmitz Note Added: 27980
2014-01-19 23:55 dichteros Note Added: 28016
2014-01-20 01:38 c_schmitz Note Added: 28017
2014-01-20 11:31 dichteros Note Added: 28022
2014-01-20 11:49 dichteros Note Added: 28025
2014-01-20 12:19 dichteros Note Added: 28027
2014-01-20 19:12 dichteros Note Added: 28030
2014-01-21 11:33 dichteros Note Added: 28037
2014-01-21 12:51 dichteros Note Added: 28042
2014-01-21 13:23 c_schmitz Note Added: 28043
2014-01-21 13:32 dichteros Note Added: 28044
2014-01-21 14:32 c_schmitz Note Added: 28047
2014-01-21 17:39 dichteros Note Added: 28051
2014-01-22 09:04 c_schmitz Changeset attached => LimeSurvey master 0abd4aef
2014-01-22 09:04 c_schmitz Note Added: 28066
2014-01-25 17:22 c_schmitz Note Added: 28154
2014-01-25 17:22 c_schmitz Status resolved => closed