[asterisk-bugs] [JIRA] (ASTERISK-25267) [patch] Alembic - broken compatibility for Oracle and Microsoft SQL

Fri Jun 10 06:43:56 CDT 2016

    [ https://issues.asterisk.org/jira/browse/ASTERISK-25267?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=230974#comment-230974 ] 

Joshua Colp commented on ASTERISK-25267:
----------------------------------------

The message "Commit length validation failed" means you did not adhere to the commit message guidelines of 80 columns. Gerrit won't accept the review until it's fixed.

> [patch] Alembic - broken compatibility for Oracle and Microsoft SQL
> -------------------------------------------------------------------
>
>                 Key: ASTERISK-25267
>                 URL: https://issues.asterisk.org/jira/browse/ASTERISK-25267
>             Project: Asterisk
>          Issue Type: Bug
>      Security Level: None
>          Components: Contrib/General
>    Affects Versions: 13.4.0, 13.5.0, 13.6.0, 13.7.0
>         Environment: asterisk 13.4.0 / Debian x64 . Oracle 10.2.0.5 x64, cx_oracle component for alembic 
>            Reporter: HZMI8gkCvPpom0tM
>         Attachments: create.sql
>
>
> 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'])
> Update3: 
> During initial creation of table ps_endpoints massively used constraint names which contains more than 30 characters but oracle not support so long names of constraints and alembic fail with : _sqlalchemy.exc.DatabaseError: (cx_Oracle.DatabaseError) ORA-00972: identifier is too long_
> Update4: Script add_pjsip_endpoint_options_for_12_1.py contains mistakes which crash alembic with error : _ORA-00972: identifier is too long_
> Update5: Script 28887f25a46f_create_queue_tables.py during creation table queues crash with error _ORA-02264: name already used by an existing constraint_
> Update6: Script c6d929b23a8_create_pjsip_subscription_persistence_.py during creation index ps_subscription_persistence_id crash with error 
> _sqlalchemy.exc.DatabaseError: (cx_Oracle.DatabaseError) ORA-01408: such column list already indexed_
> update7:  Script 51f8cb66540e_add_further_dtls_options.py have mistake during alter table ps_endpoints because constraint yesno_values already exist .  Error _sqlalchemy.exc.DatabaseError: (cx_Oracle.DatabaseError) ORA-02264: name already used by an existing constraint_
> Update8: Look like nobody check anything before add to release brance sources. 5950038a6ead_fix_pjsip_verifiy_typo.py 
> ORA-00904: "VERIFIY_SERVER": invalid identifier. Source contain stupid syntax error since 2014.
> Update9: 371a3bf4143e_add_user_eq_phone_option_to_pjsip.py ORA-02264: name already used by an existing constraint during alter ps_endpoints for user_eq_phone
> update10 : 23530d604b96_add_rpid_immediate.py in moment of alter ps_endpoints for rpid_immediate ORA-02264: name already used by an existing constraint 

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