MantisBT - TestLink
View Issue Details
0008421TestLinkDatabase Postgrespublic2018-10-21 08:462018-10-25 09:38
gsac 
fman 
normalblockalways
resolvedfixed 
LinuxUbuntu 18.04
1.9.18 (2018 Q3) 
1.9.19 (2019 Q1) 
Postgresql
7
READY FOR TESTING
0008421: POSTGRES - Upgrade database script issue from 1.9.17 to 1.9.18
Hello,

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.
A working insurance of 1.9.17 on Postgresql and you run, line by line the SQL upgrade script.
No tags attached.
child of 0008397closed fman Availables hot-fixes for 1.9.18 & How To get full fixed package from GitHub 
Issue History
2018-10-21 08:46gsacNew Issue
2018-10-22 10:39fmanNote Added: 0028053
2018-10-22 13:39gsacNote Added: 0028058
2018-10-22 13:41gsacNote Edited: 0028058bug_revision_view_page.php?bugnote_id=28058#r5615
2018-10-22 13:46gsacNote Edited: 0028058bug_revision_view_page.php?bugnote_id=28058#r5616
2018-10-22 14:15fmanNote Added: 0028059
2018-10-24 09:15gsacNote Added: 0028076
2018-10-24 17:53fmanNote Added: 0028083
2018-10-24 17:59fmanNote Added: 0028084
2018-10-24 18:04fmanNote Added: 0028085
2018-10-24 18:06fmanNote Edited: 0028076bug_revision_view_page.php?bugnote_id=28076#r5623
2018-10-24 18:08fmanQA Team - Task Workflow Status => READY FOR TESTING
2018-10-24 18:08fmanNote Added: 0028086
2018-10-24 18:08fmanStatusnew => resolved
2018-10-24 18:08fmanFixed in Version => 1.9.19 (2019 Q1)
2018-10-24 18:08fmanResolutionopen => fixed
2018-10-24 18:08fmanAssigned To => fman
2018-10-24 18:08fmanRelationship addedchild of 0008397
2018-10-24 18:09fmanSummaryUpgrade 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:10fmanCategoryInstaller => Database Postgres
2018-10-25 09:38gsacNote Added: 0028089

Notes
(0028053)
fman   
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   
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   
2018-10-22 14:15   
0 as default is OK
(0028076)
gsac   
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   
2018-10-24 17:53   
Thanks for collaboration
(0028084)
fman   
2018-10-24 17:59   
issue reason:
MySQL accepts this sql without complaints.
(0028085)
fman   
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   
2018-10-24 18:08   
https://github.com/TestLinkOpenSourceTRMS/testlink-code/commit/a0f5ab41a66a43e9b07b0873f76f224f2e9a467a [^]
(0028089)
gsac   
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.