Pgloader - MSSQL to Postgres (YB) throwing TDS_MAX_CONN error

[seethb]

Thanks for contributing to pgloader by reporting an issue! Reporting an issue is the only way we can solve problems, fix bugs, and improve both the software and its user experience in general.

The best bug reports follow those 3 simple steps:

1. show what you did,
2. show the result you got,
3. explain how the result is not what you expected.

In the case of pgloader, here's the information I will need to read in your bug report. Having all of this is a big help, and often means the bug you reported can be fixed very efficiently as soon as I get to it.

Please provide the following information:

• [x] pgloader --version

  pgloader version "3.6.1"

• [ x] did you test a fresh compile from the source tree?

  Compiling pgloader from sources is documented in the README, it's easy to do, and if patches are to be made to fix your bug, you're going to have to build from sources to get the fix anyway…

• [x ] did you search for other similar issues?

• [x] how can I reproduce the bug?

  Incude a self-contained pgloader command file.

  If you're loading from a database, consider attaching a database dump to your issue. For MySQL, use mysqldump. For SQLite, just send over your source file, that's easy. Maybe be the one with your production data, of course, the one with just the sample of data that allows me to reproduce your bug.

  When using a proprietary database system as a source, consider creating a sample database on some Cloud service or somewhere you can then give me access to, and see my email address on my GitHub profile to send me the credentials. Still open a public issue for tracking and as documentation for other users.

--
-- EDIT THIS FILE TO MATCH YOUR BUG REPORT
--

load database
        from mssql://SA:@localhost:1433/master
        into postgresql://yugabyte@localhost:5433/nw
including only table names like 'staff' in schema 'dbo'

set work_mem to '16MB', maintenance_work_mem to '512 MB'

before load do $$ drop schema if exists dbo cascade;
 $$;

• [x] pgloader output you obtain

 balas@balas:~$ /usr/bin/pgloader --dry-run /home/balas/pgloadersql.conf 
2022-01-22T11:54:05.011000Z LOG pgloader version "3.6.1"
2022-01-22T11:54:05.044000Z LOG Loading the FreeTDS shared librairy (sybdb)
2022-01-22T11:54:05.046000Z LOG DRY RUN, only checking connections.
2022-01-22T11:54:05.046000Z LOG Attempting to connect to #<MSSQL-CONNECTION mssql://SA@localhost:1433/test {1008CAC0E3}>
Max connections reached, increase value of TDS_MAX_CONN
2022-01-22T11:54:05.064000Z LOG Success, opened #<MSSQL-CONNECTION mssql://SA@localhost:1433/test {1008CAC0E3}>.
2022-01-22T11:54:05.064000Z LOG Running a simple query: SELECT 1;
2022-01-22T11:54:05.069000Z LOG Attempting to connect to #<PGSQL-CONNECTION pgsql://yugabyte@localhost:5433/nw {1008CAD673}>
2022-01-22T11:54:05.124000Z LOG Success, opened #<PGSQL-CONNECTION pgsql://yugabyte@localhost:5433/nw {1008CAD673}>.
2022-01-22T11:54:05.124000Z LOG Running a simple query: SELECT 1;
2022-01-22T11:54:05.125000Z LOG report summary reset
       table name     errors       rows      bytes      total time
-----------------  ---------  ---------  ---------  --------------
-----------------  ---------  ---------  ---------  --------------

balas@balas:~$ /usr/bin/pgloader --verbose /home/balas/pgloadersql.conf 
2022-01-22T11:54:32.004000Z NOTICE Starting pgloader, log system is ready.
2022-01-22T11:54:32.010000Z LOG pgloader version "3.6.1"
2022-01-22T11:54:32.122000Z NOTICE Executing SQL block for before load
2022-01-22T11:54:32.183000Z LOG Migrating from #<MSSQL-CONNECTION mssql://SA@localhost:1433/test {1008C940A3}>
2022-01-22T11:54:32.184000Z LOG Migrating into #<PGSQL-CONNECTION pgsql://yugabyte@localhost:5433/nw {1008C95633}>
Max connections reached, increase value of TDS_MAX_CONN
2022-01-22T11:54:32.265000Z NOTICE Prepare PostgreSQL database.
KABOOM!
INFO: Control stack guard page unprotected
Control stack guard page temporarily disabled: proceed with caution

What I am doing here?

Control stack exhausted (no more space for function call frames).
This is probably due to heavily nested or infinitely recursive function
calls, or a tail call that SBCL cannot or has not optimized away.

• [x] data that is being loaded, if relevant

PASTE HERE THE DATA THAT HAS BEEN LOADED

• [x] How the data is different from what you expected, if relevant

[TestSubject1498]

What happens when you increase the number of pooled connections in the freetds pool.conf? (The TDS MAX Connections can be configured in FreeTDS's pool.conf. You may have to create this file and put it in the appropriate directory, it was not on my machine by default.) I don't think running out of pooled connections should be a blocker, I've migrated plenty of data even when I've hit the max pooled connections. Your error messages seem like they might be similar to #810 do you see any similarities in your environment and configuration.

[seethb]

Hi I haven't created pool.conf. Its called freedts.conf and it was there as default and I updated and tried with following TDS max conn details. but no luck, its the same error.

This file is installed by FreeTDS if no file by the same name is found in the installation directory.

For information about the layout of this file and its settings, see the freetds.conf manpage "man freetds.conf".
Global settings are overridden by those in a database server specific section [global]

TDS protocol version

    tds version = 7.3
    tds_max_conn = 300
    min pool conn = 20
    max pool conn = 300
    max member age = 120
    # Whether to write a TDSDUMP file for diagnostic purposes
    # (setting this to /tmp is insecure on a multi-user system)
    client charset = UTF-8

; dump file = /tmp/freetds.log ; debug flags = 0xffff

    # Command and connection timeouts

; timeout = 10 ; connect timeout = 10

    # To reduce data sent from server for BLOBs (like TEXT or
    # IMAGE) try setting 'text size' to a reasonable limit

; text size = 64512

    # If you experience TLS handshake errors and are using openssl,
    # try adjusting the cipher list (don't surround in double or single quotes)
    # openssl ciphers = HIGH:!SSLv2:!aNULL:-DH

[localhost] host = 172.17.0.2 port = 1433 tds version = 7.3 TDS_MAX_CONN = 3000 client charset = UTF-8 ~

[seethb]

hi any further progress or update on this please.

[ericadams]

Hi @seethb I'm fairly new to pgloader, but don't think the Max connections reached, increase value of TDS_MAX_CONN warning you're seeing is related to the fatal error bombing your loader run. I can confirm what @TestSubject1498 said : my load runs don't crash despite typically seeing this message multiple times during a migration.

The message here is more clear when taking into account the fact that pgloader is implemented in Lisp: Control stack exhausted (no more space for function call frames). It means a possible infinite-recursion error in the program, or (less likely IMHO) a recursion with too many steps to fit in the function call stack. It could be a bug, but it might also be something about the shape of your data, possibly a circular reference?

On another topic, with regard to TDS_MAX_CONN,
I'm wondering if you've seen this page, which implies heavily that the pool.conf must be a separate file in /etc/freetds : https://www.freetds.org/userguide/tdspool.html