Uploaded image for project: 'Data Management'
  1. Data Management
  2. DM-21454

Foreign key error when running makeButlerRepo.py against Oracle

    Details

    • Type: Bug
    • Status: Done
    • Resolution: Done
    • Fix Version/s: None
    • Component/s: daf_butler
    • Labels:
      None
    • Templates:
    • Story Points:
      3
    • Team:
      Data Release Production

      Description

      Running with daily stack d_2019_09_26 + master ci_hsc_gen3 + Oracle. Start with clean local dir and clean Oracle db. scons dies in makeButlerRepo.py

      sqlalchemy.exc.DatabaseError: (cx_Oracle.DatabaseError) ORA-02264: name already used by an existing constraint [SQL: 'ALTER TABLE run ADD CONSTRAINT run_dataset_fkey FOREIGN KEY(pipeline_id) REFERENCES dataset (dataset_id) ON DELETE SET NULL'] (Background on this error at: http://sqlalche.me/e/4xp6)
      

      See end of description for full traceback. Ran it with sqlalchemy echo=True and then looked for run_dataset_fkey in output:

      $ grep run_dataset_fkey run.out
      2019-09-26 13:58:39,356 INFO sqlalchemy.engine.base.Engine ALTER TABLE run ADD CONSTRAINT run_dataset_fkey FOREIGN KEY(environment_id) REFERENCES dataset (dataset_id) ON DELETE SET NULL
      2019-09-26 13:58:39,591 INFO sqlalchemy.engine.base.Engine ALTER TABLE run ADD CONSTRAINT run_dataset_fkey FOREIGN KEY(pipeline_id) REFERENCES dataset (dataset_id) ON DELETE SET NULL
      sqlalchemy.exc.DatabaseError: (cx_Oracle.DatabaseError) ORA-02264: name already used by an existing constraint [SQL: 'ALTER TABLE run ADD CONSTRAINT run_dataset_fkey FOREIGN KEY(pipeline_id) REFERENCES dataset (dataset_id) ON DELETE SET NULL'] (Background on this error at: http://sqlalche.me/e/4xp6)
      

      Full traceback

      Traceback (most recent call last):
        File "/usr/local/lsst_stack/d_2019_09_26/stack/miniconda3-4.5.12-1172c30/Linux64/daf_butler/18.1.0-26-gfeec7ef/bin/makeButlerRepo.py", line 56, in <module>
          outfile=args.outfile)
        File "/usr/local/lsst_stack/d_2019_09_26/stack/miniconda3-4.5.12-1172c30/Linux64/daf_butler/18.1.0-26-gfeec7ef/python/lsst/daf/butler/butler.py", line 226, in makeRepo
          registryClass.fromConfig(config, create=createRegistry, butlerRoot=root)
        File "/usr/local/lsst_stack/d_2019_09_26/stack/miniconda3-4.5.12-1172c30/Linux64/daf_butler/18.1.0-26-gfeec7ef/python/lsst/daf/butler/core/registry.py", line 165, in fromConfig
          butlerRoot=butlerRoot)
        File "/usr/local/lsst_stack/d_2019_09_26/stack/miniconda3-4.5.12-1172c30/Linux64/daf_butler/18.1.0-26-gfeec7ef/python/lsst/daf/butler/registries/oracleRegistry.py", line 79, in __init__
          butlerRoot=butlerRoot)
        File "/usr/local/lsst_stack/d_2019_09_26/stack/miniconda3-4.5.12-1172c30/Linux64/daf_butler/18.1.0-26-gfeec7ef/python/lsst/daf/butler/registries/sqlRegistry.py", line 112, in __init__
          self._createTables(self._schema, self._connection)
        File "/usr/local/lsst_stack/d_2019_09_26/stack/miniconda3-4.5.12-1172c30/Linux64/daf_butler/18.1.0-26-gfeec7ef/python/lsst/daf/butler/registries/sqlRegistry.py", line 193, in _createTables
          schema.metadata.create_all(connection)
        File "/usr/local/lsst_stack/d_2019_09_26/stack/miniconda3-4.5.12-1172c30/Linux64/sqlalchemy/1.2.16+2/lib/python/SQLAlchemy-1.2.16-py3.7-linux-x86_64.egg/sqlalchemy/sql/schema.py", line 4201, in create_all
          ddl.SchemaGenerator, self, checkfirst=checkfirst, tables=tables
        File "/usr/local/lsst_stack/d_2019_09_26/stack/miniconda3-4.5.12-1172c30/Linux64/sqlalchemy/1.2.16+2/lib/python/SQLAlchemy-1.2.16-py3.7-linux-x86_64.egg/sqlalchemy/engine/base.py", line 1593, in _run_visitor
          visitorcallable(self.dialect, self, **kwargs).traverse_single(element)
        File "/usr/local/lsst_stack/d_2019_09_26/stack/miniconda3-4.5.12-1172c30/Linux64/sqlalchemy/1.2.16+2/lib/python/SQLAlchemy-1.2.16-py3.7-linux-x86_64.egg/sqlalchemy/sql/visitors.py", line 130, in traverse_single
          return meth(obj, **kw)
        File "/usr/local/lsst_stack/d_2019_09_26/stack/miniconda3-4.5.12-1172c30/Linux64/sqlalchemy/1.2.16+2/lib/python/SQLAlchemy-1.2.16-py3.7-linux-x86_64.egg/sqlalchemy/sql/ddl.py", line 788, in visit_metadata
          self.traverse_single(fkc)
        File "/usr/local/lsst_stack/d_2019_09_26/stack/miniconda3-4.5.12-1172c30/Linux64/sqlalchemy/1.2.16+2/lib/python/SQLAlchemy-1.2.16-py3.7-linux-x86_64.egg/sqlalchemy/sql/visitors.py", line 130, in traverse_single
          return meth(obj, **kw)
        File "/usr/local/lsst_stack/d_2019_09_26/stack/miniconda3-4.5.12-1172c30/Linux64/sqlalchemy/1.2.16+2/lib/python/SQLAlchemy-1.2.16-py3.7-linux-x86_64.egg/sqlalchemy/sql/ddl.py", line 857, in visit_foreign_key_constraint
          self.connection.execute(AddConstraint(constraint))
        File "/usr/local/lsst_stack/d_2019_09_26/stack/miniconda3-4.5.12-1172c30/Linux64/sqlalchemy/1.2.16+2/lib/python/SQLAlchemy-1.2.16-py3.7-linux-x86_64.egg/sqlalchemy/engine/base.py", line 974, in execute
          return meth(self, multiparams, params)
        File "/usr/local/lsst_stack/d_2019_09_26/stack/miniconda3-4.5.12-1172c30/Linux64/sqlalchemy/1.2.16+2/lib/python/SQLAlchemy-1.2.16-py3.7-linux-x86_64.egg/sqlalchemy/sql/ddl.py", line 72, in _execute_on_connection
          return connection._execute_ddl(self, multiparams, params)
        File "/usr/local/lsst_stack/d_2019_09_26/stack/miniconda3-4.5.12-1172c30/Linux64/sqlalchemy/1.2.16+2/lib/python/SQLAlchemy-1.2.16-py3.7-linux-x86_64.egg/sqlalchemy/engine/base.py", line 1036, in _execute_ddl
          compiled,
        File "/usr/local/lsst_stack/d_2019_09_26/stack/miniconda3-4.5.12-1172c30/Linux64/sqlalchemy/1.2.16+2/lib/python/SQLAlchemy-1.2.16-py3.7-linux-x86_64.egg/sqlalchemy/engine/base.py", line 1234, in _execute_context
          e, statement, parameters, cursor, context
        File "/usr/local/lsst_stack/d_2019_09_26/stack/miniconda3-4.5.12-1172c30/Linux64/sqlalchemy/1.2.16+2/lib/python/SQLAlchemy-1.2.16-py3.7-linux-x86_64.egg/sqlalchemy/engine/base.py", line 1452, in _handle_dbapi_exception
          util.raise_from_cause(sqlalchemy_exception, exc_info)
        File "/usr/local/lsst_stack/d_2019_09_26/stack/miniconda3-4.5.12-1172c30/Linux64/sqlalchemy/1.2.16+2/lib/python/SQLAlchemy-1.2.16-py3.7-linux-x86_64.egg/sqlalchemy/util/compat.py", line 296, in raise_from_cause
          reraise(type(exception), exception, tb=exc_tb, cause=cause)
        File "/usr/local/lsst_stack/d_2019_09_26/stack/miniconda3-4.5.12-1172c30/Linux64/sqlalchemy/1.2.16+2/lib/python/SQLAlchemy-1.2.16-py3.7-linux-x86_64.egg/sqlalchemy/util/compat.py", line 276, in reraise
          raise value.with_traceback(tb)
        File "/usr/local/lsst_stack/d_2019_09_26/stack/miniconda3-4.5.12-1172c30/Linux64/sqlalchemy/1.2.16+2/lib/python/SQLAlchemy-1.2.16-py3.7-linux-x86_64.egg/sqlalchemy/engine/base.py", line 1230, in _execute_context
          cursor, statement, parameters, context
        File "/usr/local/lsst_stack/d_2019_09_26/stack/miniconda3-4.5.12-1172c30/Linux64/sqlalchemy/1.2.16+2/lib/python/SQLAlchemy-1.2.16-py3.7-linux-x86_64.egg/sqlalchemy/engine/default.py", line 536, in do_execute
          cursor.execute(statement, parameters)
      sqlalchemy.exc.DatabaseError: (cx_Oracle.DatabaseError) ORA-02264: name already used by an existing constraint [SQL: 'ALTER TABLE run ADD CONSTRAINT run_dataset_fkey FOREIGN KEY(pipeline_id) REFERENCES dataset (dataset_id) ON DELETE SET NULL'] (Background on this error at: http://sqlalche.me/e/4xp6)
      

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                jbosch Jim Bosch
                Reporter:
                mgower Michelle Gower
                Reviewers:
                Michelle Gower
                Watchers:
                Christopher Stephens, Jim Bosch, John Swinbank, Michelle Gower, Tim Jenness
              • Votes:
                0 Vote for this issue
                Watchers:
                5 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:

                  Summary Panel