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-329784869 Looking at that SQL for creating the query table - the issue is that it is attempting to use a reserved word "rows" as a column name. You can tell SQLAlchemy to add double quotes to columns that it does not know to be reserved words by including the quote=True attribute on the column definition `sa.Column('rows', sa.Integer(), nullable=True, quote=True),` I did this and reran the db upgrade and it now completes all of the migration scripts but fails with the following output: > INFO [alembic.runtime.migration] Context impl OracleImpl. > INFO [alembic.runtime.migration] Will assume transactional DDL. > INFO [alembic.runtime.migration] Running upgrade -> 4e6a06bad7a8, Init > INFO [alembic.runtime.migration] Running upgrade 4e6a06bad7a8 -> 5a7bad26f2a7, empty message > INFO [alembic.runtime.migration] Running upgrade 5a7bad26f2a7 -> 1e2841a4128, empty message > INFO [alembic.runtime.migration] Running upgrade 1e2841a4128 -> 2929af7925ed, TZ offsets in data sources > INFO [alembic.runtime.migration] Running upgrade 2929af7925ed -> 289ce07647b, Add encrypted password field > INFO [alembic.runtime.migration] Running upgrade 289ce07647b -> 1a48a5411020, adding slug to dash > INFO [alembic.runtime.migration] Running upgrade 1a48a5411020 -> 315b3f4da9b0, adding log model > INFO [alembic.runtime.migration] Running upgrade 315b3f4da9b0 -> 55179c7f25c7, sqla_descr > INFO [alembic.runtime.migration] Running upgrade 55179c7f25c7 -> 12d55656cbca, is_featured > INFO [alembic.runtime.migration] Running upgrade 12d55656cbca -> 2591d77e9831, user_id > INFO [alembic.runtime.migration] Running upgrade 2591d77e9831 -> 8e80a26a31db, empty message > INFO [alembic.runtime.migration] Running upgrade 8e80a26a31db -> 7dbf98566af7, empty message > INFO [alembic.runtime.migration] Running upgrade 7dbf98566af7 -> 43df8de3a5f4, empty message > INFO [alembic.runtime.migration] Running upgrade 43df8de3a5f4 -> d827694c7555, css templates > INFO [alembic.runtime.migration] Running upgrade d827694c7555 -> 430039611635, log more > INFO [alembic.runtime.migration] Running upgrade 430039611635 -> 18e88e1cc004, making audit nullable > INFO [alembic.runtime.migration] Running upgrade 18e88e1cc004 -> 836c0bf75904, cache_timeouts > INFO [alembic.runtime.migration] Running upgrade 18e88e1cc004 -> a2d606a761d9, adding favstar model > INFO [alembic.runtime.migration] Running upgrade a2d606a761d9, 836c0bf75904 -> d2424a248d63, empty message > INFO [alembic.runtime.migration] Running upgrade d2424a248d63 -> 763d4b211ec9, fixing audit fk > INFO [alembic.runtime.migration] Running upgrade d2424a248d63 -> 1d2ddd543133, log dt > INFO [alembic.runtime.migration] Running upgrade 1d2ddd543133, 763d4b211ec9 -> fee7b758c130, empty message > INFO [alembic.runtime.migration] Running upgrade fee7b758c130 -> 867bf4f117f9, Adding extra field to Database model > INFO [alembic.runtime.migration] Running upgrade 867bf4f117f9 -> bb51420eaf83, add schema to table model > INFO [alembic.runtime.migration] Running upgrade bb51420eaf83 -> b4456560d4f3, change_table_unique_constraint > INFO [alembic.runtime.migration] Running upgrade b4456560d4f3 -> 4fa88fe24e94, owners_many_to_many > 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. > INFO [alembic.runtime.migration] Running upgrade ad82a75afd82 -> 3c3ffe173e4f, add_sql_string_to_table > INFO [alembic.runtime.migration] Running upgrade 3c3ffe173e4f -> 41f6a59a61f2, database options for sql lab > INFO [alembic.runtime.migration] Running upgrade 41f6a59a61f2 -> 4500485bde7d, allow_run_sync_async > INFO [alembic.runtime.migration] Running upgrade 4500485bde7d -> 65903709c321, allow_dml > INFO [alembic.runtime.migration] Running upgrade 41f6a59a61f2 -> 33d996bcc382 > INFO [alembic.runtime.migration] Running upgrade 33d996bcc382, 65903709c321 -> b347b202819b, empty message > INFO [alembic.runtime.migration] Running upgrade b347b202819b -> 5e4a03ef0bf0, Add access_request table to manage requests to access datastores. > INFO [alembic.runtime.migration] Running upgrade 5e4a03ef0bf0 -> eca4694defa7, sqllab_setting_defaults > INFO [alembic.runtime.migration] Running upgrade eca4694defa7 -> ab3d66c4246e, add_cache_timeout_to_druid_cluster > INFO [alembic.runtime.migration] Running upgrade eca4694defa7 -> 3b626e2a6783, Sync DB with the models.py. > WARNI [root] (cx_Oracle.DatabaseError) ORA-01418: specified index does not exist [SQL: '\nDROP INDEX table_name'] > INFO [alembic.runtime.migration] Running upgrade 3b626e2a6783, ab3d66c4246e -> ef8843b41dac, empty message > INFO [alembic.runtime.migration] Running upgrade ef8843b41dac -> b46fa1b0b39e, Add json_metadata to the tables table. > INFO [alembic.runtime.migration] Running upgrade b46fa1b0b39e -> 7e3ddad2a00b, results_key to query > INFO [alembic.runtime.migration] Running upgrade 7e3ddad2a00b -> ad4d656d92bc, Add avg() to default metrics > INFO [alembic.runtime.migration] Running upgrade ad4d656d92bc -> c611f2b591b8, dim_spec > INFO [alembic.runtime.migration] Running upgrade c611f2b591b8 -> e46f2d27a08e, materialize perms > INFO [alembic.runtime.migration] Running upgrade e46f2d27a08e -> f1f2d4af5b90, Enable Filter Select > INFO [alembic.runtime.migration] Running upgrade e46f2d27a08e -> 525c854f0005, log_this_plus > INFO [alembic.runtime.migration] Running upgrade 525c854f0005, f1f2d4af5b90 -> 6414e83d82b7, empty message > INFO [alembic.runtime.migration] Running upgrade 6414e83d82b7 -> 1296d28ec131, Adds params to the datasource (druid) table > INFO [alembic.runtime.migration] Running upgrade 1296d28ec131 -> f18570e03440, Add index on the result key to the query table. > INFO [alembic.runtime.migration] Running upgrade f18570e03440 -> bcf3126872fc, Add keyvalue table > INFO [alembic.runtime.migration] Running upgrade f18570e03440 -> db0c65b146bd, update_slice_model_json > INFO [alembic.runtime.migration] Running upgrade db0c65b146bd -> a99f2f7c195a, rewriting url from shortner with new format > INFO [alembic.runtime.migration] Running upgrade a99f2f7c195a, bcf3126872fc -> d6db5a5cdb5d, empty message > INFO [alembic.runtime.migration] Running upgrade d6db5a5cdb5d -> b318dfe5fb6c, adding verbose_name to druid column > INFO [alembic.runtime.migration] Running upgrade d6db5a5cdb5d -> 732f1c06bcbf, add fetch values predicate > INFO [alembic.runtime.migration] Running upgrade 732f1c06bcbf, b318dfe5fb6c -> ea033256294a, empty message > INFO [alembic.runtime.migration] Running upgrade b318dfe5fb6c -> db527d8c4c78, Add verbose name to DruidCluster and Database > INFO [alembic.runtime.migration] Running upgrade db527d8c4c78, ea033256294a -> 979c03af3341, empty message > INFO [alembic.runtime.migration] Running upgrade 979c03af3341 -> a6c18f869a4e, query.start_running_time > INFO [alembic.runtime.migration] Running upgrade a6c18f869a4e -> 2fcdcb35e487, saved_queries > INFO [alembic.runtime.migration] Running upgrade 2fcdcb35e487 -> a65458420354, add_result_backend_time_logging > INFO [alembic.runtime.migration] Running upgrade a65458420354 -> ca69c70ec99b, tracking_url > Loaded your LOCAL configuration at [/etc/superset/superset_config.py] > 2017-09-15 13:56:33,818:INFO:root:Syncing role definition > 2017-09-15 13:56:33,818:INFO:root:Creating database reference > 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.IntegrityError: ORA-01400: cannot insert NULL into ("XDB"."DBS"."ID") > > 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/superset/cli.py", line 28, in init > security.sync_role_definitions() > File "/usr/local/lib/python3.6/site-packages/superset/security.py", line 202, in sync_role_definitions > get_or_create_main_db() > File "/usr/local/lib/python3.6/site-packages/superset/security.py", line 101, in get_or_create_main_db > db.session.commit() > File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/scoping.py", line 157, in do > return getattr(self.registry(), name)(*args, **kwargs) > File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 906, in commit > self.transaction.commit() > File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 461, in commit > self._prepare_impl() > File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 441, in _prepare_impl > self.session.flush() > File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 2171, in flush > self._flush(objects) > File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 2291, in _flush > transaction.rollback(_capture_exception=True) > File "/usr/local/lib/python3.6/site-packages/sqlalchemy/util/langhelpers.py", line 66, in __exit__ > compat.reraise(exc_type, exc_value, exc_tb) > File "/usr/local/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 187, in reraise > raise value > File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 2255, in _flush > flush_context.execute() > File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/unitofwork.py", line 389, in execute > rec.execute(self) > File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/unitofwork.py", line 548, in execute > uow > File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/persistence.py", line 181, in save_obj > mapper, table, insert) > File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/persistence.py", line 835, in _emit_insert_statements > execute(statement, 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/elements.py", line 263, in _execute_on_connection > return connection._execute_clauseelement(self, multiparams, params) > File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1053, in _execute_clauseelement > compiled_sql, distilled_params > 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.IntegrityError: (cx_Oracle.IntegrityError) ORA-01400: cannot insert NULL into ("XDB"."DBS"."ID") [SQL: 'INSERT INTO dbs (created_on, changed_on, verbose_name, database_name, sqlalchemy_uri, password, cache_timeout, select_as_create_table_as, expose_in_sqllab, allow_run_sync, allow_run_async, allow_ctas, allow_dml, force_ctas_schema, extra, perm, created_by_fk, changed_by_fk) VALUES (:created_on, :changed_on, :verbose_name, :database_name, :sqlalchemy_uri, :password, :cache_timeout, :select_as_create_table_as, :expose_in_sqllab, :allow_run_sync, :allow_run_async, :allow_ctas, :allow_dml, :force_ctas_schema, :extra, :perm, :created_by_fk, :changed_by_fk) RETURNING dbs.id INTO :ret_0'] [parameters: {'created_on': datetime.datetime(2017, 9, 15, 13, 56, 33, 831803), 'changed_on': datetime.datetime(2017, 9, 15, 13, 56, 33, 831810), 'verbose_name': None, 'database_name': 'main', 'sqlalchemy_uri': 'oracle://XDB:XXXXXXXXXX@oracledb:1521/XE', 'password': b'I5GyQ4mhCkC7uNGFm rwXLw==', 'cache_timeout': None, 'select_as_create_table_as': 0, 'expose_in_sqllab': 1, 'allow_run_sync': 1, 'allow_run_async': 0, 'allow_ctas': 0, 'allow_dml': 0, 'force_ctas_schema': None, 'extra': '{\n "metadata_params": {},\n "engine_params": {}\n}\n', 'perm': None, 'created_by_fk': None, 'changed_by_fk': None, 'ret_0': <cx_Oracle.NUMBER with value None>}] ---------------------------------------------------------------- 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
