jgbolger commented on issue #3464: Issue using Oracle DB as superset database - Error running a migration script URL: https://github.com/apache/incubator-superset/issues/3464#issuecomment-329761103 I notice that in line 77 of env.py it states the following: ``` if engine.name in ('sqlite', 'mysql'): kwargs = { 'transaction_per_migration': True, 'transactional_ddl': True, } ``` https://github.com/apache/incubator-superset/blob/master/superset/migrations/env.py I therefore modified that if statement to include 'oracle' but that caused all sorts of errors. I changed it so that for oracle it only sets 'transactional_ddl': True and it got past the "Materialising permissions" script successfully ``` if engine.name in ('sqlite', 'mysql'): kwargs = { 'transaction_per_migration': True, 'transactional_ddl': True, } if engine.name in ('oracle'): kwargs = { 'transactional_ddl': True, } ``` but failed further down: > INFO [alembic.runtime.migration] Running upgrade 4fa88fe24e94 -> c3a8f8611885, Materializing permission > INFO [alembic.runtime.migration] Running upgrade c3a8f8611885 -> f0fbf6129e13, Adding verbose_name to tablecolumn > INFO [alembic.runtime.migration] Running upgrade f0fbf6129e13 -> 956a063c52b3, adjusting key length > INFO [alembic.runtime.migration] Running upgrade 956a063c52b3 -> 1226819ee0e3, Fix wrong constraint on table columns > WARNI [root] Could not find or drop constraint on `columns` > INFO [alembic.runtime.migration] Running upgrade 1226819ee0e3 -> d8bc074f7aad, Add new field 'is_restricted' to SqlMetric and DruidMetric > INFO [alembic.runtime.migration] Running upgrade d8bc074f7aad -> 27ae655e4247, Make creator owners > INFO [alembic.runtime.migration] Running upgrade 27ae655e4247 -> 960c69cb1f5b, add dttm_format related fields in table_columns > INFO [alembic.runtime.migration] Running upgrade 960c69cb1f5b -> f162a1dea4c4, d3format_by_metric > INFO [alembic.runtime.migration] Running upgrade f162a1dea4c4 -> **ad82a75afd82, Update models to support storing the queries.** > Traceback (most recent call last): > File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1182, in _execute_context > context) > File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 470, in do_execute > cursor.execute(statement, parameters) > cx_Oracle.DatabaseError: ORA-00904: : invalid identifier > > The above exception was the direct cause of the following exception: > > Traceback (most recent call last): > File "/usr/local/bin/superset", line 15, in <module> > manager.run() > File "/usr/local/lib/python3.6/site-packages/flask_script/__init__.py", line 412, in run > result = self.handle(sys.argv[0], sys.argv[1:]) > File "/usr/local/lib/python3.6/site-packages/flask_script/__init__.py", line 383, in handle > res = handle(*args, **config) > File "/usr/local/lib/python3.6/site-packages/flask_script/commands.py", line 216, in __call__ > return self.run(*args, **kwargs) > File "/usr/local/lib/python3.6/site-packages/flask_migrate/__init__.py", line 244, in upgrade > command.upgrade(config, revision, sql=sql, tag=tag) > File "/usr/local/lib/python3.6/site-packages/alembic/command.py", line 254, in upgrade > script.run_env() > File "/usr/local/lib/python3.6/site-packages/alembic/script/base.py", line 425, in run_env > util.load_python_file(self.dir, 'env.py') > File "/usr/local/lib/python3.6/site-packages/alembic/util/pyfiles.py", line 93, in load_python_file > module = load_module_py(module_id, path) > File "/usr/local/lib/python3.6/site-packages/alembic/util/compat.py", line 64, in load_module_py > module_id, path).load_module(module_id) > File "<frozen importlib._bootstrap_external>", line 399, in _check_name_wrapper > File "<frozen importlib._bootstrap_external>", line 823, in load_module > File "<frozen importlib._bootstrap_external>", line 682, in load_module > File "<frozen importlib._bootstrap>", line 251, in _load_module_shim > File "<frozen importlib._bootstrap>", line 675, in _load > File "<frozen importlib._bootstrap>", line 655, in _load_unlocked > File "<frozen importlib._bootstrap_external>", line 678, in exec_module > File "<frozen importlib._bootstrap>", line 205, in _call_with_frames_removed > File "/usr/local/lib/python3.6/site-packages/superset/migrations/env.py", line 107, in <module> > run_migrations_online() > File "/usr/local/lib/python3.6/site-packages/superset/migrations/env.py", line 100, in run_migrations_online > context.run_migrations() > File "<string>", line 8, in run_migrations > File "/usr/local/lib/python3.6/site-packages/alembic/runtime/environment.py", line 836, in run_migrations > self.get_context().run_migrations(**kw) > File "/usr/local/lib/python3.6/site-packages/alembic/runtime/migration.py", line 330, in run_migrations > step.migration_fn(**kw) > File "/usr/local/lib/python3.6/site-packages/superset/migrations/versions/ad82a75afd82_add_query_model.py", line 44, in upgrade > sa.PrimaryKeyConstraint('id') > File "<string>", line 8, in create_table > File "<string>", line 3, in create_table > File "/usr/local/lib/python3.6/site-packages/alembic/operations/ops.py", line 1120, in create_table > return operations.invoke(op) > File "/usr/local/lib/python3.6/site-packages/alembic/operations/base.py", line 318, in invoke > return fn(self, operation) > File "/usr/local/lib/python3.6/site-packages/alembic/operations/toimpl.py", line 101, in create_table > operations.impl.create_table(table) > File "/usr/local/lib/python3.6/site-packages/alembic/ddl/impl.py", line 194, in create_table > self._exec(schema.CreateTable(table)) > File "/usr/local/lib/python3.6/site-packages/alembic/ddl/oracle.py", line 22, in _exec > result = super(OracleImpl, self)._exec(construct, *args, **kw) > File "/usr/local/lib/python3.6/site-packages/alembic/ddl/impl.py", line 118, in _exec > return conn.execute(construct, *multiparams, **params) > File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 945, in execute > return meth(self, multiparams, params) > File "/usr/local/lib/python3.6/site-packages/sqlalchemy/sql/ddl.py", line 68, in _execute_on_connection > return connection._execute_ddl(self, multiparams, params) > File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1002, in _execute_ddl > compiled > File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1189, in _execute_context > context) > File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1402, in _handle_dbapi_exception > exc_info > File "/usr/local/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 203, in raise_from_cause > reraise(type(exception), exception, tb=exc_tb, cause=cause) > File "/usr/local/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 186, in reraise > raise value.with_traceback(tb) > File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1182, in _execute_context > context) > File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 470, in do_execute > cursor.execute(statement, parameters) > sqlalchemy.exc.DatabaseError: (cx_Oracle.DatabaseError) ORA-00904: : invalid identifier [SQL: '\nCREATE TABLE query (\n\tid INTEGER NOT NULL, \n\tclient_id VARCHAR2(11 CHAR) NOT NULL, \n\tdatabase_id INTEGER NOT NULL, \n\ttmp_table_name VARCHAR2(256 CHAR), \n\ttab_name VARCHAR2(256 CHAR), \n\tsql_editor_id VARCHAR2(256 CHAR), \n\tuser_id INTEGER, \n\tstatus VARCHAR2(16 CHAR), \n\tname VARCHAR2(256 CHAR), \n\tschema VARCHAR2(256 CHAR), \n\tsql CLOB, \n\tselect_sql CLOB, \n\texecuted_sql CLOB, \n\tlimit INTEGER, \n\tlimit_used SMALLINT, \n\tselect_as_cta SMALLINT, \n\tselect_as_cta_used SMALLINT, \n\tprogress INTEGER, \n\trows INTEGER, \n\terror_message CLOB, \n\tstart_time NUMERIC(20, 6), \n\tchanged_on DATE, \n\tend_time NUMERIC(20, 6), \n\tPRIMARY KEY (id), \n\tFOREIGN KEY(database_id) REFERENCES dbs (id), \n\tFOREIGN KEY(user_id) REFERENCES ab_user (id), \n\tCHECK (limit_used IN (0, 1)), \n\tCHECK (select_as_cta IN (0, 1)), \n\tCHECK (select_as_cta_used IN (0, 1))\n)\n\n'] ---------------------------------------------------------------- This is an automated message from the Apache Git Service. To respond to the message, please log on GitHub and use the URL above to go to the specific comment. For queries about this service, please contact Infrastructure at: [email protected]
With regards, Apache Git Services
