ASTERISK-25267: [patch] Alembic - broken compatibility for Oracle and Microsoft SQL

[Home]

Summary: ASTERISK-25267: [patch] Alembic - broken compatibility for Oracle and Microsoft SQL

Reporter: HZMI8gkCvPpom0tM (y2fbo4IevYm5Ve9u) Labels: patch

Date Opened: 2015-07-20 03:24:33 Date Closed:

Priority: Major Regression?

Status: Open/New Components: Contrib/General

Versions: 13.4.0 13.5.0 13.6.0 13.7.0 Frequency of
Occurrence Constant

Related
Issues:

Environment: asterisk 13.4.0 / Debian x64 . Oracle 10.2.0.5 x64, cx_oracle component for alembic Attachments: ( 0) create.sql

Description: 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

Comments: By: Asterisk Team (asteriskteam) 2015-07-20 03:24:35.806-0500

The severity of this issue has been automatically downgraded from "Blocker" to "Major". The "Blocker" severity is reserved for issues which have been determined to block the next release of Asterisk. This severity can only be set by privileged users. If this issue is deemed to block the next release it will be updated accordingly during the triage process.
By: Asterisk Team (asteriskteam) 2015-07-20 03:24:36.664-0500

Thanks for creating a report! The issue has entered the triage process. That means the issue will wait in this status until a Bug Marshal has an opportunity to review the issue. Once the issue has been reviewed you will receive comments regarding the next steps towards resolution.

A good first step is for you to review the [Asterisk Issue Guidelines|https://wiki.asterisk.org/wiki/display/AST/Asterisk+Issue+Guidelines] if you haven't already. The guidelines detail what is expected from an Asterisk issue report.

Then, if you are submitting a patch, please review the [Patch Contribution Process|https://wiki.asterisk.org/wiki/display/AST/Patch+Contribution+Process].
By: HZMI8gkCvPpom0tM (y2fbo4IevYm5Ve9u) 2015-07-20 03:26:43.285-0500

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.

By: HZMI8gkCvPpom0tM (y2fbo4IevYm5Ve9u) 2015-07-20 05:18:58.770-0500

1st patch for fix initial default tables creation
By: HZMI8gkCvPpom0tM (y2fbo4IevYm5Ve9u) 2015-07-20 05:49:50.125-0500

next patch in package of patches to fix oracle mistakes
By: HZMI8gkCvPpom0tM (y2fbo4IevYm5Ve9u) 2015-07-20 06:17:27.317-0500

patch for add_pjsip_endpoint_options_for_12_1.py script to make it oracle compatible
By: HZMI8gkCvPpom0tM (y2fbo4IevYm5Ve9u) 2015-07-20 06:33:49.111-0500

patch to make oracle compatible script create_queue_tables.py
By: HZMI8gkCvPpom0tM (y2fbo4IevYm5Ve9u) 2015-07-20 07:04:08.099-0500

another patch (i am tired already to fix it )
By: HZMI8gkCvPpom0tM (y2fbo4IevYm5Ve9u) 2015-07-20 08:20:50.817-0500

next patch
By: HZMI8gkCvPpom0tM (y2fbo4IevYm5Ve9u) 2015-07-20 08:34:44.047-0500

next patch from bundle
By: Rusty Newton (rnewton) 2015-07-20 18:05:51.026-0500

Thanks for the contribution! If you'd like your contribution to be included faster, you should submit your patch for code review by the Asterisk Developer Community. To do so, please follow the Code Review [1] instructions on the wiki. Be sure to:
* Verify that your patch conforms to the Coding Guidelines [2]
* Review the Code Review Checklist [3] for common items reviewers will look for
* If necessary, provide tests for the Asterisk Test Suite that verify the correctness of your patch [4]

When ready, submit your patch and any tests to Gerrit [5] for code review.

Thanks!

[1] https://wiki.asterisk.org/wiki/display/AST/Code+Review
[2] https://wiki.asterisk.org/wiki/display/AST/Coding+Guidelines
[3] https://wiki.asterisk.org/wiki/display/AST/Code+Review+Checklist
[4] https://wiki.asterisk.org/wiki/display/AST/Asterisk+Test+Suite+Documentation
[5] https://wiki.asterisk.org/wiki/display/AST/Gerrit+Usage

By: HZMI8gkCvPpom0tM (y2fbo4IevYm5Ve9u) 2015-07-21 04:31:03.433-0500

Safe version of patch to avoid breaking another databases support.
By: HZMI8gkCvPpom0tM (y2fbo4IevYm5Ve9u) 2015-07-21 05:56:20.211-0500

It is all good . Butwhen i created public key to use with gerrit.
It constantly answer to me . all configured in accordance with
https://wiki.asterisk.org/wiki/display/AST/Gerrit+Usage

debug3: authmethod_lookup publickey
debug3: remaining preferred: keyboard-interactive,password
debug3: authmethod_is_enabled publickey
debug1: Next authentication method: publickey
debug1: Offering RSA public key: /root/.ssh/id_rsa
debug3: send_pubkey_test
debug2: we sent a publickey packet, wait for reply
debug1: Authentications that can continue: publickey
debug2: we did not send a packet, disable method
debug1: No more authentication methods to try.
Permission denied (publickey).
fatal: Could not read from remote repository.

Update 1: I deleted patches because they were quick and dirty. Prepared new bundle of patches which not break anther DB engines. will try to send it via git review after it start to work with my public key.
By: Rusty Newton (rnewton) 2015-07-21 15:49:01.162-0500

Sorry you are having issues with Gerrit. Did you try the steps from the [Gerrit Usage - Creating an Account|https://wiki.asterisk.org/wiki/display/AST/Gerrit+Usage#GerritUsage-CreatinganAccount] section exactly?

I know you have performed steps 1 and 2 already:

* Browse to Gerrit, and click Sign In.
* This will redirect to openid.asterisk.org. Sign in with your Atlassian username/password.
* Upon signing in successfully, you will need to authorize Gerrit to access your OpenID. When you have done so, you should be redirected back to Gerrit, and will be signed in.

You need to make sure that you use the same account name as your issues.asterisk.org/wiki.asterisk.org account.

By: HZMI8gkCvPpom0tM (y2fbo4IevYm5Ve9u) 2015-07-22 07:01:28.382-0500

Nevermind. Appeared that was needed to setup username in gerrit gui.
Bow i get
[remote rejected] HEAD -> refs/publish/master/ASTERISK-25267 (Commit length validation failed)
will try to review one by one
By: John Roth (voip4me) 2015-08-23 09:10:14.872-0500

(Broken) Create script produced by alembic for use in Microsoft SQL Server.
By: John Roth (voip4me) 2015-08-23 09:12:39.036-0500

I am running into the exact same problem when using Microsoft SQL Server. Alembic is attempting to create constraints using names based on the constraint type, whereas it needs to base the name on a combination of constraint type and table/column name or some other unique identifier. Microsoft SQL Server does not allow two constraints with the same name to exist in a database - each must be unique. The problem was most evident with yes_no_values, but is seen with several other constraints as well.

Create script produced by alembic is attached.
By: Joshua C. Colp (jcolp) 2016-06-10 06:42:21.420-0500

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.
By: HZMI8gkCvPpom0tM (y2fbo4IevYm5Ve9u) 2016-06-10 07:24:16.882-0500

Basically it depend from you guys. What you want more or you want to have a lot of bureaucracy or want to have issue fixed to decrease issues for people who use asterisk. I am not a professional developer. And i really not want to spend my own time for creation patches which will be placed to garbage because of somebody not like representation format. As a solution it will be good if Gerrit itself detect issues and convert data to form it like. Because currently for dev team form of representation is more important than content.
Btw exactly for this issue i created a huge patch (i not remember exactly but about 60kb of text data for which i spend couple of days) and even posted successfully to gerrit but it was placed to garbage by maintainer because he not like tabs. And even after i fixed everything it was just canceled.