Mantis Bugtracker          
testlink.org

View Issue Details Jump to Notes ] Issue History ] Print ]
IDProjectCategoryView StatusDate SubmittedLast Update
0008421TestLinkDatabase Postgrespublic2018-10-21 08:462018-10-25 09:38
Reportergsac 
Assigned Tofman 
PrioritynormalSeverityblockReproducibilityalways
StatusresolvedResolutionfixed 
PlatformOSLinuxOS VersionUbuntu 18.04
Product Version1.9.18 (2018 Q3) 
Fixed in Version1.9.19 (2019 Q1) 
Summary0008421: POSTGRES - Upgrade database script issue from 1.9.17 to 1.9.18
DescriptionHello,

I'm upgrading from 1.9.17 to 1.9.18 using postgresql.
I'm hitting an issue in the database upgrade on this:

ALTER TABLE req_coverage ADD COLUMN req_version_id INTEGER NOT NULL;
ALTER TABLE req_coverage ADD COLUMN tcversion_id INTEGER NOT NULL;


I'm not a DB expert but I think that if you want to alter a table and add a new column "not null", you need to set a default value.
Steps To ReproduceA working insurance of 1.9.17 on Postgresql and you run, line by line the SQL upgrade script.
TagsNo tags attached.
Database (MySQL,Postgres,etc)Postgresql
Browser
PHP Version7
TestCaseID
QA Team - Task Workflow StatusREADY FOR TESTING
Attached Files

- Relationships
child of 0008397assignedfman Availables hot-fixes for 1.9.18 & How To get full fixed package from GitHub 

-  Notes
(0028053)
fman (administrator)
2018-10-22 10:39

>> I'm not a DB expert but I think that if you want to alter a table and add a new column "not null", you need to set a default value.

this kind of comment are of very low value, just stick with

1. steps you do to reproduce
2. how you fixed it
(0028058)
gsac (reporter)
2018-10-22 13:39
edited on: 2018-10-22 13:46

Steps to reproduce:

1. Install TestLink 1.9.17 with postgresql
2. Download TestLink 1.9.18
3. Follow the upgrade guide from the README
4. Run the SQL upgrade script -> error

I can tell you the reason: because in Postgresql you cannot add a column with a "NOT NULL" directive without specifying a default value.
What I cannot do is to have a proper fix given that I don't know what you need the default value to be for existing data in the tables impacted by the "ADD COLUMN ... NOT NULL" without "DEFAULT"

(0028059)
fman (administrator)
2018-10-22 14:15

0 as default is OK
(0028076)
gsac (reporter)
2018-10-24 09:15
edited on: 2018-10-24 18:06

Created pull request

https://github.com/TestLinkOpenSourceTRMS/testlink-code/pull/178 [^]

to update this SQL script

(0028083)
fman (administrator)
2018-10-24 17:53

Thanks for collaboration
(0028084)
fman (administrator)
2018-10-24 17:59

issue reason:
MySQL accepts this sql without complaints.
(0028085)
fman (administrator)
2018-10-24 18:04

it's very strange that you do not have found an issue on:

ALTER TABLE /*prefix*/testcase_keywords ADD COLUMN tcversion_id INTEGER NOT NULL;


it seems also that this
ALTER TABLE /*prefix*/req_coverage ADD COLUMN id BIGSERIAL NOT NULL;

does not create issue
(0028086)
fman (administrator)
2018-10-24 18:08

https://github.com/TestLinkOpenSourceTRMS/testlink-code/commit/a0f5ab41a66a43e9b07b0873f76f224f2e9a467a [^]
(0028089)
gsac (reporter)
2018-10-25 09:38

I read that BIGSERIAL does something funky that would not need a default value
 even on a not null column (https://stackoverflow.com/questions/31035830/postgresql-bigserial-nextval#31036911 [^]). I guess it's because it creates some kind of sequence that is then linked to that column, so the not null constraint doesn't complain.
For MySQL, I read that the "NOT NULL" constrain does not apply on the existing data, but on new data. If I understood, this can be changed by some config somewhere.

- Issue History
Date Modified Username Field Change
2018-10-21 08:46 gsac New Issue
2018-10-22 10:39 fman Note Added: 0028053
2018-10-22 13:39 gsac Note Added: 0028058
2018-10-22 13:41 gsac Note Edited: 0028058 View Revisions
2018-10-22 13:46 gsac Note Edited: 0028058 View Revisions
2018-10-22 14:15 fman Note Added: 0028059
2018-10-24 09:15 gsac Note Added: 0028076
2018-10-24 17:53 fman Note Added: 0028083
2018-10-24 17:59 fman Note Added: 0028084
2018-10-24 18:04 fman Note Added: 0028085
2018-10-24 18:06 fman Note Edited: 0028076 View Revisions
2018-10-24 18:08 fman QA Team - Task Workflow Status => READY FOR TESTING
2018-10-24 18:08 fman Note Added: 0028086
2018-10-24 18:08 fman Status new => resolved
2018-10-24 18:08 fman Fixed in Version => 1.9.19 (2019 Q1)
2018-10-24 18:08 fman Resolution open => fixed
2018-10-24 18:08 fman Assigned To => fman
2018-10-24 18:08 fman Relationship added child of 0008397
2018-10-24 18:09 fman Summary Upgrade database script issue from 1.9.17 to 1.9.18 => POSTGRES - Upgrade database script issue from 1.9.17 to 1.9.18
2018-10-24 18:10 fman Category Installer => Database Postgres
2018-10-25 09:38 gsac Note Added: 0028089



Copyright © 2000 - 2018 MantisBT Team
Powered by Mantis Bugtracker