Problem

When using the xml_export tool to export a large collection of Routes and Route Templates, the export might fail and the following messages might be thrown in the server's console:

2021-05-05 07:25:50,034 CDT ERROR [main] org.hibernate.engine.jdbc.spi.SqlExceptionHelper - Closed Connection
2021-05-05 07:25:50,036 CDT WARN [main] com.mchange.v2.c3p0.impl.NewPooledConnection - [c3p0] A PooledConnection that has already signalled a Connection error is still in use!
2021-05-05 07:25:50,036 CDT WARN [main] com.mchange.v2.c3p0.impl.NewPooledConnection - [c3p0] Another error has occurred [ java.sql.SQLRecoverableException: Closed Connection ] which will not be reported to listeners!
java.sql.SQLRecoverableException: Closed Connection
    ...
2021-05-05 07:25:50,036 CDT WARN [main] com.mchange.v2.c3p0.impl.NewPooledConnection - [c3p0] A PooledConnection that has already signalled a Connection error is still in use!
2021-05-05 07:25:50,037 CDT WARN [main] com.mchange.v2.c3p0.impl.NewPooledConnection - [c3p0] Another error has occurred [ java.sql.SQLRecoverableException: Closed Connection ] which will not be reported to listeners!
java.sql.SQLRecoverableException: Closed Connection
    ... 
2021-05-05 07:25:50,038 CDT ERROR [main] com.tumbleweed.st.server.appframework.xml.XmlExportUtil - error exporting: java.lang.reflect.InvocationTargetException
    ...
Caused by: com.tumbleweed.st.server.api.route.RouteException: Route with id 8a01ba5274bea0d80174c4ab0fca5d47 not found.
    ...
Caused by: org.hibernate.exception.JDBCConnectionException: could not prepare statement
    ...
Caused by: java.sql.SQLRecoverableException: Closed Connection
    ... 

Resolution

The complex design of Routes and Route Templates might cause the system to require a lot of time to prepare the result set. The connection to the database might not survive long enough and might time out before the export is prepared.

The solution is to add the following hibernate parameter in the configuration.xml file for the Database_ToolsComponent only.

hibernate.c3p0.unreturnedConnectionTimeout="1800"

Here is an example how the Database_ToolsComponent will look like after adding this parameter.

<Database_ToolsComponent dataPump="true" databaseName="orcl" databaseType="oracle" host="myhost" jdbcDriver="oracle.jdbc.driver.OracleDriver" jdbcUrl="jdbc:oracle:thin:${user}/${password}@//${host}:${port}/${databaseName}" partitioning="true" password="{AES128}Ha9GQ+Tyx7nGRxJbifEUTA==" port="1521" user="genata">
    <Options hibernate.c3p0.checkoutTimeout="300000" hibernate.c3p0.max_size="8" hibernate.c3p0.min_size="1" hibernate.c3p0.preferredTestQuery="SELECT * FROM ALL_TABLES WHERE 1=0" hibernate.c3p0.timeout="1800" hibernate.c3p0.unreturnedConnectionTimeout="1800" hibernate.cache.use_minimal_puts="false" hibernate.cache.use_query_cache="false" hibernate.cache.use_second_level_cache="false" hibernate.connection.oracle.jdbc.ReadTimeout="300000" hibernate.dialect="org.hibernate.dialect.Oracle10gDialect" hibernate.show_sql="false"/>
</Database_ToolsComponent/>

Do not copy the code above, but use your version of configuration.xml and just add the hibernate parameter.

No service restart is required, because the xml_export script will spawn a new JVM when it is started.