New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Oracle.ProgrammingError: positional and named binds cannot be intermixed (==6.4) #199

Closed
jakereps opened this Issue Jul 3, 2018 · 28 comments

Comments

Projects
None yet
10 participants
@jakereps
Copy link

jakereps commented Jul 3, 2018

Discovered in unit testing due to no version pinning in the project's Pipfile. Locally (6.3.1) remains functional, while all database set up tasks fail in our CI pipeline on cx_Oracle==6.4.

Binding errors stating that positional and named binds cannot be intermixed when there are no positional binds being used.

Answer the following questions:

  1. What is your version of Python? Is it 32-bit or 64-bit?
$ python -V
Python 3.6.5
$ python -c 'import sys; print(sys.maxsize)'
9223372036854775807  # 64bit
  1. What is your version of cx_Oracle?

Locally:

$ python -c 'import cx_Oracle; print(cx_Oracle.__version__)'
6.3.1

Remote (issue causing)

$ python -c 'import cx_Oracle; print(cx_Oracle.__version__)'
6.4
  1. What is your version of the Oracle client (e.g. Instant Client)? How was it
    installed? Where is it installed?
$ ls /opt/oracle/
instantclient_12_2
  1. What is your version of the Oracle Database?
$ docker ps
CONTAINER ID        IMAGE                                            COMMAND                  CREATED             STATUS                    PORTS                                 NAMES
ffe765d011a3        store/oracle/database-enterprise:12.2.0.1-slim   "/bin/sh -c '/bin/ba…"   29 minutes ago      Up 29 minutes (healthy)   5500/tcp, 127.0.0.1:XXXXX->1521/tcp   inspiring_shtern
  1. What is your OS and version?
$ uname -a
Darwin Jordens-MacBook-Pro.local 17.6.0 Darwin Kernel Version 17.6.0: Tue May  8 15:22:16 PDT 2018; root:xnu-4570.61.1~1/RELEASE_X86_64 x86_64
$ system_profiler SPSoftwareDataType
Software:

    System Software Overview:

      System Version: macOS 10.13.5 (17F77)
      Kernel Version: Darwin 17.6.0
      Boot Volume: Macintosh HD
      Boot Mode: Normal
      Computer Name: Jorden’s MacBook Pro
      User Name: Jorden Kreps (jordenkreps)
      Secure Virtual Memory: Enabled
      System Integrity Protection: Enabled
      Time since boot: 6 days 1:57
  1. What compiler version did you use? For example, with GCC, run
    gcc --version.
$ gcc --version
Configured with: --prefix=/Library/Developer/CommandLineTools/usr --with-gxx-include-dir=/usr/include/c++/4.2.1
Apple LLVM version 9.1.0 (clang-902.0.39.2)
Target: x86_64-apple-darwin17.6.0
Thread model: posix
InstalledDir: /Library/Developer/CommandLineTools/usr/bin
  1. What environment variables did you set? How exactly did you set them?

N/A

  1. What exact command caused the problem (e.g. what command did you try to
    install with)? Who were you logged in as?
$ pytest --cov <package_name>
  1. What error(s) you are seeing?

6.3.1

...
<package_name>/tests/test_util.py ...............                            [100%]
...

6.4

...
<package_name>/tests/test_util.py EEEEEE.........                            [100%]
...

self = <sqlalchemy.dialects.oracle.cx_oracle.OracleDialect_cx_oracle object at 0x7f977ca76550>
cursor = <cx_Oracle.Cursor on <cx_Oracle.Connection to SYSTEM@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=XXXXX))(CONNECT_DATA=(SID=ORCLCDB)(SERVICE_NAME=ORCLPDB1.localdomain)))>>
statement = 'SELECT table_name FROM all_tables WHERE table_name = :name AND owner = :schema_name'
parameters = {'name': 'MYTABLE', 'schema_name': 'MYUSER'}
context = <sqlalchemy.dialects.oracle.cx_oracle.OracleExecutionContext_cx_oracle object at 0x7f977cb570b8>

    def do_execute(self, cursor, statement, parameters, context=None):
>       cursor.execute(statement, parameters)
E       cx_Oracle.ProgrammingError: positional and named binds cannot be intermixed

Pinning to 6.3.1 resolves the issue.

@anthony-tuininga

This comment has been minimized.

Copy link
Member

anthony-tuininga commented Jul 3, 2018

Can you provide a test case that fails? Or provide instructions on getting the unit tests you are referencing up and running so I can see what is going on? Thanks!

@jakereps

This comment has been minimized.

Copy link
Author

jakereps commented Jul 3, 2018

For sure, it happens in the setUp portion of the unittest.TestCase, where we are making and destroying tables before and after every test so the data remains at the initial state for each test. Since the tests are erring out at the set up stage, the test code itself isn't even being ran.

Here is essentially our setUp code that recreates the error on cx_Oracle==6.4, but works as expected on ==6.3.1.

import sqlalchemy
import cx_Oracle
from sqlalchemy import Table, Column, MetaData, String

user = "SYSTEM"
password = "Oradoc_db1"

dsn = cx_Oracle.makedsn("127.0.0.1", <DOCKER_ASSIGNED_PORT>, "ORCLCDB", 
                        service_name="ORCLPDB1.localdomain")
connection_str = f"oracle+cx_oracle://{user}:{password}@{dsn}"
engine = sqlalchemy.create_engine(connection_str)

md = MetaData(bind=engine)

schema_name = "sone"
table_name = "table1"

engine.execute(f"CREATE USER {schema_name} IDENTIFIED BY test")
engine.execute(f"ALTER USER {schema_name} QUOTA UNLIMITED ON USERS")

columns = []
for column in ('foo', 'bar', 'baz'):
    columns.append(Column(column.upper(), String(50), nullable=True, 
                          quote=False))

_ = Table(table_name, md, *columns, schema=schema_name, quote=False, 
          quote_schema=False)

md.create_all()
@anthony-tuininga

This comment has been minimized.

Copy link
Member

anthony-tuininga commented Jul 3, 2018

Thanks for the test case. That was sufficient to track this issue down. Essentially a bug fix in cx_Oracle 6.4 inadvertently introduced this issue. Calling cursor.setinputsizes() with no parameters at all would make the assumption that you were binding by position, so a subsequent call to cursor.execute() with named arguments would immediately raise this issue. Although it makes no sense to call cursor.setinputsizes() with no parameters, it also shouldn't cause any problems! I'll get this corrected.

@anthony-tuininga anthony-tuininga added the bug label Jul 3, 2018

anthony-tuininga added a commit that referenced this issue Jul 3, 2018

If cursor.setinputsizes() is called without any parameters, do not se…
…t the flag

indicating that bind variables should be returned since otherwise binding with
named arguments will raise the error "cx_Oracle.ProgrammingError: positional
and named binds cannot be intermixed"
(#199).
@anthony-tuininga

This comment has been minimized.

Copy link
Member

anthony-tuininga commented Jul 3, 2018

I have corrected this issue and added a test case that should prevent this issue from happening in the future. I'll wait a while before releasing cx_Oracle 6.4.1, just to ensure there aren't any other issues. Thanks again for reporting this issue.

@jakereps

This comment has been minimized.

Copy link
Author

jakereps commented Jul 3, 2018

Awesome, thanks! No problem on holding out a hot fix, as we’ll just keep an eye out and unpin the version at that time!

@anthony-tuininga

This comment has been minimized.

Copy link
Member

anthony-tuininga commented Jul 4, 2018

Re-opening as requested. Will close again once 6.4.1 has been released.

@zzzeek

This comment has been minimized.

Copy link

zzzeek commented Jul 5, 2018

yep...i'm getting this too :) can you refer to the patch, and/or did you add tests for this?

@zzzeek

This comment has been minimized.

Copy link

zzzeek commented Jul 5, 2018

oh sorry i see it above

zzzeek added a commit to zzzeek/alembic that referenced this issue Jul 5, 2018

block cx_Oracle 6.4
Prevents oracle/python-cx_Oracle#199

Change-Id: Ie964f4cf0d57b65f18761e5673f9ce67c948114a

zzzeek added a commit to zzzeek/sqlalchemy that referenced this issue Jul 5, 2018

block cx_Oracle 6.4
Prevents oracle/python-cx_Oracle#199

Change-Id: I0f94bde38919a027f094ca016621c1364e845332
(cherry picked from commit 4f4d47f)

zzzeek added a commit to zzzeek/sqlalchemy that referenced this issue Jul 5, 2018

block cx_Oracle 6.4
Prevents oracle/python-cx_Oracle#199

Change-Id: I0f94bde38919a027f094ca016621c1364e845332
@vinayinfo

This comment has been minimized.

Copy link

vinayinfo commented Jul 6, 2018

I am getting the Same error. to fix that I have downgraded to v6.3.1

@pgacv2

This comment has been minimized.

Copy link

pgacv2 commented Jul 6, 2018

Confirmed, downgrading to 6.3.1 avoids that error.

@mkai

This comment has been minimized.

Copy link

mkai commented Jul 9, 2018

I have the same problem – I think it affects everyone using SQLAlchemy. Waiting to upgrade until 6.4.1 is released.

@anthony-tuininga

This comment has been minimized.

Copy link
Member

anthony-tuininga commented Jul 9, 2018

cx_Oracle 6.4.1 has been released with this issue corrected in it.

@felixxm

This comment has been minimized.

Copy link

felixxm commented Jul 11, 2018

Many thanks Anthony 🚀

@mPyth

This comment has been minimized.

Copy link

mPyth commented Aug 15, 2018

hi,
I've got the same error with cx-Oracle==6.4.1, but with downgrade to version 6.3.1 everything worked nice (I've did downgrade three times to avoid mistakenly reported problem).
Problem is reproduced on CentOS (centos-release-7-5.1804.el7.centos.2.x86_64), with Python 3.6.3 which runs in virtual environment. The rest of configuration: Apache with mod_wsgi and Django 2.0.7; Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production.

Query is quite long, so I put it at the post end.
Here is the way how query is called:

    with connections['ed'].cursor() as cursor:
        cursor.execute(query, {'p_id_ptr': 12345})

Below is a query. Please give me further instructions which are needed for bug catching (for example a kind of multi-step query bisection, to remove all comments, ...).
Remark: named query param p_id_ptr is used on 7 places inside the query.

@anthony-tuininga

This comment has been minimized.

Copy link
Member

anthony-tuininga commented Aug 16, 2018

Not sure what you are asking exactly. The issue on which you are commenting was corrected in cx_Oracle 6.4.1. If you have a new issue, please open a new issue and carefully explain what you are asking. Thanks.

@mPyth

This comment has been minimized.

Copy link

mPyth commented Aug 17, 2018

Ok, thx for prompt answer.

@hmkaraca

This comment has been minimized.

Copy link

hmkaraca commented Dec 26, 2018

We have the same with version cx_oracle 7.0 which we didn' t have with version 6.4. Can you please check and advice?

@cjbj

This comment has been minimized.

Copy link
Member

cjbj commented Dec 27, 2018

@hmkaraca please give a (runnable) python script and sql to create any data. Thanks.

@hmkaraca

This comment has been minimized.

Copy link

hmkaraca commented Dec 27, 2018

Please find document attached. Code works with version 6.2.1 properly, whereas it fails with above error with 7.0. Thanks.
docs.zip

@hmkaraca

This comment has been minimized.

Copy link

hmkaraca commented Dec 30, 2018

Is there any update on this?

@cjbj

This comment has been minimized.

Copy link
Member

cjbj commented Dec 31, 2018

@hmkaraca please simplify your scripts to a minimal test case with logical code and as few external dependencies as possible. Make sure all necessary SQL is provided (what is LS_ANR_3G_OPTIMIZER_SETTINGS?)

@hmkaraca

This comment has been minimized.

Copy link

hmkaraca commented Dec 31, 2018

code.zip
Please find code attached. I tried to minimize and modified sql, like below.
args={}
args['EXECUTIONGUID']='B3B6A70F330CA04CA1609D7AC8FD1FC0'
sql="""SELECT 'EXECUTIONGUID' FROM DUAL"""

@hmkaraca

This comment has been minimized.

Copy link

hmkaraca commented Jan 6, 2019

Is this information enough to check?

@cjbj

This comment has been minimized.

Copy link
Member

cjbj commented Jan 6, 2019

How do we run it, where do we set the password, does it need all those imports, what's the different between the two files? I could spend time on it, or I could be on vacation :) Here's an example of a simple testcase: #250

@hmkaraca

This comment has been minimized.

Copy link

hmkaraca commented Jan 7, 2019

Sorry, I converted it to a single file as attached. You can run just as python test_db_con.py. Thanks.
test_db_con.zip

@anthony-tuininga

This comment has been minimized.

Copy link
Member

anthony-tuininga commented Jan 7, 2019

Ok. That does indeed replicate the issue. The problem is that you are calling cursor.setinputsizes() with an empty dictionary and the code looks for the presence of at least one keyword argument before considering the method to be "bind by name". So the code is assuming that you are binding by position since you didn't actually provide any keyword arguments. There isn't much point in calling setinputsizes() in this case...although I will grant you that the error message is a bit confusing!

As an aside, you aren't actually using bind variables at all but simply doing a search/replace. What you should have instead is something like this:

cursor.execute("select :execution_guid from dual",
        execution_guid="B3B6A70F330CA04CA1609D7AC8FD1FC0")

That's far simpler! You should only use setinputsizes() if you need to deviate from the default bind types.

@hmkaraca

This comment has been minimized.

Copy link

hmkaraca commented Jan 7, 2019

Actually, this was the simplest case to reproduce the problem, however, even if I use setinputsizes without empty dictionary, we have the same problem, that' s why I submitted this. I don' t have the problem with same code using version cx_oracle 6.2.1

anthony-tuininga added a commit that referenced this issue Jan 8, 2019

Add additional check for calling setinputsizes() with an empty dictio…
…nary in

order to avoid the error "cx_Oracle.ProgrammingError: positional and named
binds cannot be intermixed"
(#199).
@anthony-tuininga

This comment has been minimized.

Copy link
Member

anthony-tuininga commented Jan 8, 2019

I corrected the issue with calling cursor.setinputsizes() with an empty dict. If you are having problems in other situations, please provide a new test case -- and open a new issue! Thanks.

vvvrrooomm pushed a commit to vvvrrooomm/alembic that referenced this issue Jan 10, 2019

block cx_Oracle 6.4
Prevents oracle/python-cx_Oracle#199

Change-Id: Ie964f4cf0d57b65f18761e5673f9ce67c948114a
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment