Add support for PostgreSQL 13 - Githubissues

tds-fdw / tds_fdw

A PostgreSQL foreign data wrapper to connect to TDS databases (Sybase and Microsoft SQL Server)

Other

381 stars 102 forks source link

Add support for PostgreSQL 13 #261

Closed MThomassen closed 4 years ago

jenkins-juliogonzalez commented 4 years ago

Can one of the admins verify this patch?

GeoffMontee commented 4 years ago

Thanks, @MThomassen !

GeoffMontee commented 4 years ago

Test this, please

MThomassen commented 4 years ago

Note that any CI/CD test images (f.e; https://github.com/tds-fdw/ci-setup/blob/master/ubuntu18.04/Dockerfile) are not yet available for PG13. I've only tested this PR in limited capacity on an Ubuntu 18.04 server.

jenkins-juliogonzalez commented 4 years ago

Test PASSed.

GeoffMontee commented 4 years ago

Hi @MThomassen,

Right, thanks. I just added PostgreSQL 13 to the ci-setup and ci-build repositories:

https://github.com/tds-fdw/ci-setup/commit/4d49dd962524af1f3a14c7598d196159e4e8d976

https://github.com/tds-fdw/ci-build/commit/97ae59629310314c318bf58a25bb8c7a5aa6baf8

I don't think that I can add the new version to Jenkins though. @juliogonzalez hosts Jenkins, and he usually handles the configuration. I'll check whether he can add support for PostgreSQL 13 to the Jenkins configuration.

Thanks!

juliogonzalez commented 4 years ago

Working on it.

Plan:

[x] Update https://github.com/juliogonzalez/docker-postgresql to support PostgreSQL13 (base image for tds_fdw docker images)
[x] Fix the commits for ci-build (couple of typos) (done by @GeoffMontee)
[x] Update all the docker images at hub.docker.com (add 13 as supported, remove 9.4, possibly deprecate CentOS6)
[x] Build the docker images and publish them
[x] Test if things are working (in case I need to tweak something for Postgres13)
[x] Enable PostgreSQL 13 on the CI

Besides adding PostgreSQL I will use the opportunity to change CentOS6 (EoL) soon) to CentOS7 for the tests, and mybe Ubuntu16.04 to Ubuntu18.04,

juliogonzalez commented 4 years ago

Ok, last remaining problem before we can enable PostgreSQL on the CI.

For some reason pymssql 2.1.5 is not working on CentOS7, while it works at Ubuntu 18.04:

Traceback (most recent call last):
  File "/test.py", line 4, in <module>
    port='1433', tds_version='7.1')
  File "src/pymssql.pyx", line 642, in pymssql.connect
pymssql.OperationalError: (20002, 'DB-Lib error message 20002, severity 9:\nAdaptive Server connection failed (instance.database.windows.net)\n')

Same code, same credentials, but not working (worked fine on CentOS6).

Still trying to debug the problem.

juliogonzalez commented 4 years ago

@GeoffMontee do you see anything on the MSSQL logs for the following connection attempts?

[root@244c24cb7b03 tmp]# date; python /test.py 
Sat Sep 26 15:21:16 UTC 2020
Traceback (most recent call last):
  File "/test.py", line 4, in <module>
    port='1433', tds_version='7.1')
  File "src/pymssql.pyx", line 642, in pymssql.connect
pymssql.OperationalError: (20002, 'DB-Lib error message 20002, severity 9:\nAdaptive Server connection failed (instance.database.windows.net)\n')

[root@244c24cb7b03 tmp]# date; python /test.py 
Sat Sep 26 15:21:20 UTC 2020
Traceback (most recent call last):
  File "/test.py", line 4, in <module>
    port='1433', tds_version='7.1')
  File "src/pymssql.pyx", line 642, in pymssql.connect
pymssql.OperationalError: (20002, 'DB-Lib error message 20002, severity 9:\nAdaptive Server connection failed (instance.database.windows.net)\n')

juliogonzalez commented 4 years ago

Something tells me the problem is the difference on the FreeTDS version.

As CentOS7 gets it from EPEL, it gets 1.1.20, while CentOS6 had 0.91 (IIRC) and Ubuntu18.04 has 1.0.82.

juliogonzalez commented 4 years ago

Apparently the connection works, but the login information is not accepted by the MSSQL server:


net.c:340:tds_setup_socket: connect(2) returned "Operation now in progress"
net.c:528:tds_open_socket() succeeded
packet.c:852:Sending packet
0000 12 01 00 34 00 00 00 00-00 00 15 00 06 01 00 1b |...4.... ........|
0010 00 01 02 00 1c 00 0c 03-00 28 00 04 ff 08 00 01 |........ .(......|
0020 55 00 00 02 4d 53 53 51-4c 53 65 72 76 65 72 00 |U...MSSQ LServer.|
0030 c1 03 00 00            -                        |....|

packet.c:410:Received packet
0000 04 01 00 25 00 00 01 00-00 00 15 00 06 01 00 1b |...%.... ........|
0010 00 01 02 00 1c 00 01 03-00 1d 00 00 ff 0c 00 07 |........ ........|
0020 d0 00 00 03 00         -                        |.....|

login.c:1281:detected crypt flag 3
login.c:571:login packet rejected
query.c:3757:tds_disconnect() 
util.c:179:Changed query state from IDLE to DEAD
util.c:333:tdserror(0xec8f90, 0xd54d40, 20002, 0)
dblib.c:8144:dbperror(0xdc2290, 20002, 0)
dblib.c:8212:dbperror: Calling dblib_err_handler with msgno = 20002; msg->msgtext = "Adaptive Server connection failed (instance.database.windows.net)"
dblib.c:8234:dbperror: dblib_err_handler for msgno = 20002; msg->msgtext = "Adaptive Server connection failed (instance.database.windows.net)" -- returns 2 (INT_CANCEL)
util.c:363:tdserror: client library returned TDS_INT_CANCEL(2)
util.c:386:tdserror: returning TDS_INT_CANCEL(2)
dblib.c:1282:tdsdbopen: tds_connect_and_login failed for "instance.database.windows.net"!

juliogonzalez commented 4 years ago

And problem solved.

It seems this happens because of the FreeTDS version that pymssql used during build time.

Using pip to get pymssql sources and install and self-build binary fixes the issue (https://groups.google.com/g/pymssql/c/HtohOBWUTCY/m/PiJt9EUFEgAJ)

I will regenerate the CentOS7 docker images, and then we should be ready to test this PR and merge it :-)

juliogonzalez commented 4 years ago

Retest this, please

jenkins-juliogonzalez commented 4 years ago

Test PASSed.

juliogonzalez commented 4 years ago

@GeoffMontee ready to be merged and released :-)

GeoffMontee commented 4 years ago

Thanks, @juliogonzalez!