[postgis-users] ODBC connection problem, connection lost

[Marco Boeringa]

Hi Brian,

Yes, I may need to resolve to posting this on the Virtualbox forums, 
still, there might be someone around here having a similar setup.

Yes, I have realised it is actually the VirtualBox "Bridged adapter" 
network adapter that is failing, the one that uses the host's device 
driver for the host's physical network card according to the VirtualBox 
manual. The second "NAT" based network adapter, that regulates the 
internet connection, apparently stays up when this happens. So it might 
be something with the device driver of the network card, although I am 
unsure if that is a driver installed by VirtualBox itself, or that it 
uses the Window's one (I actually have no idea if there can be more than 
one device driver in an OS, likely not...).

Marco

Op 21-8-2020 om 15:44 schreef Brent Fraser:
> Hi Marco,
>
>   Based on the fact that after the failure, the guest and host 
> machines cannot communicate (but the guest can access the internet), I 
> suggest looking into VirtualBox and the network "adapter" settings of 
> the guest container.
>
> The VirtualBox community may have some insights:
> https://www.virtualbox.org/wiki/Community
>
> Best Regards,
> Brent Fraser
>
>
> ------------------------------------------------------------------------
> *From*: Marco Boeringa <marco at boeringa.demon.nl>
> *Sent*: 8/21/20 3:24 AM
> *To*: postgis-users at lists.osgeo.org
> *Subject*: [postgis-users] ODBC connection problem, connection lost
>
> Hi all,
>
> I am running into an elusive issue that I have great difficulty to
> explain. Please note that, although I can find my way through it, I am
> not an Ubuntu nor network savvy person, so I may be overlooking
> something obvious.
>
> What is my setup:
>
> Ubuntu 20.04 with PostgreSQL 12 + PostGIS 3.0.2 running on an Oracle
> VirtualBox machine, so the Ubuntu OS being the guest operating system.
> This runs on a Windows 10 host.
>
> Both guest and host have fixed, manually set, IP addresses set in their
> network connections. Ubuntu runs the UFW firewall with the proper ports
> set to allow traffic, Windows has the same set in the firewall software
> I use.
>
> This all works fine, I can connect and do lots of stuff without issues.
>
> *The problem*:
>
> I have some code that does a multi-threaded set of INSERTs into my
> database, via Python multi-threading using 'pyodbc'. This needs to
> insert some 175M spatial records into a table. I have tested this with
> smaller tables (+/-15M), and it works fine. However, as soon as I try to
> insert the 175M records, at some point, probably close to 10-15% of
> records, I suddenly loose my network connection with a generic HY000
> error, that as far as I can tell is an ODBC level error, not returned by
> PostgreSQL. I have tried this multiple times, and it consistently
> happens. Note that the code commits in batches of about 10.000 records 
> each.
>
> At this point, I have verified the following:
>
> - The guest Ubuntu OS runs on a 32GB RAM, quad core test machine. Memory
> usage is not at its limits when this happens, neither is swap space
> running out as far as I can tell (although I haven't been able to
> monitor this at the exact moment it fails, this is just an observation
> from logging into the guest after the fact). Note though that I set 72
> GB swap in Ubuntu, and I have never seen Ubuntu use more than a few GB
> of swap in its System Monitor, so the 72 GB is probably totally overkill.
>
> - No problem with disk space, there is 2x 1.4TB SSD available to the
> guest, and only about half of total available disk space is used, so no
> disk space problem.
>
> - The UFW firewall is up and running when I check the status with 'sudo
> ufw status', nothing else to be seen there.
>
> - Not unsurprisingly, when I try to 'ping' the host from the guest or
> the other way around, the network connection is indeed down, and host /
> guest unreachable at this point.
>
> - However, I *can* open Firefox on the Ubuntu guest, and connect to the
> internet and browse the internet no problem
>
> - I have two Ubuntu network connections, both of them report status
> "Connected" at this point, so no error despite not being able to ping
> the host.
>
> - If I open 'pgAdmin 4' on the 'host', it *cannot* connect to the
> database. When I open pgAdmin 4 on the guest, it connects without
> issues, and the database is shown to be up and running. I can open
> tables, view records, and even open the table receiving the INSERTs and
> view its contents. So PostgreSQL seems to be fine.
>
> - I have had a look at the Ubuntu logs, but to be honest, it is like a
> foreign language to me, and I don't see anything obvious.
>
> - When I restart the Ubuntu guest, the issue is resolved and I can
> connect and successfully ping host and guest again.
>
> - As to pyodbc, I have set the connections 'maxwrite' to the recommended
> '1024 * 1024 * 1024' value according to the pyodbc documentation here:
> https://github.com/mkleehammer/pyodbc/wiki/Connecting-to-PostgreSQL, but
> I think this setting is wholly unrelated to this issue, but the
> documentation is in fact unclear as to what this setting really means.
>
> Does anyone have a clue of what may be going on, suggestions how to
> tackle such issues, or ever encountered similar issues and been able to
> deal with it somehow? I am kind of lost at this point.
>
> Marco
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20200821/69961d82/attachment.html>