SQLalchemy Integrity Error when running msnoise new_jobs

[zibiwilczynski]

Hi,

I am facing a problem with sqlalchemy when running msnoise new_jobs. I would appreciate your help on that.

I get the error message:

msnoise new_jobs
2024-07-22 09:35:54.898052 msnoise [INFO]: *** Starting: New Jobs ***
2024-07-22 09:35:55.027316 msnoise [INFO]: Scanning New/Modified files
2024-07-22 09:38:02.175639 msnoise [INFO]: Determining available data for each "updated date"
Traceback (most recent call last):
  File "/home/zibi/miniconda3/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1910, in _execute_context
    self.dialect.do_execute(
  File "/home/zibi/miniconda3/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
    cursor.execute(statement, parameters)
  File "/home/zibi/.local/lib/python3.10/site-packages/pymysql/cursors.py", line 153, in execute
    result = self._query(query)
  File "/home/zibi/.local/lib/python3.10/site-packages/pymysql/cursors.py", line 322, in _query
    conn.query(q)
  File "/home/zibi/.local/lib/python3.10/site-packages/pymysql/connections.py", line 558, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/home/zibi/.local/lib/python3.10/site-packages/pymysql/connections.py", line 822, in _read_query_result
    result.read()
  File "/home/zibi/.local/lib/python3.10/site-packages/pymysql/connections.py", line 1200, in read
    first_packet = self.connection._read_packet()
  File "/home/zibi/.local/lib/python3.10/site-packages/pymysql/connections.py", line 772, in _read_packet
    packet.raise_for_error()
  File "/home/zibi/.local/lib/python3.10/site-packages/pymysql/protocol.py", line 221, in raise_for_error
    err.raise_mysql_exception(self._data)
  File "/home/zibi/.local/lib/python3.10/site-packages/pymysql/err.py", line 143, in raise_mysql_exception
    raise errorclass(errno, errval)
pymysql.err.IntegrityError: (1062, "Duplicate entry '2022-06-17-LUDV.CH100.100:LUDV.CH1-CC' for key 'jobs.job_index'")

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/home/zibi/miniconda3/bin/msnoise", line 8, in <module>
    sys.exit(run())
  File "/home/zibi/miniconda3/lib/python3.10/site-packages/msnoise/scripts/msnoise.py", line 1536, in run
    cli(obj={})
  File "/home/zibi/.local/lib/python3.10/site-packages/click/core.py", line 1157, in __call__
    return self.main(*args, **kwargs)
  File "/home/zibi/.local/lib/python3.10/site-packages/click/core.py", line 1078, in main
    rv = self.invoke(ctx)
  File "/home/zibi/.local/lib/python3.10/site-packages/click/core.py", line 1688, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/home/zibi/.local/lib/python3.10/site-packages/click/core.py", line 1434, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/home/zibi/.local/lib/python3.10/site-packages/click/core.py", line 783, in invoke
    return __callback(*args, **kwargs)
  File "/home/zibi/miniconda3/lib/python3.10/site-packages/msnoise/scripts/msnoise.py", line 736, in new_jobs
    main(init, nocc)
  File "/home/zibi/miniconda3/lib/python3.10/site-packages/msnoise/s02new_jobs.py", line 164, in main
    update_job(db, job['day'], job['pair'],
  File "/home/zibi/miniconda3/lib/python3.10/site-packages/msnoise/api.py", line 817, in update_job
    .params(day=day, pair=pair, jobtype=jobtype).first()
  File "/home/zibi/miniconda3/lib/python3.10/site-packages/sqlalchemy/orm/query.py", line 2824, in first
    return self.limit(1)._iter().first()
  File "/home/zibi/miniconda3/lib/python3.10/site-packages/sqlalchemy/orm/query.py", line 2916, in _iter
    result = self.session.execute(
  File "/home/zibi/miniconda3/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 1665, in execute
    ) = compile_state_cls.orm_pre_session_exec(
  File "/home/zibi/miniconda3/lib/python3.10/site-packages/sqlalchemy/orm/context.py", line 312, in orm_pre_session_exec
    session._autoflush()
  File "/home/zibi/miniconda3/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 2264, in _autoflush
    util.raise_(e, with_traceback=sys.exc_info()[2])
  File "/home/zibi/miniconda3/lib/python3.10/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
    raise exception
  File "/home/zibi/miniconda3/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 2253, in _autoflush
    self.flush()
  File "/home/zibi/miniconda3/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 3449, in flush
    self._flush(objects)
  File "/home/zibi/miniconda3/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 3588, in _flush
    with util.safe_reraise():
  File "/home/zibi/miniconda3/lib/python3.10/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
    compat.raise_(
  File "/home/zibi/miniconda3/lib/python3.10/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
    raise exception
  File "/home/zibi/miniconda3/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 3549, in _flush
    flush_context.execute()
  File "/home/zibi/miniconda3/lib/python3.10/site-packages/sqlalchemy/orm/unitofwork.py", line 456, in execute
    rec.execute(self)
  File "/home/zibi/miniconda3/lib/python3.10/site-packages/sqlalchemy/orm/unitofwork.py", line 630, in execute
    util.preloaded.orm_persistence.save_obj(
  File "/home/zibi/miniconda3/lib/python3.10/site-packages/sqlalchemy/orm/persistence.py", line 245, in save_obj
    _emit_insert_statements(
  File "/home/zibi/miniconda3/lib/python3.10/site-packages/sqlalchemy/orm/persistence.py", line 1238, in _emit_insert_statements
    result = connection._execute_20(
  File "/home/zibi/miniconda3/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1710, in _execute_20
    return meth(self, args_10style, kwargs_10style, execution_options)
  File "/home/zibi/miniconda3/lib/python3.10/site-packages/sqlalchemy/sql/elements.py", line 334, in _execute_on_connection
    return connection._execute_clauseelement(
  File "/home/zibi/miniconda3/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1577, in _execute_clauseelement
    ret = self._execute_context(
  File "/home/zibi/miniconda3/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1953, in _execute_context
    self._handle_dbapi_exception(
  File "/home/zibi/miniconda3/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 2134, in _handle_dbapi_exception
    util.raise_(
  File "/home/zibi/miniconda3/lib/python3.10/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
    raise exception
  File "/home/zibi/miniconda3/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1910, in _execute_context
    self.dialect.do_execute(
  File "/home/zibi/miniconda3/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
    cursor.execute(statement, parameters)
  File "/home/zibi/.local/lib/python3.10/site-packages/pymysql/cursors.py", line 153, in execute
    result = self._query(query)
  File "/home/zibi/.local/lib/python3.10/site-packages/pymysql/cursors.py", line 322, in _query
    conn.query(q)
  File "/home/zibi/.local/lib/python3.10/site-packages/pymysql/connections.py", line 558, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/home/zibi/.local/lib/python3.10/site-packages/pymysql/connections.py", line 822, in _read_query_result
    result.read()
  File "/home/zibi/.local/lib/python3.10/site-packages/pymysql/connections.py", line 1200, in read
    first_packet = self.connection._read_packet()
  File "/home/zibi/.local/lib/python3.10/site-packages/pymysql/connections.py", line 772, in _read_packet
    packet.raise_for_error()
  File "/home/zibi/.local/lib/python3.10/site-packages/pymysql/protocol.py", line 221, in raise_for_error
    err.raise_mysql_exception(self._data)
  File "/home/zibi/.local/lib/python3.10/site-packages/pymysql/err.py", line 143, in raise_mysql_exception
    raise errorclass(errno, errval)
sqlalchemy.exc.IntegrityError: (raised as a result of Query-invoked autoflush; consider using a session.no_autoflush block if this flush is occurring prematurely)
(pymysql.err.IntegrityError) (1062, "**Duplicate entry** '2022-06-17-LUDV.CH100.100:LUDV.CH1-CC' for key 'jobs.job_index'")
[SQL: INSERT INTO jobs (day, pair, jobtype, flag, lastmod) VALUES (%(day)s, %(pair)s, %(jobtype)s, %(flag)s, %(lastmod)s)]
[parameters: {'day': '2022-06-17', 'pair': 'LUDV.CH100.100:LUDV.CH102.102', 'jobtype': 'CC', 'flag': 'T', 'lastmod': datetime.datetime(2024, 7, 22, 9, 35, 54, 753714)}]
(Background on this error at: https://sqlalche.me/e/14/gkpj)

Seeing a duplicate entry error, I try to remove the duplicates using the msnoise utils clean_duplicates functionality, and receive the following error:

msnoise db clean_duplicates
Traceback (most recent call last):
  File "/home/zibi/miniconda3/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1910, in _execute_context
    self.dialect.do_execute(
  File "/home/zibi/miniconda3/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
    cursor.execute(statement, parameters)
  File "/home/zibi/.local/lib/python3.10/site-packages/pymysql/cursors.py", line 153, in execute
    result = self._query(query)
  File "/home/zibi/.local/lib/python3.10/site-packages/pymysql/cursors.py", line 322, in _query
    conn.query(q)
  File "/home/zibi/.local/lib/python3.10/site-packages/pymysql/connections.py", line 558, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/home/zibi/.local/lib/python3.10/site-packages/pymysql/connections.py", line 822, in _read_query_result
    result.read()
  File "/home/zibi/.local/lib/python3.10/site-packages/pymysql/connections.py", line 1200, in read
    first_packet = self.connection._read_packet()
  File "/home/zibi/.local/lib/python3.10/site-packages/pymysql/connections.py", line 772, in _read_packet
    packet.raise_for_error()
  File "/home/zibi/.local/lib/python3.10/site-packages/pymysql/protocol.py", line 221, in raise_for_error
    err.raise_mysql_exception(self._data)
  File "/home/zibi/.local/lib/python3.10/site-packages/pymysql/err.py", line 143, in raise_mysql_exception
    raise errorclass(errno, errval)
pymysql.err.OperationalError: (1109, "Unknown table 'jobs' in MULTI DELETE")

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/home/zibi/miniconda3/bin/msnoise", line 8, in <module>
    sys.exit(run())
  File "/home/zibi/miniconda3/lib/python3.10/site-packages/msnoise/scripts/msnoise.py", line 1536, in run
    cli(obj={})
  File "/home/zibi/.local/lib/python3.10/site-packages/click/core.py", line 1157, in __call__
    return self.main(*args, **kwargs)
  File "/home/zibi/.local/lib/python3.10/site-packages/click/core.py", line 1078, in main
    rv = self.invoke(ctx)
  File "/home/zibi/.local/lib/python3.10/site-packages/click/core.py", line 1688, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/home/zibi/.local/lib/python3.10/site-packages/click/core.py", line 1688, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/home/zibi/.local/lib/python3.10/site-packages/click/core.py", line 1434, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/home/zibi/.local/lib/python3.10/site-packages/click/core.py", line 783, in invoke
    return __callback(*args, **kwargs)
  File "/home/zibi/miniconda3/lib/python3.10/site-packages/msnoise/scripts/msnoise.py", line 427, in db_clean_duplicates
    db.execute(text(query))
  File "/home/zibi/miniconda3/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 1717, in execute
    result = conn._execute_20(statement, params or {}, execution_options)
  File "/home/zibi/miniconda3/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1710, in _execute_20
    return meth(self, args_10style, kwargs_10style, execution_options)
  File "/home/zibi/miniconda3/lib/python3.10/site-packages/sqlalchemy/sql/elements.py", line 334, in _execute_on_connection
    return connection._execute_clauseelement(
  File "/home/zibi/miniconda3/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1577, in _execute_clauseelement
    ret = self._execute_context(
  File "/home/zibi/miniconda3/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1953, in _execute_context
    self._handle_dbapi_exception(
  File "/home/zibi/miniconda3/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 2134, in _handle_dbapi_exception
    util.raise_(
  File "/home/zibi/miniconda3/lib/python3.10/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
    raise exception
  File "/home/zibi/miniconda3/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1910, in _execute_context
    self.dialect.do_execute(
  File "/home/zibi/miniconda3/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
    cursor.execute(statement, parameters)
  File "/home/zibi/.local/lib/python3.10/site-packages/pymysql/cursors.py", line 153, in execute
    result = self._query(query)
  File "/home/zibi/.local/lib/python3.10/site-packages/pymysql/cursors.py", line 322, in _query
    conn.query(q)
  File "/home/zibi/.local/lib/python3.10/site-packages/pymysql/connections.py", line 558, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/home/zibi/.local/lib/python3.10/site-packages/pymysql/connections.py", line 822, in _read_query_result
    result.read()
  File "/home/zibi/.local/lib/python3.10/site-packages/pymysql/connections.py", line 1200, in read
    first_packet = self.connection._read_packet()
  File "/home/zibi/.local/lib/python3.10/site-packages/pymysql/connections.py", line 772, in _read_packet
    packet.raise_for_error()
  File "/home/zibi/.local/lib/python3.10/site-packages/pymysql/protocol.py", line 221, in raise_for_error
    err.raise_mysql_exception(self._data)
  File "/home/zibi/.local/lib/python3.10/site-packages/pymysql/err.py", line 143, in raise_mysql_exception
    raise errorclass(errno, errval)
sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (1109, "Unknown table 'jobs' in MULTI DELETE")
[SQL: DELETE from jobs USING jobs as j1, jobs as j2 WHERE (j1.ref > j2.ref) AND (j1.day=j2.day) AND (j1.pair=j2.pair) AND (j1.jobtype=j2.jobtype)]
(Background on this error at: https://sqlalche.me/e/14/e3q8)

If that helps: I set up a project for a near-surface study using geophones and DAS fiber optics. The data is thus not in seismological format and I had to write the Location and Channel fields myself in my datafiles. An example header looks as follows: 'network': 'LUDV', 'station': 'CH50', 'location': '050', 'channel': 'BPZ'. I ran the scan_archive --path and msnoise populate --fromDA before.

I would really appreciate your help on that. Thank you!
Zibi

If needed, the bug report msnoise utils bugreport -s -m

Let's Bug Report MSNoise !

************* Computer Report *************

----------------+SYSTEM+-------------------
Linux
GEO-GFY-D0003
6.5.0-44-generic
#44~22.04.1-Ubuntu SMP PREEMPT_DYNAMIC Tue Jun 18 14:36:16 UTC 2
x86_64
x86_64

----------------+PYTHON+-------------------
Python:3.10.13 | packaged by conda-forge | (main, Dec 23 2023, 15:36:39) [GCC 12.3.0]

This script is at /home/zibi/miniconda3/lib/python3.10/site-packages/msnoise/bugreport.py

---------------+MODULES+-------------------

Required:
[X] setuptools: 69.1.1
[X] numpy: 1.26.4
[X] scipy: 1.13.1
[X] pandas: 2.1.4
[X] matplotlib: 3.8.4
[X] sqlalchemy: 1.4.51
[X] obspy: 1.4.1
[X] click: 8.1.6
[X] pymysql: 1.4.6
[X] flask: 2.3.2
[X] flask_admin: 1.6.1
[X] markdown: 3.6
[X] wtforms: 3.0.0
[X] folium: 0.14.0
[X] jinja2: 3.1.2
[X] tables: 3.9.2 (pytables)
[X] xarray: 2024.6.0
[X] logbook: 1.5.3

Only necessary if you plan to build the doc locally:
[ ] sphinx: not found
[ ] sphinx_bootstrap_theme: not found
[ ] sphinx_rtd_theme: not found
[ ] sphinx_gallery: not found
[ ] numpydoc: not found

Graphical Backends:
[ ] wx: not found
[ ] qt: not found
[ ] qt4: not found
[ ] qt5: not found
[ ] pyqt: not found
[ ] PyQt4: not found
[X] PyQt5: present (no version)
[ ] PySide: not found

Not required, just checking:
[X] json: 2.0.9
[X] psutil: 5.9.4
[ ] reportlab: not found
[ ] configobj: not found
[X] pkg_resources: present (no version)
[ ] paramiko: not found
[X] ctypes: 1.1.0
[X] pyparsing: 3.0.9
[X] distutils: 3.10.13
[X] IPython: 8.7.0
[ ] vtk: not found
[ ] enable: not found
[ ] traitsui: not found
[ ] traits: not found
[ ] scikits.samplerate: not found

[ThomasLecocq]

Hi,

which version of MSNoise is this ? Master?

The error you get when running new_jobs seems strange, as, in theory, jobs shouldn't be inserted twice, but updated... Your location code is >2 chars (which was forbidden with miniseed2, allowed in miniseed3 norm, which isn't implemented yet in msnoise). Could you check the content of the data_availability table to see if that field is truncated to 2char (leading to, eg. 101 and 102 being both 10, triggering the error.

edit: actually the network code is also 3 char, not allowed (and could be trimmed)

[zibiwilczynski]

Hi,
Thanks for a prompt response. Yes, it is a master version installed with pip install http://msnoise.org/master.zip.

The data_availability table did not truncate anything. All the fields are same length as initially. Same for the populated stations table.

Initially I had my data in .SEGY format and I converted to .SAC myself adding all the headers myself as well. Do you suggest updating the Location and Network codes to 2 characters?

[ThomasLecocq]

Hummm, no, could you send me two of those files (two stations)?

[zibiwilczynski]

Of course, here are the files:

SAC_files.zip

For what it's worth: I have numerous files per each day. The filenames represent a time when they were recorded, and files for different channels are stored in different directories but with the same name. Here is the data_availability_snippet.csv.

Thank you!

[zibiwilczynski]

Hi again,

I merged the files to 1-day long .SAC files and changed the network and location to 2-char headers. Then the new_job process went through without any errors.

[zibiwilczynski]

Hi again @ThomasLecocq,

Merging helped and the msnoise new_job process works correctly. I am running a CC now to see if it produces any results. However, the msnoise db clean_duplicates command still generates the same error: pymysql.err.OperationalError: (1109, "Unknown table 'jobs' in MULTI DELETE").

Cheers,
Zibi

[ThomasLecocq]

the clean_duplicates error should be OK with fa3b043