spark-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "AnfengYuan (JIRA)" <j...@apache.org>
Subject [jira] [Created] (SPARK-19726) Faild to insert null timestamp value to mysql using spark jdbc
Date Fri, 24 Feb 2017 10:41:44 GMT
AnfengYuan created SPARK-19726:
----------------------------------

             Summary: 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.1.0, 2.0.2, 2.0.1, 2.0.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.3.15#6346)

---------------------------------------------------------------------
To unsubscribe, e-mail: issues-unsubscribe@spark.apache.org
For additional commands, e-mail: issues-help@spark.apache.org


Mime
View raw message