Welcome, Guest
Username: Password: Remember me

TOPIC: Problems during database update from 1.91+ to 200+ (PostgreSQL 8.3 + PHP 5.2.4)

Problems during database update from 1.91+ to 200+ (PostgreSQL 8.3 + PHP 5.2.4) 2 months 1 week ago #104551

  • arensa
  • arensa's Avatar
  • OFFLINE
  • Fresh Lemon
  • Posts: 1
  • Karma: 0
Hi,

I experienced a lot of problems during database upgrade from Limesurvey 1.91+ to 200+ version. A note ahead: as the installed PHP version 5.2.4 is less the requested 5.3 for 2.05+, I report the problem in the forum instead of the bugtracker, in which only reports should be posted relating to the latest stable version.

Basically, I came accross two major problems.

1) The first one was quite easy to solve: The DDL command
ALTER TABLE lime_user_in_groups ADD PRIMARY KEY (ugid,uid)
results in an error as the table in question contains duplicate entries (for what reason soever)
ERROR: could not create unique index "lime_user_in_groups_pkey"
DETAIL: Table contains duplicated values.

This can be solved by a simple statement:
delete from lime_user_in_groups where ctid not in (select max (s.ctid) from  lime_user_in_groups s group by s.ugid, s.uid) ;

2) The seconf problem was much harder to solve. Basically, it looks like as the try-catch statements don't show the expected effect on my installation. Concretely, whenever the DDL statement in the try clause resulted in a failure, the postgres server tried to "catch" all the following statements as well. In the logs, it looked like this:
LOG:  execute pdo_pgsql_stmt_b6268fac: ALTER TABLE lime_user_in_groups ADD PRIMARY KEY (ugid,uid)
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "lime_user_in_groups_pkey" for table "lime_user_in_groups"                                          
ERROR:  could not create unique index "lime_user_in_groups_pkey"
DETAIL:  Table contains duplicated values.
STATEMENT:  ALTER TABLE lime_user_in_groups ADD PRIMARY KEY (ugid,uid)
LOG:  statement: DEALLOCATE pdo_pgsql_stmt_b6268fac
ERROR:  current transaction is aborted, commands ignored until end of transaction block
STATEMENT:  DEALLOCATE pdo_pgsql_stmt_b6268fac                                              
LOG:  execute pdo_pgsql_stmt_b623595c: ROLLBACK TO SAVEPOINT limesurvey;
LOG:  statement: DEALLOCATE pdo_pgsql_stmt_b623595c
LOG:  execute pdo_pgsql_stmt_b6268e2c: ALTER TABLE "lime_participant_attribute" ALTER COLUMN "value" TYPE character varying(50)                                     
LOG:  statement: DEALLOCATE pdo_pgsql_stmt_b6268e2c
LOG:  execute pdo_pgsql_stmt_b6268e2c: ALTER TABLE lime_participant_attribute ALTER COLUMN value DROP DEFAULT                                                        
LOG:  statement: DEALLOCATE pdo_pgsql_stmt_b6268e2c
LOG:  execute pdo_pgsql_stmt_b6268e2c: ALTER TABLE lime_participant_attribute ALTER COLUMN value DROP NOT NULL                                                       
LOG:  statement: DEALLOCATE pdo_pgsql_stmt_b6268e2c
LOG:  execute pdo_pgsql_stmt_b6268e2c: ALTER TABLE lime_participant_attribute ALTER COLUMN value SET NOT NULL                                                           
LOG:  statement: DEALLOCATE pdo_pgsql_stmt_b6268e2c
LOG:  execute pdo_pgsql_stmt_b6268eac: ALTER TABLE "lime_participant_attribute" ALTER COLUMN "value" TYPE character varying(50)                                     
LOG:  statement: DEALLOCATE pdo_pgsql_stmt_b6268eac
LOG:  execute pdo_pgsql_stmt_b6268eac: ALTER TABLE "lime_participant_shares" ALTER COLUMN "date_added"
TYPE timestamp                                                 
LOG:  statement: DEALLOCATE pdo_pgsql_stmt_b6268eac


This happended at infinutum and eventually killed the server. The only solution I found was to comment out the offending statements in the updatedb_helper.php script. For example, line 825:
#try { setTransactionBookmark(); alterColumn('{{sessions}}','id',"{$sVarchar}(32)",false); } catch(Exception $e) { rollBackToTransactionBookmark();}

This was no problem as the statements did not have a real effect, e.g. they tried to rename columns, that were already renamed or tried to drop 'NOT NULL' constraints from primary keys.

HTH,
Andreas
The administrator has disabled public write access.
Moderators: ITEd
Time to create page: 0.261 seconds
Donation Image