Skip to content
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

test ExceptionTest_exasol_pyodbc::()::test_integrity_error fails #42

Closed
jank opened this issue Sep 3, 2015 · 7 comments
Closed

test ExceptionTest_exasol_pyodbc::()::test_integrity_error fails #42

jank opened this issue Sep 3, 2015 · 7 comments
Assignees
Labels
bug Unwanted / harmful behavior

Comments

@jank
Copy link

jank commented Sep 3, 2015

The test case ExceptionTest_exasol_pyodbc::()::test_integrity_error (added with SQLA 1.0.5 fails). The test expects an exception upon insert with a duplicate key. However, the exception is not raised.

link to test case:
https://github.com/zzzeek/sqlalchemy/blob/0766c80b9c02fdbad3203835ab850ad690f4c03b/lib/sqlalchemy/testing/suite/test_dialect.py#L28

@jank jank added the bug Unwanted / harmful behavior label Sep 3, 2015
@andrii-kravchenko-by
Copy link

Any updates on this issue?
My code is similar to that test case and I've found out that the primary key violation error (IntegrityError) is still not raised.

@jank
Copy link
Author

jank commented Oct 5, 2021

@andrii-kravchenko-by - no change on this one and currently no priority to get this fixed. If you are willing and able to help out this might be a good issue to start with.

@Nicoretti Nicoretti self-assigned this Mar 7, 2022
@Nicoretti
Copy link
Contributor

Nicoretti commented Mar 17, 2022

The analysis of this issue has shown, that depending on the kind of insert statement, the underlying odbc driver parts do not seem to propagate the sql-error, which causes this test to fail.

Details

Validated with

DATABASE VERSION

EXASOL DB 7.1.6

EXASOL ODBC DRIVER

libexasolodbc 7.0.11 (latest 7.0.x)

PYTHON VERSION

Python 3.9.7

PYTHON ODBC VERSION

Name: pyodbc
Version: 4.0.32
Summary: DB API Module for ODBC
Home-page: https://github.com/mkleehammer/pyodbc
Author:
Author-email:
License: MIT
Location: /****/venv/lib/python3.9/site-packages
Requires:
Required-by: sqlalchemy-exasol
PLATFORM
Linux-5.13.0-35-generic-x86_64-with-glibc2.34

Steps to reproduce:

  • download the exasol odbc driver
  • install pyodbc
  • run the script regression.py (see attachment) with the parameters mentioned below:

Attention: replace driver/libexaodbc-uo2214lv1.so with the actual path to the driver.

python regression.py --odbc-log --system-info prepared-insert driver/libexaodbc-uo2214lv1.so

Expected behaviour:

  • The test program crashes with an exception (pyodbc.Error)
  • Behaviour should be equal or similar to the following test
python regression.py --odbc-log --system-info insert driver/libexaodbc-uo2214lv1.so

Observed behaviour:

  • No exception is risen/propaged from odbc or pyodbc
  • The test application exits normaly
  • The obdc log (odbc.trace) shows the sql error
[ODBC][18404][1647511527.052723][SQLEndTran.c][566]
		Exit:[SQL_ERROR]
		DIAG [42X91] [EXASOL][EXASolution driver]Exception when finishing statement with handle 1 : constraint violation - primary key (SYS_132514577408852929873408 on table MANUAL_PK) (Session: 1727541046964781056)

TODO's

  • Test with latest Exasol odbc driver (7.1.x)
  • Clarify with Exasol odbc driver team if this is an odbc or pyodbc issue

Attachments

regression.py

import argparse
import os
import sys
from contextlib import contextmanager
from pathlib import Path
from platform import platform
from subprocess import run
from tempfile import TemporaryDirectory
from textwrap import dedent

from pyodbc import connect

ODBCINST_INI_TEMPLATE = dedent(
    """
    [ODBC]
    Trace = {trace}
    TraceFile = {trace_file} 

    [EXAODBC]
    #Driver location will be appended in build environment:
    DRIVER={driver}

    """
)


@contextmanager
def environment(env_vars):
    _env = os.environ.copy()
    os.environ.update(env_vars)
    yield os.environ
    os.environ.clear()
    os.environ.update(_env)


@contextmanager
def temporary_odbc_config(config):
    with TemporaryDirectory() as tmp_dir:
        tmp_dir = Path(tmp_dir)
        config_dir = tmp_dir / "odbcconfig"
        config_dir.mkdir(exist_ok=True)
        config_file = config_dir / "odbcinst.ini"
        with open(config_file, "w") as f:
            f.write(config)
        yield config_file


@contextmanager
def odbcconfig(settings):
    with temporary_odbc_config(
        ODBCINST_INI_TEMPLATE.format(
            driver=settings["driver"],
            trace="yes" if settings["log"] is not None else "no",
            trace_file=settings["log"],
        )
    ) as cfg:
        env_vars = {"ODBCSYSINI": f"{cfg.parent.resolve()}"}
        with environment(env_vars) as env:
            yield cfg, env


@contextmanager
def odbc_connection(settings):
    with odbcconfig(settings):
        cfg = {
            "driver": "EXAODBC",
            f"server": f"{settings['db-address']}:{settings['db-port']}",
            "user": "sys",
            "password": "exasol",
        }
        connection = connect(
            "".join(
                [
                    "DRIVER={driver};",
                    "EXAHOST={server};",
                    "UID={user};",
                    "PWD={password};",
                    "EXALOGFILE=exa-odbc.trace" if settings['log'] else ""
                ]
            ).format(**cfg), autocommit=settings['autocommit']
        )
        yield connection.cursor()
        connection.close()


def insert_scenario(settings):
    with odbc_connection(settings) as cursor:
        setup_table(cursor)
        cursor.execute("INSERT INTO TEST.manual_pk VALUES (1, 'd1');")
        cursor.execute("INSERT INTO TEST.manual_pk VALUES (1, 'd1');")


def prepared_insert_scenario(settings):
    with odbc_connection(settings) as cursor:
        setup_table(cursor)
        cursor.execute("INSERT INTO TEST.manual_pk VALUES (?, ?);", (1, "foo"))
        cursor.execute("INSERT INTO TEST.manual_pk VALUES (?, ?);", (1, "foo"))


def setup_table(cursor):
    cursor.execute("DROP TABLE IF EXISTS TEST.manual_pk")
    cursor.execute(
        dedent(
            """
        CREATE TABLE TEST.manual_pk (
        id INTEGER NOT NULL,
        "data" VARCHAR(50),
        PRIMARY KEY (id)
        );
        """
        )
    )


def system_info():
    print("System Information")
    print("-" * 50)
    print("PYTHON VERSION")
    run(["python", "--version"])
    print("-" * 50)
    print("PYTHON ODBC VERSION")
    run(["python", "-m", "pip", "show", "pyodbc"])
    print("PLATFORM")
    print(platform())
    print("EXASOL ODBC DRIVER")
    print("libexasolodbc driver to 7.0.11")


def create_parser():
    parser = argparse.ArgumentParser()
    parser.add_argument(
        "scenario",
        choices=["insert", "prepared-insert"],
        help="Test scenario to execute",
    )
    parser.add_argument(
        "driver",
        type=Path,
        help="Path to the exasol odbc driver which shall be used.",
    )
    parser.add_argument(
        "--system-info",
        action="store_true",
        default=False,
        help="Print system information.",
    )
    parser.add_argument(
        "--db-address",
        default="localhost",
        help="DNS name or address of the database server to connect to.",
    )
    parser.add_argument(
        "--db-port", type=int, default=8888, help="Port of the database to connect to."
    )
    parser.add_argument(
        "--odbc-log",
        action="store_true",
        default=False,
        help="Whether or not to store the odbc trace",
    )
    parser.add_argument(
        "--autocommit",
        action="store_true",
        default=False,
        help="Whether or not to store the odbc trace",
    )
    return parser


def main(argv=None):
    parser = create_parser()
    args = parser.parse_args(argv)
    if args.system_info:
        system_info()

    settings = {
        "db-address": args.db_address,
        "db-port": args.db_port,
        "driver": args.driver,
        "autocommit": args.autocommit,
        "log": Path(".").joinpath("odbc.trace") if args.odbc_log else None,
    }
    scenario = {
        "insert": insert_scenario,
        "prepared-insert": prepared_insert_scenario,
    }[args.scenario]
    scenario(settings)
    sys.exit(0)


if __name__ == "__main__":
    main()

Sorry, something went wrong.

@Nicoretti
Copy link
Contributor

Discussion Results

Discussion and analysis with the Exasol interface team showed that the observed behavior is the expected one.
Turned out that the autocommit is turned off for the prepared inserts and therefore the error will earliest surface
when a commit is done. If the pyodbc is forced to used autocommit=True the exception will be thrown immediately
after the statement.

The script in the comment above was updated so this can be validated by running the following command:

python regression.py --odbc-log --system-info --autocommit prepared-insert driver/libexaodbc-uo2214lv1.so

Follow up

Discuss and consider how to address this in sqlalchemy-exasol

Potential Courses of Action

  • Passing autocommit to driver, because autocomit on an engine level is deprecated with sqla 1.4
  • Customize the unit test
    • use inserts instead of prepared inserts
    • disable autocommit in engine
    • ...

@Nicoretti
Copy link
Contributor

Nicoretti commented Mar 25, 2022

Update On Current Analysis

A more thorough analysis has shown, that the disabled unit test uncovered a couple of similar, but different
issues of the sqlalchemy-exasol plugin.

Scenarios

The following scenarios will trigger (uncover) the different error scenarios found with the sqlalchemy-exasol plugin,
during the analysis of this issue.

Summary

Try to insert exactly the same row twice, including the same PK using the sqlalchemy class abstractions.

Variations/Settings

Dialect:

  • pyodbc
  • turbodbc

Auto commit:

  • yes
  • no

Dialect: pyodbc, Autocommit: no

python sqla_regression.py --logging --dialect=pyodbc sqla-insert driver/libexaodbc-uo2214lv1.so

Expected:
💥 sqlalchemy raises and IntegrityError

Actual:
✔️ program finishes "successfully", but sqlalchemy log shows "swallowed" error.

ERROR:sqlalchemy.pool.impl.QueuePool:Error closing cursor: ('HY000', 'The driver did not supply an error!')
Traceback (most recent call last):
  File ".../sqlalchemy-exasol/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1368, in _safe_close_cursor
    cursor.close()
pyodbc.Error: ('HY000', 'The driver did not supply an error!')

Dialect: pyodbc, Autocommit: yes

python sqla_regression.py --logging --autocommit --dialect=pyodbc sqla-insert driver/libexaodbc-uo2214lv1.so

Expected:
💥 sqlalchemy raises and IntegrityError

Actual:
💥 sqlalchemy raises an ProgrammingError

    self.dialect.do_execute(
  File ".../sqlalchemy-exasol/venv/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 593, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('42X91', '[42X91] [EXASOL][EXASolution driver]Exception when finishing statement with handle 4 : constraint violation - primary key (SYS_1325145854795323310309632 on table MANUAL_PK) (Session: 1728273060234919936) (-6854869) (SQLExecDirectW)')
[SQL: INSERT INTO manual_pk (id, "data") VALUES (?, ?)]
[parameters: (1, 'd1')]

Dialect: trubodbc, Autocommit: no

python sqla_regression.py --logging --dialect=turbodbc sql-insert driver/libexaodbc-uo2214lv1.so

Expected:
💥 sqlalchemy raises and IntegrityError

Actual:
✔️ program finishes "successfully", but sqlalchemy log shows "swallowed" error.

ERROR:sqlalchemy.pool.impl.QueuePool:Error closing cursor: Obtaining diagnostic record from unixODBC handle failed
Traceback (most recent call last):
  File ".../sqlalchemy-exasol/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1368, in _safe_close_cursor
    cursor.close()
  File ".../sqlalchemy-exasol/venv/lib/python3.9/site-packages/turbodbc/cursor.py", line 384, in close
    self.impl._reset()
turbodbc_intern.Error: Obtaining diagnostic record from unixODBC handle failed

Dialect: trubodbc, Autocommit: yes

python sqla_regression.py --logging  --autocommit--dialect=turbodbc sql-insert driver/libexaodbc-uo2214lv1.so

Expected:
💥 sqlalchemy raises and IntegrityError

Actual:
✔️ program finishes "successfully", but sqlalchemy log shows "swallowed" error.

ERROR:sqlalchemy.pool.impl.QueuePool:Error closing cursor: Obtaining diagnostic record from unixODBC handle failed
Traceback (most recent call last):
  File ".../sqlalchemy-exasol/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1368, in _safe_close_cursor
    cursor.close()
  File ".../sqlalchemy-exasol/venv/lib/python3.9/site-packages/turbodbc/cursor.py", line 384, in close
    self.impl._reset()
turbodbc_intern.Error: Obtaining diagnostic record from unixODBC handle failed

🗒️ Note

In all combinations where swallowed errors occur, it seems that the final status/error wasn't read before the cursor/connection is closed.

sqla_regression.py

import argparse
import logging
import os
import sys
from contextlib import contextmanager
from pathlib import Path
from platform import platform
from subprocess import run
from tempfile import TemporaryDirectory
from textwrap import dedent

from sqlalchemy import Column, Integer, MetaData, String, Table, create_engine, text

ODBCINST_INI_TEMPLATE = dedent(
    """
    [ODBC]
    Trace = {trace}
    TraceFile = {trace_file} 

    [EXAODBC]
    #Driver location will be appended in build environment:
    DRIVER={driver}

    """
)


@contextmanager
def environment(env_vars):
    _env = os.environ.copy()
    os.environ.update(env_vars)
    yield os.environ
    os.environ.clear()
    os.environ.update(_env)


@contextmanager
def temporary_odbc_config(config):
    with TemporaryDirectory() as tmp_dir:
        tmp_dir = Path(tmp_dir)
        config_dir = tmp_dir / "odbcconfig"
        config_dir.mkdir(exist_ok=True)
        config_file = config_dir / "odbcinst.ini"
        with open(config_file, "w") as f:
            f.write(config)
        yield config_file


@contextmanager
def odbcconfig(driver, log_file=None):
    with temporary_odbc_config(
            ODBCINST_INI_TEMPLATE.format(
                driver=driver,
                trace="yes" if log_file is not None else "no",
                trace_file=log_file,
            )
    ) as cfg:
        env_vars = {"ODBCSYSINI": f"{cfg.parent.resolve()}"}
        with environment(env_vars) as env:
            yield cfg, env


def sqla_sql_insert(engine):
    table = setup_table(engine)
    with engine.connect() as connection:
        connection.execute(text(f"INSERT INTO {table} VALUES (1, 'd1')"))
        connection.execute(text(f"INSERT INTO {table} VALUES (1, 'd1')"))


def sqla_insert(engine):
    table = setup_table(engine)
    with engine.connect() as connection:
        connection.execute(table.insert(), {"id": 1, "data": "d1"})
        connection.execute(table.insert(), {"id": 1, "data": "d1"})


def sqla_single_insert(engine):
    table = setup_table(engine)
    with engine.connect() as connection:
        connection.execute(table.insert(), {"id": 1, "data": "d1"})


def setup_table(engine):
    engine.execute(text("DROP TABLE IF EXISTS TEST.manual_pk"))
    meta_data = MetaData()
    t = Table(
        "manual_pk",
        meta_data,
        Column("id", Integer, primary_key=True, autoincrement=False),
        Column("data", String(50)),
    )
    meta_data.create_all(engine)
    return meta_data.tables["manual_pk"]


def system_info():
    print("System Information")
    print("-" * 50)
    print("PYTHON VERSION")
    run(["python", "--version"])
    print("-" * 50)
    print("PYTHON ODBC VERSION")
    run(["python", "-m", "pip", "show", "pyodbc"])
    print("PLATFORM")
    print(platform())
    print("EXASOL ODBC DRIVER")
    print("libexasolodbc driver to 7.0.11")


def create_parser():
    parser = argparse.ArgumentParser()
    parser.add_argument(
        "scenario",
        choices=["sqla-insert", "sqla-sql-insert", "sqla-single-insert"],
        help="Test scenario to execute",
    )
    parser.add_argument(
        "driver",
        type=Path,
        help="Path to the exasol odbc driver which shall be used.",
    )
    parser.add_argument(
        "--system-info",
        action="store_true",
        default=False,
        help="Print system information.",
    )
    parser.add_argument(
        "--db-address",
        default="localhost",
        help="DNS name or address of the database server to connect to.",
    )
    parser.add_argument(
        "--db-port", type=int, default=8888, help="Port of the database to connect to."
    )
    parser.add_argument(
        "--logging",
        action="store_true",
        default=False,
        help="Whether or not to enable logging",
    )
    parser.add_argument(
        "--dialect",
        choices=["pyodbc", "turbodbc"],
        default="pyodbc",
        help="Dialect which shall be used for the connection",
    )
    parser.add_argument(
        "--autocommit",
        action="store_true",
        default=False,
        help="Whether or not to store the odbc trace",
    )
    return parser


def main(argv=None):
    parser = create_parser()
    args = parser.parse_args(argv)

    if args.system_info:
        system_info()

    if args.logging:
        log_folder = Path(os.getcwd()) / "logs"
        log_folder.mkdir(exist_ok=True)

        odbc_log = log_folder / "odbc.trace"
        exa_log = log_folder / "exa-odbc.trace"
        sqla_log = log_folder / "sqla.trace"

        logging.basicConfig(filename=f"{sqla_log}", level=logging.DEBUG)
        logging.getLogger("sqlalchemy").setLevel(logging.DEBUG)

    def make_connection_url(args):
        return "".join(
            (
                "exa+{dialect}://sys:exasol@{address}:{port}",
                "/TEST?DRIVER=EXAODBC&CONNECTIONCALL=en_US.UTF-8",
                f"&EXALOGFILE={exa_log}" if args.logging else "",
            )
        ).format(dialect=args.dialect, address=args.db_address, port=args.db_port)

    with odbcconfig(args.driver, f"{odbc_log}" if args.logging else None):
        url = make_connection_url(args)
        engine = create_engine(url, connect_args={"autocommit": args.autocommit})
        scenario = {
            "sqla-insert": sqla_insert,
            "sqla-sql-insert": sqla_sql_insert,
            "sqla-single-insert": sqla_single_insert,
        }[args.scenario]
        scenario(engine)

    sys.exit(0)


if __name__ == "__main__":
    main()

@Nicoretti
Copy link
Contributor

Nicoretti commented Mar 25, 2022

Follow Up

  • discuss error codes reported from exasol odbc driver with odbc driver team
  • outcome: odbc driver supports error mapping see this comment

Sorry, something went wrong.

@Nicoretti
Copy link
Contributor

This issue will be closed and the sub issues which have been found will be used to track and investigate and fix the individual
issues. Basically this issue will be split in 3 parts

⚠️ #120 and #119 may share some or a common error path(s)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
bug Unwanted / harmful behavior
Projects
None yet
Development

No branches or pull requests

3 participants