search

sqitchers / sqitch

Sensible database change management
https://sqitch.org
MIT License
2.77k stars 215 forks source link

WHENEVER SQLERROR EXIT SQL.SQLCODE is not safe on UNIX #831

Open vectro opened 4 months ago

vectro commented 4 months ago

On Oracle, Sqitch runs the SQL*Plus command WHENEVER SQLERROR EXIT SQL.SQLCODE, so that we can detect errors. However, on UNIX, SQL*Plus will return the actual error code modulo 256, which means the application can return zero exit status if the error code is a multiple of 256.

For example, if Sqitch tries to deploy a script creating a table with too many columns, causing error ORA-01792: maximum number of columns in a table or view is 1000, SQL*Plus will still exit with zero exit status (because 1792 mod 256 = 0), so Sqitch will not detect the error.

theory commented 4 months ago

Oh man, that's wild. Would WHENEVER SQLERROR EXIT FAILURE fix it? The Exasol engine uses WHENEVER SQLERROR EXIT 4 instead. I have no clue what's right for either of these cases TBH 😂

vectro commented 3 months ago

It seems that on UNIX at least, FAILURE gives an exit status of 1. I think that would fix this bug.

theory commented 3 months ago

This is wild. Maybe should do EXIT 4, if only because in some places Sqitch thinks of error code 1 as more of a warning. Not as much as it used to, but still…