spark dataframe save to SQL table with auto increment column
<p>I have the following table in db</p>

<pre><code>+----------------+------------+------+-----+---------+----------------+
| Field          | Type       | Null | Key | Default | Extra          |
+----------------+------------+------+-----+---------+----------------+
| id             | bigint(20) | NO   | PRI | NULL    | auto_increment |
| VERSION        | bigint(20) | NO   |     | NULL    |                |
| user_id        | bigint(20) | NO   | MUL | NULL    |                |
| measurement_id | bigint(20) | NO   | MUL | NULL    |                |
| day            | timestamp  | NO   |     | NULL    |                |
| hour           | tinyint(4) | NO   |     | NULL    |                |
| hour_timestamp | timestamp  | NO   |     | NULL    |                |
| value          | bigint(20) | NO   |     | NULL    |                |
+----------------+------------+------+-----+---------+----------------+
</code></pre>

<p>I'm trying to save spark dataframe that holds multiple rows that have the following case class structure:</p>

<pre><code>case class Record(val id : Int,
                  val VERSION : Int,
                  val user_id : Int,
                  val measurement_id : Int,
                  val day : Timestamp,
                  val hour : Int,
                  val hour_timestamp : Timestamp,
                  val value : Long  )
</code></pre>

<p>When I'm trying to save the dataframe to my sql through jdbc driver using:</p>

<pre><code>dataFrame.insertIntoJDBC(...)
</code></pre>

<p>I get a primary key violation error:</p>

<pre><code>com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '1' for key 'PRIMARY'
    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:423)
</code></pre>

<p>I tried to set id=0 as the default value of all the rows and also tried to remove the id field from the case class, neither worked.</p>

<p>Can anyone help?</p>

<p>Thanks,
Tomer</p>