RDB Loader 0.18.1: “[Amazon][JDBC](10040) Cannot use commit while Connection is in auto-commit mode.”

For engineers Storage targets
1

Hi all,

My last EMR cluster failed at the [rdb_load] Load Redshift Storage Target step, with the following logs:

06:14:23.646: Consistency check passed after 1 attempt. Following run ids found:
+ run=2021-04-29-06-00-20 with 46 atomic files (54 Mb) and with following shredded types:
  * iglu:nl.basjes/yauaa_context/jsonschema/1-*-* (s3://bucket/4-storage/redshift-storage/jsonpaths/nl.basjes/yauaa_context_1.json)
  * iglu:com.snowplowanalytics.snowplow/web_page/jsonschema/1-*-* (s3://bucket/4-storage/redshift-storage/jsonpaths/com.snowplowanalytics.snowplow/web_page_1.json)
  * iglu:com.snowplowanalytics.snowplow/link_click/jsonschema/1-*-* (s3://bucket/4-storage/redshift-storage/jsonpaths/com.snowplowanalytics.snowplow/link_click_1.json)
  * iglu:com.snowplowanalytics.snowplow/duplicate/jsonschema/1-*-* (s3://bucket/4-storage/redshift-storage/jsonpaths/com.snowplowanalytics.snowplow/duplicate_1.json)
06:14:24.017: Loading s3://bucket/shredded/good/run=2021-04-29-06-00-20/
RDB Loader unknown error in executeUpdate
java.sql.SQLException: [Amazon](500051) ERROR processing query/statement. Error: Query execution failed , Query: COMMIT.
	at com.amazon.redshift.dataengine.PGQueryExecutor.execute(Unknown Source)
	at com.amazon.jdbc.common.SStatement.executeNoParams(Unknown Source)
	at com.amazon.jdbc.common.SStatement.executeAnyUpdate(Unknown Source)
	at com.amazon.jdbc.common.SStatement.executeUpdate(Unknown Source)
	at com.snowplowanalytics.snowplow.rdbloader.dsl.JDBC$$anon$1.$anonfun$executeUpdate$1(JDBC.scala:134)
	at scala.runtime.java8.JFunction0$mcJ$sp.apply(JFunction0$mcJ$sp.java:23)
	at cats.effect.internals.IORunLoop$.cats$effect$internals$IORunLoop$$loop(IORunLoop.scala:87)
	at cats.effect.internals.IORunLoop$.startCancelable(IORunLoop.scala:41)
	at cats.effect.internals.IOBracket$BracketStart.run(IOBracket.scala:90)
	at cats.effect.internals.Trampoline.cats$effect$internals$Trampoline$$immediateLoop(Trampoline.scala:67)
	at cats.effect.internals.Trampoline.startLoop(Trampoline.scala:35)
	at cats.effect.internals.TrampolineEC$JVMTrampoline.super$startLoop(TrampolineEC.scala:90)
	at cats.effect.internals.TrampolineEC$JVMTrampoline.$anonfun$startLoop$1(TrampolineEC.scala:90)
	at scala.runtime.java8.JFunction0$mcV$sp.apply(JFunction0$mcV$sp.java:23)
	at scala.concurrent.BlockContext$.withBlockContext(BlockContext.scala:85)
	at cats.effect.internals.TrampolineEC$JVMTrampoline.startLoop(TrampolineEC.scala:90)
	at cats.effect.internals.Trampoline.execute(Trampoline.scala:43)
	at cats.effect.internals.TrampolineEC.execute(TrampolineEC.scala:42)
	at cats.effect.internals.IOBracket$BracketStart.apply(IOBracket.scala:70)
	at cats.effect.internals.IOBracket$BracketStart.apply(IOBracket.scala:50)
	at cats.effect.internals.IORunLoop$.cats$effect$internals$IORunLoop$$loop(IORunLoop.scala:141)
	at cats.effect.internals.IORunLoop$.start(IORunLoop.scala:34)
	at cats.effect.internals.IOBracket$.$anonfun$apply$1(IOBracket.scala:43)
	at cats.effect.internals.IOBracket$.$anonfun$apply$1$adapted(IOBracket.scala:33)
	at cats.effect.internals.IORunLoop$RestartCallback.start(IORunLoop.scala:352)
	at cats.effect.internals.IORunLoop$.cats$effect$internals$IORunLoop$$loop(IORunLoop.scala:124)
	at cats.effect.internals.IORunLoop$RestartCallback.signal(IORunLoop.scala:366)
	at cats.effect.internals.IORunLoop$RestartCallback.apply(IORunLoop.scala:387)
	at cats.effect.internals.IORunLoop$RestartCallback.apply(IORunLoop.scala:330)
	at cats.effect.internals.IOShift$Tick.run(IOShift.scala:36)
	at cats.effect.internals.PoolUtils$$anon$2$$anon$3.run(PoolUtils.scala:52)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
Caused by: com.amazon.support.exceptions.GeneralException: [Amazon](500051) ERROR processing query/statement. Error: Query execution failed , Query: COMMIT.
	... 33 more
Caused by: com.amazon.support.exceptions.GeneralException: [Amazon][JDBC](10040) Cannot use commit while Connection is in auto-commit mode.
	at com.amazon.jdbc.common.SConnection.notifyCommit(Unknown Source)
	at com.amazon.jdbc.common.STransactionStateListener.notifyCommit(Unknown Source)
	at com.amazon.redshift.core.PGJDBCStatement.notifyTransaction(Unknown Source)
	at com.amazon.redshift.dataengine.PGQueryExecutor.execute(Unknown Source)
	at com.amazon.jdbc.common.SStatement.executeNoParams(Unknown Source)
	at com.amazon.jdbc.common.SStatement.executeAnyUpdate(Unknown Source)
	at com.amazon.jdbc.common.SStatement.executeUpdate(Unknown Source)
	at com.snowplowanalytics.snowplow.rdbloader.dsl.JDBC$$anon$1.$anonfun$executeUpdate$1(JDBC.scala:134)
	at scala.runtime.java8.JFunction0$mcJ$sp.apply(JFunction0$mcJ$sp.java:23)
	at cats.effect.internals.IORunLoop$.cats$effect$internals$IORunLoop$$loop(IORunLoop.scala:87)
	at cats.effect.internals.IORunLoop$.startCancelable(IORunLoop.scala:41)
	at cats.effect.internals.IOBracket$BracketStart.run(IOBracket.scala:90)
	at cats.effect.internals.Trampoline.cats$effect$internals$Trampoline$$immediateLoop(Trampoline.scala:67)
	at cats.effect.internals.Trampoline.startLoop(Trampoline.scala:35)
	at cats.effect.internals.TrampolineEC$JVMTrampoline.super$startLoop(TrampolineEC.scala:90)
	at cats.effect.internals.TrampolineEC$JVMTrampoline.$anonfun$startLoop$1(TrampolineEC.scala:90)
	at scala.runtime.java8.JFunction0$mcV$sp.apply(JFunction0$mcV$sp.java:23)
	at scala.concurrent.BlockContext$.withBlockContext(BlockContext.scala:85)
	at cats.effect.internals.TrampolineEC$JVMTrampoline.startLoop(TrampolineEC.scala:90)
	at cats.effect.internals.Trampoline.execute(Trampoline.scala:43)
	at cats.effect.internals.TrampolineEC.execute(TrampolineEC.scala:42)
	at cats.effect.internals.IOBracket$BracketStart.apply(IOBracket.scala:70)
	at cats.effect.internals.IOBracket$BracketStart.apply(IOBracket.scala:50)
	at cats.effect.internals.IORunLoop$.cats$effect$internals$IORunLoop$$loop(IORunLoop.scala:141)
	at cats.effect.internals.IORunLoop$.start(IORunLoop.scala:34)
	at cats.effect.internals.IOBracket$.$anonfun$apply$1(IOBracket.scala:43)
	at cats.effect.internals.IOBracket$.$anonfun$apply$1$adapted(IOBracket.scala:33)
	at cats.effect.internals.IORunLoop$RestartCallback.start(IORunLoop.scala:352)
	at cats.effect.internals.IORunLoop$.cats$effect$internals$IORunLoop$$loop(IORunLoop.scala:124)
	at cats.effect.internals.IORunLoop$RestartCallback.signal(IORunLoop.scala:366)
	at cats.effect.internals.IORunLoop$RestartCallback.apply(IORunLoop.scala:387)
	at cats.effect.internals.IORunLoop$RestartCallback.apply(IORunLoop.scala:330)
	at cats.effect.internals.IOShift$Tick.run(IOShift.scala:36)
	at cats.effect.internals.PoolUtils$$anon$2$$anon$3.run(PoolUtils.scala:52)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
	at java.lang.Thread.run(Thread.java:748)
Caused by: java.sql.SQLException: [Amazon][JDBC](10040) Cannot use commit while Connection is in auto-commit mode.
	at com.amazon.exceptions.ExceptionConverter.toSQLException(Unknown Source)
	... 37 more
06:14:43.363: Data loading error [Amazon](500051) ERROR processing query/statement. Error: Query execution failed , Query: COMMIT.
06:14:43.631: INFO: Logs successfully dumped to S3 [s3://bucket/logs/rdb-loader/2021-04-29-06-00-20/78460a48-051f-4552-bb77-6c9ea2b70cf3]

Not sure what does that mean, didn’t find much on google.

Any help will be appreciated. :slight_smile:

2

@guillaume, could you check if the data has actually loaded already despite the error. Look for the last max timestamp of the loaded data and see if it is a part of this batch processing - match it with when your batch job started.

If data is loaded, archive it from your “output” bucket to “archive” bucket. If not, resume the job from rdb_load step. The recovery steps are described here.

In any case, seems like a networking issue (glitch) or Redshift was unavailable for a bit.

1 Like
3

I resumed the job from the rdb_load step and it finished without issue.

Thanks for the quick answer!

4

@guillaume, I still suspect you might have loaded the data twice. If it’s important not to have duplicates you might want to recheck that unless you did that already.

1 Like