Question

SQL Server JDBC writing error

oliver.morris
Rank
Badge +10

Hi,

Has anyone had an error when writing to sql server from FME Cloud, when I run locally it runs fine.

 

Transferring input attributes onto output feature, this may take several minutes...COM.safe.fme.jdbc.TranslationFailureException: com.microsoft.sqlserver.jdbc.SQLServerException: com.microsoft.sqlserver.jdbc.SQLServerException: SQL Server did not return a response. The connection has been closed.COM.safe.fme.jdbc.TranslationFailureException: com.microsoft.sqlserver.jdbc.SQLServerException: com.microsoft.sqlserver.jdbc.SQLServerException: SQL Server did not return a response. The connection has been closed.Destination Feature Type Routing Correlator(RoutingFactory): COM.safe.fme.jdbc.TranslationFailureException: com.microsoft.sqlserver.jdbc.SQLServerException: com.microsoft.sqlserver.jdbc.SQLServerException: SQL Server did not return a response. The connection has been closed.COM.safe.fme.jdbc.TranslationFailureException: com.microsoft.sqlserver.jdbc.SQLServerException: com.microsoft.sqlserver.jdbc.SQLServerException: SQL Server did not return a response. The connection has been closed.2018-06-04 15:15:55| 924.6| 0.0|ERROR |com.microsoft.sqlserver.jdbc.SQLServerException: The connection is closed.Feature output statistics for `MSSQL_JDBC_SPATIAL' writer using keyword `MSSQL_JDBC_SPATIAL_1':Features Written_dsg_combined 596Total Features Written 596A fatal error has occurred. Check the logfile above for detailsTranslation FAILED with 7 error(s) and 0 warning(s) (596 feature(s) output)FME Session Duration: 15 minutes 28.0 seconds. (CPU: 804.5s user, 120.2s system)END - ProcessID: 26813, peak process memory usage: 819628 kB, current process memory usage: 85253 kB

13 replies

david_r
Rank
Userlevel 4

Have you tried increasing the query timeout value when creating the writer?

oliver.morris
Rank
Badge +10

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.

ravenkopelman
Rank
Badge
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.)

 

oliver.morris
Rank
Badge +10

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

david_r
Rank
Userlevel 4

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?
oliver.morris
Rank
Badge +10

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.

ravenkopelman
Rank
Badge
I have a couple of thoughts

 

  1. Is your working desktop Linux or Windows? If Windows, can you try a Linux desktop?
  2. Is your database hosted within AWS? If no, can you try an AWS hosted instance?

 

oliver.morris
Rank
Badge +10
I have a couple of thoughts

 

  1. Is your working desktop Linux or Windows? If Windows, can you try a Linux desktop?
  2. 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
ravenkopelman
Rank
Badge
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?"
steveatsafe
Rank
Badge +11

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?

SteveatSafe
steveatsafe
Rank
Badge +11

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.

SteveatSafe
steveatsafe
Rank
Badge +11
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.

 

 

SteveatSafe
oliver.morris
Rank
Badge +10
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

Reply


Community Stats

28,698
Posts
109,447
Replies
36,548
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.