Michael Kunkel created SPARK-21230: -------------------------------------- Summary: Spark Encoder with mysql Enum and data truncated Error Key: SPARK-21230 URL: https://issues.apache.org/jira/browse/SPARK-21230 Project: Spark Issue Type: Bug Components: Java API Affects Versions: 2.1.1 Environment: macosX Reporter: Michael Kunkel
I am using Spark via Java for a MYSQL/ML(machine learning) project. In the mysql database, I have a column "status_change_type" of type enum = {broke, fixed} in a table called "status_change" in a DB called "test". I have an object StatusChangeDB that constructs the needed structure for the table, however for the "status_change_type", I constructed it as a String. I know the bytes from MYSQL enum to Java string are much different, but I am using Spark, so the encoder does not recognize enums properly. However when I try to set the value of the enum via a Java string, I receive the "data truncated" error h5. org.apache.spark.SparkException: Job aborted due to stage failure: Task 0 in stage 4.0 failed 1 times, most recent failure: Lost task 0.0 in stage 4.0 (TID 9, localhost, executor driver): java.sql.BatchUpdateException: Data truncated for column 'status_change_type' at row 1 at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:2055) I have tried to use enum for "status_change_type", however it fails with a stack trace of h5. Exception in thread "AWT-EventQueue-0" java.lang.NullPointerException at org.spark_project.guava.reflect.TypeToken.method(TypeToken.java:465) at org.apache.spark.sql.catalyst.JavaTypeInference$$anonfun$2.apply(JavaTypeInference.scala:126) at org.apache.spark.sql.catalyst.JavaTypeInference$$anonfun$2.apply(JavaTypeInference.scala:125) at scala.collection.TraversableLike$$anonfun$map$1.apply(TraversableLike.scala:234) at scala.collection.TraversableLike$$anonfun$map$1.apply(TraversableLike.scala:234) at scala.collection.IndexedSeqOptimized$class.foreach(IndexedSeqOptimized.scala:33) at scala.collection.mutable.ArrayOps$ofRef.foreach(ArrayOps.scala:186) at scala.collection.TraversableLike$class.map(TraversableLike.scala:234) at scala.collection.mutable.ArrayOps$ofRef.map(ArrayOps.scala:186) at org.apache.spark.sql.catalyst.JavaTypeInference$.org$apache$spark$sql$catalyst$JavaTypeInference$$inferDataType(JavaTypeInference.scala:125) at org.apache.spark.sql.catalyst.JavaTypeInference$$anonfun$2.apply(JavaTypeInference.scala:127) at org.apache.spark.sql.catalyst.JavaTypeInference$$anonfun$2.apply(JavaTypeInference.scala:125) at scala.collection.TraversableLike$$anonfun$map$1.apply(TraversableLike.scala:234) at scala.collection.TraversableLike$$anonfun$map$1.apply(TraversableLike.scala:234) at scala.collection.IndexedSeqOptimized$class.foreach(IndexedSeqOptimized.scala:33) at scala.collection.mutable.ArrayOps$ofRef.foreach(ArrayOps.scala:186) at scala.collection.TraversableLike$class.map(TraversableLike.scala:234) at scala.collection.mutable.ArrayOps$ofRef.map(ArrayOps.scala:186) at org.apache.spark.sql.catalyst.JavaTypeInference$.org$apache$spark$sql$catalyst$JavaTypeInference$$inferDataType(JavaTypeInference.scala:125) at org.apache.spark.sql.catalyst.JavaTypeInference$$anonfun$2.apply(JavaTypeInference.scala:127) at org.apache.spark.sql.catalyst.JavaTypeInference$$anonfun$2.apply(JavaTypeInference.scala:125) at scala.collection.TraversableLike$$anonfun$map$1.apply(TraversableLike.scala:234) at scala.collection.TraversableLike$$anonfun$map$1.apply(TraversableLike.scala:234) at scala.collection.IndexedSeqOptimized$class.foreach(IndexedSeqOptimized.scala:33) at scala.collection.mutable.ArrayOps$ofRef.foreach(ArrayOps.scala:186) at scala.collection.TraversableLike$class.map(TraversableLike.scala:234) at scala.collection.mutable.ArrayOps$ofRef.map(ArrayOps.scala:186) ... ... h5. I have tried to use the jdbc setting "jdbcCompliantTruncation=false" but this does nothing as I get the same error of "data truncated" as first stated. Here are my jdbc options map, in case I am using the "jdbcCompliantTruncation=false" incorrectly. public static Map<String, String> jdbcOptions() { Map<String, String> jdbcOptions = new HashMap<String, String>(); jdbcOptions.put("url", "jdbc:mysql://localhost:3306/test?jdbcCompliantTruncation=false"); jdbcOptions.put("driver", "com.mysql.jdbc.Driver"); jdbcOptions.put("dbtable", "status_change"); jdbcOptions.put("user", "root"); jdbcOptions.put("password", ""); return jdbcOptions; } Here is the Spark method for inserting into the mysql DB private void insertMYSQLQuery(Dataset<Row> changeDF) { try { changeDF.write().mode(SaveMode.Append).jdbc(SparkManager.jdbcAppendOptions(), "status_change", new java.util.Properties()); } catch (Exception e) { System.out.println(e); } } where jdbcAppendOptions uses the jdbcOptions methods as: public static String jdbcAppendOptions() { return SparkManager.jdbcOptions().get("url") + "&user=" + SparkManager.jdbcOptions().get("user") + "&password=" + SparkManager.jdbcOptions().get("password"); } How do I achieve getting the values of type enum into the mysqlDB using spark, or avoiding this "data truncated" error? My only other thought would be to change the DB itself to use VARCHAR, but the project leader is not to happy with the idea. -- This message was sent by Atlassian JIRA (v6.4.14#64029) --------------------------------------------------------------------- To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org For additional commands, e-mail: issues-h...@spark.apache.org