[asterisk-bugs] [JIRA] (ASTERISK-25267) Broken oracle support of alembic

[HZMI8gkCvPpom0tM (JIRA)]

     [ https://issues.asterisk.org/jira/browse/ASTERISK-25267?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

HZMI8gkCvPpom0tM updated ASTERISK-25267:
----------------------------------------

    Attachment: 4da0c5f79a9c_create_tables.patch

1st patch for fix initial default tables creation 

> Broken oracle support of alembic
> --------------------------------
>
>                 Key: ASTERISK-25267
>                 URL: https://issues.asterisk.org/jira/browse/ASTERISK-25267
>             Project: Asterisk
>          Issue Type: Bug
>      Security Level: None
>          Components: General
>    Affects Versions: 13.4.0
>         Environment: asterisk 13.4.0 / Debian x64 . Oracle 10.2.0.5 x64, cx_oracle component for alembic 
>            Reporter: HZMI8gkCvPpom0tM
>         Attachments: 4da0c5f79a9c_create_tables.patch
>
>
> In moment of installation realtime table like suggested in https://wiki.asterisk.org/wiki/display/AST/Managing+Realtime+Databases+with+Alembic 
> Command 
> alembic -c config.ini upgrade head fail to execute with message : 
> {panel:title=Error Output}
> sqlalchemy.exc.DatabaseError: (cx_Oracle.DatabaseError) ORA-02264: name already used by an existing constraint
>  [SQL: '\nCREATE TABLE sippeers (\n\tid INTEGER NOT NULL, \n\tname VARCHAR2(40 CHAR) NOT NULL, \n\tipaddr VARCHAR2(45 CHAR), \n\tport INTEGER, \n\tregseconds INTEGER, \n\tdefaultuser VARCHAR2(40 CHAR), \n\tfullcontact VARCHAR2(80 CHAR), \n\tregserver VARCHAR2(20 CHAR), \n\tuseragent VARCHAR2(20 CHAR), \n\tlastms INTEGER, \n\thost VARCHAR2(40 CHAR), \n\ttype VARCHAR(6 CHAR), \n\tcontext VARCHAR2(40 CHAR), \n\tpermit VARCHAR2(95 CHAR), \n\tdeny VARCHAR2(95 CHAR), \n\tsecret VARCHAR2(40 CHAR), \n\tmd5secret VARCHAR2(40 CHAR), \n\tremotesecret VARCHAR2(40 CHAR), \n\ttransport VARCHAR(7 CHAR), \n\tdtmfmode VARCHAR(9 CHAR), \n\tdirectmedia VARCHAR(6 CHAR), \n\tnat VARCHAR2(29 CHAR), \n\tcallgroup VARCHAR2(40 CHAR), \n\tpickupgroup VARCHAR2(40 CHAR), \n\tlanguage VARCHAR2(40 CHAR), \n\tdisallow VARCHAR2(200 CHAR), \n\tallow VARCHAR2(200 CHAR), \n\tinsecure VARCHAR2(40 CHAR), \n\ttrustrpid VARCHAR(3 CHAR), \n\tprogressinband VARCHAR(5 CHAR), \n\tpromiscredir VARCHAR(3 CHAR), \n\tuseclientcode VARCHAR(3 CHAR), \n\taccountcode VARCHAR2(40 CHAR), \n\tsetvar VARCHAR2(200 CHAR), \n\tcallerid VARCHAR2(40 CHAR), \n\tamaflags VARCHAR2(40 CHAR), \n\tcallcounter VARCHAR(3 CHAR), \n\tbusylevel INTEGER, \n\tallowoverlap VARCHAR(3 CHAR), \n\tallowsubscribe VARCHAR(3 CHAR), \n\tvideosupport VARCHAR(3 CHAR), \n\tmaxcallbitrate INTEGER, \n\trfc2833compensate VARCHAR(3 CHAR), \n\tmailbox VARCHAR2(40 CHAR), \n\t"session-timers" VARCHAR(9 CHAR), \n\t"session-expires" INTEGER, \n\t"session-minse" INTEGER, \n\t"session-refresher" VARCHAR(3 CHAR), \n\tt38pt_usertpsource VARCHAR2(40 CHAR), \n\tregexten VARCHAR2(40 CHAR), \n\tfromdomain VARCHAR2(40 CHAR), \n\tfromuser VARCHAR2(40 CHAR), \n\tqualify VARCHAR2(40 CHAR), \n\tdefaultip VARCHAR2(45 CHAR), \n\trtptimeout INTEGER, \n\trtpholdtimeout INTEGER, \n\tsendrpid VARCHAR(3 CHAR), \n\toutboundproxy VARCHAR2(40 CHAR), \n\tcallbackextension VARCHAR2(40 CHAR), \n\ttimert1 INTEGER, \n\ttimerb INTEGER, \n\tqualifyfreq INTEGER, \n\tconstantssrc VARCHAR(3 CHAR), \n\tcontactpermit VARCHAR2(95 CHAR), \n\tcontactdeny VARCHAR2(95 CHAR), \n\tusereqphone VARCHAR(3 CHAR), \n\ttextsupport VARCHAR(3 CHAR), \n\tfaxdetect VARCHAR(3 CHAR), \n\tbuggymwi VARCHAR(3 CHAR), \n\tauth VARCHAR2(40 CHAR), \n\tfullname VARCHAR2(40 CHAR), \n\ttrunkname VARCHAR2(40 CHAR), \n\tcid_number VARCHAR2(40 CHAR), \n\tcallingpres VARCHAR(21 CHAR), \n\tmohinterpret VARCHAR2(40 CHAR), \n\tmohsuggest VARCHAR2(40 CHAR), \n\tparkinglot VARCHAR2(40 CHAR), \n\thasvoicemail VARCHAR(3 CHAR), \n\tsubscribemwi VARCHAR(3 CHAR), \n\tvmexten VARCHAR2(40 CHAR), \n\tautoframing VARCHAR(3 CHAR), \n\trtpkeepalive INTEGER, \n\t"call-limit" INTEGER, \n\tg726nonstandard VARCHAR(3 CHAR), \n\tignoresdpversion VARCHAR(3 CHAR), \n\tallowtransfer VARCHAR(3 CHAR), \n\tdynamic VARCHAR(3 CHAR), \n\tpath VARCHAR2(256 CHAR), \n\tsupportpath VARCHAR(3 CHAR), \n\tPRIMARY KEY (id), \n\tUNIQUE (name), \n\tCONSTRAINT type_values CHECK (type IN (\'friend\', \'user\', \'peer\')), \n\tCONSTRAINT sip_transport_values CHECK (transport IN (\'udp\', \'tcp\', \'tls\', \'ws\', \'wss\', \'udp,tcp\', \'tcp,udp\')), \n\tCONSTRAINT sip_dtmfmode_values CHECK (dtmfmode IN (\'rfc2833\', \'info\', \'shortinfo\', \'inband\', \'auto\')), \n\tCONSTRAINT sip_directmedia_values CHECK (directmedia IN (\'yes\', \'no\', \'nonat\', \'update\')), \n\tCONSTRAINT yes_no_values CHECK (trustrpid IN (\'yes\', \'no\')), \n\tCONSTRAINT sip_progressinband_values CHECK (progressinband IN (\'yes\', \'no\', \'never\')), \n\tCONSTRAINT yes_no_values CHECK (promiscredir IN (\'yes\', \'no\')), \n\tCONSTRAINT yes_no_values CHECK (useclientcode IN (\'yes\', \'no\')), \n\tCONSTRAINT yes_no_values CHECK (callcounter IN (\'yes\', \'no\')), \n\tCONSTRAINT yes_no_values CHECK (allowoverlap IN (\'yes\', \'no\')), \n\tCONSTRAINT yes_no_values CHECK (allowsubscribe IN (\'yes\', \'no\')), \n\tCONSTRAINT yes_no_values CHECK (videosupport IN (\'yes\', \'no\')), \n\tCONSTRAINT yes_no_values CHECK (rfc2833compensate IN (\'yes\', \'no\')), \n\tCONSTRAINT sip_session_timers_values CHECK ("session-timers" IN (\'accept\', \'refuse\', \'originate\')), \n\tCONSTRAINT sip_session_refresher_values CHECK ("session-refresher" IN (\'uac\', \'uas\')), \n\tCONSTRAINT yes_no_values CHECK (sendrpid IN (\'yes\', \'no\')), \n\tCONSTRAINT yes_no_values CHECK (constantssrc IN (\'yes\', \'no\')), \n\tCONSTRAINT yes_no_values CHECK (usereqphone IN (\'yes\', \'no\')), \n\tCONSTRAINT yes_no_values CHECK (textsupport IN (\'yes\', \'no\')), \n\tCONSTRAINT yes_no_values CHECK (faxdetect IN (\'yes\', \'no\')), \n\tCONSTRAINT yes_no_values CHECK (buggymwi IN (\'yes\', \'no\')), \n\tCONSTRAINT sip_callingpres_values CHECK (callingpres IN (\'allowed_not_screened\', \'allowed_passed_screen\', \'allowed_failed_screen\', \'allowed\', \'prohib_not_screened\', \'prohib_passed_screen\', \'prohib_failed_screen\', \'prohib\')), \n\tCONSTRAINT yes_no_values CHECK (hasvoicemail IN (\'yes\', \'no\')), \n\tCONSTRAINT yes_no_values CHECK (subscribemwi IN (\'yes\', \'no\')), \n\tCONSTRAINT yes_no_values CHECK (autoframing IN (\'yes\', \'no\')), \n\tCONSTRAINT yes_no_values CHECK (g726nonstandard IN (\'yes\', \'no\')), \n\tCONSTRAINT yes_no_values CHECK (ignoresdpversion IN (\'yes\', \'no\')), \n\tCONSTRAINT yes_no_values CHECK (allowtransfer IN (\'yes\', \'no\')), \n\tCONSTRAINT yes_no_values CHECK (dynamic IN (\'yes\', \'no\')), \n\tCONSTRAINT yes_no_values CHECK (supportpath IN (\'yes\', \'no\'))\n)\n\n']
> {panel}
> Update : All operations were tested on completely fresh installed oracle instance without any another schemas (except fresh created for asterisk).
> I think that problem happens because oracle table creation script contains
> multiply lines
> CONSTRAINT yes_no_values CHECK (promiscredir IN ('yes', 'no')),
> CONSTRAINT yes_no_values CHECK (useclientcode IN ('yes', 'no')),
> CONSTRAINT yes_no_values CHECK (callcounter IN ('yes', 'no')),
> But oracle require that constraints names must be UNIQUE.
> Possible solution can be to use operator AND during creation of constraint like
> _CONSTRAINT yes_no_values CHECK ((sendrpid IN ('yes', 'no')) and (constantssrc IN ('yes', 'no')) and (trustrpid IN ('yes', 'no')) ..... )_ but it will not fix issue during creation constraint for next table which also have name "yes_no_values" . So i suggest to create separate unique constraint name for every checked column.
> Update2: After fixing constrant names in accordance with ORACLE requirment appeared another issue : 
> __sqlalchemy.exc.DatabaseError: (cx_Oracle.DatabaseError) ORA-01408: such column list already indexed
>  [SQL: 'CREATE INDEX sippeers_name ON sippeers (name)']__
> Look like alembic try to create Index which already exist. 
> op.create_index('sippeers_name', 'sippeers', ['name'])



--
This message was sent by Atlassian JIRA
(v6.2#6252)