Table already created - failing tests when upgrading from 1.2.19 to 1.3.6

[Jan Karstens]

Hello,

I am trying to upgrade sqlalchemy-exasol from 1.2.19 to 1.3.6.

With the new release, a number of new test cases have been added to the test suite. A quite large number of them fails, as during test setup a table is trying to be created that already exists. This happens before the test is run (I tried to stop in the debugger with the --trace option of py.test but the issue occurs earlier).

Most of the offending tests live in ComponentReflectionTest. Here is an SQL trace of test_varchar_reflection:

INFO     sqlalchemy.engine.base.Engine:base.py:1305 SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1 FROM DUAL

INFO     sqlalchemy.engine.base.Engine:base.py:1306 ()

INFO     sqlalchemy.engine.base.Engine:base.py:1305 SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1 FROM DUAL

INFO     sqlalchemy.engine.base.Engine:base.py:1306 ()

INFO     sqlalchemy.engine.base.Engine:base.py:1209 SELECT table_name from SYS.EXA_ALL_TABLES WHERE table_name = ? AND table_schema = ?

INFO     sqlalchemy.engine.base.Engine:base.py:1211 ('USERS', 'TEST_SCHEMA')

INFO     sqlalchemy.engine.base.Engine:base.py:1209 SELECT table_name from SYS.EXA_ALL_TABLES WHERE table_name = ? AND table_schema = ?

INFO     sqlalchemy.engine.base.Engine:base.py:1211 ('DINGALINGS', 'TEST_SCHEMA')

INFO     sqlalchemy.engine.base.Engine:base.py:1209 SELECT table_name from SYS.EXA_ALL_TABLES WHERE table_name = ? AND table_schema = ?

INFO     sqlalchemy.engine.base.Engine:base.py:1211 ('EMAIL_ADDRESSES', 'TEST_SCHEMA')

INFO     sqlalchemy.engine.base.Engine:base.py:1209 SELECT table_name from SYS.EXA_ALL_TABLES WHERE table_name = ? AND table_schema = ?

INFO     sqlalchemy.engine.base.Engine:base.py:1211 ('COMMENT_TEST', 'TEST_SCHEMA')

INFO     sqlalchemy.engine.base.Engine:base.py:1209 SELECT table_name from SYS.EXA_ALL_TABLES WHERE table_name = ? AND table_schema = ?

INFO     sqlalchemy.engine.base.Engine:base.py:1211 ('USERS', 'TEST_SCHEMA')

INFO     sqlalchemy.engine.base.Engine:base.py:1209 SELECT table_name from SYS.EXA_ALL_TABLES WHERE table_name = ? AND table_schema = ?

INFO     sqlalchemy.engine.base.Engine:base.py:1211 ('DINGALINGS', 'TEST_SCHEMA')

INFO     sqlalchemy.engine.base.Engine:base.py:1209 SELECT table_name from SYS.EXA_ALL_TABLES WHERE table_name = ? AND table_schema = ?

INFO     sqlalchemy.engine.base.Engine:base.py:1211 ('EMAIL_ADDRESSES', 'TEST_SCHEMA')

INFO     sqlalchemy.engine.base.Engine:base.py:1209 SELECT table_name from SYS.EXA_ALL_TABLES WHERE table_name = ? AND table_schema = ?

INFO     sqlalchemy.engine.base.Engine:base.py:1211 ('COMMENT_TEST', 'TEST_SCHEMA')

INFO     sqlalchemy.engine.base.Engine:base.py:1209

CREATE TABLE users (

user_id INTEGER IDENTITY NOT NULL,

test1 CHAR(5) NOT NULL,

test2 FLOAT NOT NULL,

parent_user_id INTEGER,

PRIMARY KEY (user_id)

)

INFO     sqlalchemy.engine.base.Engine:base.py:1211 ()

INFO     sqlalchemy.engine.base.Engine:base.py:759 COMMIT

INFO     sqlalchemy.engine.base.Engine:base.py:1209 ALTER TABLE users ADD CONSTRAINT user_id_fk FOREIGN KEY(parent_user_id) REFERENCES users (user_id)

INFO     sqlalchemy.engine.base.Engine:base.py:1211 ()

INFO     sqlalchemy.engine.base.Engine:base.py:759 COMMIT

INFO     sqlalchemy.engine.base.Engine:base.py:1209

CREATE TABLE comment_test (

id INTEGER IDENTITY NOT NULL,

"data" VARCHAR(20),

d2 VARCHAR(20),

PRIMARY KEY (id)

)

INFO     sqlalchemy.engine.base.Engine:base.py:1211 ()

INFO     sqlalchemy.engine.base.Engine:base.py:759 COMMIT

INFO     sqlalchemy.engine.base.Engine:base.py:1209

CREATE TABLE test_schema.users (

user_id INTEGER IDENTITY NOT NULL,

test1 CHAR(5) NOT NULL,

test2 FLOAT NOT NULL,

parent_user_id INTEGER,

PRIMARY KEY (user_id)

)

INFO     sqlalchemy.engine.base.Engine:base.py:1211 ()

INFO     sqlalchemy.engine.base.Engine:base.py:737 ROLLBACK

What I noticed is, that the first create for table USERS happens without schema name, the second with. In Exasol the default schema during tests is 'test_schema', so the second create conflicts and leads to this error:

sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('42500', '[42500] [EXASOL][EXASolution driver]object USERS already exists (Session: 1641408017719985021) (-1) (SQLExecDirectW)')

[SQL:

CREATE TABLE test_schema.users (

user_id INTEGER IDENTITY NOT NULL,

test1 CHAR(5) NOT NULL,

test2 FLOAT NOT NULL,

parent_user_id INTEGER,

PRIMARY KEY (user_id)

)

I am a bit stuck as there seems to be quite some magic at work how tests are setup and prepared. Any help on where to look closer is greatly appreciated.

Jan

[Mike Bayer]

The test suite has an assumption that schemas named "test_schema" and "test_schema_2" should exist and be distinct from whatever the "default" schema of the database is (the names of these schemas are mentioned at https://github.com/sqlalchemy/sqlalchemy/blob/master/README.unittests.rst#database-configuration ).  So when you run the tests, you would need to configure a database connection where the implicit namespace does not overlap with the "test_schema" or "test_schema_2" explicit namespaces, if you want to enable the explicit schema tests.  These tests can also be entirely disabled within the test/requirements.py file under the "schemas" method.

These tests are not new in the 1.3 series, the ComponentReflectionTest has been there for many years since before version 1.0 as well as the hardcoded "test_schema" name used throughout for explicit schema tests, so there should be nothing from 1.2 -> 1.3 that would cause this problem to newly appear.

sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('42500', '[42500] [EXASOL][EXASolution driver]object USERS already exists (Session: 1641408017719985021) (-1) (SQLExecDirectW)')

[SQL:

CREATE TABLE test_schema.users (

user_id INTEGER IDENTITY NOT NULL,

test1 CHAR(5) NOT NULL,

test2 FLOAT NOT NULL,

parent_user_id INTEGER,

PRIMARY KEY (user_id)

)

I am a bit stuck as there seems to be quite some magic at work how tests are setup and prepared. Any help on where to look closer is greatly appreciated.

Jan

--

You received this message because you are subscribed to the Google Groups "sqlalchemy-devel" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy-dev...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy-devel/d278de94-9fda-4b42-b606-baf92999f76a%40googlegroups.com.

[Jan]

Thank you for the details. I did had two test schemas but was using the wrong schema name in my local tests. Changing the schema name did the trick. I just did - unintentionally - two changes at once: using the wrong schema name and bumping the SQLAlchemy. That led me down the path of thinking it was an issue with newly introduced test cases.