Mantis Bugtracker          
testlink.org

View Issue Details Jump to Notes ] Issue History ] Print ]
IDProjectCategoryView StatusDate SubmittedLast Update
0002325TestLinkDatabase Postgrespublic2009-04-02 15:312010-01-13 21:42
Reportertoshi 
Assigned Totoshi 
PriorityhighSeveritymajorReproducibilityalways
StatusclosedResolutionfixed 
PlatformOSOS Version
Product Version1.8.0 
Fixed in Version1.8.4 
Summary0002325: Cannot upgrade from 1.7.x to 1.8.0 with PostgreSQL
DescriptionWhen we upgrade 1.7.5 database to 1.8.0, an error about database occurs.
Steps To Reproduce1. Create PostgreSQL evironment for 1.7.5.
2. Click "Upgrade Database (Needed while migrating from 1.7.2)" on installer of 1.8.0.
3. Setting database info.
4. Agree with License
5. At the next step, the error occurs.
Additional InformationError Message:

TestLink setup will now attempt to setup the database:

Creating connection to Database Server: OK!


Checking PostGres version: OK! (8.3.4 >= 8)


Connecting to database `testlink_test_175`: OK!
Creating Testlink DB user `testlink`: OK! (ok - user_exists ok - grant assignment)
Processing:sql/alter_tables/1.8/postgres/DB.1.2/db_schema_update.sqlERROR ON exec_query() - database.class.php
-1 - ERROR: syntax error at or near "MODIFY" LINE 2: ALTER TABLE tcversions MODIFY COLUMN importance INT2 NOT NUL... ^ - ALTER TABLE tcversions MODIFY COLUMN importance INT2 NOT NULL default '2'

THE MESSAGE :: SQL [22] executed [took 0.0030 secs][all took 1.4758 secs]: ALTER TABLE tcversions MODIFY COLUMN importance INT2 NOT NULL default '2' Query failed: errorcode[-1] errormsg:ERROR: syntax error at or near "MODIFY" LINE 2: ALTER TABLE tcversions MODIFY COLUMN importance INT2 NOT NUL... ^
ERROR ON exec_query() - database.class.php
-1 - ERROR: syntax error at or near "TABLE" LINE 3: ALTER TABLE testplan_tcversions ADD COLUMN node_order INT NO... ^ - ALTER TABLE testplan_tcversions ADD COLUMN urgency INT2 NOT NULL default '2', ALTER TABLE testplan_tcversions ADD COLUMN node_order INT NOT NULL default '1'

THE MESSAGE :: SQL [35] executed [took 0.0030 secs][all took 3.3556 secs]: ALTER TABLE testplan_tcversions ADD COLUMN urgency INT2 NOT NULL default '2', ALTER TABLE testplan_tcversions ADD COLUMN node_order INT NOT NULL default '1' Query failed: errorcode[-1] errormsg:ERROR: syntax error at or near "TABLE" LINE 3: ALTER TABLE testplan_tcversions ADD COLUMN node_order INT NO... ^
ERROR ON exec_query() - database.class.php
-18 - ERROR: column "node_order" of relation "testplan_tcversions" does not exist - COMMENT ON COLUMN testplan_tcversions.node_order IS 'order in execution tree'

THE MESSAGE :: SQL [36] executed [took 0.0031 secs][all took 3.3617 secs]: COMMENT ON COLUMN testplan_tcversions.node_order IS 'order in execution tree' Query failed: errorcode[-18] errormsg:ERROR: column "node_order" of relation "testplan_tcversions" does not exist
ERROR ON exec_query() - database.class.php
-1 - ERROR: syntax error at or near "TABLE" LINE 3: ALTER TABLE custom_fields ADD COLUMN enable_on_testplan_desi... ^ - ALTER TABLE custom_fields ADD COLUMN show_on_testplan_design SMALLINT NOT NULL DEFAULT '0', ALTER TABLE custom_fields ADD COLUMN enable_on_testplan_design SMALLINT NOT NULL DEFAULT '0'

THE MESSAGE :: SQL [38] executed [took 0.0029 secs][all took 3.3691 secs]: ALTER TABLE custom_fields ADD COLUMN show_on_testplan_design SMALLINT NOT NULL DEFAULT '0', ALTER TABLE custom_fields ADD COLUMN enable_on_testplan_design SMALLINT NOT NULL DEFAULT '0' Query failed: errorcode[-1] errormsg:ERROR: syntax error at or near "TABLE" LINE 3: ALTER TABLE custom_fields ADD COLUMN enable_on_testplan_desi... ^

Processing:sql/alter_tables/1.8/postgres/DB.1.2/z_final_step.sql
Failed! - Upgrade failed!

TestLink setup couldn't install the default site into the selected database. The last error to occur was ERROR: syntax error at or near "TABLE" LINE 3: ALTER TABLE custom_fields ADD COLUMN enable_on_testplan_desi... ^ during the execution of SQL statement ALTER TABLE custom_fields ADD COLUMN show_on_testplan_design SMALLINT NOT NULL DEFAULT '0', ALTER TABLE custom_fields ADD COLUMN enable_on_testplan_design SMALLINT NOT NULL DEFAULT '0'
TagsNo tags attached.
Database (MySQL,Postgres,etc)
Browser
PHP Version
TestCaseID
QA Team - Task Workflow Status
Attached Filespatch file icon db_schema_update.sql.patch [^] (2,718 bytes) 2009-07-03 19:34 [Show Content]
patch file icon migrate_16_to_17_functions.php.patch [^] (863 bytes) 2009-07-03 19:35 [Show Content]
patch file icon migrate_17_to_18_functions.php.patch [^] (651 bytes) 2009-07-03 19:35 [Show Content]
txt file icon error_message.txt [^] (21,576 bytes) 2009-09-07 11:28 [Show Content]

- Relationships
related to 0002699closedmhavlat Update of 1.7 to 1.8 fails because connect_2_db() is MySQL specific 

-  Notes
(0006168)
mhavlat (reporter)
2009-04-02 18:19

Thank you for this test. Added target: the next hot-fix.
(0007269)
Sniff (reporter)
2009-06-17 23:28

same issues while upgrading from 1.7.4 to 1.8.2:

resolved by fixing some sql syntax:

in file 'install/sql/alter_tables/1.8/postgres/DB.1.2/db_schema_update.sql':

117c117,121
< ALTER TABLE tcversions MODIFY COLUMN importance INT2 NOT NULL default '2';
---
> BEGIN;
> ALTER TABLE tcversions ALTER COLUMN importance DROP DEFAULT;
> ALTER TABLE tcversions ALTER COLUMN importance TYPE int2 USING CAST(importance AS int2);
> ALTER TABLE tcversions ALTER COLUMN importance SET DEFAULT 2;
> COMMIT;
118a123,126
> BEGIN;
> ALTER TABLE tcversions ADD COLUMN tc_external_id numeric(10) NOT NULL DEFAULT 0;
> ALTER TABLE tcversions ALTER COLUMN tc_external_id DROP DEFAULT;
> COMMIT;
139c147
< ALTER TABLE testplan_tcversions ADD COLUMN urgency INT2 NOT NULL default '2',
---
> ALTER TABLE testplan_tcversions ADD COLUMN urgency INT2 NOT NULL default '2';
145c153
< ALTER TABLE custom_fields ADD COLUMN show_on_testplan_design SMALLINT NOT NULL DEFAULT '0',
---
> ALTER TABLE custom_fields ADD COLUMN show_on_testplan_design SMALLINT NOT NULL DEFAULT '0';



in file 'install/migration/migrate_17/migrate_17_to_18_functions.php':

I belive that for postgres, sql statement in function updateExecutionsTCVersionInfo might look like:

UPDATE executions SET tcversion_number = (SELECT version FROM tcversions WHERE id = executions.tcversion_id);

instead of

update executions E,tcversions TCV set tcversion_number=TCV.version where TCV.id = E.tcversion_id
(0007435)
hiddenVar1 (reporter)
2009-07-03 19:36
edited on: 2009-07-03 19:50

Had the same problem with 1.8.3. Created three patches which hopefully help to create a working install package for Postgres.
Also added the "UPDATE executions"-hint by Sniff.

(0007877)
mhavlat (reporter)
2009-08-30 08:05

Guys, thank you for help.

Toshi, I applied patches (+ code review). Could retest with 1.8 branch?
(0007912)
toshi (reporter)
2009-09-03 22:56

Thank you for your patches, I will retest as soon as possible.
(0007931)
toshi (reporter)
2009-09-07 11:27
edited on: 2009-09-07 11:29

I retested with today's 1.8 branch code. But migration failed. I attach error message. Could you investigate further?

(0007935)
mhavlat (reporter)
2009-09-07 18:38

Toshi,
your log includes some error -24 for all requests. Is not there credential problem or something similar? Definitely I don't see syntax error.
(0007938)
hiddenVar1 (reporter)
2009-09-07 22:48

Toshi,
it really looks like you have some connection problem. In adodb-error.inc.php the error code -24 is defined for DB_ERROR_CONNECT_FAILED.
Could you please retest again?
(0007945)
toshi (reporter)
2009-09-09 08:51

Thank you very much for right-on suggestions.

I just retest and succeed at the migration.
Excuse me, previous misguided report.
(0007946)
toshi (reporter)
2009-09-09 08:54

Thanks for the great patch.
(0007949)
mhavlat (reporter)
2009-09-09 18:05

Thank you all for support. I'm going to release 1.8.4 in a few days.

- Issue History
Date Modified Username Field Change
2009-04-02 15:31 toshi New Issue
2009-04-02 18:19 mhavlat Note Added: 0006168
2009-04-02 18:19 mhavlat Priority normal => high
2009-06-17 23:28 Sniff Note Added: 0007269
2009-07-03 19:34 hiddenVar1 File Added: db_schema_update.sql.patch
2009-07-03 19:35 hiddenVar1 File Added: migrate_16_to_17_functions.php.patch
2009-07-03 19:35 hiddenVar1 File Added: migrate_17_to_18_functions.php.patch
2009-07-03 19:36 hiddenVar1 Note Added: 0007435
2009-07-03 19:50 hiddenVar1 Note Edited: 0007435
2009-07-04 07:33 mhavlat Relationship added related to 0002699
2009-08-30 08:05 mhavlat Note Added: 0007877
2009-08-30 08:05 mhavlat Assigned To => toshi
2009-08-30 08:05 mhavlat Status new => feedback
2009-09-03 22:56 toshi Note Added: 0007912
2009-09-07 11:27 toshi Note Added: 0007931
2009-09-07 11:28 toshi File Added: error_message.txt
2009-09-07 11:29 toshi Note Edited: 0007931
2009-09-07 18:38 mhavlat Note Added: 0007935
2009-09-07 22:48 hiddenVar1 Note Added: 0007938
2009-09-09 08:51 toshi Note Added: 0007945
2009-09-09 08:54 toshi Note Added: 0007946
2009-09-09 08:54 toshi Status feedback => resolved
2009-09-09 08:54 toshi Fixed in Version => 1.8.4 (bug fixing)
2009-09-09 08:54 toshi Resolution open => fixed
2009-09-09 18:05 mhavlat Note Added: 0007949
2010-01-13 21:42 mhavlat Status resolved => closed



Copyright © 2000 - 2019 MantisBT Team
Powered by Mantis Bugtracker