I added one table (or so I believed) into meltano.yml and tried to run the pipeline, but I get this big crash. I note the first problem is 'catalog discovery failed' - which again suggests the timeout problem. How can I extend this, or at least get Meltano to only look at a subset of tables? ```avid@kafka:~/BC-to-Snowflake$ meltano run tap-mssql target-snowflake 2023-09-05T054138.329477Z [info ] Environment 'dev' is active 2023-09-05T054138.712367Z [warning ] No state was found, complete import. 2023-09-05T054655.771965Z [error ] Cannot start plugin tap-mssql: Catalog discovery failed: command ['/home/david/BC-to-Snowflake/.meltano/extractors/tap-mssql/venv/bin/tap-mssql', '--config', '/home/david/BC-to-Snowflake/.meltano/run/tap-mssql/tap.e19ab3c5-af2f-4e03-9d8b-9302aa60c3dc.config.json', '--discover'] returned 1 with stderr: INFO Server Parameters: version: Microsoft SQL Server 2019 (RTM-CU18-GDR) (KB5021124) - 15.0.4280.7 (X64) Jan 23 2023 123713 Copyright (C) 2019 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2019 Datacenter 10.0 <X64> (Build 17763: ) (Hypervisor) , lock_timeout: -1, INFO Preparing Catalog INFO Fetching tables INFO Tables fetched, fetching columns CRITICAL (20047, b'DB-Lib error message 20047, severity 9:\nDBPROCESS is dead or not enabled\n') Traceback (most recent call last): File "src/pymssql/_pymssql.pyx", line 459, in pymssql._pymssql.Cursor.execute File "src/pymssql/_mssql.pyx", line 1087, in pymssql._mssql.MSSQLConnection.execute_query File "src/pymssql/_mssql.pyx", line 1118, in pymssql._mssql.MSSQLConnection.execute_query File "src/pymssql/_mssql.pyx", line 1251, in pymssql._mssql.MSSQLConnection.format_and_run_query File "src/pymssql/_mssql.pyx", line 1789, in pymssql._mssql.check_cancel_and_raise File "src/pymssql/_mssql.pyx", line 1835, in pymssql._mssql.raise_MSSQLDatabaseException pymssql._mssql.MSSQLDatabaseException: (20047, b'DB-Lib error message 20047, severity 9:\nDBPROCESS is dead or not enabled\n') During handling of the above exception, another exception occurred: Traceback (most recent call last): File "/home/david/BC-to-Snowflake/.meltano/extractors/tap-mssql/venv/bin/tap-mssql", line 8, in <module> sys.exit(main()) File "/home/david/BC-to-Snowflake/.meltano/extractors/tap-mssql/venv/lib/python3.10/site-packages/tap_mssql/__init__.py", line 780, in main raise exc File "/home/david/BC-to-Snowflake/.meltano/extractors/tap-mssql/venv/lib/python3.10/site-packages/tap_mssql/__init__.py", line 777, in main main_impl() File "/home/david/BC-to-Snowflake/.meltano/extractors/tap-mssql/venv/lib/python3.10/site-packages/tap_mssql/__init__.py", line 763, in main_impl do_discover(mssql_conn, args.config) File "/home/david/BC-to-Snowflake/.meltano/extractors/tap-mssql/venv/lib/python3.10/site-packages/tap_mssql/__init__.py", line 314, in do_discover discover_catalog(mssql_conn, config).dump() File "/home/david/BC-to-Snowflake/.meltano/extractors/tap-mssql/venv/lib/python3.10/site-packages/tap_mssql/__init__.py", line 232, in discover_catalog cur.execute( File "src/pymssql/_pymssql.pyx", line 479, in pymssql._pymssql.Cursor.execute pymssql._pymssql.OperationalError: (20047, b'DB-Lib error message 20047, severity 9:\nDBPROCESS is dead or not enabled\n') 2023-09-05T054655.772200Z [error ] Block run completed. block_type=ExtractLoadBlocks err=RunnerError('Cannot start plugin tap-mssql: Catalog discovery failed: command [\'/home/david/BC-to-Snowflake/.meltano/extractors/tap-mssql/venv/bin/tap-mssql\', \'--config\', \'/home/david/BC-to-Snowflake/.meltano/run/tap-mssql/tap.e19ab3c5-af2f-4e03-9d8b-9302aa60c3dc.config.json\', \'--discover\'] returned 1 with stderr:\n INFO Server Parameters: version: Microsoft SQL Server 2019 (RTM-CU18-GDR) (KB5021124) - 15.0.4280.7 (X64) \n\tJan 23 2023 123713 \n\tCopyright (C) 2019 Microsoft Corporation\n\tStandard Edition (64-bit) on Windows Server…

That's odd. AFAIK, Error 20047 would mean that the underlying TDS session has expired on the SQL Server, but your client is trying to re-use it. Not sure why that would happen during Meltano Catalog Discovery. Catalog Discovery should be quick and this situation should be unlikely to occur.

Oh! I am not sure what to do with that 🙂 but interesting and intriguing.

Well, basically the SQL Server thinks that the client has disappeared and SQL Server kills the session. When the client wakes up again during the meltano catalog discovery, it just keeps on going as if nothing happened and is trying to use a session ID that has been purged by the SQL Server already.

Interesting!

At least that's my interpretation of the situation.

Should I send the logs somewhere for analysis?

I'm not sure if there would be any useful logs at this stage. My go-to for SQL Server troubleshooting in pymssql is usually the FreeTDS tace log file, which can be pretty helpful. I think it should be possible to generate such FreeTDS trace log by simply invoking the meltano run with an extra environment variable added in

TDSDUMP=/tmp/freetds.log meltano ...

Thank you, @christoph, I appreciate the details and support. I will give this a try in the morning (GMT +10h here).

no genius, just do a bunch of work in MS land 😄 I've had really bad luck with pymssql (https://github.com/pymssql/pymssql/issues/695) , to be fair I haven't had this issue specifically but I tend to recommend pyodbc Could give that a shot, not sure it'd fix your issue here though 🤷

I couldn't disagree more. Pymssql and FreeTDS has been absolutetly tremenduous for me. I could not fathom using a piece of software like Microsoft ODBC drivers where I can't do any troubleshooting myself and have to rely on the vendor to tell me what the problem is. 🤮 Luckily, I have never even been in the situation where I actually managed to install the ODBC drivers! 😂

@christoph I'd be happy to use pymssql and freetds, I've just hit random issues like the one I linked to and there's no fix 😕

Interesting. Wasn't aware of that issue. I'll have a look through and see what I can find. Thanks for linking it here.

@christoph are you in Australia? My LinkedIn is https://linkedin.com/in/dataexcellence - would be funny if we ever did.

https://github.com/pymssql/pymssql/issues/300 I think was my other one @christoph https://gitlab.com/autoidm/autoidm-target-mssql/-/commit/6fc584bcd8ff365dcb40cbc0e38d6791d0b6a573 is all I have left in my memory

Different David Williams 😁 Yup. I'm Sydney based.

@david_m_williams sorry I kinda hijacked this thread We want to get you up and running, try the stuff Chris recommended and get the info back I don't care what you use as long as it works! Sending the logs here would give you a better shot of someone pointing you in the right direction!

Ok ... I tried removing the cache and setting the TDSDUMP env variable. Unfortunately, no change, and alas, no new log created. I then found the tap-mssql variant I was using - the default one - hasn't been updated for some time and some people (e.g., a GitLab post) suggest it is abandonware. I changed to 'buzzcutnorman'. Now my meltano job is not crashing ... but at the same time I'm not sure what it's doing. It's been running for almost two hours, but without any interesting output at all. I will leave it overnight and see what happens ....

When you're debugging I'd start with just making sure one side works so

meltano invoke tap-mssql > out

no output is a bit concerning as you'd expect snowflake to say something as it's writing the data odd that it'd just hang

Ok, it eventually failed ...

2023-09-06T06:23:38.986448Z [info     ] Environment 'dev' is active
2023-09-06T06:23:41.764464Z [warning  ] No state was found, complete import.

2023-09-06T20:38:56.095445Z [error    ] Cannot start plugin tap-mssql: Catalog discovery failed: command ['/home/david/BC-to-Snowflake/.meltano/extractors/tap-mssql/venv/bin/tap-mssql', '--config', '/home/david/BC-to-Snowflake/.meltano/run/tap-mssql/tap.655beb03-af65-471d-a83c-8d5360dbb793.config.json', '--discover'] returned 1 with stderr:
 2023-09-06 22:36:47,835 | ERROR    | sqlalchemy.pool.impl.QueuePool | Exception during reset or similar
Traceback (most recent call last):
  File "/home/david/BC-to-Snowflake/.meltano/extractors/tap-mssql/venv/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 763, in _finalize_fairy
    fairy._reset(pool, transaction_was_reset)
  File "/home/david/BC-to-Snowflake/.meltano/extractors/tap-mssql/venv/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 1038, in _reset
    pool._dialect.do_rollback(self)
  File "/home/david/BC-to-Snowflake/.meltano/extractors/tap-mssql/venv/lib/python3.10/site-packages/sqlalchemy/dialects/mssql/base.py", line 2845, in do_rollback
    super(MSDialect, self).do_rollback(dbapi_connection)
  File "/home/david/BC-to-Snowflake/.meltano/extractors/tap-mssql/venv/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 683, in do_rollback
    dbapi_connection.rollback()
  File "src/pymssql/_pymssql.pyx", line 316, in pymssql._pymssql.Connection.rollback
  File "src/pymssql/_pymssql.pyx", line 300, in pymssql._pymssql.Connection.rollback
  File "src/pymssql/_mssql.pyx", line 1056, in pymssql._mssql.MSSQLConnection.execute_non_query
  File "src/pymssql/_mssql.pyx", line 1077, in pymssql._mssql.MSSQLConnection.execute_non_query
  File "src/pymssql/_mssql.pyx", line 1251, in pymssql._mssql.MSSQLConnection.format_and_run_query
  File "src/pymssql/_mssql.pyx", line 1789, in pymssql._mssql.check_cancel_and_raise
  File "src/pymssql/_mssql.pyx", line 1835, in pymssql._mssql.raise_MSSQLDatabaseException
pymssql._mssql.MSSQLDatabaseException: (20047, b'DB-Lib error message 20047, severity 9:\nDBPROCESS is dead or not enabled\n')
Traceback (most recent call last):
  File "src/pymssql/_pymssql.pyx", line 461, in pymssql._pymssql.Cursor.execute
  File "src/pymssql/_mssql.pyx", line 1087, in pymssql._mssql.MSSQLConnection.execute_query
  File "src/pymssql/_mssql.pyx", line 1118, in pymssql._mssql.MSSQLConnection.execute_query
  File "src/pymssql/_mssql.pyx", line 1251, in pymssql._mssql.MSSQLConnection.format_and_run_query
  File "src/pymssql/_mssql.pyx", line 1789, in pymssql._mssql.check_cancel_and_raise
  File "src/pymssql/_mssql.pyx", line 1835, in pymssql._mssql.raise_MSSQLDatabaseException
pymssql._mssql.MSSQLDatabaseException: (20047, b'DB-Lib error message 20047, severity 9:\nDBPROCESS is dead or not enabled\n')

Sounds like the same problem? Getting stuck during discovery?

No promises this will work but some have had luck by enabling

pool_pre_ping

. You should be able to enable it for tap-mssql buzzcutnorman variant with this command:

meltano config tap-mssql set sqlalchemy_eng_params.pool_pre_ping "True"

Hi @visch, re: https://meltano.slack.com/archives/C01TCRBBJD7/p1693918627511039?thread_ts=1693892948.065239&amp;cid=C01TCRBBJD7 As a FYI, tap-mssql in the latest release supports datetime2 and datetimeoffset https://github.com/wintersrd/pipelinewise-tap-mssql/pull/54 .