To work around this issue, Strategy recommends reducing the connection caching lifetime or disabling connection caching entirely for Presto connections in Strategy. If users have not experienced this error in their environment, there is no need to disable connection caching.
Important Note: Strategy uses the Presto JDBC Driver for connections to Presto
When a Strategy Database connection is first created, it has default values for the amount of time that a connection should be left open and available for subsequent reports (the connection caching lifetime). The option that controls this behavior is found in the "Advanced" tab of the Database Connection; the "Connection lifetime (sec)" with a default value of 3600 seconds.
A Presto report using DDL/DML statements fails with an error "Inserting into an unpartitioned table that were added, altered, or inserted into in the same transaction is not supported" and the connection is freed up and made available for use with other reports. This error does not happen for all DDL but seems to happen when tables are created implicitly (CTAS) and those DDL are followed by the Analytical Engine DDL used to emulate a SQL function that is unsupported on the Presto side.
Any subsequent reports that use that same Strategy Database connection will fail with the error: "Error type: JDBC error. JDBC operation attempted: MIX_JdbcConnection_setAutoCommit. [:0: on SQLHANDLE] Connection is in auto-commit mode."
Error: SQL Generation Complete
QueryEngine encountered error: JdbcResultWrapper::SetAutoCommit failed.
Error type: Jdbc error. Jdbc operation attempted: MIX_JdbcConnection_setAutoCommit. [:0: on SQLHANDLE] Connection is in auto-commit mode.
Error in Process method of Component: QueryEngineServer, Project Presto_Modeled_Access, Job 109, Error Code= -2147212544.
An existing Presto connection should be re-usable for subsequent report execution even after a failure.
Set the Connection lifetime to 1 second in the DB instance for the project
Restart the I-server and repeat the above steps.
KB441436
