- Posts: 9
- Thank you received: 0
Welcome to the LimeSurvey Community Forum
Ask the community, share ideas, and connect with other LimeSurvey users!
Possible bug on migrating database to 2.05+
- CrystalBlood
- Topic Author
- Offline
- New Member
Less
More
10 years 4 months ago - 10 years 4 months ago #102630
by CrystalBlood
My Blog: crystalblood.wordpress.com/
Possible bug on migrating database to 2.05+ was created by CrystalBlood
Hello,
I am running Limesurvey Versión 1.90+ Build 9642 and PostgreSQL 8.4.9 on x86_64, compiled by GCC gcc (GCC) 4.4.5 20110214, 64-bit. (This actually is the RPM build by the Postgres people published on their repository).
On the Postgres ecosystem, we have this:
And we have the database limesurvey_db with the lime owner.
Then querying the lime_settings_global table the record "DBVersion" is set to "143".
To proceed with an upgrade, I made a complete copy of the database called limesurvey_db_test.
Then, I prepared a complete new server with Centos 6.5 and extracted the last version of Limesurvey 2.05+ (limesurvey205-build131209.tar.gz to this date).
After installing all the requirements and optional packages:
The wizard reads the parameters to migrate the database, pointed to the limesurvey_db_test and then appears an error without details.
Looking forward the way to debug I changed the limesurvey/application/config/config.php file with debug=>2 and debugsql=>1.
Finally I got this output:
And the Postgres Log
This looks like a bug to me than anything else. It is supposed to be a smooth migration.
Some clues? Or another way to debug? Where are these update instructions?
I am running Limesurvey Versión 1.90+ Build 9642 and PostgreSQL 8.4.9 on x86_64, compiled by GCC gcc (GCC) 4.4.5 20110214, 64-bit. (This actually is the RPM build by the Postgres people published on their repository).
On the Postgres ecosystem, we have this:
Code:
CREATE ROLE lime LOGIN
NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE;
Then querying the lime_settings_global table the record "DBVersion" is set to "143".
To proceed with an upgrade, I made a complete copy of the database called limesurvey_db_test.
Code:
#su - postgres $psql $createdb --template=limesurvey_db --owner=lime limesurvey_db_test
Then, I prepared a complete new server with Centos 6.5 and extracted the last version of Limesurvey 2.05+ (limesurvey205-build131209.tar.gz to this date).
After installing all the requirements and optional packages:
The wizard reads the parameters to migrate the database, pointed to the limesurvey_db_test and then appears an error without details.
Looking forward the way to debug I changed the limesurvey/application/config/config.php file with debug=>2 and debugsql=>1.
Finally I got this output:
Code:
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 SQL statement executed was: ALTER TABLE lime_labels ALTER COLUMN language SET NOT NULL
And the Postgres Log
Code:
ERROR: column "language" is in a primary key STATEMENT: ALTER TABLE lime_labels ALTER COLUMN language DROP NOT NULL ERROR: current transaction is aborted, commands ignored until end of transaction block STATEMENT: ALTER TABLE lime_labels ALTER COLUMN language SET NOT NULL
This looks like a bug to me than anything else. It is supposed to be a smooth migration.
Some clues? Or another way to debug? Where are these update instructions?
My Blog: crystalblood.wordpress.com/
Last edit: 10 years 4 months ago by CrystalBlood.
The topic has been locked.
- CrystalBlood
- Topic Author
- Offline
- New Member
Less
More
- Posts: 9
- Thank you received: 0
10 years 4 months ago #102632
by CrystalBlood
My Blog: crystalblood.wordpress.com/
Replied by CrystalBlood on topic Possible bug on migrating database to 2.05+
I studied the line where these statements were built, in the file ./application/helpers/update/updatedb_helper.php
I compare the final structure that would provide the function alterColumn with this specific call (line 504):
And was exactly the same, so I proceed to comment that line to see how behaves.
But now throws another error on another table:
On Postgres Log:
Is not that straightforward as the documentations says about these versions. There is some recommended old release to migrate first? and then to 2.05?
I compare the final structure that would provide the function alterColumn with this specific call (line 504):
Code:
alterColumn('{{labels}}','language',"{$sVarchar}(20)",false , 'en');
But now throws another error on another table:
Code:
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 SQL statement executed was: UPDATE "lime_user_groups" SET "name"=:name WHERE name is NULL. Bound with :name=''
On Postgres Log:
Code:
ERROR: index "email" does not exist STATEMENT: DROP INDEX "email" ERROR: current transaction is aborted, commands ignored until end of transaction block STATEMENT: UPDATE "lime_user_groups" SET "name"='' WHERE name is NULL
Is not that straightforward as the documentations says about these versions. There is some recommended old release to migrate first? and then to 2.05?
My Blog: crystalblood.wordpress.com/
The topic has been locked.
- CrystalBlood
- Topic Author
- Offline
- New Member
Less
More
- Posts: 9
- Thank you received: 0
10 years 3 months ago #102953
by CrystalBlood
My Blog: crystalblood.wordpress.com/
Replied by CrystalBlood on topic Possible bug on migrating database to 2.05+
Somebody have a clue/advice?
My Blog: crystalblood.wordpress.com/
The topic has been locked.
- atisne
- Offline
- New Member
Less
More
- Posts: 2
- Thank you received: 1
10 years 2 months ago #104774
by atisne
Replied by atisne on topic Possible bug on migrating database to 2.05+
Please report to
bugs.limesurvey.org/view.php?id=8675
.
The following user(s) said Thank You: DenisChenu
The topic has been locked.
- CrystalBlood
- Topic Author
- Offline
- New Member
Less
More
- Posts: 9
- Thank you received: 0
10 years 2 months ago - 10 years 2 months ago #104778
by CrystalBlood
My Blog: crystalblood.wordpress.com/
Replied by CrystalBlood on topic Possible bug on migrating database to 2.05+
Hey! Thanks atisne!
I will try the patch and then I will post it. Ok?
Best regards
I will try the patch and then I will post it. Ok?
Best regards
My Blog: crystalblood.wordpress.com/
Last edit: 10 years 2 months ago by CrystalBlood.
The topic has been locked.
- CrystalBlood
- Topic Author
- Offline
- New Member
Less
More
- Posts: 9
- Thank you received: 0
9 years 5 months ago - 9 years 5 months ago #114389
by CrystalBlood
My Blog: crystalblood.wordpress.com/
Replied by CrystalBlood on topic Possible bug on migrating database to 2.05+
@atisne
Hello,
I finally tried your patch, but now I have some issues with the rest of the migration.
New bug report here: bugs.limesurvey.org/view.php?id=9359
Did you had this kind of output? Theoretically you had the same database structure than I.
Hello,
I finally tried your patch, but now I have some issues with the rest of the migration.
New bug report here: bugs.limesurvey.org/view.php?id=9359
Did you had this kind of output? Theoretically you had the same database structure than I.
Code:
ERROR: column "surveyls_survey_id" is in a primary key STATEMENT: ALTER TABLE lime_surveys_languagesettings ALTER COLUMN surveyls_survey_id DROP NOT NULL ERROR: current transaction is aborted, commands ignored until end of transaction block STATEMENT: ALTER TABLE lime_surveys_languagesettings ALTER COLUMN surveyls_survey_id SET NOT NULL ERROR: relation "questions_idx3" already exists STATEMENT: CREATE INDEX "questions_idx3" ON "lime_questions" ("gid") ERROR: index "user_in_groups_idx1" does not exist STATEMENT: DROP INDEX "user_in_groups_idx1" ERROR: index "lime_user_name_key" does not exist STATEMENT: DROP INDEX "lime_user_name_key" ERROR: column "attribute_type" is in a primary key STATEMENT: ALTER TABLE lime_participant_attribute_names ALTER COLUMN attribute_type DROP NOT NULL ERROR: current transaction is aborted, commands ignored until end of transaction block STATEMENT: ALTER TABLE lime_participant_attribute_names ALTER COLUMN attribute_type SET NOT NULL ERROR: column "id" of relation "lime_participant_attribute_names_lang" does not exist STATEMENT: ALTER TABLE "lime_participant_attribute_names_lang" DROP COLUMN "id" ERROR: column "shared_uid" does not exist STATEMENT: ALTER TABLE "lime_participant_shares" RENAME COLUMN "shared_uid" TO "share_uid" ERROR: index "assessments_idx" does not exist STATEMENT: DROP INDEX "assessments_idx" ERROR: relation "assessments_idx3" already exists STATEMENT: CREATE INDEX "assessments_idx3" ON "lime_assessments" ("gid") ERROR: index "ixcode" does not exist STATEMENT: DROP INDEX "ixcode" ERROR: syntax error at or near "PRIMARY" at character 32 STATEMENT: ALTER TABLE lime_questions DROP PRIMARY KEY, ADD PRIMARY KEY (qid,language)
My Blog: crystalblood.wordpress.com/
Last edit: 9 years 5 months ago by CrystalBlood. Reason: Formatting
The topic has been locked.
- atisne
- Offline
- New Member
Less
More
- Posts: 2
- Thank you received: 1
9 years 5 months ago #114436
by atisne
Replied by atisne on topic Possible bug on migrating database to 2.05+
Hi CrystalBlood,
I don't have this issue. I currently have the constraint NOT NULL on surveyls_survey_id.
We can see that the next error (due to the first failure) is the creation of the same constraint.
You should read the source code to analyse why the constraint is dropped an created just after. Maybe, you can just comment out the DROP...
I don't have this issue. I currently have the constraint NOT NULL on surveyls_survey_id.
We can see that the next error (due to the first failure) is the creation of the same constraint.
You should read the source code to analyse why the constraint is dropped an created just after. Maybe, you can just comment out the DROP...
The topic has been locked.
- CrystalBlood
- Topic Author
- Offline
- New Member
Less
More
- Posts: 9
- Thank you received: 0
9 years 5 months ago #114441
by CrystalBlood
My Blog: crystalblood.wordpress.com/
Replied by CrystalBlood on topic Possible bug on migrating database to 2.05+
Thanks atisne!
Actually, Carsten Schmitz already set the bug report as a duplicate ( bugs.limesurvey.org/view.php?id=9359 ). I applied the patch that he mentioned here github.com/LimeSurvey/LimeSurvey/commit/...d91342e353185854601e and worked.
Well... still shows those errors regarding the index drop, but at least finished the migration.
Actually, Carsten Schmitz already set the bug report as a duplicate ( bugs.limesurvey.org/view.php?id=9359 ). I applied the patch that he mentioned here github.com/LimeSurvey/LimeSurvey/commit/...d91342e353185854601e and worked.
Well... still shows those errors regarding the index drop, but at least finished the migration.
My Blog: crystalblood.wordpress.com/
The topic has been locked.
- CrystalBlood
- Topic Author
- Offline
- New Member
Less
More
- Posts: 9
- Thank you received: 0
9 years 5 months ago - 9 years 5 months ago #114486
by CrystalBlood
My Blog: crystalblood.wordpress.com/
Replied by CrystalBlood on topic Possible bug on migrating database to 2.05+ [CLOSED]
After upgrading, I tried to export-import the structure of a survey into the new version. But has some glitches.
So, Pitifully I am going to make a new installation to avoid any possible inconsistency.
So, Pitifully I am going to make a new installation to avoid any possible inconsistency.
My Blog: crystalblood.wordpress.com/
Last edit: 9 years 5 months ago by CrystalBlood. Reason: Add Pitifully
The topic has been locked.