Repository navigation
Liquibase failed to apply changeset33 if field SEC_OBSERVED not empty #135
Description
Is your feature request related to a problem? Please describe.
The changes applied at 78c8153 commit fix the problems related to TIMESTAMP fields at Oracle Database.
When liquibase tries to apply the SQL command: ALTER TABLE STM_SERVICE_CHECK MODIFY (SEC_OBSERVED TIMESTAMP(6) WITH TIME ZONE) an error occurs if the field SEC_OBSERVED contains some data.
Error Message = ORA-01439: column to be modified must be empty to change datatype
The table STM_SERVICE_CHECK is filled automatically from data services, so one possible solution is to delete all the registers of this table after apply the ALTER TABLE SQL
Describe the solution you'd like
Could we considered the behaviour described above as normal? In this case there isn't nothing to do.
If not, some additional SQL sentences could be added in order to delete all the records from STM_SERVICE_CHECK o r, may be, only data from SEC_OBSERVED field?
Additional context
1.2.9 (dev branch)
Activity
fjlopez commentedon Oct 30, 2026
A solution is ready for the future version 1.3.0 in https://github.com/sitmun/sitmun but meanwhile I will backport it to 1.2.9 dev branch.
fjlopez commentedon Oct 30, 2026
Validation: OPEN_DEFECT (evidence:confirmed).
Oracle changeset 33-service-check-sec-observed-timestamptz-oracle in profiles/oracle/liquibase/changelog/33_service_check_observed_timestamptz.yaml (commit 78c8153, pull request 122, on origin/dev) runs ALTER TABLE STM_SERVICE_CHECK MODIFY (SEC_OBSERVED TIMESTAMP(6) WITH TIME ZONE). It does not delete STM_SERVICE_CHECK rows or clear SEC_OBSERVED first. Development changelog 89 runs the same statement. That is the change that raises ORA-01439 when the column already has values.
The same ALTER is in open sitmun-backend-core pull request 355, branch fix/service-check-oracle-observed, commits 2bedc837, e6635f8c, and a9ba615b (config/db/changelog/32_service_check_observed_timestamptz.yaml). Uncommitted edits on that branch only reword the changelog and test comments. They do not change the migration.
https://github.com/sitmun/sitmun main has no SEC_OBSERVED timestamptz changeset. Oracle profile changelogs there stop at 25.
The upgrade of a non-empty SEC_OBSERVED column is still open.
fjlopez commentedon Oct 30, 2026
The upgrade copies populated SEC_OBSERVED and SCS_OBSERVED values as UTC before the empty-column ALTER. The ALTER changesets are unchanged.
Backend pull request: sitmun/sitmun-backend-core#355
Stack pull request: #137
oracle-from and dev-oracle-from run the new changesets on empty tables. Backend test-oracle runs the populated case.
fjlopez commentedon Oct 31, 2026
Both pull requests are merged into dev. Backend sitmun/sitmun-backend-core#355 is on dev as 747461c4. Stack #137 is on dev as a3da5ea and points at that backend commit. This issue stays open because the pull request merged into dev, and GitHub only closes an issue when the closing pull request merges into main.
fjlopez commentedon Oct 31, 2026
Validation: DONE (evidence:confirmed). Oracle changelog 35 copies populated SEC_OBSERVED and SCS_OBSERVED with FROM_TZ to UTC before changeset 33. The ALTER is unchanged. Backend dev 747461c4 (pull request 355) ran the populated case in test-oracle. Stack pull request 137 ran oracle-from from 1.2.6, 1.2.7, and 1.2.8.