[
https://issues.apache.org/jira/browse/SPARK-19726?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16060739#comment-16060739
]
Chang chen edited comment on SPARK-19726 at 6/24/17 5:17 AM:
-------------------------------------------------------------
@srowen, As we investigated, Spark translate null to 0 in case of timestamp
which is wrong, since 0 is interpreted as "1970-01-01 00:00:00"
{code:title=UnsafeRow.scala|borderStyle=solid}
public void setNullAt(int i) {
assertIndexIsValid(i);
BitSetMethods.set(baseObject, baseOffset, i);
// To preserve row equality, zero out the value when setting the column to
null.
// Since this row does does not currently support updates to
variable-length values, we don't
// have to worry about zeroing out that data.
Platform.putLong(baseObject, getFieldOffset(i), 0);
}
{code}
Yes, user insert null into a non-null column, but
# Spark should pass *null* to underlying DB engine instead of 0. then let DB
report error, _or_
# Spark report error by itself
was (Author: baibaichen):
@srowen, As we investigated, Spark translate null to 0 in case of timestamp
which is wrong, since 0 is interpreted as "1970-01-01 00:00:00"
{code:title=UnsafeRow.scala|borderStyle=solid}
public void setNullAt(int i) {
assertIndexIsValid(i);
BitSetMethods.set(baseObject, baseOffset, i);
// To preserve row equality, zero out the value when setting the column to
null.
// Since this row does does not currently support updates to
variable-length values, we don't
// have to worry about zeroing out that data.
Platform.putLong(baseObject, getFieldOffset(i), 0);
}
{code}
Yes, user insert null into a non-null column, but
# Spark should pass *null* to underlying DB engine instead of 0. then let DB
report error Or
# Spark report error by itself
> Faild to insert null timestamp value to mysql using spark jdbc
> --------------------------------------------------------------
>
> Key: SPARK-19726
> URL: https://issues.apache.org/jira/browse/SPARK-19726
> Project: Spark
> Issue Type: Bug
> Components: SQL
> Affects Versions: 2.0.0, 2.0.1, 2.0.2, 2.1.0
> Reporter: AnfengYuan
>
> 1. create a table in mysql
> {code:borderStyle=solid}
> CREATE TABLE `timestamp_test` (
> `id` bigint(23) DEFAULT NULL,
> `time_stamp` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE
> CURRENT_TIMESTAMP
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8
> {code}
> 2. insert one row using spark
> {code:borderStyle=solid}
> CREATE OR REPLACE TEMPORARY VIEW jdbcTable
> USING org.apache.spark.sql.jdbc
> OPTIONS (
> url
> 'jdbc:mysql://xxx.xxx.xxx.xxx:3306/default?characterEncoding=utf8&useServerPrepStmts=false&rewriteBatchedStatements=true',
> dbtable 'timestamp_test',
> driver 'com.mysql.jdbc.Driver',
> user 'root',
> password 'root'
> );
> insert into jdbcTable values (1, null);
> {code}
> the insert statement failed with exceptions:
> {code:borderStyle=solid}
> Error: org.apache.spark.SparkException: Job aborted due to stage failure:
> Task 599 in stage 1.0 failed 4 times, most recent failure: Lost task 599.3 in
> stage 1.0 (TID 1202, A03-R07-I12-135.JD.LOCAL):
> java.sql.BatchUpdateException: Data truncation: Incorrect datetime value:
> '1970-01-01 08:00:00' for column 'time_stamp' at row 1
> at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
> at
> sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
> at
> sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
> at java.lang.reflect.Constructor.newInstance(Constructor.java:422)
> at com.mysql.jdbc.Util.handleNewInstance(Util.java:404)
> at com.mysql.jdbc.Util.getInstance(Util.java:387)
> at
> com.mysql.jdbc.SQLError.createBatchUpdateException(SQLError.java:1154)
> at
> com.mysql.jdbc.PreparedStatement.executeBatchedInserts(PreparedStatement.java:1582)
> at
> com.mysql.jdbc.PreparedStatement.executeBatchInternal(PreparedStatement.java:1248)
> at com.mysql.jdbc.StatementImpl.executeBatch(StatementImpl.java:959)
> at
> org.apache.spark.sql.execution.datasources.jdbc.JdbcUtils$.savePartition(JdbcUtils.scala:227)
> at
> org.apache.spark.sql.execution.datasources.jdbc.JdbcUtils$$anonfun$saveTable$1.apply(JdbcUtils.scala:300)
> at
> org.apache.spark.sql.execution.datasources.jdbc.JdbcUtils$$anonfun$saveTable$1.apply(JdbcUtils.scala:299)
> at
> org.apache.spark.rdd.RDD$$anonfun$foreachPartition$1$$anonfun$apply$28.apply(RDD.scala:902)
> at
> org.apache.spark.rdd.RDD$$anonfun$foreachPartition$1$$anonfun$apply$28.apply(RDD.scala:902)
> at
> org.apache.spark.SparkContext$$anonfun$runJob$5.apply(SparkContext.scala:1899)
> at
> org.apache.spark.SparkContext$$anonfun$runJob$5.apply(SparkContext.scala:1899)
> at org.apache.spark.scheduler.ResultTask.runTask(ResultTask.scala:70)
> at org.apache.spark.scheduler.Task.run(Task.scala:86)
> at org.apache.spark.executor.Executor$TaskRunner.run(Executor.scala:274)
> at
> java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
> at
> java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
> at java.lang.Thread.run(Thread.java:745)
> Caused by: com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect
> datetime value: '1970-01-01 08:00:00' for column 'time_stamp' at row 1
> at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3876)
> at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3814)
> at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2478)
> at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2625)
> at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2551)
> at
> com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1861)
> at
> com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2073)
> at
> com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2009)
> at
> com.mysql.jdbc.PreparedStatement.executeLargeUpdate(PreparedStatement.java:5094)
> at
> com.mysql.jdbc.PreparedStatement.executeBatchedInserts(PreparedStatement.java:1543)
> ... 15 more
> {code}
--
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
