Have you tried increasing the query timeout value when creating the writer?
thank you, I am trying increasing the timeout now to 600. I am also going to try with PostGIS to see if it works. Looking forward to 2018.1 for the performance improvements.
Hi Oliver - this doesn't sound familiar. This might be better discussed via
https://www.safe.com/support/report-a-problem/ where we can discuss details about your network topology, etc.
(It would be interesting to see if bulk loading mode in FME 2018.1 improves this problem. At the very least it would reduce the window for errors to occur.)
Finished trying with postgis on fmecloud, same result. PostGIS is set to do a bulk insert as well and there is no connection timeout settings. very odd, as it works perfectly on my desktop.
Full log:
Opening POSTGIS writer for dataset 'dsg_grids'...PostGIS client encoding: 'UTF8'PostgreSQL 9.6.8 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.2 20140120 (Red Hat 4.8.2-16), 64-bit2.3 USE_GEOS=1 USE_PROJ=1 USE_STATS=1Testing for the existence of table 'dsg_grids_schema.dsg_combined'...Using existing table 'dsg_grids_schema.dsg_combined'POSTGIS Writer: Creating 'unquoted WKB' geometry processor for feature type 'dsg_grids_schema.dsg_combined'POSTGIS Writer: Creating 'streaming Bulk Copy' feature type processor for feature type 'dsg_grids_schema.dsg_combined'PolygonDissolveFactory: Group 4 of 15: Completed 100% of processing on pass 7.PolygonDissolveFactory: Group 4 of 15: Completed 66.66% of processing on pass 8.PolygonDissolveFactory: Group 4 of 15: Completed 100% of processing on pass 8.Sorting 570 features...Constructing donuts from 570 features...PolygonDissolveFactory: Group 4 of 15: Completed 100% of processing on pass 9.Transferring input attributes onto output feature, this may take several minutes...FME Configuration: Using FME Reprojection EngineTransferring input attributes onto output feature, this may take several minutes...PolygonDissolveFactory: Group 7 of 15: Completed 56.66% of processing on pass 5.PolygonDissolveFactory: Group 7 of 15: Completed 100% of processing on pass 5.PolygonDissolveFactory: Group 7 of 15: Completed 73.33% of processing on pass 6.PolygonDissolveFactory: Group 7 of 15: Completed 100% of processing on pass 6.PolygonDissolveFactory: Group 7 of 15: Completed 100% of processing on pass 7.PolygonDissolveFactory: Group 7 of 15: Completed 100% of processing on pass 8.Sorting 748 features...Constructing donuts from 748 features...Completed intersection processing, phase #3. 2 new nodes were generated among 1207 intermediate linesFinish splitting 1208 lines into 1209 intermediate lines for phase 3Sorting 919 features...Constructing donuts from 919 features...Transferring input attributes onto output feature, this may take several minutes...Bulk copy failed on table 'dsg_grids_schema.dsg_combined' using delimiter ':'. Error was 'server closed the connection unexpectedlyThis probably means the server terminated abnormallybefore or while processing the request.A fatal error has occurred. Check the logfile above for details... Last line repeated 2 times ...2018-06-04 19:17:31| 916.2| 0.0|ERROR |Bulk copy failed on table 'dsg_grids_schema.dsg_combined' using delimiter ':'. Error was 'server closed the connection unexpectedlyThis probably means the server terminated abnormallybefore or while processing the request.A fatal error has occurred. Check the logfile above for details... Last line repeated 2 times ...Translation FAILED with 6 error(s) and 0 warning(s) (514 feature(s) output)FME Session Duration: 15 minutes 47.0 seconds. (CPU: 800.4s user, 115.9s system)END - ProcessID: 2368, peak process memory usage: 1203418 kB, current process memory usage: 86396 kB
Finished trying with postgis on fmecloud, same result. PostGIS is set to do a bulk insert as well and there is no connection timeout settings. very odd, as it works perfectly on my desktop.
Full log:
Opening POSTGIS writer for dataset 'dsg_grids'...PostGIS client encoding: 'UTF8'PostgreSQL 9.6.8 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.2 20140120 (Red Hat 4.8.2-16), 64-bit2.3 USE_GEOS=1 USE_PROJ=1 USE_STATS=1Testing for the existence of table 'dsg_grids_schema.dsg_combined'...Using existing table 'dsg_grids_schema.dsg_combined'POSTGIS Writer: Creating 'unquoted WKB' geometry processor for feature type 'dsg_grids_schema.dsg_combined'POSTGIS Writer: Creating 'streaming Bulk Copy' feature type processor for feature type 'dsg_grids_schema.dsg_combined'PolygonDissolveFactory: Group 4 of 15: Completed 100% of processing on pass 7.PolygonDissolveFactory: Group 4 of 15: Completed 66.66% of processing on pass 8.PolygonDissolveFactory: Group 4 of 15: Completed 100% of processing on pass 8.Sorting 570 features...Constructing donuts from 570 features...PolygonDissolveFactory: Group 4 of 15: Completed 100% of processing on pass 9.Transferring input attributes onto output feature, this may take several minutes...FME Configuration: Using FME Reprojection EngineTransferring input attributes onto output feature, this may take several minutes...PolygonDissolveFactory: Group 7 of 15: Completed 56.66% of processing on pass 5.PolygonDissolveFactory: Group 7 of 15: Completed 100% of processing on pass 5.PolygonDissolveFactory: Group 7 of 15: Completed 73.33% of processing on pass 6.PolygonDissolveFactory: Group 7 of 15: Completed 100% of processing on pass 6.PolygonDissolveFactory: Group 7 of 15: Completed 100% of processing on pass 7.PolygonDissolveFactory: Group 7 of 15: Completed 100% of processing on pass 8.Sorting 748 features...Constructing donuts from 748 features...Completed intersection processing, phase #3. 2 new nodes were generated among 1207 intermediate linesFinish splitting 1208 lines into 1209 intermediate lines for phase 3Sorting 919 features...Constructing donuts from 919 features...Transferring input attributes onto output feature, this may take several minutes...Bulk copy failed on table 'dsg_grids_schema.dsg_combined' using delimiter ':'. Error was 'server closed the connection unexpectedlyThis probably means the server terminated abnormallybefore or while processing the request.A fatal error has occurred. Check the logfile above for details... Last line repeated 2 times ...2018-06-04 19:17:31| 916.2| 0.0|ERROR |Bulk copy failed on table 'dsg_grids_schema.dsg_combined' using delimiter ':'. Error was 'server closed the connection unexpectedlyThis probably means the server terminated abnormallybefore or while processing the request.A fatal error has occurred. Check the logfile above for details... Last line repeated 2 times ...Translation FAILED with 6 error(s) and 0 warning(s) (514 feature(s) output)FME Session Duration: 15 minutes 47.0 seconds. (CPU: 800.4s user, 115.9s system)END - ProcessID: 2368, peak process memory usage: 1203418 kB, current process memory usage: 86396 kB
Based on the log message I would suspect a server side setting. Can you look in the postgresql server logs for any errors or warnings related to the client connections?
Ran the sql trace for the desktop version (1st image which works) and the cloud run version (2nd image), just appears not to even try to write the output.
I have a couple of thoughts
- Is your working desktop Linux or Windows? If Windows, can you try a Linux desktop?
- Is your database hosted within AWS? If no, can you try an AWS hosted instance?
I have a couple of thoughts
- Is your working desktop Linux or Windows? If Windows, can you try a Linux desktop?
- Is your database hosted within AWS? If no, can you try an AWS hosted instance?
Hi @ravenkopelman
I am using a windows environment, I dont have any linux desktops at the moment, I will try to set one up. Point 2 - I am using FME cloud.
Thanks
Hi @ravenkopelman
I am using a windows environment, I dont have any linux desktops at the moment, I will try to set one up. Point 2 - I am using FME cloud.
Thanks
So we can't discount the possibility of it being a Linux/Windows difference, although I think that's unlikely. Regarding point 2, I meant "is FME leaving AWS to talk to the database, or are all components hosted within AWS?"
Hi @olivermorris,
Are you sure that the FME Cloud system can reach (through all the firewall/security rules) the hosted SQL Server or Postgres databases?
I'm getting the impression that the Desktop environment is able to access the database systems as the rules for access to those database systems have been properly configured... but I'm not sure if the FME Cloud Engine (the system it runs on) can access these same systems.
Can you read from these databases with FME Cloud?
Hi @olivermorris,
Yes, let's make sure you have poked a hole in the firewall. I spoke with Stewart and learned about this Doc: https://docs.safe.com/fme_cloud/FME_Cloud/Content/About_Instances/Configuring_Security.htm
This should help if you haven't already done this.
Based on the log message I would suspect a server side setting. Can you look in the postgresql server logs for any errors or warnings related to the client connections?
Postgresql is pretty locked down by default and since he can connect already if FME Workbench, I think the Postgres instance is OK. I think it might be an issue with the FME Cloud Engines not able to get out. Hopefully the Doc link I shared above will help.
So we can't discount the possibility of it being a Linux/Windows difference, although I think that's unlikely. Regarding point 2, I meant "is FME leaving AWS to talk to the database, or are all components hosted within AWS?"
@SteveAtSafe @ravenkopelman I just send Steve some screenshots of our configuration between FME Cloud (Ireland) and our AWS RDS environment (Ireland) they can talk to each other. In fact the first thing the workspace does is to read from the table it ends up writing to. It succeeds in reading from this and other tables in sql server it just fails to write to it at the end. But, when the workspace takes less time to run it does work. I dont believe it is the timeout issue on the writer because the data gets written fast enough when it does work.
I have got round the issue by writing instead to our PostGIS AWS RDS instance, this appears to be working fine.
thanks for the continued support
