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

Foreign key error when running makeButlerRepo.py against Oracle

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Done
    • Resolution: Done
    • Fix Version/s: None
    • Component/s: daf_butler
    • Labels:
      None

      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

            Hide
            mgower Michelle Gower added a comment -

            We're seeing weird behavior that we're trying to track down. The data doesn't have NULL in it, but the insert statement tries to insert NULL (and does insert NULL if we turn off the constraints). Jim BoschI wouldn't spend too much time yet digging through code until we can figure out why this is happening.

            p.s., A separate note: The code outputs "RuntimeError: A skymap with the same name or hash already exists." when the error really is that it couldn't insert for different reasons.

            Show
            mgower Michelle Gower added a comment - We're seeing weird behavior that we're trying to track down. The data doesn't have NULL in it, but the insert statement tries to insert NULL (and does insert NULL if we turn off the constraints). Jim Bosch I wouldn't spend too much time yet digging through code until we can figure out why this is happening. p.s., A separate note: The code outputs "RuntimeError: A skymap with the same name or hash already exists." when the error really is that it couldn't insert for different reasons.
            Hide
            mgower Michelle Gower added a comment - - edited

            The character set mismatch error just disappears if we use the newer version of SQLAlchemy in eups (1.3.8). (The daily that I had been using was still the older version.) Are we all happy with just saying that we can't use the older version of SQLAlchemy?

            Show
            mgower Michelle Gower added a comment - - edited The character set mismatch error just disappears if we use the newer version of SQLAlchemy in eups (1.3.8). (The daily that I had been using was still the older version.) Are we all happy with just saying that we can't use the older version of SQLAlchemy?
            Hide
            tjenness Tim Jenness added a comment -

            Yes. Seems like it was a sql alchemy python 3 bug that they fixed.

            Show
            tjenness Tim Jenness added a comment - Yes. Seems like it was a sql alchemy python 3 bug that they fixed.
            Hide
            mgower Michelle Gower added a comment -

            With these changes and a newer version of SQLAlchemy, I was able to run ci_hsc_gen3 with Oracle.

            Show
            mgower Michelle Gower added a comment - With these changes and a newer version of SQLAlchemy, I was able to run ci_hsc_gen3 with Oracle.
            Hide
            swinbank John Swinbank added a comment -

            Jim Bosch, could you please add an SP count to this ticket? Thanks.

            Show
            swinbank John Swinbank added a comment - Jim Bosch , could you please add an SP count to this ticket? Thanks.

              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:

                  CI Builds

                  No builds found.