ODK Aggregate - unable to export - (KML file Failure - will retry later)

vans28 · October 23, 2017, 3:56am

Hello again ODK peeps,

Im just wondering how exporting kml file works? when u have a bunch of data. (90mb)
in exporting CSV file nothing to worry because works very well..

I tried some workarounds like the ffg:

set global max_allowed_packet = 1073741824; (but it didnt change)
i already set JVM - in tomcat free memory 1g but max memory to 2gb

I saw tomcat log file and here's what it says.

Oct 23, 2017 11:25:02 AM org.opendatakit.aggregate.task.KmlWorkerImpl failureRecovery
SEVERE: Exception caught: org.opendatakit.common.persistence.exception.ODKEntityPersistException: org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [INSERT INTO `odk_prod_new`.`_persistent_result_file_blb` ( `_URI`, `_CREATOR_URI_USER`, `_CREATION_DATE`, `_LAST_UPDATE_URI_USER`, `_LAST_UPDATE_DATE`, `_TOP_LEVEL_AURI`, `VALUE` )  VALUES  ( ?, ?, ?, ?, ?, ?, ? ) ]; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: The size of BLOB/TEXT data inserted in one transaction is greater than 10% of redo log size. Increase the redo log size using innodb_log_file_size. for  survey_form
org.opendatakit.common.persistence.exception.ODKEntityPersistException: org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [INSERT INTO `odk_prod_new`.`_persistent_result_file_blb` ( `_URI`, `_CREATOR_URI_USER`, `_CREATION_DATE`, `_LAST_UPDATE_URI_USER`, `_LAST_UPDATE_DATE`, `_TOP_LEVEL_AURI`, `VALUE` )  VALUES  ( ?, ?, ?, ?, ?, ?, ? ) ]; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: The size of BLOB/TEXT data inserted in one transaction is greater than 10% of redo log size. Increase the redo log size using innodb_log_file_size.
	at org.opendatakit.common.persistence.engine.mysql.DatastoreImpl.putEntity(DatastoreImpl.java:1033)
	at org.opendatakit.common.datamodel.BinaryContentManipulator$BlobManipulator.<init>(BinaryContentManipulator.java:120)
	at org.opendatakit.common.datamodel.BinaryContentManipulator.setValueFromByteArray(BinaryContentManipulator.java:521)
	at org.opendatakit.aggregate.form.PersistentResults.setResultFile(PersistentResults.java:269)
	at org.opendatakit.aggregate.task.KmlWorkerImpl.generateKml(KmlWorkerImpl.java:128)
	at org.opendatakit.aggregate.task.tomcat.KmlGeneratorImpl$KmlRunner.run(KmlGeneratorImpl.java:55)
	at org.springframework.scheduling.support.DelegatingErrorHandlingRunnable.run(DelegatingErrorHandlingRunnable.java:54)
	at java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)
	at java.util.concurrent.FutureTask.run(Unknown Source)
	at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$201(Unknown Source)
	at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(Unknown Source)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
	at java.lang.Thread.run(Unknown Source)
Caused by: org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [INSERT INTO `odk_prod_new`.`_persistent_result_file_blb` ( `_URI`, `_CREATOR_URI_USER`, `_CREATION_DATE`, `_LAST_UPDATE_URI_USER`, `_LAST_UPDATE_DATE`, `_TOP_LEVEL_AURI`, `VALUE` )  VALUES  ( ?, ?, ?, ?, ?, ?, ? ) ]; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: The size of BLOB/TEXT data inserted in one transaction is greater than 10% of redo log size. Increase the redo log size using innodb_log_file_size.
	at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:91)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:649)
	at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:870)
	at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:931)
	at org.opendatakit.common.persistence.engine.mysql.DatastoreImpl.putEntity(DatastoreImpl.java:1029)
	... 13 more
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: The size of BLOB/TEXT data inserted in one transaction is greater than 10% of redo log size. Increase the redo log size using innodb_log_file_size.
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
	at java.lang.reflect.Constructor.newInstance(Unknown Source)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
	at com.mysql.jdbc.Util.getInstance(Util.java:408)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:943)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3973)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3909)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2527)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2501)
	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1858)
	at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2079)
	at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2013)
	at com.mysql.jdbc.PreparedStatement.executeLargeUpdate(PreparedStatement.java:5104)
	at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1998)
	at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:98)
	at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:98)
	at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:877)
	at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:870)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:633)
	... 16 more
Oct 23, 2017 11:25:02 AM org.opendatakit.aggregate.task.KmlWorkerImpl failureRecovery
INFO: Exception recovery during KML generation - mark as failed for survey_form
Oct 23, 2017 11:25:02 AM org.opendatakit.common.persistence.engine.mysql.DatastoreImpl deleteEntity
INFO: Executing DELETE FROM `odk_prod_new`.`_persistent_result_file_bin` WHERE `_URI` = ? with key uuid:684671c6-a6b1-4aad-a8a4-1d9d2ad265b3 by user aggregate.opendatakit.org:web-service

and why is it bad sql grammar where all the fields came from the survey_form? im just exporting the kml file.

bad SQL grammar [INSERT INTO `odk_prod_new`.`_persistent_result_file_blb` ( `_URI`, `_CREATOR_URI_USER`, `_CREATION_DATE`, `_LAST_UPDATE_URI_USER`, `_LAST_UPDATE_DATE`, `_TOP_LEVEL_AURI`, `VALUE` )  VALUES  ( ?, ?, ?, ?, ?, ?, ? ) ];

i hope you can help me. (all of my queries)

thank you everyone.

vans28 · October 30, 2017, 1:14am

hello can anyone help me? thanks

yanokwa · November 2, 2017, 11:01pm

Hi @vans28. What version of ODK Aggregate are you using? And what version of MySQL?

The logs you posted say the following:

The size of BLOB/TEXT data inserted in one transaction is greater than 10% of redo log size. Increase the redo log size using innodb_log_file_size.

Have you tried increasing innodb_log_file_size? If so what was the result?

Finally, when you get a chance, please introduce yourself here. I'd also encourage you to add a real picture as your avatar because it helps build community!

vans28 · November 6, 2017, 3:11am

finally, thanks for the response yaw!

I'm using ODK Aggregate v1.4.14 and mysql-5.6.36.0

i set innodb_log_file_size.to 128m but it failed to export. i saw my task manager, when exported kml files, tom-cat RAM reaches to 2gb. Is there any other way to reduce eating the RAM or without sacrificing the allocated RAM?

and yes, I already introduce myself. thanks for reminding me yaw!

yanokwa · November 6, 2017, 4:11am

Evan, let's first see if we can get export working first by allocating Tomcat as much RAM as you have. Have you tried that?

yanokwa · November 6, 2017, 4:11am

@ggalmazor You were just poking around in the KML code. Did you see anything that would cause this kind of problem?

ggalmazor · November 6, 2017, 8:05am

Hi Evan and Yaw! I can't say I did find something that would justify this error but my knowledge of the code is limited yet.

@vans28, after increasing the innodb_log_file_size, is the error trace the same as the one in your original post or is it something different? If it's different I'd need that to pinpoint the place in the code that is having trouble. I could look into this later in the day.

Meanwhile, I'm filing a new issue to investigate this. It's really interesting that exporting CSV works with big data sets and KMLs don't.

vans28 · November 7, 2017, 12:35am

YAW!! I saw my error why I didn't export KML file, I just found out that my max_allowed_packet didn't change after I set to 1gb, I tried to stop mysql server and set global max_allowed_packet, and then start it again. and whoaalaa!! I just exported KML FILE! woooooohhhoooooooo!!

but my second concern now is tom-cat RAM sacrificing too much.. when i tried to max out the RAM of my workstation, it starting to lag. I'm trying to explore tom-cat settings to configure why is that happening or is it possible to reduce eating allocated RAM.

thank a bunch for your help yaw.